全表扫描的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决定,这个数据反应了索引数据在表上的聚集情况。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/225056/viewspace-1022479/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/225056/viewspace-1022479/