全表扫描的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决定，这个数据反应了索引数据在表上的聚集情况。