Oracle执行计划filter下多个节点的优化

最新推荐文章于 2022-02-23 13:41:49 发布

coco3600

最新推荐文章于 2022-02-23 13:41:49 发布

阅读量631

点赞数 1

文章标签：数据库面试

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

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 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.

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------

| 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 > 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

-------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------

| 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 > 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

-------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------

| 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

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

| 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);

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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);

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 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);

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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);

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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);

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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);

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 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);

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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大大的提高查询效率。

About Me

...............................................................................................................................

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群：230161599 微信群：私聊

● 联系我请加QQ好友(646634621)，注明添加缘由

● 于 2017-07-01 09:00 ~ 2017-07-31 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。