about subquery unnest/push pred
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: about subquery unnest/push pred
落落的sql优化班级确实牛叉，我也试听了1次，学不到不少东西，如果大家有兴趣，可以加他的试听QQ群：179221471
今天抽空做了下关于子查询和谓词推入的实验，我这里通过vm 10gR2环境来模拟展示。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
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了，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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 等函数
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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等
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
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) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
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') |
我们再来看下试图合并，首先我们创建几个测试表。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
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进行强制推进下。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
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") |
|
1 |
实际上这里如果进行merge的话，也可以实现类似的效果，如下： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
<pre class="brush:sql">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操作，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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的：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
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 ---------------------------------------------------------------------------------- | 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。
One Response to “about subquery unnest/push pred”
不错 写的很好
Leave a Reply
You must be logged in to post a comment.