了不起的 “filter(NULL IS NOT NULL)”
经常会在执行计划中看到很奇怪的"FILTER"操作，然后看对应的执行信息是"filter(NULL IS NOT NULL)". 其实这是优化器非常聪明的“短路”操作。
比如下面的这个执行计划，（尤其是从统计信息中可以看到logical/physical reads都是0)
(注：在Ask Tom: On Constraints, Metadata, and Truth (http://www.oracle.com/technetwork/issue-archive/2011/11-mar/o21asktom-312223.html) 有提到)
Execution Plan ---------------------------------------------------------- Plan hash value: 3049830378 --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 58 | 2 (100)| 00:00:01 | | 1 | VIEW | V_LO_SEC_USER_US | 2 | 58 | 2 (100)| 00:00:01 | | 2 | SORT UNIQUE | | 2 | 38 | 2 (100)| 00:00:01 | | 3 | UNION-ALL | | | | | | |* 4 | FILTER | | | | | | |* 5 | HASH JOIN | | 5 | 160 | 3 (34)| 00:00:01 | | 6 | VIEW | | 3 | 78 | 1 (0)| 00:00:01 | |* 7 | CONNECT BY WITHOUT FILTERING| | | | | | | 8 | INDEX FULL SCAN | PK_LO_SEC_USER_SET_LINKS | 3 | 18 | 1 (0)| 00:00:01 | | 9 | INDEX FULL SCAN | PK_LO_SEC_USER_US_LINKS | 8 | 48 | 1 (0)| 00:00:01 | |* 10 | FILTER | | | | | | | 11 | INDEX FULL SCAN | PK_LO_SEC_USER_US_LINKS | 8 | 48 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter(NULL IS NOT NULL) 5 - access("US"."CHILD_USER_SET_ID"="USL"."USER_SET_ID") 7 - access("CHILD_USER_SET_ID"=PRIOR "PARENT_USER_SET_ID") 10 - filter(NULL IS NOT NULL) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 467 bytes sent via SQL*Net to client 493 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 0 rows processed
这个是很赞的。
不过今天在看一条SQL执行计划的时候，却发现有点异常。
SQL是这样的
select * from V_LO_SEC_BU where is_org=1;
View的定义如下：
CREATE OR REPLACE FORCE VIEW V_LO_SEC_BU ( IS_ORG, BU_ID, CHILD_BU_ID, IS_DIRECT ) AS SELECT /* IS_ORG = 1 only return organzational tree, IS_ORG=0 return all the tree*/ 1 AS is_org, CONNECT_BY_ROOT parent_bu_id AS bu_id, child_bu_id AS child_bu_id, CASE WHEN level > 1 THEN 0 ELSE 1 END AS is_direct FROM (SELECT * FROM lo_sec_bu_links WHERE is_organization = 'Y') CONNECT BY PRIOR child_bu_id = parent_bu_id UNION ALL SELECT 0 AS is_org, CONNECT_BY_ROOT parent_bu_id AS bu_id, child_bu_id AS child_bu_id, CASE WHEN level > 1 THEN 0 ELSE 1 END AS is_direct FROM lo_sec_bu_links CONNECT BY PRIOR child_bu_id = parent_bu_id;
这个View很简单了，就是个两部分结果的合集。UNION 之前的是 is_org=1的数据， UNION之后的是is_org=0的数据。上面的SQL是只想得到UNION之前的数据，按道理是SQL优化器是可以知道只要执行UNION之前的SQL就好了，UNION下面的SQL可以直接短路掉。但是执行计划看来不是这样的，
Execution Plan ---------------------------------------------------------- Plan hash value: 1778311211 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 25 | 800 | 4 (0)| 00:00:01 | | 1 | VIEW | V_LO_SEC_BU | 25 | 800 | 4 (0)| 00:00:01 | | 2 | UNION-ALL | | | | | | |* 3 | FILTER | | | | | | |* 4 | CONNECT BY WITHOUT FILTERING| | | | | | |* 5 | TABLE ACCESS FULL | LO_SEC_BU_LINKS | 12 | 96 | 3 (0)| 00:00:01 | |* 6 | FILTER | | | | | | |* 7 | CONNECT BY WITHOUT FILTERING| | | | | | | 8 | INDEX FULL SCAN | PK_LO_SEC_BU_LINKS | 13 | 78 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(1=1) 4 - access("LO_SEC_BU_LINKS"."PARENT_BU_ID"=PRIOR "LO_SEC_BU_LINKS"."CHILD_BU_ID") 5 - filter("IS_ORGANIZATION"='Y') 6 - filter(0=1) 7 - access("PARENT_BU_ID"=PRIOR "CHILD_BU_ID") Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 1193 bytes sent via SQL*Net to client 515 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 26 rows processed
注意第6步操作是 filter(0=1)， 而不是filter(NULL IS NOT NULL)！ 虽然0=1和 NULL IS NOT NULL 都是FALSE, 但是filter (0=1)却只是个简单的对结果进行过滤的操作，而不是短路(i.e. Filter的子操作(step7和8）都不执行）！
那么怎么帮助SQL优化器知道这是个可以“短路”的操作呢。试着改下这个View， 如下 -
CREATE OR REPLACE FORCE VIEW V_LO_SEC_BU ( IS_ORG, BU_ID, CHILD_BU_ID, IS_DIRECT ) AS SELECT is_org, bu_id, child_bu_id, is_direct FROM ( SELECT /* IS_ORG = 1 only return organzational tree, IS_ORG=0 return all the tree*/ 1 AS is_org, CONNECT_BY_ROOT parent_bu_id AS bu_id, child_bu_id AS child_bu_id, CASE WHEN level > 1 THEN 0 ELSE 1 END AS is_direct FROM (SELECT * FROM lo_sec_bu_links WHERE is_organization = 'Y') CONNECT BY PRIOR child_bu_id = parent_bu_id ) WHERE is_org = 1 UNION ALL SELECT is_org, bu_id, child_bu_id, is_direct FROM ( SELECT 0 AS is_org, CONNECT_BY_ROOT parent_bu_id AS bu_id, child_bu_id AS child_bu_id, CASE WHEN level > 1 THEN 0 ELSE 1 END AS is_direct FROM lo_sec_bu_links CONNECT BY PRIOR child_bu_id = parent_bu_id ) WHERE is_org = 0 ;
再次执行下上面的SQL，执行计划如下，
Execution Plan ---------------------------------------------------------- Plan hash value: 959389615 ------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 13 | 416 | 3 (0)| 00:00:01 | | 1 | VIEW | V_LO_SEC_BU | 13 | 416 | 3 (0)| 00:00:01 | | 2 | UNION-ALL | | | | | | |* 3 | VIEW | | 12 | 384 | 3 (0)| 00:00:01 | |* 4 | CONNECT BY WITHOUT FILTERING | | | | | | |* 5 | TABLE ACCESS FULL | LO_SEC_BU_LINKS | 12 | 96 | 3 (0)| 00:00:01 | |* 6 | FILTER | | | | | | |* 7 | VIEW | | 13 | 403 | 1 (0)| 00:00:01 | |* 8 | CONNECT BY WITHOUT FILTERING| | | | | | | 9 | INDEX FULL SCAN | PK_LO_SEC_BU_LINKS | 13 | 78 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("IS_ORG"=1) 4 - access("LO_SEC_BU_LINKS"."PARENT_BU_ID"=PRIOR "LO_SEC_BU_LINKS"."CHILD_BU_ID") 5 - filter("IS_ORGANIZATION"='Y') 6 - filter(NULL IS NOT NULL) 7 - filter("IS_ORG"=0) 8 - access("PARENT_BU_ID"=PRIOR "CHILD_BU_ID") Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 1193 bytes sent via SQL*Net to client 515 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 26 rows processed
这次就出现了filter(NULL IS NOT NULL), 而且从consistent gets (由7下降为6)可以看出UNION之后的部分是没有执行的。如果SQL改成读取is_org=0效果会更加明显。
Execution Plan ---------------------------------------------------------- Plan hash value: 4072402958 ------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 448 | 1 (0)| 00:00:01 | | 1 | VIEW | V_LO_SEC_BU | 14 | 448 | 1 (0)| 00:00:01 | | 2 | UNION-ALL | | | | | | |* 3 | FILTER | | | | | | |* 4 | VIEW | | 12 | 384 | 3 (0)| 00:00:01 | |* 5 | CONNECT BY WITHOUT FILTERING| | | | | | |* 6 | TABLE ACCESS FULL | LO_SEC_BU_LINKS | 12 | 96 | 3 (0)| 00:00:01 | |* 7 | VIEW | | 13 | 403 | 1 (0)| 00:00:01 | |* 8 | CONNECT BY WITHOUT FILTERING | | | | | | | 9 | INDEX FULL SCAN | PK_LO_SEC_BU_LINKS | 13 | 78 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(NULL IS NOT NULL) 4 - filter("IS_ORG"=1) 5 - access("LO_SEC_BU_LINKS"."PARENT_BU_ID"=PRIOR "LO_SEC_BU_LINKS"."CHILD_BU_ID") 6 - filter("IS_ORGANIZATION"='Y') 7 - filter("IS_ORG"=0) 8 - access("PARENT_BU_ID"=PRIOR "CHILD_BU_ID") Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1 consistent gets 0 physical reads 0 redo size 1215 bytes sent via SQL*Net to client 515 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 28 rows processed
这次发现consistent gets 为1， 正好印证了is_org=1短路了（consistent gets 减少了1） UNION之后的那部分的操作。
--------------------------------------
Regards,
FangwenYu
