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

Concept: subquery expansion (Subquery Unnesting) is an optimization method for the optimizer to deal with the target sql of a belt query, which means that the optimizer no longer executes the target sql subquery as a separate processing unit, but transforms the subquery into an equivalent table join between itself and the external query. This equivalent join transformation either expands the subquery (that is, the table in the subquery, the view is taken out of the subquery, and then joins the table with the table in the external query), or it does not disassemble but converts the subquery into an embedded view (Inline View) and then joins the table with the table in the external query. Oracle ensures the correctness of the equivalent join transformation corresponding to the expansion of the subquery, that is, the transformed sql and the original sql must be semantically equivalent. Of course, not all subqueries can be expanded as subqueries, and some subqueries cannot be transformed into equivalent table joins. In this case, oracle will not expand subqueries, that is to say, oracle will execute the subqueries as a separate processing unit. In addition, in later versions of oracle10g, for those subqueries that do not split the subquery but transform the subquery into an embedded view, only when the cost value of the equivalent rewrite sql after the subquery expansion is less than the cost value of the original sql, oracle will execute the subquery expansion of the original sql.

Subquery expansion usually improves the execution efficiency of the original sql, because if the original sql does not expand the subquery, the subquery is usually executed at the last step of its execution plan and follows the filter type execution plan, which means that the subquery will be executed for how many times without a single record in the result set of the external query, and the execution efficiency of this method is usually not too high. Especially when the subquery contains two or more table joins, the execution efficiency of the expanded subquery is often much higher than that of the filter type execution plan.

If the where condition before a subquery in the Oracle database is one of the following conditions, then this type of target sql can be expanded as a subquery after meeting certain conditions.

Single-row,exists,not exists,in, not in,any,all .

Example 1:

SQL > set lines 20000 pagesize 1000in: SELECT t1.cust_last_name, t1.cust_id FROM customers T1 WHERE t1.cust_id IN 4 (SELECT t2.cust_id FROM sales T2 WHERE t2.amount_sold > 700) 5 Execution Plan---Plan hash value: 2448612695 Murray- -| Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time | Pstart | Pstop |- -| 0 | SELECT STATEMENT | | 7059 | 158K | | 1583 (1) | 00:00:20 | | * 1 | HASH JOIN SEMI | | 7059 | 158K | 1360K | 1583 (1) | 00:00:20 | | | | 2 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 704K | | 405K (1) | 00:00:05 | | 3 | PARTITION RANGE ALL | | 560K | 5469K | | 00:00:07 | 1 | 28 | * 4 | TABLE ACCESS FULL | SALES | 560K | 5469K | | 526 (2) | 00:00:07 | 1 | 28 |-| -Predicate Information (identified by operation id):- -any equivalent: SELECT t1.cust_last_name T1.cust_id FROM customers T1 WHERE t1.cust_id = ANY 4 (SELECT t2.cust_id FROM sales T2 WHERE t2.amount_sold > 700) 5 Execution Plan---Plan hash value: 2448612695 Murray- -| Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time | Pstart | Pstop |- -| 0 | SELECT STATEMENT | | 7059 | 158K | | 1583 (1) | 00:00:20 | | * 1 | HASH JOIN SEMI | | 7059 | 158K | 1360K | 1583 (1) | 00:00:20 | | | | 2 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 704K | | 405K (1) | 00:00:05 | | 3 | PARTITION RANGE ALL | | 560K | 5469K | | 00:00:07 | 1 | 28 | * 4 | TABLE ACCESS FULL | SALES | 560K | 5469K | | 526 (2) | 00:00:07 | 1 | 28 |-| -exists equivalent: SELECT t1.cust_last_name T1.cust_id FROM customers T1 WHERE t1.cust_id = ANY 4 (SELECT t2.cust_id FROM sales T2 WHERE t2.amount_sold > 700) 5 Execution Plan---Plan hash value: 2448612695 Murray- -| Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time | Pstart | Pstop |- -| 0 | SELECT STATEMENT | | 7059 | 158K | | 1583 (1) | 00:00:20 | | * 1 | HASH JOIN SEMI | | 7059 | 158K | 1360K | 1583 (1) | 00:00:20 | | | | 2 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 704K | | 405K (1) | 00:00:05 | | 3 | PARTITION RANGE ALL | | 560K | 5469K | | 00:00:07 | 1 | 28 | * 4 | TABLE ACCESS FULL | SALES | 560K | 5469K | | 526 (2) | 00:00:07 | 1 | 28 |-| -do not expand Obviously unreasonable, the sales table has to be executed many times: SELECT t1.cust_last_name, t1.cust_id FROM customers T1 WHERE t1.cust_id IN (SELECT / * + no_unnest * / t2.cust_id FROM sales T2 WHERE t2.amount_sold > 700)

When the subquery is expanded, it becomes a hash semi-join:

Equivalent writing: (if cust_id is the only key value) can be converted to internal connection:

SELECT t1.cust_last_name, t1.cust_id FROM customers T1, sales T2 WHERE T1 1.custroomid= t2.cust_id AND t2.amount_sold > 700

If it is not in, it will be converted to hash disconnect:

SQL > set autot traceSELECT t1.cust_last_name, t1.cust_id FROM customers T1 WHERE t1.cust_id not in 4 (SELECT t2.cust_id FROM sales T2 WHERE t2.amount_sold > 700) Execution Plan---Plan hash value: 2850422635 Murray- -| Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time | Pstart | Pstop |- -| 0 | SELECT STATEMENT | | 48441 | 1088K | | 1583 (1) | 00:00:20 | | * 1 | HASH JOIN ANTI | | 48441 | 1088K | 1360K | 1583 (1) | 00:00:20 | 2 | | | TABLE ACCESS FULL | CUSTOMERS | 55500 | 704K | | 405K (1) | 00:00:05 | | 3 | PARTITION RANGE ALL | 560K | 5469K | | 00:00:07 | 1 | 28 | * 4 | TABLE ACCESS FULL | SALES | 560K | 5469K | | 526 (2) | 00:00:07 | 1 | 28 |-|

Convert a subquery to an inline view:

SELECT t1.cust_last_name, t1.cust_id FROM customers T1 WHERE t1.cust_id NOT IN (SELECT t2.cust_id FROM sales T2) Products T3 WHERE t2.prod_id = t3.prod_id and t2.amount_sold > 700) Execution Plan---Plan hash value: 1272298339 muri- | Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time | Pstart | Pstop |- -| 0 | SELECT STATEMENT | | 48441 | 1229K | | 1665 (1) | 00:00:20 | * 1 | HASH JOIN ANTI | | 48441 | 1229K | 1360K | 1665 (1) | 00:00:20 | | 2 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 704k | | 405k (1) | 00:00:05 | 3 | VIEW | VW_NSO_1 | 560K | 7110K | | 529K | 00:00:07 | | | | * 4 | HASH JOIN | | 560K | 9844K | | 529K | 00:00:07 | 5 | INDEX FULL SCAN | PRODUCTS_PK | 72 | 288 | | 1 (0) | 00:00:01 | 6 | PARTITION RANGE ALL | | 560K | 7657K | | 526 (2) | 00:00:07 | 1 | 28 | * 7 | TABLE ACCESS FULL | | | SALES | 560K | 7657K | | 526K (2) | 00:00:07 | 1 | 28 |-| -

Here oracle converts the subquery to the inline view VM_NSO_1, and then makes a hash semi-join with the table customers in the external query.

Equivalent:

SELECT t1.cust_last_name, t1.cust_id FROM customers T1, (SELECT t2.cust_id FROM sales T2, products T3 WHERE t2.prod_id = t3.prod_id AND t2.amount_sold > 700) vm_nso_1 WHERE t1.cust_id semi = vm_nso_1.cust_id

Whether a subquery can be expanded depends on the following two conditions:

The equivalent rewriting sql and the original sql corresponding to the subquery expansion must be semantically equivalent. If the rewritten sql and the original sql do not necessarily maintain the semantic equivalence, this type of subquery cannot be expanded as a subquery.

For a subquery that cannot be split but is transformed into an embedded view, only the equivalent rewrite sql cost value that has been expanded by the subquery is less than the original sql cost value. Oracle will execute the subquery expansion against the target sql.

For the first case of subquery expansion (that is, the subquery is expanded, the tables and views in the subquery are taken out of the subquery, and then joined with the tables and views in the external query), even in versions after oracle 10g, oracle will not consider the cost of subquery expansion, that is, oracle will think that the efficiency of subquery expansion is always higher than that of non-expansion. This means that if the target sql satisfies the first case of subquery expansion. Then oracle will always expand the subquery, regardless of whether the cost value of the equivalent sql after the subquery expansion is less than the cost value of the original sql.

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.