oracle 子查询解嵌套和谓词推入（sqlsubquery unnest and pre_push）

Subquery Unnesting  In subquery unnesting, the optimizer transforms a nested query

into an equivalent join statement, and then optimizes the join. This transformation

enables the optimizer to take advantage ofthe join optimizer technique. The optimizer

can perform thistransformation only if the resulting join statement is guaranteed to

return exactly the samerows as the origin al statement, and if subqueries do not

contain aggregatefunctions such as AVG .

If the optimizer cannot transform a complexst atement into a join statement, it selects

execution plans for the parent statementand the subquery as though they were

separate statements. The optimizer then ex ecutesthe subquery and uses the rows

returned to execute the parent query. Toimprove execution speed of the overall query

plan, the optimizer orders the subplansefficiently.

 

Predicate Pushing   In predicate pushing, the optimizer"pushes" the relevant predicates

from the containing query block into theview query block. For views that are not

merged, this technique improves the subplanof the unmerged view because the

database can use the pushed-in predicatesto access indexes or to use as filters.

 

hr@ORCL>

hr@ORCL> create table t1 as select *from dba_objects where object_id<3000;

 

Table created.

 

hr@ORCL> create table t2 as select *from dba_objects ;

 

Table created.

 

hr@ORCL> create index idx_id1_t2 ont2(object_id,owner);

 

Index created.

 

hr@ORCL> create index idx_id1_t1 ont1(object_id);

 

Index created.

hr@ORCL> analyze table t1 computestatistics for all indexed columns;

 

Table analyzed.

 

hr@ORCL> analyze table t2 computestatistics for all indexed columns;

 

Table analyzed.

 

hr@ORCL> set autot traceonly

 

 

 

 

hr@ORCL> select object_id from t1 whereexists(

 2  select 1 from t2 wheret1.object_id=t2.object_id-10);

 

2874 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2210107937

 

------------------------------------------------------------------------------------

| Id | Operation             |Name       | Rows  | Bytes | Cost (%CPU)| Time    |

------------------------------------------------------------------------------------

|   0| SELECT STATEMENT      |            |     1 |   17 |   294   (1)| 00:00:04 |

|*  1|  HASH JOIN SEMI       |            |     1 |   17 |   294   (1)| 00:00:04 |

|   2|   INDEX FAST FULL SCAN| IDX_ID1_T1|  2931 | 38103 |     5  (0)| 00:00:01 |

|   3 |  TABLE ACCESS FULL   | T2         | 62741 |   245K|  289   (1)| 00:00:04 |

------------------------------------------------------------------------------------

 

Predicate Information (identified byoperation id):

---------------------------------------------------

 

   1-access("T1"."OBJECT_ID"="T2"."OBJECT_ID"-10)

 

Note

-----

   -dynamic sampling used for this statement (level=2)

 

 

Statistics

----------------------------------------------------------

        32  recursive calls

         0  db block gets

      1345  consistent gets

         7  physical reads

         0  redo size

     50399  bytes sent via SQL*Net toclient

      2620  bytes received via SQL*Netfrom client

       193  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

      2874  rows processed

 

上面的例子中，oracle自动将子查询进行了展开，下面我们使用no_unnest hint来不让oracle在这里进行子查询展开，就通过
嵌套在子查询里面，那么这样比如就会走fiter了，如下：

hr@ORCL> alter system flush shared_pool;

 

System altered.

 

hr@ORCL> select object_id

 2  from t1

 3  where exists (select /*+no_unnest */

 4  1

 5  from t2

 6  wheret1.object_id=t2.object_id-10);

 

2874 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 895956251

 

---------------------------------------------------------------------------

| Id | Operation          | Name |Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0| SELECT STATEMENT   |      |    1 |     3 |  4414  (1)| 00:00:53 |

|*  1|  FILTER            |      |      |       |            |          |

|   2|   TABLE ACCESS FULL| T1   | 2931 |  8793 |    13  (0)| 00:00:01 |

|*  3|   TABLE ACCESS FULL| T2   |  627 |  2508 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

 

Predicate Information (identified byoperation id):

---------------------------------------------------

 

   1- filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T2" "T2"WHERE

             "T2"."OBJECT_ID"-10=:B1))

   3- filter("T2"."OBJECT_ID"-10=:B1)

 

Note

-----

   -dynamic sampling used for this statement (level=2)

 

 

Statistics

----------------------------------------------------------

       735  recursive calls

         0  db block gets

    123829  consistent gets

      1090  physical reads

         0  redo size

     50399  bytes sent via SQL*Net toclient

      2620  bytes received via SQL*Netfrom client

       193  SQL*Net roundtrips to/fromclient

        20  sorts (memory)

         0  sorts (disk)

      2874  rows processed

 

那么什么情况下存在子查询的情况，oracle不会进行子查询的展开呢 

 

l   包含rownum

hr@ORCL> select object_id

  2  fromt1

  3 where exists