【DB筆試面試258】在Oracle中，執行計劃里的access和filter有什麼區別（上）？
- 2019 年 10 月 11 日
- 筆記
題目如下所示：
在Oracle中，執行計劃里的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連用、複雜子查詢等情況。
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.
DB筆試面試歷史連接
http://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w
About Me：小麥苗
● 本文作者：小麥苗，只專註於數據庫的技術，更注重技術的運用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列題目來源於作者的學習筆記，部分整理自網絡，若有侵權或不當之處還請諒解
● 版權所有，歡迎分享本文，轉載請保留出處
● QQ：646634621 QQ群：230161599
● 題目解答若有不當之處，還望各位朋友批評指正，共同進步
