本文共 11196 字，大约阅读时间需要 37 分钟。
[20160111]使用提示no_unnest.txt
--今天被人问及这个提示,我记忆使用这个提示,就会出现filter过滤条件.我以前遇到这种语句,一般两者提示都使用看看那个效果好.
--英文的理解有时候很烦,nest表示嵌套,no_unnest 2次取反也表示嵌套. --还是通过例子来说明问题:1.环境:
SCOTT@book> @ &r/ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionSCOTT@book> create table t11 as select rownum id,lpad(rownum,10,'t11') name from dual connect by level<=100;
Table created.SCOTT@book> create table t12 as select rownum id,lpad(rownum,100,'t12') name from dual connect by level<=1e5;
Table created.--分析表使用Method_Opt => 'FOR ALL COLUMNS SIZE 1 '.
2.测试:
select * from t11 where exists (select null from t12 where t12.id=t11.id); select * from t11 where id in (select id from t12 );--以上两者写法执行计划都是一样,实际上现在许多情况不需考虑到底还是使用in 还是exists.
Plan hash value: 3627696898
------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 432 (100)| | 100 |00:00:00.01 | 7 | | | | |* 1 | HASH JOIN SEMI | | 1 | 100 | 1900 | 432 (1)| 00:00:06 | 100 |00:00:00.01 | 7 | 1519K| 1519K| 1546K (0)| | 2 | TABLE ACCESS FULL| T11 | 1 | 100 | 1400 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 2 | | | | | 3 | TABLE ACCESS FULL| T12 | 1 | 100K| 488K| 429 (1)| 00:00:06 | 100 |00:00:00.01 | 5 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$5DA710D3 2 - SEL$5DA710D3 / T11@SEL$1 3 - SEL$5DA710D3 / T12@SEL$2 Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T12"."ID"="T11"."ID")--如果你仔细看执行计划可以发现一个细节,T12执行的是全表扫描,但是逻辑读仅仅5个,很明显全表扫描T12的逻辑读不可能能这么小,这
--个是因为前面的100条已经满足全部的输出,从A-Rows=100也可以看出问题在那里.3.如果我修改T12表,ID=100变成id=1e5+100呢?会出现什么情况呢?
SCOTT@book> update t12 set id=1e5+100 where id=100;
1 row updated.SCOTT@book> commit ;
Commit complete.Plan hash value: 3627696898
------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 432 (100)| | 99 |00:00:00.06 | 1546 | | | | |* 1 | HASH JOIN SEMI | | 1 | 100 | 1900 | 432 (1)| 00:00:06 | 99 |00:00:00.06 | 1546 | 1519K| 1519K| 1528K (0)| | 2 | TABLE ACCESS FULL| T11 | 1 | 100 | 1400 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 2 | | | | | 3 | TABLE ACCESS FULL| T12 | 1 | 100K| 488K| 429 (1)| 00:00:06 | 100K|00:00:00.02 | 1544 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$5DA710D3 2 - SEL$5DA710D3 / T11@SEL$1 3 - SEL$5DA710D3 / T12@SEL$2 Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T12"."ID"="T11"."ID")--因为这个时候T12表ID=100的记录不存在,在做HASH JOIN SEMI连接的时候必须扫描整个T12表,才知道结果.
--修改回来:
SCOTT@book> update t12 set id=100 where id=1e5+100; 1 row updated.SCOTT@book> commit ;
Commit complete.4.使用提示no_unnest看看:
select * from t11 where exists (select /*+ no_unnest */ null from t12 where t12.id=t11.id);
select * from t11 where id in (select /*+ no_unnest */ id from t12 );Plan hash value: 2069773301
--------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 21445 (100)| | 100 |00:00:00.01 | 337 | |* 1 | FILTER | | 1 | | | | | 100 |00:00:00.01 | 337 | | 2 | TABLE ACCESS FULL| T11 | 1 | 100 | 1400 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 3 | |* 3 | TABLE ACCESS FULL| T12 | 100 | 1 | 5 | 429 (1)| 00:00:06 | 100 |00:00:00.01 | 334 | --------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T11@SEL$1 3 - SEL$2 / T12@SEL$2 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( IS NOT NULL) 3 - filter("ID"=:B1)--逻辑读上升到337. 以前我总以为使用在这种情况下ID=1使用filter效果不好.实际上许多情况下要具体问题具体分析.
--以上执行计划从T11取一行,然后扫描T12时只要遇到满足执行计划的情况停止扫描,输出该行.相当于扫描100次,因为我满足记录的行靠 --前,逻辑读不是很高.5.如果我修改T12表,ID=100变成id=1e5+100呢?会出现什么情况呢?
SCOTT@book> update t12 set id=1e5+100 where id=100; 1 row updated.SCOTT@book> commit ;
Commit complete.select * from t11 where exists (select /*+ no_unnest */ null from t12 where t12.id=t11.id);
select * from t11 where id in (select /*+ no_unnest */ id from t12 );Plan hash value: 2069773301
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 21445 (100)| | 99 |00:00:00.01 | 1876 | |* 1 | FILTER | | 1 | | | | | 99 |00:00:00.01 | 1876 | | 2 | TABLE ACCESS FULL| T11 | 1 | 100 | 1400 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 3 | |* 3 | TABLE ACCESS FULL| T12 | 100 | 1 | 5 | 429 (1)| 00:00:06 | 99 |00:00:00.01 | 1873 | --------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T11@SEL$1 3 - SEL$2 / T12@SEL$2 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( IS NOT NULL) 3 - filter("T12"."ID"=:B1)--因为这个时候T12表ID=100的记录不存在,在T11的id=100时做扫描T12时必须扫描整个T12表,才知道结果.这样导致最后一次逻辑读很大.
--当然我没有建立索引,如果建立索引就没有这种情况.6.这样什么时候使用no_unnest获得好的效果呢?
--修改回来:
SCOTT@book> update t12 set id=100 where id=1e5+100; 1 row updated.SCOTT@book> commit ;
Commit complete.SCOTT@book> update t11 set id=1e5;
100 rows updated.SCOTT@book> commit ;
Commit complete.--把T11全部修改一个值1e5.
select * from t11 where exists (select /*+ no_unnest */ null from t12 where t12.id=t11.id);
select * from t11 where id in (select /*+ no_unnest */ id from t12 );Plan hash value: 2069773301
--------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 21445 (100)| | 100 |00:00:00.01 | 1546 | |* 1 | FILTER | | 1 | | | | | 100 |00:00:00.01 | 1546 | | 2 | TABLE ACCESS FULL| T11 | 1 | 100 | 1400 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 3 | |* 3 | TABLE ACCESS FULL| T12 | 1 | 1 | 5 | 429 (1)| 00:00:06 | 1 |00:00:00.01 | 1543 | --------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T11@SEL$1 3 - SEL$2 / T12@SEL$2 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( IS NOT NULL) 3 - filter("T12"."ID"=:B1)--因为T11里面id都是一样的等于1e5,可以发现仅仅扫描T12一次(Starts=1).
--如果T11的id=1这样逻辑读更小,继续测试看看.SCOTT@book> update t11 set id=1;
100 rows updated.SCOTT@book> commit ;
Commit complete.Plan hash value: 2069773301
--------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 21445 (100)| | 100 |00:00:00.01 | 6 | |* 1 | FILTER | | 1 | | | | | 100 |00:00:00.01 | 6 | | 2 | TABLE ACCESS FULL| T11 | 1 | 100 | 1400 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 3 | |* 3 | TABLE ACCESS FULL| T12 | 1 | 1 | 5 | 429 (1)| 00:00:06 | 1 |00:00:00.01 | 3 | --------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T11@SEL$1 3 - SEL$2 / T12@SEL$2 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( IS NOT NULL) 3 - filter("T12"."ID"=:B1)--可以发现在这样的情况逻辑读仅仅6个.
总结:
--希望以上的测试,能够帮助大家理解这种提示的作用.--补充最后不用提示的情况:
Plan hash value: 3627696898 ------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 432 (100)| | 100 |00:00:00.01 | 5 | | | | |* 1 | HASH JOIN SEMI | | 1 | 100 | 1900 | 432 (1)| 00:00:06 | 100 |00:00:00.01 | 5 | 1519K| 1519K| 684K (0)| | 2 | TABLE ACCESS FULL| T11 | 1 | 100 | 1400 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 2 | | | | | 3 | TABLE ACCESS FULL| T12 | 1 | 100K| 488K| 429 (1)| 00:00:06 | 1 |00:00:00.01 | 3 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$5DA710D3 2 - SEL$5DA710D3 / T11@SEL$1 3 - SEL$5DA710D3 / T12@SEL$2 Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T12"."ID"="T11"."ID")--因为T11的id值都一样,仅仅扫描T12一次,并且因为很快定位.逻辑读很小.
转载地址：http://kvjaa.baihongyu.com/