全表掃描的cost 與 索引掃描Cost的比較 – 無直方圖（10.1.0.3以後）

全表掃描的cost 與 索引掃描Cost的比較 – 無直方圖（10.1.0.3以後）

簡單的例子說明為什麼很多時候CBO會選擇FTS，而不是索引

1. 建立測試資料

SQL> EXEC DBMS_RANDOM.SEED(0);

PL/SQL procedure successfully completed.

SQL> CREATE TABLE t2 AS

SELECT

TRUNC(DBMS_RANDOM.VALUE(1, 101)) n1,

LEVEL n2,

DBMS_RANDOM.STRING('A', 300) c1

FROM DUAL

CONNECT BY LEVEL <= 10000; 2 3 4 5 6 7

Table created.

SQL> CREATE INDEX t2_n1 ON t2 (n1);

Index created.

SQL> CREATE INDEX t2_n2 ON t2 (n2);

Index created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T2', estimate_percent => 100, method_opt => 'FOR ALL COLUMNS SIZE 1', cascade => TRUE);

PL/SQL procedure successfully completed.

SQL>

2. 需要用到的統計資料

++ 系統的統計資料

SQL> SELECT pname, pval1 FROM sys.aux_stats$ WHERE pname IN ('MBRC', 'MREADTIM', 'SREADTIM');

PNAME PVAL1

------------------------------ ----------

SREADTIM 12

MREADTIM 42

MBRC 16

++ 表T2上的統計資料

SQL> SELECT num_rows, blocks FROM user_tables WHERE table_name = 'T2';

NUM_ROWS BLOCKS

---------- ----------

10000 471

++ 索引T2_N1的統計資料

SQL> SELECT blevel, leaf_blocks, clustering_factor FROM user_indexes WHERE index_name = 'T2_N1';

BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR

---------- ----------- -----------------

1 20 9021

++ 索引T2_N2的統計資料

SQL> SELECT blevel, leaf_blocks, clustering_factor FROM user_indexes WHERE index_name = 'T2_N2';

BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR

---------- ----------- -----------------

1 21 455

SQL>

3. 全表掃描cost的計算

++ 從explain中獲取IO Cost

SQL> DELETE plan_table;

9 rows deleted.

SQL> COL operation FOR A40;

SQL> EXPLAIN PLAN FOR SELECT COUNT(*) FROM t2;

Explained.

SQL> SELECT id, cardinality, io_cost,

2 SUBSTR(operation||' '||options||' '||object_name, 1, 40) operation

3 FROM plan_table ORDER BY id;

ID CARDINALITY IO_COST OPERATION

---------- ----------- ---------- ----------------------------------------

0 1 105 SELECT STATEMENT

1 1 SORT AGGREGATE

2 10000 105 TABLE ACCESS FULL T2 <=== IO_Cost為105

SQL>

++ 透過公式計算

I/O Cost = 1 + CEIL((#Blks / MBRC) * (mreadtim / sreadtim))

I/O Cost = 1 + CEIL(( 471 / 16 ) * ( 42 / 12))

I/O Cost = 1 + 104 = 105 <=== 與plain_table中的吻合

4. Full Index Range Scan的IO Cost

++ 從explain中獲取IO Cost

SQL> DELETE plan_table;

3 rows deleted.

SQL> EXPLAIN PLAN FOR

SELECT /*+ INDEX(t2 t2_n1) */ COUNT(*) FROM t2 WHERE n1 > 0 AND c1 IS NOT NULL; 2

Explained.

SQL> SELECT id, cardinality, io_cost,

SUBSTR(operation||' '||options||' '||object_name, 1, 40) operation

FROM plan_table ORDER BY id; 2 3

ID CARDINALITY IO_COST OPERATION

---------- ----------- ---------- ----------------------------------------

0 1 9042 SELECT STATEMENT

1 1 SORT AGGREGATE

2 10000 9042 TABLE ACCESS BY INDEX ROWID T2 <== IO_Cost為9042

3 10000 21 INDEX RANGE SCAN T2_N1

SQL>

++ 透過公式計算

I/O Cost = LVLS + CEIL(#LB * ix_sel) + CEIL(CLUF * ix_sel_with_filters)

I/O Cost = LVLS + CEIL(#LB * 1) + CEIL(CLUF * 1) （<== 由於是full scan，所以selectivity均為1）

I/O Cost = LVLS + CEIL(#LB) + CEIL(CLUF)

I/O Cost = 1 + 20 + 9021 = 9042 <== 與plan_table中的吻合

另一個索引也是同樣的步驟去測試，IO_Cost 為 477

5. 總結

a． 全表掃描的Cost只與表的block數有關，由於全表掃描採用的是資料塊的連續讀，因此有比較好的效能。即使有索引存在，也很可能會走FTS

b． 索引掃描的IO COST 由索引讀 和 表資料讀兩部分組成， 索引IO由Blevel和Leaf決定。 表資料則主要由Clustering_factor決定，這個資料反應了索引資料在表上的聚集情況。