經常會在執行計劃中看到很奇怪的"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之後的那部分的操作。