Oracle中"TABLE ACCESS FULL"的”欺骗时刻“
基础表信息
缓存前提下，全表扫描表zkmbak需要1.69s，约229000个逻辑读。
14:53:18 ZKM@dev-app73/pdb(400)> select count(*) from zkmbak; COUNT(*) ---------- 150994944 Elapsed: 00:00:01.69 14:53:29 ZKM@dev-app73/pdb(400)> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------- SQL_ID f7mhuvxws84fv, child number 0 ------------------------------------- select count(*) from zkmbak Plan hash value: 2160384922 --------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:01.68 | 229K| | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.68 | 229K| | 2 | TABLE ACCESS FULL| ZKMBAK | 1 | 150M| 150M|00:00:01.65 | 229K| --------------------------------------------------------------------------------------- 14 rows selected. Elapsed: 00:00:00.06
案例1
14:53:32 ZKM@dev-app73/pdb(400)> select count(*) from zkmbak where rownum<=10; COUNT(*) ---------- 10 Elapsed: 00:00:00.00 14:56:30 ZKM@dev-app73/pdb(400)> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------- SQL_ID 22uwfszq8a9v7, child number 0 ------------------------------------- select count(*) from zkmbak where rownum<=10 Plan hash value: 3390400203 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 | |* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 3 | | 3 | TABLE ACCESS FULL| ZKMBAK | 1 | 10 | 10 |00:00:00.01 | 3 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(ROWNUM<=10) 20 rows selected. Elapsed: 00:00:00.06
加了rownum<=10后，虽然有"TABLE ACCESS FULL"但是实际上扫描出符合条件的前10行数据后Oracle就停止扫描了。
因此并不会产生229000个逻辑读。
案例2
15:02:44 ZKM@dev-app73/pdb(400)> select count(*) from zkm where id in (select id from zkmbak); COUNT(*) ---------- 1000 Elapsed: 00:00:00.00 15:02:44 ZKM@dev-app73/pdb(400)> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------- SQL_ID 7ypyj251q62s2, child number 0 ------------------------------------- select count(*) from zkm where id in (select id from zkmbak) Plan hash value: 783349724 ------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 6 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 6 | | | | |* 2 | HASH JOIN SEMI | | 1 | 1000 | 1000 |00:00:00.01 | 6 | 2546K| 2546K| 1416K (0)| | 3 | TABLE ACCESS FULL| ZKM | 1 | 1000 | 1000 |00:00:00.01 | 3 | | | | | 4 | TABLE ACCESS FULL| ZKMBAK | 1 | 150M| 256 |00:00:00.01 | 3 | | | | ------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"="ID") 21 rows selected. Elapsed: 00:00:00.06
其中，id=4这一步实际消耗逻辑读3个，实际取的行数为256（这个就不懂CBO是怎么判定的了）。
所以也没有进行实际上执行计划显示的全表扫描。