[20170606]11G _optimizer_null_aware_antijoin.txt
[20170606]11G _optimizer_null_aware_antijoin.txt
--//上午測試_optimizer_null_aware_antijoin,發現自己不經意間又犯了一個低級錯誤,做1個記錄.
1.環境:
SYS@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
SYS@book> @ &r/hide _optimizer_null_aware_antijoin
NAME?????????????????????????? DESCRIPTION?????????????????? DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
------------------------------ ----------------------------- ------------- ------------- ------------
_optimizer_null_aware_antijoin null-aware antijoin parameter TRUE????????? TRUE????????? TRUE
2.做一個測試例子:
SCOTT@book> create table t1 as select * from dba_objects;
Table created.
SCOTT@book> create table t2 as select * from dba_objects;
Table created.
SCOTT@book> select count(*) from t2;
? COUNT(*)
----------
???? 86996
SCOTT@book> select count(*) from t1;
? COUNT(*)
----------
???? 86995
--//分析表略.
--//因為T1,T2建立有先后,這樣T2的記錄數量比T1記錄的數量多1.注意1個特點允許NULL(這樣建立的表),如果你使用all_objects來建立就情況不同了:
--//你可以查看定義desc all_objects, desc dba_objects.
SCOTT@book> @ &r/desc t1
?? Name??????????? Null? Type
?? --------------- ----- --------------
?1 OWNER???????????????? VARCHAR2(30)
?2 OBJECT_NAME?????????? VARCHAR2(128)
?3 SUBOBJECT_NAME??????? VARCHAR2(30)
?4 OBJECT_ID???????????? NUMBER
?5 DATA_OBJECT_ID??????? NUMBER
?6 OBJECT_TYPE?????????? VARCHAR2(19)
?7 CREATED?????????????? DATE
?8 LAST_DDL_TIME???????? DATE
?9 TIMESTAMP???????????? VARCHAR2(19)
10 STATUS??????????????? VARCHAR2(7)
11 TEMPORARY???????????? VARCHAR2(1)
12 GENERATED???????????? VARCHAR2(1)
13 SECONDARY???????????? VARCHAR2(1)
14 NAMESPACE???????????? NUMBER
15 EDITION_NAME????????? VARCHAR2(30)
SCOTT@book> select * from t1 where object_name='T2';
no rows selected
SCOTT@book> select * from t2 where object_name='T2';
OWNER? OBJECT_NAME SUBOBJECT_? OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED???????????? LAST_DDL_TIME?????? TIMESTAMP?????????? STATUS? T G S? NAMESPACE EDITION_NAME
------ ----------- ---------- ---------- -------------- ----------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
SCOTT? T2????????????????????????? 90863????????? 90863 TABLE?????? 2017-06-06 16:12:27 2017-06-06 16:12:27 2017-06-06:16:12:27 VALID?? N N N????????? 1
SCOTT@book> select? * from t1 where OBJECT_ID=90863;
no rows selected
3.測試:
SCOTT@book> alter session set statistics_level=all;
Session altered.
SCOTT@book> select count(*) from t2 where object_id not in(select object_id from t1);
? COUNT(*)
----------
???????? 0
SCOTT@book> select * from t2 where object_id not in(select object_id from t1);
no rows selected
--//why? 理論應該返回1行啊.
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID? 45ppus55apd1z, child number 1
-------------------------------------
select * from t2 where object_id not in(select object_id from t1)
Plan hash value: 35395643
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id? | Operation?????????????? | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time?? | A-Rows |?? A-Time?? | Buffers | Reads? |? OMem |? 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT??????? |????? |????? 1 |??????? |?????? |?????? |? 1218 (100)|????????? |????? 0 |00:00:00.06 |??? 1244 |?? 1242 |?????? |?????? |????????? |
|*? 1 |? HASH JOIN RIGHT ANTI NA|????? |????? 1 |??? 870 | 89610 |? 1448K|? 1218?? (1)| 00:00:15 |????? 0 |00:00:00.06 |??? 1244 |?? 1242 |? 1696K|? 1696K| 2503K (0)|
|?? 2 |?? TABLE ACCESS FULL???? | T1?? |????? 1 |? 86995 |?? 424K|?????? |?? 347?? (1)| 00:00:05 |? 86994 |00:00:00.02 |??? 1244 |?? 1242 |?????? |?????? |????????? |
|?? 3 |?? TABLE ACCESS FULL???? | T2?? |????? 0 |? 86996 |? 8325K|?????? |?? 347?? (1)| 00:00:05 |????? 0 |00:00:00.01 |?????? 0 |????? 0 |?????? |?????? |????????? |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
?? 1 - SEL$5DA710D3
?? 2 - SEL$5DA710D3 / T1@SEL$2
?? 3 - SEL$5DA710D3 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("OBJECT_ID"="OBJECT_ID")
27 rows selected.
--//這才想起來以前的一個錯誤,不能這樣查詢,因為IN中的記錄存在NULL,這樣NULL<>NULL.應該改寫如下:
SCOTT@book> select * from t2 where object_id not in(select object_id from t1 where t1.object_id is not null );
OWNER? OBJECT_NAME????????? SUBOBJECT_? OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE???????? CREATED???????????? LAST_DDL_TIME?????? TIMESTAMP?????????? STATUS? T G S? NAMESPACE EDITION_NAME
------ -------------------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
SCOTT? T2?????????????????????????????????? 90863????????? 90863 TABLE?????????????? 2017-06-06 16:12:27 2017-06-06 16:12:27 2017-06-06:16:12:27 VALID?? N N N????????? 1
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID? 9cn6amuwk12r4, child number 0
-------------------------------------
select * from t2 where object_id not in(select object_id from t1 where
t1.object_id is not null )
Plan hash value: 2323664790
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id? | Operation??????????????? | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time?? | A-Rows |?? A-Time?? | Buffers | Reads? |? OMem |? 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT???????? |????? |????? 1 |??????? |?????? |?????? |? 1218 (100)|????????? |????? 1 |00:00:00.15 |??? 2489 |?? 2484 |?????? |?????? |????????? |
|*? 1 |? HASH JOIN RIGHT ANTI SNA|????? |????? 1 |??? 870 | 89610 |? 1448K|? 1218?? (1)| 00:00:15 |????? 1 |00:00:00.15 |??? 2489 |?? 2484 |? 5416K|? 2890K| 5574K (0)|
|*? 2 |?? TABLE ACCESS FULL????? | T1?? |????? 1 |? 86993 |?? 424K|?????? |?? 347?? (1)| 00:00:05 |? 86993 |00:00:00.02 |??? 1244 |?? 1242 |?????? |?????? |????????? |
|?? 3 |?? TABLE ACCESS FULL????? | T2?? |????? 1 |? 86996 |? 8325K|?????? |?? 347?? (1)| 00:00:05 |? 86996 |00:00:00.03 |??? 1245 |?? 1242 |?????? |?????? |????????? |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
?? 1 - SEL$5DA710D3
?? 2 - SEL$5DA710D3 / T1@SEL$2
?? 3 - SEL$5DA710D3 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("OBJECT_ID"="OBJECT_ID")
?? 2 - filter("T1"."OBJECT_ID" IS NOT NULL)
--//注意執行計劃中的HASH JOIN RIGHT ANTI SNA.
4.測試"_optimizer_null_aware_antijoin"=false看看.
SCOTT@book> alter session set "_optimizer_null_aware_antijoin"=false;
Session altered.
SCOTT@book> Select * from t2 where object_id not in(select object_id from t1 where t1.object_id is not null );
OWNER? OBJECT_NAME????????? SUBOBJECT_? OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE???????? CREATED???????????? LAST_DDL_TIME?????? TIMESTAMP?????????? STATUS? T G S? NAMESPACE EDITION_NAME
------ -------------------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
SCOTT? T2?????????????????????????????????? 90863????????? 90863 TABLE?????????????? 2017-06-06 16:12:27 2017-06-06 16:12:27 2017-06-06:16:12:27 VALID?? N N N????????? 1
--//昏,那個慢受不了.......
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID? 4ps64yjz4g7pu, child number 0
-------------------------------------
Select * from t2 where object_id not in(select object_id from t1 where
t1.object_id is not null )
Plan hash value: 2132554994
------------------------------------------------------------------------------------------------------------------------------
| Id? | Operation????????? | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time?? | A-Rows |?? A-Time?? | Buffers | Reads? |
------------------------------------------------------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |????? 1 |??????? |?????? |? 8847K(100)|????????? |????? 1 |00:04:06.56 |????? 53M|???? 67M|
|*? 1 |? FILTER??????????? |????? |????? 1 |??????? |?????? |??????????? |????????? |????? 1 |00:04:06.56 |????? 53M|???? 67M|
|?? 2 |?? TABLE ACCESS FULL| T2?? |????? 1 |? 86996 |? 8325K|?? 347?? (1)| 00:00:05 |? 86996 |00:00:00.05 |??? 1245 |?? 1242 |
|*? 3 |?? TABLE ACCESS FULL| T1?? |? 86995 |????? 2 |??? 10 |?? 116?? (0)| 00:00:02 |? 86994 |00:04:05.95 |????? 53M|???? 67M|
------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
?? 1 - SEL$1
?? 2 - SEL$1 / T2@SEL$1
?? 3 - SEL$2 / T1@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - filter( IS NULL)
?? 3 - filter(("T1"."OBJECT_ID" IS NOT NULL AND LNNVL("OBJECT_ID"<>:B1)))
29 rows selected.
3.換1個方式:
SCOTT@book> create table t11 as select * from all_objects;
Table created.
SCOTT@book> create table t22 as select * from all_objects;
Table created.
--//分析表略.
SCOTT@book> @ &r/desc t11
Name?????????? Null???? Type
-------------- -------- ------------
OWNER????????? NOT NULL VARCHAR2(30)
OBJECT_NAME??? NOT NULL VARCHAR2(30)
SUBOBJECT_NAME????????? VARCHAR2(30)
OBJECT_ID????? NOT NULL NUMBER
DATA_OBJECT_ID????????? NUMBER
OBJECT_TYPE???????????? VARCHAR2(19)
CREATED??????? NOT NULL DATE
LAST_DDL_TIME? NOT NULL DATE
TIMESTAMP?????????????? VARCHAR2(19)
STATUS????????????????? VARCHAR2(7)
TEMPORARY?????????????? VARCHAR2(1)
GENERATED?????????????? VARCHAR2(1)
SECONDARY?????????????? VARCHAR2(1)
NAMESPACE????? NOT NULL NUMBER
EDITION_NAME??????????? VARCHAR2(30)
--// 注意這樣定義的表OBJECT_ID是非空.
SCOTT@book> select * from t22 where object_id not in(select object_id from t11);
OWNER? OBJECT_NAME????????? SUBOBJECT_? OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE???????? CREATED???????????? LAST_DDL_TIME?????? TIMESTAMP?????????? STATUS? T G S? NAMESPACE EDITION_NAME
------ -------------------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
SCOTT? T22????????????????????????????????? 90869????????? 90869 TABLE?????????????? 2017-06-06 16:33:29 2017-06-06 16:33:29 2017-06-06:16:33:29 VALID?? N N N????????? 1
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID? 31yq53mnd3g7k, child number 1
-------------------------------------
select * from t22 where object_id not in(select object_id from t11)
Plan hash value: 3941821364
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id? | Operation??????????? | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time?? | A-Rows |?? A-Time?? | Buffers | Reads? |? OMem |? 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT???? |????? |????? 1 |??????? |?????? |?????? |? 1187 (100)|????????? |????? 1 |00:00:00.15 |??? 2427 |?? 2422 |?????? |?????? |????????? |
|*? 1 |? HASH JOIN RIGHT ANTI|????? |????? 1 |??? 848 | 87344 |? 1408K|? 1187?? (1)| 00:00:15 |????? 1 |00:00:00.15 |??? 2427 |?? 2422 |? 5236K|? 2890K| 5538K (0)|
|?? 2 |?? TABLE ACCESS FULL? | T11? |????? 1 |? 84763 |?? 413K|?????? |?? 338?? (1)| 00:00:05 |? 84763 |00:00:00.02 |??? 1213 |?? 1211 |?????? |?????? |????????? |
|?? 3 |?? TABLE ACCESS FULL? | T22? |????? 1 |? 84764 |? 8112K|?????? |?? 338?? (1)| 00:00:05 |? 84764 |00:00:00.03 |??? 1214 |?? 1211 |?????? |?????? |????????? |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
?? 1 - SEL$5DA710D3
?? 2 - SEL$5DA710D3 / T11@SEL$2
?? 3 - SEL$5DA710D3 / T22@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("OBJECT_ID"="OBJECT_ID")
--//注意執行計劃HASH JOIN RIGHT ANTI.我想起來以前10g我也遇到類型問題,將數據類型修改為NULL.
4.退出測試使用not exists的情況:
SCOTT@book> select * from t2 where not exists? (select 1 from t1? where t1.OBJECT_ID=t2.object_id);
OWNER? OBJECT_NAME SUBOBJECT_? OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE???????? CREATED???????????? LAST_DDL_TIME?????? TIMESTAMP?????????? STATUS? T G S? NAMESPACE EDITION_NAME
------ ----------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
SCOTT? T2????????????????????????? 90863????????? 90863 TABLE?????????????? 2017-06-06 16:12:27 2017-06-06 16:12:27 2017-06-06:16:12:27 VALID?? N N N????????? 1
PUBLIC LOOPBACK???????????????????????????????????????? DATABASE LINK?????? 2016-12-14 15:17:00???????????????????????????????????????? VALID?? N N N
PUBLIC TEST040????????????????????????????????????????? DATABASE LINK?????? 2017-01-09 09:14:26???????????????????????????????????????? VALID?? N N N
--//這樣返回3條.也就是這樣查詢注意空值的情況.也就是要加入t2.object_id is not null 條件,才會返回1條.
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID? 4jx0g5ndct9vk, child number 0
-------------------------------------
select * from t2 where not exists? (select 1 from t1? where
t1.OBJECT_ID=t2.object_id)
Plan hash value: 1142061071
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id? | Operation??????????? | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time?? | A-Rows |?? A-Time?? | Buffers | Reads? |? OMem |? 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT???? |????? |????? 1 |??????? |?????? |?????? |? 1218 (100)|????????? |????? 3 |00:00:00.14 |??? 2489 |?? 2484 |?????? |?????? |????????? |
|*? 1 |? HASH JOIN RIGHT ANTI|????? |????? 1 |??? 870 | 89610 |? 1448K|? 1218?? (1)| 00:00:15 |????? 3 |00:00:00.14 |??? 2489 |?? 2484 |? 5416K|? 2890K| 5759K (0)|
|?? 2 |?? TABLE ACCESS FULL? | T1?? |????? 1 |? 86995 |?? 424K|?????? |?? 347?? (1)| 00:00:05 |? 86995 |00:00:00.02 |??? 1244 |?? 1242 |?????? |?????? |????????? |
|?? 3 |?? TABLE ACCESS FULL? | T2?? |????? 1 |? 86996 |? 8325K|?????? |?? 347?? (1)| 00:00:05 |? 86996 |00:00:00.02 |??? 1245 |?? 1242 |?????? |?????? |????????? |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
?? 1 - SEL$5DA710D3
?? 2 - SEL$5DA710D3 / T1@SEL$2
?? 3 - SEL$5DA710D3 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
SCOTT@book> select * from t1 where object_id is null ;
OWNER? OBJECT_NAME????????? SUBOBJECT_? OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE???????? CREATED???????????? LAST_DDL_TIME?????? TIMESTAMP?????????? STATUS? T G S? NAMESPACE EDITION_NAME
------ -------------------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
PUBLIC LOOPBACK????????????????????????????????????????????????? DATABASE LINK?????? 2016-12-14 15:17:00???????????????????????????????????????? VALID?? N N N
PUBLIC TEST040?????????????????????????????????????????????????? DATABASE LINK?????? 2017-01-09 09:14:26???????????????????????????????????????? VALID?? N N N
--//正好2條object_id is null.
5.總結:
1.再次注意not in 與NULL的查詢.
2.建立良好的約束,規避一些這樣的問題.
轉載于:https://www.cnblogs.com/lfree/p/6952574.html
《新程序員》：云原生和全面數字化實踐50位技術專家共同創作，文字、視頻、音頻交互閱讀總結
以上是生活随笔為你收集整理的[20170606]11G _optimizer_null_aware_antijoin.txt的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 部署ajax服务-支持jsonp
- 下一篇： 洛谷P1071 潜伏者 字符串