Oracle Query Transformation Subquery expansion

Concept: Sub-query unwrapping (subquery unnesting) is an optimization of the target SQL that the optimizer handles for the band query, which means that the optimizer no longer treats the target SQL subquery as a separate processing unit to execute separately, Instead, the subquery is converted to an equivalent table connection between itself and the external query. This equivalent connection transformation either expands the subquery (the table in the subquery, the view from the subquery, and then joins the table in the outer query, the view does the table), or it does not disassemble but transforms the subquery into an inline view and then the table in the outer query, The view makes a table connection. Oracle ensures the correctness of the equivalent join transformation for the subquery expansion, that is, the converted SQL and the original SQL must be semantically equivalent. Of course not all subqueries can do sub-query expansion, some subqueries are not able to do this equivalent table connection conversion, in this case, Oracle will not do its sub-query expansion, that is, at this time, Oracle will be treated as a separate processing unit to execute alone. In addition, in the later version of oracle10g, for the kind of sub-query that does not disassemble the subquery but will transform the subquery into an inline view, only if the cost value of the equivalent rewrite SQL after the sub-query expansion is less than the cost value of the original SQL, Oracle executes the subquery for the original SQL expansion

Sub-query expansion usually improves the execution efficiency of the original SQL, because if the original SQL does not do sub-query expansion, then normally the subquery will be executed at the last step of its execution plan, and will take the execution plan of the filter type, which means that there is no record of the result set of the external query. How many times the subquery is executed, the execution of which is usually not too high, especially if the subquery contains two or more table connections, when the sub-query is expanded, the execution efficiency is often much higher than the execution plan of the filter type.

Oracle database lining query before the WHERE condition if it is one of the following conditions, then this type of target SQL can be a sub-query expansion after satisfying certain conditions,

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

Example 1:

sql> set lines 200 pagesize 1000in notation: 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----------------------------------------------------------------------------------------------------- -----| 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| |   405    (1) | 00:00:05  | | | |    3 |   PARTITION RANGE ALL| |   560K|   5469K| |   526    (2) | 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 notation: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----------------------------------------------------------------------------------------------------- -----| 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| |   405    (1) | 00:00:05  | | | |    3 |   partition range all| |   560k|  5469k| |   526    (2) | 00:00:07 |      1 |    28 | | *  4 |    table access full | sales |    560K|  5469K| |   526    (2) | 00:00:07 |      1 |    28 |--------------------------------------------- exists equivalence of-------------------------------------------------------------: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----------------------------------------------------------------------------------------------------------| 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| |   405    (1) | 00:00:05 | | | |    3 |   PARTITION RANGE ALL| |   560K|   5469K| |   526    (2) | 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 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 > 70 0)

When the subquery expands, it becomes a hash half-connection:

Equivalent notation: (if cust_id is a unique key value) can be converted to an inner join:

SELECT T1.cust_last_name, t1.cust_id from customers T1, Sales T2 WHERE t1.cust_id= t2.cust_id and T2.amount_sold > 7 00

If it is not, it is converted to a hash reverse connection:

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----------------------------------------------------------------------------------------------------- -----| 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| |   405    (1) | 00:00:05  | | | |    3 |   PARTITION RANGE ALL| |   560K|   5469K| |   526    (2) | 00:00:07 |      1 |    28 | | *  4 |    table access full | sales |    560K|  5469K| |   526    (2) | 00:00:07 | & nbsp;    1 |    28 |-------------------------------------------------- --------------------------------------------------------

Translate the query into 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---------------------------------------------------------------------------------- ----------------------------| 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|     | 405     (1) | 00:00:05 |     |     | |    3 |   view       | vw_nso_1    | 560k|7110k|     |  529    (2) | 00:00:07 |     |     | | *  4 |    hash join       |      | 560K|9844K|     | 529    (2) |  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|     | 526    (2) |  00:00:07 |   1 |  28 |------------------------------------------------ --------------------------------------------------------------

Here the Oracle handle query is converted into an inline view vm_nso_1, and then the table customers in the external query does a hash half-connection.

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 >) vm_nso_1 where t1.cust_id semi = vm_nso_1.cust_id

Whether a subquery can do a subquery expansion depends on the following two conditions:

Sub-query expansion corresponding to the equivalent rewrite SQL and the original SQL must be completely equivalent in semantics, if the rewritten SQL and the original SQL does not necessarily maintain the full semantic equivalence, this type of sub-query can not do sub-query expansion.

For sub-queries that cannot be disassembled but will convert it to an inline view of the subquery expansion, only the equivalent rewrite SQL cost value that is expanded by the subquery is less than the cost value of the original SQL. Oracle does not perform sub-query expansion on target SQL.

For the first scenario where the subquery is expanded (the subquery is expanded, the table in the subquery is taken out of the subquery, and the table is connected to the table in the external query), even in later versions of Oracle 10g, Oracle does not consider the cost of sub-query expansion. That is, Oracle now thinks that the efficiency of query deployment is always more efficient than non-expansion, which means that if the target SQL satisfies the first case where the subquery is expanded. Oracle will always do sub-query expansion, regardless of whether the cost value of the equivalent SQL after the subquery expansion is less than the cost value of the original SQL.

Oracle Query Transformation Subquery expansion