当前位置：Oracle研究中心 > 运维DBA > Oracle性能优化 >
【学习笔记】Oracle SQL优化子查询和谓词推入研究测试
时间:2016-12-22 11:16 来源:Oracle研究中心 作者:网络 点击: 次
天萃荷净
Oracle研究中心学习笔记：分享一篇关于OracleSQL语句优化的文章，通过实验详细研究Oracle 子查询和谓词推入的效果。
本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: about subquery unnest/push pred
今天抽空做了下关于子查询和谓词推入的实验，我这里通过vm 10gR2环境来模拟展示。
SQL> conn roger/roger
Connected.
SQL> create table t1 as select * from dba_objects where object_id < 3000;
Table created.
SQL> create table t2 as select * from dba_objects;
Table created.
SQL> create index idx_id1_t2 on t2(object_id,owner);
Index created.
SQL> create index idx_id1_t1 on t1(object_id);
Index created.
SQL> set autot traceonly exp
SQL> analyze table t1 compute statistics for all indexed columns;
Table analyzed.
SQL> analyze table t2 compute statistics for all indexed columns;
Table analyzed.
SQL> set autot traceonly
SQL> select object_id from t1 where exists (
2 select 1 from t2 where t1.object_id=t2.object_id-10);
2911 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2210107937
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 203 (1)| 00:00:03 |
|* 1 | HASH JOIN SEMI | | 1 | 17 | 203 (1)| 00:00:03 |
| 2 | INDEX FAST FULL SCAN| IDX_ID1_T1 | 2950 | 38350 | 5 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T2 | 49910 | 194K| 198 (1)| 00:00:03 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID"-10)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
713 recursive calls
0 db block gets
1136 consistent gets
553 physical reads
0 redo size
40114 bytes sent via SQL*Net to client
2534 bytes received via SQL*Net from client
196 SQL*Net roundtrips to/from client
21 sorts (memory)
0 sorts (disk)
2911 rows processed
上面的例子中，oracle自动将子查询进行了展开，下面我们使用no_unnest hint来不让oracle在这里进行子查询展开，就通过嵌套在子查询里面，那么这样比如就会走fiter了，如下：
SQL> alter system flush shared_pool;
System altered.
SQL> select object_id
2 from t1
3 where exists (select /*+no_unnest*/
4 1
5 from t2
6 where t1.object_id = t2.object_id - 10);
2911 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 895956251
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 4442 (1)| 00:00:54 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T1 | 2950 | 8850 | 13 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 499 | 1996 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T2" "T2" WHERE
"T2"."OBJECT_ID"-10=:B1))
3 - filter("T2"."OBJECT_ID"-10=:B1)
在10g中，oracle默认就会进行子查询的展开，这是通过一个隐含参数来进行控制的，如下：
SQL> show parameter unnest
NAME TYPE VALUE
———————————— ———– ——————————
_distinct_view_unnesting boolean FALSE
_unnest_subquery boolean TRUE
SQL>
我们可以发现，_unnest_subquery 参数默认是true.
当子查询返回的结果集如果很小的话，这个时候其实是可以走fiter的，换句话讲，这个时候就可以不进行子查询的展开。
那么什么情况下存在子查询的情况，oracle不会进行子查询的展开呢 ？
—包含rownum
SQL> select object_id
2 from t1
3 where exists (select 1 from t2 where t1.object_id = t2.object_id-10 and rownum < 20);
2911 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4265634519
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 4442 (1)| 00:00:54 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T1 | 2950 | 8850 | 13 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
|* 4 | TABLE ACCESS FULL| T2 | 499 | 1996 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "T2" "T2" WHERE ROWNUM<20 AND
"T2"."OBJECT_ID"-10=:B1))
3 - filter(ROWNUM<20)
4 - filter("T2"."OBJECT_ID"-10=:B1)
—包含cube、rollup 等函数
SQL> select object_id
2 from t1
3 where exists (select 1,sum(object_id) from t2 where t1.object_id = t2.object_id-10 group by rollup(t2.object_id));
2911 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3739889183
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 293K (1)| 00:58:38 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T1 | 2950 | 8850 | 13 (0)| 00:00:01 |
| 3 | SORT GROUP BY ROLLUP| | 499 | 1996 | 199 (2)| 00:00:03 |
|* 4 | TABLE ACCESS FULL | T2 | 499 | 1996 | 198 (1)| 00:00:03 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "T2" "T2" WHERE
"T2"."OBJECT_ID"-10=:B1 GROUP BY ROLLUP ("T2"."OBJECT_ID")))
4 - filter("T2"."OBJECT_ID"-10=:B1)
—包含union all、union、INTERSECT、 MINUS等
SQL> select object_id
2 from t1
3 where exists (select 1 from t2 where t1.object_id = t2.object_id-10 union all
4 select object_id from t2 where object_id < 500);
2950 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2568596142
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 296K (1)| 00:59:14 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T1 | 2950 | 11800 | 13 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS FULL| T2 | 511 | 2555 | 198 (1)| 00:00:03 |
|* 5 | INDEX RANGE SCAN | IDX_ID1_T2 | 417 | 2085 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS ( (SELECT 1 FROM "T2" "T2" WHERE
"T2"."OBJECT_ID"-10=:B1) UNION ALL (SELECT "OBJECT_ID" FROM "T2" "T2"
WHERE "OBJECT_ID"<500)))
4 - filter("T2"."OBJECT_ID"-10=:B1)
5 - access("OBJECT_ID"<500)
SQL> select object_id
2 from t1
3 where exists (select 1 from t2 where t1.object_id = t2.object_id-10 union
4 select object_id from t2 where object_id < 500);
2950 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 583540251
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 299K (2)| 00:59:49 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T1 | 2950 | 11800 | 13 (0)| 00:00:01 |
| 3 | SORT UNIQUE | | 928 | 4640 | 203 (3)| 00:00:03 |
| 4 | UNION-ALL | | | | | |
|* 5 | TABLE ACCESS FULL| T2 | 511 | 2555 | 198 (1)| 00:00:03 |
|* 6 | INDEX RANGE SCAN | IDX_ID1_T2 | 417 | 2085 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS ( (SELECT 1 FROM "T2" "T2" WHERE
"T2"."OBJECT_ID"-10=:B1)UNION (SELECT "OBJECT_ID" FROM "T2" "T2" WHERE
"OBJECT_ID"<500)))
5 - filter("T2"."OBJECT_ID"-10=:B1)
6 - access("OBJECT_ID"<500)
SQL> select object_id
2 from t1
3 where exists (select 1 from t2 where t1.object_id = t2.object_id-10 MINUS
4 select object_id from t2 where object_id < 500);
2911 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1945478487
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 299K (2)| 00:59:49 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T1 | 2950 | 11800 | 13 (0)| 00:00:01 |
| 3 | MINUS | | | | | |
| 4 | SORT UNIQUE NOSORT| | 511 | 2555 | 199 (2)| 00:00:03 |
|* 5 | TABLE ACCESS FULL| T2 | 511 | 2555 | 198 (1)| 00:00:03 |
| 6 | SORT UNIQUE NOSORT| | 417 | 2085 | 4 (25)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_ID1_T2 | 417 | 2085 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS ( (SELECT 1 FROM "T2" "T2" WHERE
"T2"."OBJECT_ID"-10=:B1)MINUS (SELECT "OBJECT_ID" FROM "T2" "T2" WHERE
"OBJECT_ID"<500)))
5 - filter("T2"."OBJECT_ID"-10=:B1)
7 - access("OBJECT_ID"<500)
—包含start with…connect by
SQL> select object_id
2 from t1
3 where exists (select 1
4 from t2
5 start with owner='SYS'
6 connect by object_id >100 and object_id < 200);
2950 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1433996639
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2950 | 11800 | 15 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T1 | 2950 | 11800 | 13 (0)| 00:00:01 |
|* 3 | CONNECT BY WITHOUT FILTERING| | | | | |
|* 4 | INDEX FAST FULL SCAN | IDX_ID1_T2 | 23084 | 247K| 43 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T2 | 51088 | 249K| 198 (1)| 00:00:03 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "T2" "T2" CONNECT BY "OBJECT_ID">100 AND
"OBJECT_ID"<200 START WITH "OWNER"='SYS'))
3 - filter("OBJECT_ID">100 AND "OBJECT_ID"<200)
4 - filter("OWNER"='SYS')
我们再来看下试图合并，首先我们创建几个测试表。
SQL> create table t3 as select * from dba_objects where object_id < 10000;
Table created.
SQL> analyze table t3 compute statistics;
Table analyzed.
SQL> create or replace view t_view
2 as
3 select t1.* from t1,t2
4 where
5 t1.object_id=t2.object_id;
View created.
SQL>
SQL> set autot traceonly exp
SQL> select t3.object_name
2 from t3, t_view
3 where t3.object_name = t_view.object_name(+)
4 and t3.object_id = 888;
Execution Plan
----------------------------------------------------------
Plan hash value: 1962414821
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 | 95 (3)| 00:00:02 |
|* 1 | HASH JOIN OUTER | | 1 | 86 | 95 (3)| 00:00:02 |
|* 2 | TABLE ACCESS FULL | T3 | 1 | 20 | 37 (0)| 00:00:01 |
| 3 | VIEW | T_VIEW | 2950 | 190K| 57 (2)| 00:00:01 |
|* 4 | HASH JOIN | | 2950 | 76700 | 57 (2)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T1 | 2950 | 61950 | 13 (0)| 00:00:01 |
| 6 | INDEX FAST FULL SCAN| IDX_ID1_T2 | 51088 | 249K| 43 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T3"."OBJECT_NAME"="T_VIEW"."OBJECT_NAME"(+))
2 - filter("T3"."OBJECT_ID"=888)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
SQL>
从上面可以看到t3的条件进行了fiter操作，并没有推进到view里面去，下面我们使用push_pred hint进行强制推进下。
SQL> alter system flush shared_pool;
System altered.
SQL>
SQL> select /*+push_pred(t_view)*/ t3.object_name
2 from t3, t_view
3 where t3.object_name = t_view.object_name(+)
4 and t3.object_id = 888;
Execution Plan
----------------------------------------------------------
Plan hash value: 3014823912
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 51 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 1 | 22 | 51 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | T3 | 1 | 20 | 37 (0)| 00:00:01 |
| 3 | VIEW PUSHED PREDICATE | T_VIEW | 1 | 2 | 14 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 26 | 14 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T1 | 1 | 21 | 13 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_ID1_T2 | 1 | 5 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T3"."OBJECT_ID"=888)
5 - filter("T1"."OBJECT_NAME"="T3"."OBJECT_NAME")
6 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
实际上这里如果进行merge的话，也可以实现类似的效果，如下：
SQL> select /*+merge(t_view)*/t3.object_name
2 from t3, t_view
3 where t3.object_name=t_view.object_name
4 and t3.object_id = 888;
Execution Plan
----------------------------------------------------------
Plan hash value: 2983785075
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 52 (2)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 46 | 52 (2)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 41 | 51 (2)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T3 | 1 | 20 | 37 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T1 | 2950 | 61950 | 13 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_ID1_T2 | 1 | 5 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T3"."OBJECT_NAME"="T1"."OBJECT_NAME")
3 - filter("T3"."OBJECT_ID"=888)
5 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
SQL> select /*+no_merge(t_view)*/t3.object_name
2 from t3, t_view
3 where t3.object_name=t_view.object_name
4 and t3.object_id = 888;
Execution Plan
----------------------------------------------------------
Plan hash value: 2958396757
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 | 95 (3)| 00:00:02 |
|* 1 | HASH JOIN | | 1 | 86 | 95 (3)| 00:00:02 |
|* 2 | TABLE ACCESS FULL | T3 | 1 | 20 | 37 (0)| 00:00:01 |
| 3 | VIEW | T_VIEW | 2950 | 190K| 57 (2)| 00:00:01 |
|* 4 | HASH JOIN | | 2950 | 76700 | 57 (2)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T1 | 2950 | 61950 | 13 (0)| 00:00:01 |
| 6 | INDEX FAST FULL SCAN| IDX_ID1_T2 | 51088 | 249K| 43 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T3"."OBJECT_NAME"="T_VIEW"."OBJECT_NAME")
2 - filter("T3"."OBJECT_ID"=888)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
当sql查询中出现view，实际上使用merge/no_merge和push_pred，no_push_pred我感觉有些类似。
如下一段关于merge 和 push_pred的描述：
Merge / no_merge: if you use a complex view (e.g. aggregate view, or join view) in your query, should you rewrite
the query to merge the tables in the view into a single from clause with all the other tables (merge), or should
you evaluate the view to produce a “standalone” result set and then join the result set to the remaining tables (no_merge).
Push_pred / no_push_pred: If you have a non-mergeable view (possible because of a no_merge hint) in your query, how
should you operate the join from other tables; should you create one large view result and join it once (no_push_pred)
or should you push the join predicate down into the view definition and recreate the view result set for every driving
row from another table (push_pred).
merge 就是把view展开，那么你查看执行计划时就看不到view acces patch的信息了，no_merge则相反。
所以push_pred（谓词推入）就是是说将谓词条件推入到view中。
由于要发现push pred操作，必须保证试图不能被merge，所以通常在实验观察时会同时使用no_merge hint.
细心一点我们还能发现，针对谓词推入的操作，其外部操作只能是nest loop。
到最后我们可以简单的总结下，针对如下情况，子查询是不能展开的：
1.子查询存在 ROWNUM
2.子查询存在 CUBE,ROLLUP
3.子查询存在 UNION, UNION ALL,INTERSECT、 MINUS
4.子查询存在 START WITH ,CONNECT BY 字句
通常我发现针对右外连接的情况下，oracle优化器默认也不会进行view merge操作，如下：
SQL> select t3.object_name from t3,t_view where t3.object_name=t_view.object_name(+)
2 and t3.object_id=888;
Execution Plan
----------------------------------------------------------
Plan hash value: 1962414821
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 88 | 95 (3)| 00:00:02 |
|* 1 | HASH JOIN OUTER | | 1 | 88 | 95 (3)| 00:00:02 |
|* 2 | TABLE ACCESS FULL | T3 | 1 | 22 | 37 (0)| 00:00:01 |
| 3 | VIEW | T_VIEW | 2950 | 190K| 57 (2)| 00:00:01 |
|* 4 | HASH JOIN | | 2950 | 76700 | 57 (2)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T1 | 2950 | 61950 | 13 (0)| 00:00:01 |
| 6 | INDEX FAST FULL SCAN| IDX_ID1_T2 | 51088 | 249K| 43 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T3"."OBJECT_NAME"="T_VIEW"."OBJECT_NAME"(+))
2 - filter("T3"."OBJECT_ID"=888)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
事实上针对这样的情况，强制使用merge hint也不起作用，如果不存在右外连接的情况下，是可以自动进行merge的：
SQL> select /*+merge(t_view)*/t3.object_name from t3,t_view where t3.object_name=t_view.object_name(+)
2 and t3.object_id=888;
Execution Plan
----------------------------------------------------------
Plan hash value: 1962414821
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 88 | 95 (3)| 00:00:02 |
|* 1 | HASH JOIN OUTER | | 1 | 88 | 95 (3)| 00:00:02 |
|* 2 | TABLE ACCESS FULL | T3 | 1 | 22 | 37 (0)| 00:00:01 |
| 3 | VIEW | T_VIEW | 2950 | 190K| 57 (2)| 00:00:01 |
|* 4 | HASH JOIN | | 2950 | 76700 | 57 (2)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T1 | 2950 | 61950 | 13 (0)| 00:00:01 |
| 6 | INDEX FAST FULL SCAN| IDX_ID1_T2 | 51088 | 249K| 43 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T3"."OBJECT_NAME"="T_VIEW"."OBJECT_NAME"(+))
2 - filter("T3"."OBJECT_ID"=888)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
SQL> alter system flush shared_pool;
System altered.
SQL> select t3.object_name from t3,t_view where t3.object_name=t_view.object_name
2 and t3.object_id=888;
Execution Plan
----------------------------------------------------------
Plan hash value: 2983785075
----------------------------Oracle о ------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | 52 (2)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 48 | 52 (2)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 43 | 51 (2)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T3 | 1 | 22 | 37 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T1 | 2950 | 61950 | 13 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_ID1_T2 | 1 | 5 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T3"."OBJECT_NAME"="T1"."OBJECT_NAME")
3 - filter("T3"."OBJECT_ID"=888)
5 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
该默认行为是oracle通过一个参数来进行控制的，如下：
SQL> show parameter view
NAME TYPE VALUE
———————————— ———– ——————————
_complex_view_merging boolean TRUE
_distinct_view_unnesting boolean FALSE
_partition_view_enabled boolean TRUE
_project_view_columns boolean TRUE
_push_join_union_view boolean TRUE
_push_join_union_view2 boolean TRUE
_simple_view_merging boolean TRUE
optimizer_secure_view_merging boolean TRUE
在10gR2版本中 _simple_view_merging 参数默认是true。
Oracle研究中心
本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: about subquery unnest/push pred
今天抽空做了下关于子查询和谓词推入的实验，我这里通过vm 10gR2环境来模拟展示。
SQL> conn roger/roger
Connected.
SQL> create table t1 as select * from dba_objects where object_id < 3000;
Table created.
SQL> create table t2 as select * from dba_objects;
Table created.
SQL> create index idx_id1_t2 on t2(object_id,owner);
Index created.
SQL> create index idx_id1_t1 on t1(object_id);
Index created.
SQL> set autot traceonly exp
SQL> analyze table t1 compute statistics for all indexed columns;
Table analyzed.
SQL> analyze table t2 compute statistics for all indexed columns;
Table analyzed.
SQL> set autot traceonly
SQL> select object_id from t1 where exists (
2 select 1 from t2 where t1.object_id=t2.object_id-10);
2911 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2210107937
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 203 (1)| 00:00:03 |
|* 1 | HASH JOIN SEMI | | 1 | 17 | 203 (1)| 00:00:03 |
| 2 | INDEX FAST FULL SCAN| IDX_ID1_T1 | 2950 | 38350 | 5 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T2 | 49910 | 194K| 198 (1)| 00:00:03 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID"-10)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
713 recursive calls
0 db block gets
1136 consistent gets
553 physical reads
0 redo size
40114 bytes sent via SQL*Net to client
2534 bytes received via SQL*Net from client
196 SQL*Net roundtrips to/from client
21 sorts (memory)
0 sorts (disk)
2911 rows processed
上面的例子中，oracle自动将子查询进行了展开，下面我们使用no_unnest hint来不让oracle在这里进行子查询展开，就通过嵌套在子查询里面，那么这样比如就会走fiter了，如下：
SQL> alter system flush shared_pool;
System altered.
SQL> select object_id
2 from t1
3 where exists (select /*+no_unnest*/
4 1
5 from t2
6 where t1.object_id = t2.object_id - 10);
2911 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 895956251
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 4442 (1)| 00:00:54 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T1 | 2950 | 8850 | 13 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 499 | 1996 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T2" "T2" WHERE
"T2"."OBJECT_ID"-10=:B1))
3 - filter("T2"."OBJECT_ID"-10=:B1)
在10g中，oracle默认就会进行子查询的展开，这是通过一个隐含参数来进行控制的，如下：
SQL> show parameter unnest
NAME TYPE VALUE
———————————— ———– ——————————
_distinct_view_unnesting boolean FALSE
_unnest_subquery boolean TRUE
SQL>
我们可以发现，_unnest_subquery 参数默认是true.
当子查询返回的结果集如果很小的话，这个时候其实是可以走fiter的，换句话讲，这个时候就可以不进行子查询的展开。
那么什么情况下存在子查询的情况，oracle不会进行子查询的展开呢 ？
—包含rownum
SQL> select object_id
2 from t1
3 where exists (select 1 from t2 where t1.object_id = t2.object_id-10 and rownum < 20);
2911 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4265634519
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 4442 (1)| 00:00:54 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T1 | 2950 | 8850 | 13 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
|* 4 | TABLE ACCESS FULL| T2 | 499 | 1996 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "T2" "T2" WHERE ROWNUM<20 AND
"T2"."OBJECT_ID"-10=:B1))
3 - filter(ROWNUM<20)
4 - filter("T2"."OBJECT_ID"-10=:B1)
—包含cube、rollup 等函数
SQL> select object_id
2 from t1
3 where exists (select 1,sum(object_id) from t2 where t1.object_id = t2.object_id-10 group by rollup(t2.object_id));
2911 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3739889183
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 293K (1)| 00:58:38 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T1 | 2950 | 8850 | 13 (0)| 00:00:01 |
| 3 | SORT GROUP BY ROLLUP| | 499 | 1996 | 199 (2)| 00:00:03 |
|* 4 | TABLE ACCESS FULL | T2 | 499 | 1996 | 198 (1)| 00:00:03 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "T2" "T2" WHERE
"T2"."OBJECT_ID"-10=:B1 GROUP BY ROLLUP ("T2"."OBJECT_ID")))
4 - filter("T2"."OBJECT_ID"-10=:B1)
—包含union all、union、INTERSECT、 MINUS等
SQL> select object_id
2 from t1
3 where exists (select 1 from t2 where t1.object_id = t2.object_id-10 union all
4 select object_id from t2 where object_id < 500);
2950 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2568596142
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 296K (1)| 00:59:14 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T1 | 2950 | 11800 | 13 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS FULL| T2 | 511 | 2555 | 198 (1)| 00:00:03 |
|* 5 | INDEX RANGE SCAN | IDX_ID1_T2 | 417 | 2085 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS ( (SELECT 1 FROM "T2" "T2" WHERE
"T2"."OBJECT_ID"-10=:B1) UNION ALL (SELECT "OBJECT_ID" FROM "T2" "T2"
WHERE "OBJECT_ID"<500)))
4 - filter("T2"."OBJECT_ID"-10=:B1)
5 - access("OBJECT_ID"<500)
SQL> select object_id
2 from t1
3 where exists (select 1 from t2 where t1.object_id = t2.object_id-10 union
4 select object_id from t2 where object_id < 500);
2950 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 583540251
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 299K (2)| 00:59:49 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T1 | 2950 | 11800 | 13 (0)| 00:00:01 |
| 3 | SORT UNIQUE | | 928 | 4640 | 203 (3)| 00:00:03 |
| 4 | UNION-ALL | | | | | |
|* 5 | TABLE ACCESS FULL| T2 | 511 | 2555 | 198 (1)| 00:00:03 |
|* 6 | INDEX RANGE SCAN | IDX_ID1_T2 | 417 | 2085 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS ( (SELECT 1 FROM "T2" "T2" WHERE
"T2"."OBJECT_ID"-10=:B1)UNION (SELECT "OBJECT_ID" FROM "T2" "T2" WHERE
"OBJECT_ID"<500)))
5 - filter("T2"."OBJECT_ID"-10=:B1)
6 - access("OBJECT_ID"<500)
SQL> select object_id
2 from t1
3 where exists (select 1 from t2 where t1.object_id = t2.object_id-10 MINUS
4 select object_id from t2 where object_id < 500);
2911 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1945478487
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 299K (2)| 00:59:49 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T1 | 2950 | 11800 | 13 (0)| 00:00:01 |
| 3 | MINUS | | | | | |
| 4 | SORT UNIQUE NOSORT| | 511 | 2555 | 199 (2)| 00:00:03 |
|* 5 | TABLE ACCESS FULL| T2 | 511 | 2555 | 198 (1)| 00:00:03 |
| 6 | SORT UNIQUE NOSORT| | 417 | 2085 | 4 (25)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_ID1_T2 | 417 | 2085 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS ( (SELECT 1 FROM "T2" "T2" WHERE
"T2"."OBJECT_ID"-10=:B1)MINUS (SELECT "OBJECT_ID" FROM "T2" "T2" WHERE
"OBJECT_ID"<500)))
5 - filter("T2"."OBJECT_ID"-10=:B1)
7 - access("OBJECT_ID"<500)
—包含start with…connect by
SQL> select object_id
2 from t1
3 where exists (select 1
4 from t2
5 start with owner='SYS'
6 connect by object_id >100 and object_id < 200);
2950 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1433996639
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2950 | 11800 | 15 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T1 | 2950 | 11800 | 13 (0)| 00:00:01 |
|* 3 | CONNECT BY WITHOUT FILTERING| | | | | |
|* 4 | INDEX FAST FULL SCAN | IDX_ID1_T2 | 23084 | 247K| 43 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T2 | 51088 | 249K| 198 (1)| 00:00:03 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "T2" "T2" CONNECT BY "OBJECT_ID">100 AND
"OBJECT_ID"<200 START WITH "OWNER"='SYS'))
3 - filter("OBJECT_ID">100 AND "OBJECT_ID"<200)
4 - filter("OWNER"='SYS')
我们再来看下试图合并，首先我们创建几个测试表。
SQL> create table t3 as select * from dba_objects where object_id < 10000;
Table created.
SQL> analyze table t3 compute statistics;
Table analyzed.
SQL> create or replace view t_view
2 as
3 select t1.* from t1,t2
4 where
5 t1.object_id=t2.object_id;
View created.
SQL>
SQL> set autot traceonly exp
SQL> select t3.object_name
2 from t3, t_view
3 where t3.object_name = t_view.object_name(+)
4 and t3.object_id = 888;
Execution Plan
----------------------------------------------------------
Plan hash value: 1962414821
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 | 95 (3)| 00:00:02 |
|* 1 | HASH JOIN OUTER | | 1 | 86 | 95 (3)| 00:00:02 |
|* 2 | TABLE ACCESS FULL | T3 | 1 | 20 | 37 (0)| 00:00:01 |
| 3 | VIEW | T_VIEW | 2950 | 190K| 57 (2)| 00:00:01 |
|* 4 | HASH JOIN | | 2950 | 76700 | 57 (2)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T1 | 2950 | 61950 | 13 (0)| 00:00:01 |
| 6 | INDEX FAST FULL SCAN| IDX_ID1_T2 | 51088 | 249K| 43 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T3"."OBJECT_NAME"="T_VIEW"."OBJECT_NAME"(+))
2 - filter("T3"."OBJECT_ID"=888)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
SQL>
从上面可以看到t3的条件进行了fiter操作，并没有推进到view里面去，下面我们使用push_pred hint进行强制推进下。
SQL> alter system flush shared_pool;
System altered.
SQL>
SQL> select /*+push_pred(t_view)*/ t3.object_name
2 from t3, t_view
3 where t3.object_name = t_view.object_name(+)
4 and t3.object_id = 888;
Execution Plan
----------------------------------------------------------
Plan hash value: 3014823912
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 51 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 1 | 22 | 51 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | T3 | 1 | 20 | 37 (0)| 00:00:01 |
| 3 | VIEW PUSHED PREDICATE | T_VIEW | 1 | 2 | 14 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 26 | 14 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T1 | 1 | 21 | 13 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_ID1_T2 | 1 | 5 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T3"."OBJECT_ID"=888)
5 - filter("T1"."OBJECT_NAME"="T3"."OBJECT_NAME")
6 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
实际上这里如果进行merge的话，也可以实现类似的效果，如下：
SQL> select /*+merge(t_view)*/t3.object_name
2 from t3, t_view
3 where t3.object_name=t_view.object_name
4 and t3.object_id = 888;
Execution Plan
----------------------------------------------------------
Plan hash value: 2983785075
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 52 (2)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 46 | 52 (2)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 41 | 51 (2)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T3 | 1 | 20 | 37 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T1 | 2950 | 61950 | 13 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_ID1_T2 | 1 | 5 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T3"."OBJECT_NAME"="T1"."OBJECT_NAME")
3 - filter("T3"."OBJECT_ID"=888)
5 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
SQL> select /*+no_merge(t_view)*/t3.object_name
2 from t3, t_view
3 where t3.object_name=t_view.object_name
4 and t3.object_id = 888;
Execution Plan
----------------------------------------------------------
Plan hash value: 2958396757
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 | 95 (3)| 00:00:02 |
|* 1 | HASH JOIN | | 1 | 86 | 95 (3)| 00:00:02 |
|* 2 | TABLE ACCESS FULL | T3 | 1 | 20 | 37 (0)| 00:00:01 |
| 3 | VIEW | T_VIEW | 2950 | 190K| 57 (2)| 00:00:01 |
|* 4 | HASH JOIN | | 2950 | 76700 | 57 (2)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T1 | 2950 | 61950 | 13 (0)| 00:00:01 |
| 6 | INDEX FAST FULL SCAN| IDX_ID1_T2 | 51088 | 249K| 43 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T3"."OBJECT_NAME"="T_VIEW"."OBJECT_NAME")
2 - filter("T3"."OBJECT_ID"=888)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
当sql查询中出现view，实际上使用merge/no_merge和push_pred，no_push_pred我感觉有些类似。
如下一段关于merge 和 push_pred的描述：
Merge / no_merge: if you use a complex view (e.g. aggregate view, or join view) in your query, should you rewrite
the query to merge the tables in the view into a single from clause with all the other tables (merge), or should
you evaluate the view to produce a “standalone” result set and then join the result set to the remaining tables (no_merge).
Push_pred / no_push_pred: If you have a non-mergeable view (possible because of a no_merge hint) in your query, how
should you operate the join from other tables; should you create one large view result and join it once (no_push_pred)
or should you push the join predicate down into the view definition and recreate the view result set for every driving
row from another table (push_pred).
merge 就是把view展开，那么你查看执行计划时就看不到view acces patch的信息了，no_merge则相反。
所以push_pred（谓词推入）就是是说将谓词条件推入到view中。
由于要发现push pred操作，必须保证试图不能被merge，所以通常在实验观察时会同时使用no_merge hint.
细心一点我们还能发现，针对谓词推入的操作，其外部操作只能是nest loop。
到最后我们可以简单的总结下，针对如下情况，子查询是不能展开的：
1.子查询存在 ROWNUM
2.子查询存在 CUBE,ROLLUP
3.子查询存在 UNION, UNION ALL,INTERSECT、 MINUS
4.子查询存在 START WITH ,CONNECT BY 字句
通常我发现针对右外连接的情况下，oracle优化器默认也不会进行view merge操作，如下：
SQL> select t3.object_name from t3,t_view where t3.object_name=t_view.object_name(+)
2 and t3.object_id=888;
Execution Plan
----------------------------------------------------------
Plan hash value: 1962414821
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 88 | 95 (3)| 00:00:02 |
|* 1 | HASH JOIN OUTER | | 1 | 88 | 95 (3)| 00:00:02 |
|* 2 | TABLE ACCESS FULL | T3 | 1 | 22 | 37 (0)| 00:00:01 |
| 3 | VIEW | T_VIEW | 2950 | 190K| 57 (2)| 00:00:01 |
|* 4 | HASH JOIN | | 2950 | 76700 | 57 (2)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T1 | 2950 | 61950 | 13 (0)| 00:00:01 |
| 6 | INDEX FAST FULL SCAN| IDX_ID1_T2 | 51088 | 249K| 43 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T3"."OBJECT_NAME"="T_VIEW"."OBJECT_NAME"(+))
2 - filter("T3"."OBJECT_ID"=888)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
事实上针对这样的情况，强制使用merge hint也不起作用，如果不存在右外连接的情况下，是可以自动进行merge的：
SQL> select /*+merge(t_view)*/t3.object_name from t3,t_view where t3.object_name=t_view.object_name(+)
2 and t3.object_id=888;
Execution Plan
----------------------------------------------------------
Plan hash value: 1962414821
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 88 | 95 (3)| 00:00:02 |
|* 1 | HASH JOIN OUTER | | 1 | 88 | 95 (3)| 00:00:02 |
|* 2 | TABLE ACCESS FULL | T3 | 1 | 22 | 37 (0)| 00:00:01 |
| 3 | VIEW | T_VIEW | 2950 | 190K| 57 (2)| 00:00:01 |
|* 4 | HASH JOIN | | 2950 | 76700 | 57 (2)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T1 | 2950 | 61950 | 13 (0)| 00:00:01 |
| 6 | INDEX FAST FULL SCAN| IDX_ID1_T2 | 51088 | 249K| 43 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T3"."OBJECT_NAME"="T_VIEW"."OBJECT_NAME"(+))
2 - filter("T3"."OBJECT_ID"=888)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
SQL> alter system flush shared_pool;
System altered.
SQL> select t3.object_name from t3,t_view where t3.object_name=t_view.object_name
2 and t3.object_id=888;
Execution Plan
----------------------------------------------------------
Plan hash value: 2983785075
----------------------------Oracle о ------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | 52 (2)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 48 | 52 (2)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 43 | 51 (2)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T3 | 1 | 22 | 37 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T1 | 2950 | 61950 | 13 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_ID1_T2 | 1 | 5 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T3"."OBJECT_NAME"="T1"."OBJECT_NAME")
3 - filter("T3"."OBJECT_ID"=888)
5 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
该默认行为是oracle通过一个参数来进行控制的，如下：
SQL> show parameter view
NAME TYPE VALUE
———————————— ———– ——————————
_complex_view_merging boolean TRUE
_distinct_view_unnesting boolean FALSE
_partition_view_enabled boolean TRUE
_project_view_columns boolean TRUE
_push_join_union_view boolean TRUE
_push_join_union_view2 boolean TRUE
_simple_view_merging boolean TRUE
optimizer_secure_view_merging boolean TRUE
在10gR2版本中 _simple_view_merging 参数默认是true。
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle SQL优化子查询和谓词推入研究测试
本文由大师惜分飞原创分享，网址：http://www.oracleplus.net/arch/1428.html
