如果一个查询中的where 中出现 view 或者 子查询。
那么oracle的CBO在解析着这个SQL之前要做转换,把VIEW或者子查询"打开"~ 这个操作就叫做unnest~ 然后可以把主查询和子查询中的表通过表连接的方式,生成执行计划
你可以使用NO_UNNEST这个hint来告诉CBO,不要“打开” VIEW或者子查询~ 这样VIEW或者子查询只能被当作一个独立查询,来进行解析,里面的表无法和主查询中的表进行连接~
比如:
MYDB@MYDB10G >select * from tt1 where table_name in (select table_name from tt2 where tt2.table_name='TA');
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 1526020001
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 196 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 1 | 196 | 4 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TT1 | 1 | 178 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TT2_IND | 1 | 18 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TABLE_NAME"='TA')
3 - access("TT2"."TABLE_NAME"='TA')
filter("TABLE_NAME"="TABLE_NAME")
MYDB@MYDB10G >select * from tt1 where table_name in (select /*+ no_unnest */ table_name from tt2 where tt2.table_name='TA');
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 4076403977
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 178 | 57 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| TT1 | 107 | 19046 | 3 (0)| 00:00:01 |
|* 3 | FILTER | | | | | |
|* 4 | INDEX RANGE SCAN| TT2_IND | 1 | 18 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "TT2" "TT2" WHERE
:B1='TA' AND "TABLE_NAME"=:B2))
3 - filter(:B1='TA')
4 - access("TABLE_NAME"=:B1)
[本帖最后由 zergduan 于 2010-5-5 14:56 编辑]