【DB笔试面试258】在Oracle中，执行计划里的access和filter有什么区别（下）？
- 2019 年 10 月 11 日
- 筆記
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
& 说明：
有关access和filter的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2141522/
