Optimizer Null-Aware Anti Join (文档 ID 1082123.1)
建立测试用表
DROP TABLE t_obj PURGE;
DROP TABLE t_table PURGE;
CREATE TABLE t_obj AS SELECT * FROM dba_objects WHERE owner = 'SYS';
CREATE TABLE t_table AS SELECT * FROM Dba_Tables;
INSERT INTO t_obj(object_name) VALUES(NULL);
CREATE INDEX idx_t_obj ON t_obj(object_name,1);
测试 ANTI NA
TEST >SELECT * FROM t_table a WHERE a.table_name NOT IN (SELECT b.object_name FROM t_obj b);
no rows selected
Elapsed: 00:00:03.01
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID =>NULL,CURSOR_CHILD_NO =>0,FORMAT =>'ALL ALLSTATS LAST NOTE ADVANCED -PROJECTION -ALIAS -OUTLINE'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fbaq0j9mufkta, child number 0
-------------------------------------
SELECT * FROM t_table a WHERE a.table_name NOT IN (SELECT b.object_name
FROM t_obj b)
Plan hash value: 1744432315
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 316 (100)| | 0 |00:00:02.07 | 318 | | | |
|* 1 | HASH JOIN ANTI NA | | 1 | 2836 | 1611K| 1464K| 316 (1)| 00:00:04 | 0 |00:00:02.07 | 318 | 1537K| 984K| 2454K (0)|
| 2 | TABLE ACCESS FULL | T_TABLE | 1 | 2836 | 1429K| | 32 (0)| 00:00:01 | 2900 |00:00:00.04 | 105 | | | |
| 3 | INDEX FAST FULL SCAN| IDX_T_OBJ | 1 | 40695 | 2622K| | 61 (0)| 00:00:01 | 37796 |00:00:00.46 | 213 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."TABLE_NAME"="B"."OBJECT_NAME")
Note
-----
- dynamic sampling used for this statement (level=2)
25 rows selected.
Elapsed: 00:00:00.12
注意:这儿走了HASH JOIN ANTI NA,单看这个语句估计没什么印象
我们把这个特性禁用再来看下
alter session set "_optimizer_null_aware_antijoin" = false;
SELECT * FROM t_table a WHERE a.table_name NOT IN (SELECT b.object_name FROM t_obj b);
no rows selected
Elapsed: 00:00:15.65
TEST >SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID =>NULL,CURSOR_CHILD_NO =>0,FORMAT =>'ALL ALLSTATS LAST NOTE ADVANCED -PROJECTION -ALIAS -OUTLINE'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fbaq0j9mufkta, child number 1
-------------------------------------
SELECT * FROM t_table a WHERE a.table_name NOT IN (SELECT b.object_name
FROM t_obj b)
Plan hash value: 1070706980
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 209 (100)| | 0 |00:00:15.25 | 559K|
|* 1 | FILTER | | 1 | | | | | 0 |00:00:15.25 | 559K|
| 2 | TABLE ACCESS FULL | T_TABLE | 1 | 2836 | 1429K| 32 (0)| 00:00:01 | 2900 |00:00:00.39 | 105 |
|* 3 | INDEX FAST FULL SCAN| IDX_T_OBJ | 2896 | 38660 | 2491K| 2 (0)| 00:00:01 | 2896 |00:00:13.81 | 559K|
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NULL)
3 - filter(LNNVL("B"."OBJECT_NAME"<>:B1))
Note
-----
- dynamic sampling used for this statement (level=2)
26 rows selected.
Elapsed: 00:00:00.30
可以看到plan由hash变为filter
谓词部分变为
1 - filter( IS NULL)
3 - filter(LNNVL("B"."OBJECT_NAME"<>:B1))
并且对于join列作了空值处理
执行速度也由3秒飙升到了15秒
而子查询中加了is not null,变化就不明显
改前
Elapsed: 00:00:06.76
TEST >SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID =>NULL,CURSOR_CHILD_NO =>0,FORMAT =>'ALL ALLSTATS LAST NOTE ADVANCED -PROJECTION -ALIAS -OUTLINE'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID aduntks9408tm, child number 0
-------------------------------------
SELECT * FROM t_table a WHERE a.table_name NOT IN (SELECT b.object_name
FROM t_obj b WHERE b.object_name IS NOT NULL)
Plan hash value: 2665842570
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 316 (100)| | 1884 |00:00:02.17 | 318 | | | |
|* 1 | HASH JOIN ANTI | | 1 | 2836 | 1611K| 1464K| 316 (1)| 00:00:04 | 1884 |00:00:02.17 | 318 | 1537K| 984K| 1451K (0)|
| 2 | TABLE ACCESS FULL | T_TABLE | 1 | 2836 | 1429K| | 32 (0)| 00:00:01 | 2900 |00:00:00.05 | 105 | | | |
|* 3 | INDEX FAST FULL SCAN| IDX_T_OBJ | 1 | 40695 | 2622K| | 61 (0)| 00:00:01 | 37795 |00:00:00.49 | 213 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."TABLE_NAME"="B"."OBJECT_NAME")
3 - filter("B"."OBJECT_NAME" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
26 rows selected.
Elapsed: 00:00:00.11
改后
Elapsed: 00:00:07.82
TEST >SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID =>NULL,CURSOR_CHILD_NO =>0,FORMAT =>'ALL ALLSTATS LAST NOTE ADVANCED -PROJECTION -ALIAS -OUTLINE'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID aduntks9408tm, child number 1
-------------------------------------
SELECT * FROM t_table a WHERE a.table_name NOT IN (SELECT b.object_name
FROM t_obj b WHERE b.object_name IS NOT NULL)
Plan hash value: 2665842570
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 316 (100)| | 1884 |00:00:02.34 | 318 | | | |
|* 1 | HASH JOIN ANTI | | 1 | 2836 | 1611K| 1464K| 316 (1)| 00:00:04 | 1884 |00:00:02.34 | 318 | 1537K| 984K| 2466K (0)|
| 2 | TABLE ACCESS FULL | T_TABLE | 1 | 2836 | 1429K| | 32 (0)| 00:00:01 | 2900 |00:00:00.05 | 105 | | | |
|* 3 | INDEX FAST FULL SCAN| IDX_T_OBJ | 1 | 40695 | 2622K| | 61 (0)| 00:00:01 | 37795 |00:00:00.51 | 213 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."TABLE_NAME"="B"."OBJECT_NAME")
3 - filter("B"."OBJECT_NAME" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
26 rows selected.
Elapsed: 00:00:00.20