[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 Production
SCOTT@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一次,并且因为很快定位.逻辑读很小.
