Shulou(Shulou.com)06/01 Report--

The test database version here is 12.2.0.1, and in the view merge, the optimizer represents the query block of the view into the query block that contains the view. View merging improves performance by letting the optimizer consider additional join order, access methods, and other transformations. For example, after a view is merged and there are multiple tables in a query block, a table built into the view allows the optimizer to delete a table outside the view using join elimination.

Performing view merging for specific simple views always results in a better execution plan, and the optimizer automatically merges views without considering the cost. Other optimizers use cost to make decisions. The optimizer may choose not to perform view merging for a number of reasons, including cost or effective restrictions.

If optimizer_secure_view_merging is set to true (the default), the Oracle database performs checks to ensure that view merging and predicate push do not violate the security intent of the view creator. To disable these additional security checks for a particular view, you can grant merge view permissions to the user who created the view. To disable additional security checks for all views of a specific user, the user can be granted merge any view permissions.

Query blocks for view merging

The optimizer represents each nested subquery or unmerged view through separate query blocks. The database optimizes each individual query block from the bottom up. Therefore, the database first optimizes the innermost query block, generates part of the execution plan, and then generates the execution plan for the external query block. The parser expands each view in the query into a separate query block. The nature of the query block represents the view definition and the result depending on the country. One option of the optimizer is to analyze the view query block separately, generate a view child execution plan, and then generate the execution plan of the entire query by using the view subplan to process the rest of the query. However, this technique may result in a sub-optimal execution plan because the view is optimized separately. Some view merging may improve performance.

Simple view merge

In a simple view merge, the optimizer merges select-project-join views. For example, a query that queries the employees table contains a subquery that joins the departments and locations tables.

Because additional join orders and access paths are available after view merging, simple view merging usually results in a more optimized execution plan. Merging does not take effect for simple views because:

. The view contains structures that are not allowed in the select-project-join view, such as:

-group by

-distinct

-Outer join

-MODEL

-connect by

-Set operators

-Aggregation

-

. The view appears to the right of the semijoin or antijoin

. Include subqueries in the select list

. External query block contains PL/SQL function

. The view participates in an external join and does not meet any of several conditions for the view to be merged

The following query connects the hr.employees table with the dept_locs_v view, and returns the street address of each department. The dept_locs_v view connects the departments with the locations table.

SELECT e.first_name, e.last_name, dept_locs_v.street_address, dept_locs_v.postal_code FROM employees e, (SELECT d.department_id, d.department_name, l.street_address, l.postal_code FROM departments d Locations l WHERE d.location_id = l.location_id) dept_locs_v WHERE dept_locs_v.department_id = e.department_id AND e.last_name = 'Smith'

The database executes the above query to generate row records for the view by joining the departments and the locations table, and then joins the employees table with this result. Because the query contains the view dept_locs_v, and the view contains two tables, the optimizer must use one of the following join order:

Dept_locs_v (departments,locations)

Dept_locs_v (locations,departments)

.dept _ locs_v (departments,locations), employees

.dept _ locs_v (locations,departments), employees

The connection method is also constrained. Index-based nested loops for join order starting with employees tables are not appropriate because there is no index for columns in the view. Without using view merging, the optimizer generates the following execution plan:

-| Id | Operation | Name | Cost (% CPU) |-- -- | 0 | SELECT STATEMENT | | 7 (15) | | * 1 | HASH JOIN | | 7 (15) | | 2 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 2 (0) | | * 3 | INDEX RANGE SCAN | EMP_ NAME_IX | 1 (0) | | 4 | VIEW | | 5 (20) | | * 5 | HASH JOIN | | 5 (20) | | 6 | TABLE ACCESS FULL | LOCATIONS | 2 (0) | | 7 | TABLE ACCESS FULL | DEPARTMENTS | 2 (0) |- -Predicate Information (identified by operation id):-1-access ( "DEPT_LOCS_V". "DEPARTMENT_ID" = "E". "DEPARTMENT_ID") 3-access ("E". "LAST_NAME" = 'Smith') 5-access ("D". "LOCATION_ID" = "L". "LOCATION_ID")

View merge merges the tables in the view into the external query block and deletes the internal query block. After the view is merged, the query statement is as follows:

SELECT e.first_name, e.last_name, l.street_address, l.postal_codeFROM employees e, departments d, locations lWHERE d.location_id = l.location_idAND d.department_id = e.department_idAND e.last_name = 'Smith'

Because all three tables appear in a query block, the optimizer can choose one of the following six join orders:

.employees, departments, locations

.employees, locations, departments

.departments, employees, locations

.departments, locations, employees

.locations, employees, departments

.locations, departments, employees

Joining employees and departments shows that indexes can be used, and after views are merged, the optimizer chooses a more efficient execution plan, using nested loops to join:

-| Id | Operation | Name | Cost (% CPU) |-- -| 0 | SELECT STATEMENT | | 4 (0) | | 1 | NESTED LOOPS | | 2 | NESTED LOOPS | | 4 ( 0) | | 3 | NESTED LOOPS | | 3 (0) | 4 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 2 (0) | | * 5 | INDEX RANGE SCAN | EMP_NAME_IX | 1 (0) | | 6 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 1 (0) | | * 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 0 | (0) | | * 8 | INDEX UNIQUE SCAN | LOC_ID_PK | 0 (0) | | 9 | TABLE ACCESS BY INDEX ROWID | LOCATIONS | 1 (0) |-Predicate Information (identified by operation id):-5-access ("E". "LAST_NAME" = 'Smith') 7-access ("E". "DEPARTMENT_ID" = "D". "DEPARTMENT_ID") 8-access ("D". "LOCATION_ID" = "L". "LOCATION_ID")

Complex view merging

In view merging, the optimizer merges views that contain group by and distinct operations, and like simple view merges, complex view merging allows the optimizer to consider additional join order and access paths.

The optimizer may delay the evaluation of group by or distinct operations until after the optimizer has evaluated the connection. Delaying these operations can improve or damage performance depending on the characteristics of the data. If the connection uses filtering, delaying these operations after the connection can reduce the dataset that these operations will process. Do

Early evaluation can reduce the amount of data to be processed by subsequent connections, or connections may increase the amount of data to be processed by these operations. The optimizer uses cost to evaluate the view merge and only performs it at a lower cost after the merge operation.

In addition to costs, costs may not perform complex view merge operations for the following reasons:

. External query tables have no rowid or unique constraint columns

. The view appears in the connect by query block

. The view contains a grouping sets,rollup or pivot clause

. A view or external query block contains a model clause

Complex view joins that contain group by clauses the following query uses the group by clause

CREATE VIEW cust_prod_totals_v ASSELECT SUM (s.quantity_sold) total, s.cust_id, s.prod_idFROM sales sGROUP BY s.cust_id, s.prod_id

The following query will find all customers who have bought at least 100 sweaters from United States:

SELECT c.cust_id, c.cust_first_name, c.cust_last_name, c.cust_emailFROM customers c, products p, cust_prod_totals_vWHERE c.country_id = 52790AND c.cust_id = cust_prod_totals_v.cust_idAND cust_prod_totals_v.total > 100AND cust_prod_totals_v.prod_id = p.prod_idAND p.prod_name ='T3 Faux Fur-Trimmed Sweater'

Cust_prod_totals_v views meet the conditions for complex view merging. After the merge, the query statement is as follows:

SELECT c.cust_id, cust_first_name, cust_last_name, cust_emailFROM customers c, products p, sales sWHERE c.country_id = 52790AND c.cust_id = s.cust_idAND s.prod_id = p.prod_idAND p.prod_name ='T3 Faux Fur-Trimmed Sweater'GROUP BY s.cust_id, s.prod_id, p.rowid, c.rowid, c.cust_email, c. C.cust_idHAVING SUM (s.quantity_sold) > 100

The cost of the converted query is lower than that of the unconverted query, so the optimizer chooses the merged view. In statements without transformations, the group by operation operates on the entire sales table in the view. In the transformed query, joining the products and customers tables filters out most of the data in the sales table, so the cost of the group by operation is low. The join cost is higher because the sales table is not reduced, but its cost is not much higher because the group by operation does not reduce too many rows in the original query. If the previous characteristics have changed, the cost of merging the views will not be reduced. The final execution plan does not contain views, as follows:

-| Id | Operation | Name | Cost (% CPU) |- -| 0 | SELECT STATEMENT | | 2101 (18) | | * 1 | FILTER | | 2 | HASH GROUP BY | | 2101 (18) | | * 3 | HASH JOIN | | 2099 (18) | | * 4 | HASH JOIN | | 1801 (19) | ) | | * 5 | TABLE ACCESS FULL | PRODUCTS | 96 (5) | | 6 | TABLE ACCESS FULL | SALES | 1620 (15) | | * 7 | TABLE ACCESS FULL | CUSTOMERS | 296 (11) |-- Predicate Information (identified by operation id):-- -1-filter (SUM ("QUANTITY_SOLD") > 100) 3-access ("C". "CUST_ID" = "CUST_ID") 4-access ("PROD_ID" = "P". "PROD_ID") 5-filter ("P". "PROD_NAME" ='T3 Faux Fur -Trimmed Sweater') 7-filter ("C". "COUNTRY_ID" = 'US')

Complex view connections using distinct

The following query uses the distinct operation on the cust_prod_v view:

SELECT c.cust_id, c.cust_first_name, c.cust_last_name, c.cust_emailFROM customers c, products p, (SELECT DISTINCT s.cust_id, s.prod_idFROM sales s) cust_prod_vWHERE c.country_id = 52790AND c.cust_id = cust_prod_v.cust_idAND cust_prod_v.prod_id = p.prod_idAND p.prod_name ='T3 Faux Fur-Trimmed Sweater'

The execution plan generated after the decision view merge is cheaper, and the optimizer uses the following equivalent query to rewrite the original query:

SELECT nwvw.cust_id, nwvw.cust_first_name, nwvw.cust_last_name, nwvw.cust_emailFROM (SELECT DISTINCT (c.rowid), p.rowid, s.prod_id, s.custworthy name, c.cust_last_name, c.cust_emailFROM customers c, products p, sales sWHERE c.country_id = 52790AND c.cust_id = s.cust_idAND s.prod_id = p.prod_idAND p.prod_name ='T3 Faux Fur-Trimmed Sweater') nwvw

The execution plan of the above query is as follows:

-| Id | Operation | Name |-| 0 | SELECT STATEMENT | 1 | VIEW | | VM_NWVW_1 | | 2 | HASH UNIQUE | | * 3 | HASH JOIN | * 4 | HASH JOIN | * 5 | TABLE ACCESS FULL | PRODUCTS | | 6 | TABLE ACCESS FULL | SALES | | * 7 | TABLE ACCESS FULL | CUSTOMERS |-- | -Predicate Information (identified by operation id):-3-access ("C". "CUST_ID" = "S". "CUST_ID") 4-access ("S". "PROD_ID" = "P" . "PROD_ID") 5-filter ("P". "PROD_NAME" ='T3 Faux Fur-Trimmed Sweater') 7-filter ("C". "COUNTRY_ID" = 'US')

The above execution plan includes a view called vm_nwvw_1, also called projection view, even after the view is merged. The projection view appears after the distinct view in the query has been merged, or the group by view is merged into an external query block and contains group by,having or aggregate operations. In the latter case, the projection view contains the aggregate operations in the group by,having and the original external query block.

In the projection view above, when the optimizer merges the view, it moves the distinct operation to the external query block and adds several additional columns to maintain equivalence with the original query. After that, the query can select only the desired columns from the select list in the external query block. The optimizer retains all the benefits of view merging: all tables in a query block, the optimizer may transform their order in the final join order, and distinct operations may be delayed until all joins are completed.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.