Oracle执行计划filter下多个节点的优化
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle执行计划filter下多个节点的优化，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含53939字，纯文字阅读大概需要78分钟。
内容图文
Oracle执行计划filter下多个节点的优化
FILTER操作是执行计划中常见的操作，这种操作有两种情况：
l 只有一个子节点，那么就是简单过滤操作。
l 有多个子节点，那么就是类似NESTED LOOPS操作，只不过与NESTED LOOPS差别在于，FILTER内部会构建HASH表，对于重复匹配的，不会再次进行循环查找，而是利用已有结果，提高效率。但是一旦重复匹配的较少，循环次数多，那么，FILTER操作将是严重影响性能的操作，可能你的SQL几天都执行不完了。
真题1、执行计划里的access和filter有什么区别？
答案：如下所示：
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."EMPNO"="B"."MGR")
filter("A"."EMPNO"="B"."MGR")
5 - filter("B"."MGR" IS NOT NULL)
一般而言，access表示这个谓词条件的值将会影响数据的访问路径（表还是索引）；filter表示谓词条件的值不会影响数据的访问路劲，只起到过滤的作用。NOT IN或MIN函数等容易产生filter操作。
对于filter而言如果只有一个子节点，那么就是简单过滤操作（独立操作）。如果有两个或更多子节点，那么就是类似Nested Loops操作，只不过与Nested Loops差别在于，filter内部会构建HASH表，对于重复匹配的，不会再次进行循环查找，而是利用已有结果，提高效率。但是一旦重复匹配的较少，循环次数多，那么，filter操作将是严重影响性能的操作，可能会导致目标SQL几天都执行不完。
下面看看各种情况下的FILTER操作：
（一）单子节点：
LHR@orclasm > set autot on
LHR@orclasm > SELECT T.JOB, COUNT(1) FROM SCOTT.EMP T GROUP BY T.JOB HAVING COUNT(1)>3;
JOB COUNT(1)
--------- ----------
CLERK 4
SALESMAN 4
Execution Plan
----------------------------------------------------------
Plan hash value: 2138686577
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 4 (25)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 8 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 112 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(COUNT(*)>3)
Statistics
----------------------------------------------------------
25 recursive calls
4 db block gets
6 consistent gets
0 physical reads
1544 redo size
660 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
很显然ID1的filter操作只有一个子节点ID2，在这种情况下的filter操作也就是单纯的过滤操作。
（二）多子节点：
filter多子节点往往就是性能杀手，主要出现在子查询无法UNNEST查询转换，经常遇到的情况就是NOT IN子查询、子查询和OR连用、复杂子查询等情况。
避免使用复杂的集合函数，像NOT IN等。通常，要避免在索引列上使用NOT，NOT会产生和在索引列上使用函数相同的影响。当Oracle遇到NOT操作符时，它就会停止使用索引转而执行全表扫描。很多时候用EXISTS和NOT EXISTS代替IN和NOT IN语句是一个好的选择。需要注意的是，在Oracle 11g之前，若NOT IN的列没有指定非空的话（注意：是主表和子表的列未同时有NOT NULL约束，或都未加IS NOT NULL限制），则NOT IN选择的是filter操作（如果指定了非空，那么会选择ANTI的反连接），但是从Oracle 11g开始有新的ANTI NA（NULL AWARE）优化，可以对子查询进行UNNEST，NOT IN和NOT EXISTS都选择的是ANTI的反连接，所以效率是一样的。在一般情况下，ANTI的反连接算法比filter更高效。对于未UNNEST的子查询，若选择了filter操作，则至少有两个子节点，执行计划还有个特点就是Predicate谓词部分有“:B1”这种类似绑定变量的内容，内部操作走类似Nested Loops操作。如果在Oracle 11g之前，遇到NOT IN无法UNNEST，那么可以将NOT IN部分的匹配条件均设为NOT NULL约束。若不添加NOT NULL约束，则需要两个条件均增加IS NOT NULL条件。当然也可以将NOT IN修改为NOT EXISTS。
分别在Oracle 10g和Oracle 11g实验：
SELECT * FROM V$VERSION;
DROP TABLE EMP PURGE;
DROP TABLE DEPT PURGE;
CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;
CREATE TABLE DEPT AS SELECT * FROM SCOTT.DEPT;
SET TIMING ON
SET LINESIZE 1000
SET AUTOTRACE TRACEONLY
--写法1
SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);
--写法2
SELECT * FROM DEPT WHERE NOT EXISTS (SELECT DEPTNO FROM EMP WHERE EMP.DEPTNO=DEPT.DEPTNO);
--写法3
SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP WHERE DEPTNO IS NOT NULL) AND DEPTNO IS NOT NULL;
--写法4
SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP) AND DEPTNO IS NOT NULL;
--写法5
SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP WHERE DEPTNO IS NOT NULL);
其结果如下表所示：
版本 | SQL语句 | 操作 | 逻辑读 | 执行计划 |
Oracle 10g | SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP); | FILTER | 15 | |-------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |-------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 30 | 4 (0)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 2 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| EMP | 13 | 169 | 2 (0)| 00:00:01 | |-------------------------------------------------------------------------- |
SELECT * FROM DEPT WHERE NOT EXISTS (SELECT DEPTNO FROM EMP WHERE EMP.DEPTNO=DEPT.DEPTNO); | HASH JOIN ANTI | 6 | |--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |--------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 43 | 5 (20)| 00:00:01 | |* 1 | HASH JOIN ANTI | | 1 | 43 | 5 (20)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 2 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 2 (0)| 00:00:01 | |--------------------------------------------------------------------------- | |
SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP WHERE DEPTNO IS NOT NULL) AND DEPTNO IS NOT NULL; | HASH JOIN ANTI | 6 | |--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |--------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 43 | 5 (20)| 00:00:01 | |* 1 | HASH JOIN ANTI | | 1 | 43 | 5 (20)| 00:00:01 | |* 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 2 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 2 (0)| 00:00:01 | |--------------------------------------------------------------------------- | |
SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP) AND DEPTNO IS NOT NULL; | FILTER | 15 | |--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |--------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 60 | 6 (0)| 00:00:01 | |* 1 | FILTER | | | | | | |* 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 2 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| EMP | 13 | 169 | 2 (0)| 00:00:01 | |--------------------------------------------------------------------------- | |
SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP WHERE DEPTNO IS NOT NULL); | FILTER | 15 | |--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |--------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 30 | 4 (0)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 2 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| EMP | 13 | 169 | 2 (0)| 00:00:01 | |--------------------------------------------------------------------------- | |
Oracle 11g | SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP); | HASH JOIN ANTI NA | 6 | |--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |--------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 172 | 5 (20)| 00:00:01 | |* 1 | HASH JOIN ANTI NA | | 4 | 172 | 5 (20)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 2 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP | 12 | 156 | 2 (0)| 00:00:01 | |--------------------------------------------------------------------------- |
SELECT * FROM DEPT WHERE NOT EXISTS (SELECT DEPTNO FROM EMP WHERE EMP.DEPTNO=DEPT.DEPTNO); | HASH JOIN ANTI | 6 | |--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |--------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 172 | 5 (20)| 00:00:01 | |* 1 | HASH JOIN ANTI | | 4 | 172 | 5 (20)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 2 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP | 12 | 156 | 2 (0)| 00:00:01 | |--------------------------------------------------------------------------- | |
SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP WHERE DEPTNO IS NOT NULL) AND DEPTNO IS NOT NULL; | HASH JOIN ANTI | 6 | |--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |--------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 172 | 5 (20)| 00:00:01 | |* 1 | HASH JOIN ANTI | | 4 | 172 | 5 (20)| 00:00:01 | |* 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 2 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| EMP | 12 | 156 | 2 (0)| 00:00:01 | |--------------------------------------------------------------------------- | |
SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP) AND DEPTNO IS NOT NULL; | HASH JOIN ANTI SNA | 6 | |--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |--------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 172 | 5 (20)| 00:00:01 | |* 1 | HASH JOIN ANTI SNA| | 4 | 172 | 5 (20)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 2 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| EMP | 12 | 156 | 2 (0)| 00:00:01 | |--------------------------------------------------------------------------- | |
SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP WHERE DEPTNO IS NOT NULL); | HASH JOIN ANTI NA | 6 | |--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |--------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 172 | 5 (20)| 00:00:01 | |* 1 | HASH JOIN ANTI NA | | 4 | 172 | 5 (20)| 00:00:01 | |* 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 2 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP | 12 | 156 | 2 (0)| 00:00:01 | |--------------------------------------------------------------------------- |
看一下详细执行计划：
SELECT * FROM V$VERSION;
DROP TABLE EMP PURGE;
DROP TABLE DEPT PURGE;
CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;
CREATE TABLE DEPT AS SELECT * FROM SCOTT.DEPT;
SET TIMING ON
SET LINESIZE 1000
SET AUTOTRACE TRACEONLY
--写法1
SELECT /*+optimizer_features_enable('10.2.0.5')*/ * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);
SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);
LHR@orclasm > SELECT /*+optimizer_features_enable('10.2.0.5')*/ * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Execution Plan
----------------------------------------------------------
Plan hash value: 3547749009
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 5 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 13 | 169 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT 0 FROM "EMP" "EMP" WHERE
LNNVL("DEPTNO"<>:B1)))
3 - filter(LNNVL("DEPTNO"<>:B1))
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
31 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LHR@orclasm > SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Execution Plan
----------------------------------------------------------
Plan hash value: 2100826622
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 172 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN ANTI NA | | 4 | 172 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
针对上面的NOT IN子查询，如果子查询中的DEPTNO有NULL存在，那么整个查询都不会有结果，在Oracle 11g之前，如果主表和子表的DEPTNO未同时有NOT NULL约束，或都未加IS NOT NULL限制，那么Oracle会选择filter。从Oracle 11g开始有新的ANTI NA（NULL AWARE）优化，可以对子查询进行UNNEST，从而提高效率。对于未UNNEST的子查询，若选择了FILTER操作，则至少有两个子节点，执行计划还有个特点就是Predicate谓词部分有“:B1”这种类似绑定变量的内容，内部操作走类似Nested Loops操作。
如下所示：
LHR@orclasm > SELECT /*+rule gather_plan_statistics*/ *
2 FROM SCOTT.EMP
3 WHERE NOT EXISTS (SELECT 0
4 FROM SCOTT.DEPT
5 WHERE DEPT.DNAME = 'SALES'
6 AND DEPT.DEPTNO = EMP.DEPTNO)
7 AND NOT EXISTS
8 (SELECT 0 FROM SCOTT.BONUS WHERE BONUS.ENAME = EMP.ENAME);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
8 rows selected.
LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID b8w1s38hqtjkj, child number 0
-------------------------------------
SELECT /*+rule gather_plan_statistics*/ * FROM SCOTT.EMP WHERE NOT
EXISTS (SELECT 0 FROM SCOTT.DEPT WHERE DEPT.DNAME =
'SALES' AND DEPT.DEPTNO = EMP.DEPTNO) AND NOT EXISTS
(SELECT 0 FROM SCOTT.BONUS WHERE BONUS.ENAME = EMP.ENAME)
Plan hash value: 1445856646
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 |00:00:00.01 | 14 |
|* 1 | FILTER | | 1 | 8 |00:00:00.01 | 14 |
| 2 | TABLE ACCESS FULL | EMP | 1 | 14 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 3 | 1 |00:00:00.01 | 6 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 3 | 3 |00:00:00.01 | 3 |
|* 5 | TABLE ACCESS FULL | BONUS | 8 | 0 |00:00:00.01 | 0 |
-----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / EMP@SEL$1
3 - SEL$2 / DEPT@SEL$2
4 - SEL$2 / DEPT@SEL$2
5 - SEL$3 / BONUS@SEL$3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
RBO_OUTLINE
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "EMP"@"SEL$1")
FULL(@"SEL$3" "BONUS"@"SEL$3")
INDEX_RS_ASC(@"SEL$2" "DEPT"@"SEL$2" ("DEPT"."DEPTNO"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(( IS NULL AND IS NULL))
3 - filter("DEPT"."DNAME"='SALES')
4 - access("DEPT"."DEPTNO"=:B1)
5 - filter("BONUS"."ENAME"=:B1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
"EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7],
"EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
2 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
"EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7],
"EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
4 - "DEPT".ROWID[ROWID,10]
Note
-----
- rule based optimizer used (consider using cbo)
70 rows selected.
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 |00:00:00.01 | 14 |
|* 1 | FILTER | | 1 | 8 |00:00:00.01 | 14 |
| 2 | TABLE ACCESS FULL | EMP | 1 | 14 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 3 | 1 |00:00:00.01 | 6 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 3 | 3 |00:00:00.01 | 3 |
|* 5 | TABLE ACCESS FULL | BONUS | 8 | 0 |00:00:00.01 | 0 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(( IS NULL AND IS NULL))
3 - filter("DEPT"."DNAME"='SALES')
4 - access("DEPT"."DEPTNO"=:B1)
5 - filter("BONUS"."ENAME"=:B1)
该执行计划的执行顺序为：
① ID1有3个子节点ID2、ID3、ID5，由于ID2最小，故先执行ID2；
② ID2对EMP表进行全表扫描，将返回14行给ID1；
③ 在相关组合中ID2应当控制ID3和ID5的执行，由于Oracle此处对Distinct Value做了优化，所以ID3只执行了3次。
④ ID4执行3次，并返回3个RWOID到ID3；
⑤ ID3使用ID4返回的3个ROWID来访问数据表块，过滤“filter("DEPT"."DNAME"='SALES')”的数据，由于是NOT EXISTS，所以这导致ID1原来获得的14行排除6行的“"DEPT"."DNAME"='SALES'”，只剩下8行，这8行数据影响了ID5的执行次数，将执行8次，其中“filter("BONUS"."ENAME"=:B1)”过滤条件的“:B1”由ID1的8行数据提供，ID5没有返回数据，所以那8行没有减少ID1将8行彻底过滤的数据返回给客户端。
filter多子节点往往就是性能杀手，主要出现在子查询无法UNNEST查询转换，经常遇到的情况就是NOT IN子查询、子查询和OR连用、复杂子查询等情况。
DROP TABLE T_20170703_LHR_01 PURGE;
DROP TABLE T_20170703_LHR_02 PURGE;
DROP TABLE T_20170703_LHR_03 PURGE;
CREATE TABLE T_20170703_LHR_01 AS SELECT OBJECT_ID,OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS;
CREATE TABLE T_20170703_LHR_02 AS SELECT OBJECT_ID,OBJECT_NAME FROM USER_OBJECTS;
CREATE TABLE T_20170703_LHR_03 AS SELECT OBJECT_ID,OBJECT_NAME FROM USER_OBJECTS WHERE ROWNUM <=1000;
CREATE INDEX IND_T1_OBJ_ID_1 ON T_20170703_LHR_01(OBJECT_ID);
CREATE INDEX IND_T2_OBJ_ID_1 ON T_20170703_LHR_02(OBJECT_ID);
CREATE INDEX IND_T3_OBJ_ID_1 ON T_20170703_LHR_03(OBJECT_ID);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'t_20170703_LHR_01');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'t_20170703_LHR_02');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'t_20170703_LHR_03');
ALTER SESSION SET STATISTICS_LEVEL=ALL;
SELECT COUNT(1)
FROM T_20170703_LHR_01 T1
WHERE T1.OBJECT_TYPE = 'TABLE'
OR EXISTS (SELECT 1 FROM T_20170703_LHR_02 T2 WHERE T1.OBJECT_ID = T2.OBJECT_ID)
OR EXISTS (SELECT 1 FROM T_20170703_LHR_03 T3 WHERE T1.OBJECT_ID = T3.OBJECT_ID);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'allstats last'));
SELECT COUNT(1)
FROM (SELECT T1.*
FROM T_20170703_LHR_01 T1
WHERE T1.OBJECT_TYPE = 'TABLE'
UNION ALL (SELECT T1.*
FROM T_20170703_LHR_01 T1, T_20170703_LHR_02 T2
WHERE T1.OBJECT_ID = T2.OBJECT_ID
AND T1.OBJECT_TYPE <> 'TABLE'
UNION
SELECT T1.*
FROM T_20170703_LHR_01 T1, T_20170703_LHR_03 T3
WHERE T1.OBJECT_ID = T3.OBJECT_ID
AND T1.OBJECT_TYPE <> 'TABLE'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'allstats last'));
WITH TMP_T1 AS
(SELECT T1.* FROM T_20170703_LHR_01 T1)
SELECT COUNT(1)
FROM (SELECT T1.*
FROM TMP_T1 T1
WHERE T1.OBJECT_TYPE = 'TABLE'
UNION ALL (SELECT T1.*
FROM TMP_T1 T1, T_20170703_LHR_02 T2
WHERE T1.OBJECT_ID = T2.OBJECT_ID
AND T1.OBJECT_TYPE <> 'TABLE'
UNION
SELECT T1.*
FROM TMP_T1 T1, T_20170703_LHR_03 T3
WHERE T1.OBJECT_ID = T3.OBJECT_ID
AND T1.OBJECT_TYPE <> 'TABLE'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'allstats last'));
具体执行计划：
LHR@orclasm > ALTER SESSION SET STATISTICS_LEVEL=ALL;
Session altered.
LHR@orclasm > SELECT COUNT(1)
2 FROM T_20170703_LHR_01 T1
3 WHERE T1.OBJECT_TYPE = 'TABLE'
4 OR EXISTS (SELECT 1 FROM T_20170703_LHR_02 T2 WHERE T1.OBJECT_ID = T2.OBJECT_ID)
5 OR EXISTS (SELECT 1 FROM T_20170703_LHR_03 T3 WHERE T1.OBJECT_ID = T3.OBJECT_ID);
COUNT(1)
----------
5060
LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5894cbw5v4mpj, child number 0
-------------------------------------
SELECT COUNT(1) FROM T_20170703_LHR_01 T1 WHERE T1.OBJECT_TYPE =
'TABLE' OR EXISTS (SELECT 1 FROM T_20170703_LHR_02 T2 WHERE
T1.OBJECT_ID = T2.OBJECT_ID) OR EXISTS (SELECT 1 FROM
T_20170703_LHR_03 T3 WHERE T1.OBJECT_ID = T3.OBJECT_ID)
Plan hash value: 1566256780
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.17 | 149K|
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.17 | 149K|
|* 2 | FILTER | | 1 | | 5060 |00:00:00.17 | 149K|
| 3 | TABLE ACCESS FULL| T_20170703_LHR_01 | 1 | 78271 | 78271 |00:00:00.01 | 469 |
|* 4 | INDEX RANGE SCAN | IND_T2_OBJ_ID_1 | 74941 | 1 | 1741 |00:00:00.05 | 75356 |
|* 5 | INDEX RANGE SCAN | IND_T3_OBJ_ID_1 | 73200 | 1 | 0 |00:00:00.05 | 73308 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("T1"."OBJECT_TYPE"='TABLE' OR IS NOT NULL OR IS NOT NULL))
4 - access("T2"."OBJECT_ID"=:B1)
5 - access("T3"."OBJECT_ID"=:B1)
27 rows selected.
LHR@orclasm >
LHR@orclasm >
LHR@orclasm > SELECT COUNT(1)
2 FROM (SELECT T1.*
3 FROM T_20170703_LHR_01 T1
4 WHERE T1.OBJECT_TYPE = 'TABLE'
5 UNION ALL (SELECT T1.*
6 FROM T_20170703_LHR_01 T1, T_20170703_LHR_02 T2
7 WHERE T1.OBJECT_ID = T2.OBJECT_ID
8 AND T1.OBJECT_TYPE <> 'TABLE'
9 UNION
10 SELECT T1.*
11 FROM T_20170703_LHR_01 T1, T_20170703_LHR_03 T3
12 WHERE T1.OBJECT_ID = T3.OBJECT_ID
13 AND T1.OBJECT_TYPE <> 'TABLE'));
COUNT(1)
----------
5060
LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5n0xpnt0gzb0d, child number 0
-------------------------------------
SELECT COUNT(1) FROM (SELECT T1.* FROM T_20170703_LHR_01 T1
WHERE T1.OBJECT_TYPE = 'TABLE' UNION ALL (SELECT T1.*
FROM T_20170703_LHR_01 T1, T_20170703_LHR_02 T2
WHERE T1.OBJECT_ID = T2.OBJECT_ID
AND T1.OBJECT_TYPE <> 'TABLE' UNION
SELECT T1.* FROM T_20170703_LHR_01 T1,
T_20170703_LHR_03 T3 WHERE T1.OBJECT_ID =
T3.OBJECT_ID AND T1.OBJECT_TYPE <> 'TABLE'))
Plan hash value: 3651740877
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.05 | 1423 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.05 | 1423 | | | |
| 2 | VIEW | | 1 | 4641 | 5060 |00:00:00.05 | 1423 | | | |
| 3 | UNION-ALL | | 1 | | 5060 |00:00:00.05 | 1423 | | | |
|* 4 | TABLE ACCESS FULL | T_20170703_LHR_01 | 1 | 1631 | 3319 |00:00:00.01 | 469 | | | |
| 5 | SORT UNIQUE | | 1 | 4641 | 1741 |00:00:00.05 | 954 | 178K| 178K| 158K (0)|
| 6 | UNION-ALL | | 1 | | 2634 |00:00:00.05 | 954 | | | |
|* 7 | HASH JOIN | | 1 | 2010 | 1741 |00:00:00.03 | 479 | 1452K| 1452K| 1667K (0)|
| 8 | INDEX FAST FULL SCAN| IND_T2_OBJ_ID_1 | 1 | 2012 | 2010 |00:00:00.01 | 10 | | | |
|* 9 | TABLE ACCESS FULL | T_20170703_LHR_01 | 1 | 76640 | 74952 |00:00:00.01 | 469 | | | |
|* 10 | HASH JOIN | | 1 | 1000 | 893 |00:00:00.02 | 475 | 1452K| 1452K| 1571K (0)|
| 11 | INDEX FAST FULL SCAN| IND_T3_OBJ_ID_1 | 1 | 1000 | 1000 |00:00:00.01 | 6 | | | |
|* 12 | TABLE ACCESS FULL | T_20170703_LHR_01 | 1 | 76640 | 74952 |00:00:00.01 | 469 | | | |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T1"."OBJECT_TYPE"='TABLE')
7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
9 - filter("T1"."OBJECT_TYPE"<>'TABLE')
10 - access("T1"."OBJECT_ID"="T3"."OBJECT_ID")
12 - filter("T1"."OBJECT_TYPE"<>'TABLE')
40 rows selected.
LHR@orclasm >
LHR@orclasm >
LHR@orclasm >
LHR@orclasm > WITH TMP_T1 AS
2 (SELECT T1.* FROM T_20170703_LHR_01 T1)
3 SELECT COUNT(1)
4 FROM (SELECT T1.*
5 FROM TMP_T1 T1
6 WHERE T1.OBJECT_TYPE = 'TABLE'
7 UNION ALL (SELECT T1.*
8 FROM TMP_T1 T1, T_20170703_LHR_02 T2
9 WHERE T1.OBJECT_ID = T2.OBJECT_ID
10 AND T1.OBJECT_TYPE <> 'TABLE'
11 UNION
12 SELECT T1.*
13 FROM TMP_T1 T1, T_20170703_LHR_03 T3
14 WHERE T1.OBJECT_ID = T3.OBJECT_ID
15 AND T1.OBJECT_TYPE <> 'TABLE'));
COUNT(1)
----------
5060
LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9wy6ds1m0fmta, child number 0
-------------------------------------
WITH TMP_T1 AS (SELECT T1.* FROM T_20170703_LHR_01 T1) SELECT COUNT(1)
FROM (SELECT T1.* FROM TMP_T1 T1 WHERE
T1.OBJECT_TYPE = 'TABLE' UNION ALL (SELECT T1.*
FROM TMP_T1 T1, T_20170703_LHR_02 T2 WHERE
T1.OBJECT_ID = T2.OBJECT_ID AND T1.OBJECT_TYPE <>
'TABLE' UNION SELECT T1.*
FROM TMP_T1 T1, T_20170703_LHR_03 T3 WHERE
T1.OBJECT_ID = T3.OBJECT_ID AND T1.OBJECT_TYPE <>
'TABLE'))
Plan hash value: 3651740877
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.08 | 1423 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.08 | 1423 | | | |
| 2 | VIEW | | 1 | 4641 | 5060 |00:00:00.07 | 1423 | | | |
| 3 | UNION-ALL | | 1 | | 5060 |00:00:00.07 | 1423 | | | |
|* 4 | TABLE ACCESS FULL | T_20170703_LHR_01 | 1 | 1631 | 3319 |00:00:00.01 | 469 | | | |
| 5 | SORT UNIQUE | | 1 | 4641 | 1741 |00:00:00.07 | 954 | 178K| 178K| 158K (0)|
| 6 | UNION-ALL | | 1 | | 2634 |00:00:00.07 | 954 | | | |
|* 7 | HASH JOIN | | 1 | 2010 | 1741 |00:00:00.04 | 479 | 1452K| 1452K| 1620K (0)|
| 8 | INDEX FAST FULL SCAN| IND_T2_OBJ_ID_1 | 1 | 2012 | 2010 |00:00:00.01 | 10 | | | |
|* 9 | TABLE ACCESS FULL | T_20170703_LHR_01 | 1 | 76640 | 74952 |00:00:00.01 | 469 | | | |
|* 10 | HASH JOIN | | 1 | 1000 | 893 |00:00:00.03 | 475 | 1452K| 1452K| 1524K (0)|
| 11 | INDEX FAST FULL SCAN| IND_T3_OBJ_ID_1 | 1 | 1000 | 1000 |00:00:00.01 | 6 | | | |
|* 12 | TABLE ACCESS FULL | T_20170703_LHR_01 | 1 | 76640 | 74952 |00:00:00.01 | 469 | | | |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T1"."OBJECT_TYPE"='TABLE')
7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
9 - filter("T1"."OBJECT_TYPE"<>'TABLE')
10 - access("T1"."OBJECT_ID"="T3"."OBJECT_ID")
12 - filter("T1"."OBJECT_TYPE"<>'TABLE')
41 rows selected.
LHR@orclasm >
LHR@orclasm > set autot on
LHR@orclasm > SELECT COUNT(1)
2 FROM T_20170703_LHR_01 T1
3 WHERE T1.OBJECT_TYPE = 'TABLE'
4 OR EXISTS (SELECT 1 FROM T_20170703_LHR_02 T2 WHERE T1.OBJECT_ID = T2.OBJECT_ID)
5 OR EXISTS (SELECT 1 FROM T_20170703_LHR_03 T3 WHERE T1.OBJECT_ID = T3.OBJECT_ID);
COUNT(1)
----------
5060
Execution Plan
----------------------------------------------------------
Plan hash value: 1566256780
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 133 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 14 | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL| T_20170703_LHR_01 | 78271 | 1070K| 133 (1)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | IND_T2_OBJ_ID_1 | 1 | 5 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IND_T3_OBJ_ID_1 | 1 | 5 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."OBJECT_TYPE"='TABLE' OR EXISTS (SELECT 0 FROM
"T_20170703_LHR_02" "T2" WHERE "T2"."OBJECT_ID"=:B1) OR EXISTS (SELECT 0 FROM
"T_20170703_LHR_03" "T3" WHERE "T3"."OBJECT_ID"=:B2))
4 - access("T2"."OBJECT_ID"=:B1)
5 - access("T3"."OBJECT_ID"=:B1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
149133 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LHR@orclasm > SELECT COUNT(1)
2 FROM (SELECT T1.*
3 FROM T_20170703_LHR_01 T1
4 WHERE T1.OBJECT_TYPE = 'TABLE'
5 UNION ALL (SELECT T1.*
6 FROM T_20170703_LHR_01 T1, T_20170703_LHR_02 T2
7 WHERE T1.OBJECT_ID = T2.OBJECT_ID
8 AND T1.OBJECT_TYPE <> 'TABLE'
9 UNION
10 SELECT T1.*
11 FROM T_20170703_LHR_01 T1, T_20170703_LHR_03 T3
12 WHERE T1.OBJECT_ID = T3.OBJECT_ID
13 AND T1.OBJECT_TYPE <> 'TABLE'));
COUNT(1)
----------
5060
Execution Plan
----------------------------------------------------------
Plan hash value: 3651740877
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 407 (2)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 4641 | | 407 (2)| 00:00:05 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS FULL | T_20170703_LHR_01 | 1631 | 61978 | 133 (1)| 00:00:02 |
| 5 | SORT UNIQUE | | 4641 | 186K| 407 (68)| 00:00:05 |
| 6 | UNION-ALL | | | | | |
|* 7 | HASH JOIN | | 2010 | 86430 | 137 (2)| 00:00:02 |
| 8 | INDEX FAST FULL SCAN| IND_T2_OBJ_ID_1 | 2012 | 10060 | 3 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | T_20170703_LHR_01 | 76640 | 2844K| 133 (1)| 00:00:02 |
|* 10 | HASH JOIN | | 1000 | 43000 | 137 (2)| 00:00:02 |
| 11 | INDEX FAST FULL SCAN| IND_T3_OBJ_ID_1 | 1000 | 5000 | 3 (0)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | T_20170703_LHR_01 | 76640 | 2844K| 133 (1)| 00:00:02 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T1"."OBJECT_TYPE"='TABLE')
7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
9 - filter("T1"."OBJECT_TYPE"<>'TABLE')
10 - access("T1"."OBJECT_ID"="T3"."OBJECT_ID")
12 - filter("T1"."OBJECT_TYPE"<>'TABLE')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1423 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
LHR@orclasm > WITH TMP_T1 AS
2 (SELECT T1.* FROM T_20170703_LHR_01 T1)
3 SELECT COUNT(1)
4 FROM (SELECT T1.*
5 FROM TMP_T1 T1
6 WHERE T1.OBJECT_TYPE = 'TABLE'
7 UNION ALL (SELECT T1.*
8 FROM TMP_T1 T1, T_20170703_LHR_02 T2
9 WHERE T1.OBJECT_ID = T2.OBJECT_ID
10 AND T1.OBJECT_TYPE <> 'TABLE'
11 UNION
12 SELECT T1.*
13 FROM TMP_T1 T1, T_20170703_LHR_03 T3
14 WHERE T1.OBJECT_ID = T3.OBJECT_ID
15 AND T1.OBJECT_TYPE <> 'TABLE'));
COUNT(1)
----------
5060
Execution Plan
----------------------------------------------------------
Plan hash value: 3651740877
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 407 (2)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 4641 | | 407 (2)| 00:00:05 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS FULL | T_20170703_LHR_01 | 1631 | 61978 | 133 (1)| 00:00:02 |
| 5 | SORT UNIQUE | | 4641 | 186K| 407 (68)| 00:00:05 |
| 6 | UNION-ALL | | | | | |
|* 7 | HASH JOIN | | 2010 | 86430 | 137 (2)| 00:00:02 |
| 8 | INDEX FAST FULL SCAN| IND_T2_OBJ_ID_1 | 2012 | 10060 | 3 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | T_20170703_LHR_01 | 76640 | 2844K| 133 (1)| 00:00:02 |
|* 10 | HASH JOIN | | 1000 | 43000 | 137 (2)| 00:00:02 |
| 11 | INDEX FAST FULL SCAN| IND_T3_OBJ_ID_1 | 1000 | 5000 | 3 (0)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | T_20170703_LHR_01 | 76640 | 2844K| 133 (1)| 00:00:02 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T1"."OBJECT_TYPE"='TABLE')
7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
9 - filter("T1"."OBJECT_TYPE"<>'TABLE')
10 - access("T1"."OBJECT_ID"="T3"."OBJECT_ID")
12 - filter("T1"."OBJECT_TYPE"<>'TABLE')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1423 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
什么是半连接、反连接和星型连接？
（一）半连接（Semi Join）
半连接（Semi Join）是一种特殊的连接类型，当做子查询展开时，Oracle经常会把那些外部WHERE条件为EXISTS、IN或= ANY的子查询转换为对应的半连接。半连接分为嵌套循环半连接（Hint为：NL_SJ）、排序合并半连接（Hint为：MERGE_SJ）和哈希半连接（Hint为：HASH_SJ），不过在新版本数据库里，都倾向于使用哈希半连接。不过哈希半连接也有一些限制条件，例如，只能使用等值连接、不能使用GROUP BY、CONNECT BY、ROWNUM等限制条件。在执行计划中若有关键字“HASH JOIN SEMI”，则说明Oracle使用了哈希半连接。示例如下所示：
SELECT * FROM scott.DEPT A WHERE DEPTNO IN (SELECT /*+ HASH_SJ */ DEPTNO FROM scott.EMP);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 69 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 3 | 69 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 42 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
SELECT * FROM scott.DEPT A WHERE DEPTNO IN (SELECT /*+ MERGE_SJ */ DEPTNO FROM scott.EMP);
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 69 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN SEMI | | 3 | 69 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 14 | 42 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
SELECT * FROM scott.DEPT A WHERE DEPTNO IN (SELECT /*+ NL_SJ */ DEPTNO FROM scott.EMP);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 69 | 10 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 3 | 69 | 10 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 9 | 27 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
（二）反连接（Anti Join）
反连接（Anti Join）也是一种特殊的连接类型，通常用于从一个表中返回不在另一个数据源中的数据行。当做子查询展开时，Oracle经常会把那些外部WHERE条件为NOT EXISTS、NOT IN或<> ALL的子查询转换成对应的反连接。反连接分为嵌套循环反连接（NESTED LOOPS ANTI，Hint为：NL_AJ）、排序合并反连接（MERGE JOIN ANTI，Hint为：MERGE_AJ）和哈希反连接（HASH JOIN ANTI，Hint为：HASH_AJ）。示例如下所示：
CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;
CREATE TABLE DEPT AS SELECT * FROM SCOTT.DEPT;
SELECT * FROM EMP A WHERE NOT EXISTS(SELECT 1 FROM DEPT WHERE DEPTNO=A.DEPTNO);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 84 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 2 | 84 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 12 | 468 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 12 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
SELECT * FROM EMP A WHERE NOT EXISTS(SELECT /*+NL_AJ*/ 1 FROM DEPT WHERE DEPTNO=A.DEPTNO);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 84 | 7 (0)| 00:00:01 |
| 1 | NESTED LOOPS ANTI | | 2 | 84 | 7 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 12 | 468 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEPT | 4 | 12 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------
SELECT * FROM EMP A WHERE NOT EXISTS(SELECT /*+MERGE_AJ*/ 1 FROM DEPT WHERE DEPTNO=A.DEPTNO);
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 84 | 6 (34)| 00:00:01 |
| 1 | MERGE JOIN ANTI | | 2 | 84 | 6 (34)| 00:00:01 |
| 2 | SORT JOIN | | 12 | 468 | 3 (34)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 12 | 468 | 2 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 4 | 12 | 3 (34)| 00:00:01 |
| 5 | TABLE ACCESS FULL| DEPT | 4 | 12 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
需要注意的是，NOT IN和<> ALL对NULL值敏感，这意味着NOT IN后面的子查询或者常量集合一旦有NULL值出现，则整个SQL的执行结果就会为NULL，即此时的执行结果将不包含任何记录。但是，NOT EXISTS对NULL值不敏感，这意味着NULL值对NOT EXISTS的执行结果不会有什么影响。正是因为NOT IN和<> ALL对NULL值敏感，所以一旦相关的连接列上出现了NULL值，此时Oracle如果还按照通常的反连接的处理逻辑来处理，得到的结果就不对了。为了解决NOT IN和<> ALL对NULL值敏感的问题，Oracle推出了改良的反连接，这种反连接能够处理NULL值，Oracle称其为Null-Aware Anti Join，如下例：
SELECT * FROM DEPT A WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN ANTI NA | | 1 | 23 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 12 | 36 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
执行步骤的列Operation的值为“HASH JOIN ANTI NA”，关键字“NA”就是Null-Aware的缩写，表示这里采用的不是普通的哈希反连接，而是改良后的、能够处理NULL值的哈希反连接。
在Oracle 11gR2中，Oracle是否启用Null-Aware Anti Join受隐含参数“_OPTIMIZER_NULL_AWARE_ANTIJOIN”控制，其默认值为TRUE，表示启用Null-Aware Anti Join。如果把该参数的值修改为FALSE，那么表示Oracle就不能再用Null-Aware Anti Join了，而又因为NOT IN对NULL值敏感，所以Oracle此时也不能用普通的反连接。关于该隐含参数的查询如下所示：
SYS@orclasm > set pagesize 9999
SYS@orclasm > set line 9999
SYS@orclasm > col NAME format a40
SYS@orclasm > col KSPPDESC format a50
SYS@orclasm > col KSPPSTVL format a20
SYS@orclasm > SELECT a.INDX,
2 a.KSPPINM NAME,
3 a.KSPPDESC,
4 b.KSPPSTVL
5 FROM x$ksppi a,
6 x$ksppcv b
7 WHERE a.INDX = b.INDX
8 and lower(a.KSPPINM) like lower('%?meter%');
Enter value for parameter: _OPTIMIZER_NULL_AWARE_ANTIJOIN
old 8: and lower(a.KSPPINM) like lower('%?meter%')
new 8: and lower(a.KSPPINM) like lower('%_OPTIMIZER_NULL_AWARE_ANTIJOIN%')
INDX NAME KSPPDESC KSPPSTVL
---------- ---------------------------------------- -------------------------------------------------- --------------------
1907 _optimizer_null_aware_antijoin null-aware antijoin parameter TRUE
filter这个操作在《Cost Based Oracle Fundamental》此书第九章有介绍。filter的操作是对外表的每一行，都要对内表执行一次全表扫描，所以很多时候提到filter都会感到可怕。他其实很像我们熟悉的neested loop，但它的独特之处在于会维护一个hash table。其实filter 的性能实际上跟列值distinct数有关，oracle在执行的时候实际上做了很大优化，最坏情况下才会出现对外表每一行执行一次filter操作，如果distinct值比较少，那执行效率还是非常高的。甚至有可能比nl更高。
针对filter用一个简单的实例来解释一下：
假如表TMP_LIUHC_1和TMP_LIUHC_2，如果执行如下语句：
如果TMP_LIUHC_1里取出object_id=1，那么对于TMP_LIUHC_2来说即select 1 from TMP_LIUHC_2 where TMP_LIUHC_2.object_id*10=1,如果条件满足，那么对于子查询，输入输出对，即为(1（TMP_LIUHC_1.object_id）,1（常量）)。他存储在hash table里，并且由于条件满足，TMP_LIUHC_1.object_id=1被放入结果集。然后接着从TMP_LIUHC_1取出object_id=2，如果子查询依旧条件满足，那么子查询产生另一个输入和输出，即(2,1)，被放入hash table里；并且TMP_LIUHC_1.object_id=2被放入结果集。接着假设TMP_LIUHC_1里有重复的object_id，例如我们第三次从TMP_LIUHC_1取出的object_id=2,那么由于我们对于子查询来说，已经有输入输出对(2,1)在hash table里了，所以就不用去再次全表扫描TMP_LIUHC_2了，ORACLE非常聪明地知道object_id=2是结果集。这里，filter和neested loop相比,省去了一次全表扫描TMP_LIUHC_2。这个hash table是有大小限制的，当被占满的时候，后续新的TMP_LIUHC_1.object_id的FILTER就类似neested loop了。由此可见，从buffer gets层面上来看，FILTER是应该优于neested loop的，尤其当外部查询需要传递给子查询的输入（此例中为TMP_LIUHC_1.object_id）的distinct value非常小时，FILTER就会显得更优。即使在我这个例子中，TMP_LIUHC_1.object_id的distinct value上万，我对比了一下neested loop，FILTER仍然略优：
查看关联列的distinct值
SQL> select count(distinct object_type) from TMP_LIUHC_1;
COUNT(DISTINCTOBJECT_TYPE)
--------------------------
40
SQL> select count(distinct object_id) from TMP_LIUHC_1;
COUNT(DISTINCTOBJECT_ID)
------------------------
60947
下面使用filter
下面使用嵌套(NL)
完全跑不出来。。。。
由此可见，当外围 输入值(就是关联值) 的distinct的值很少时，使用filter大大的提高查询效率。
内容总结
以上是互联网集市为您收集整理的Oracle执行计划filter下多个节点的优化全部内容，希望文章能够帮你解决Oracle执行计划filter下多个节点的优化所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。