Valen Won

从COST的角度看：为何不使用索引一例.


Wangwj/valenwon
Scttsc/cnoug
2008- 05- 08


在论坛有网友问
http://www.oracle.com.cn/viewthread.php?tid=134090&extra=page%3D1
问为什么，两个索引都在谓词中引用，Oracle是怎么选择的呢？我知道Oracle是根据COST来选择合适的执行计划的，为了深入了解其机制，我做了一个测试过程如下。
-- Create table
create table TEST
(
EN_NAME VARCHAR2(6),
BS_CODE VARCHAR2(10),
DDATE   DATE,
IID   NUMBER
)；
-- Create/Recreate indexes
create index TEST_DATE on TEST (DDATE)；
create index TEST_ID on TEST (IID)；
--在IID和时间DDATE建普通索引

--插入了11万数据

INSERT INTO test t(iid,ddate)
SELECT trunc(dbms_random.value(0,100)),SYSDATE -trunc(dbms_random.value(0,100)) FROM dual
CONNECT BY ROWNUM < 110001

--删除IID为84的大部分记录，仅仅剩下23行

Delete from test where iid=84 and rownum < 1089

--为了保证信息的准确性，我重建索引，降低HWL，分析表

Alter index test_id rebuild;

Alter index teset_date rebuild;



alter table test shrink space cascade;



analyse table test analyze statistics;
SQL> exec show_space('TEST','APS2');
Unformatted Blocks .....................           0
FS1 Blocks (0-25) .....................           1
FS2 Blocks (25-50) .....................           1
FS3 Blocks (50-75) .....................           0
FS4 Blocks (75-100).....................           0
Full Blocks     .....................         269
Total Blocks............................         288
Total Bytes.............................     2,359,296
Total MBytes............................           2
Unused Blocks...........................           3
Unused Bytes............................       24,576
Last Used Ext FileId....................         10
Last Used Ext BlockId...................       8,073
Last Used Block.........................         29


PL/SQL procedure successfully completed.
--运行SQL

SELECT * FROM TEST WHERE IID=84

SQL> SELECT * FROM TEST WHERE IID=84;

23 rows selected.

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

| Id | Operation       | Name | Rows | Bytes | Cost (%CPU)| Time   |

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

|   0 | SELECT STATEMENT |     | 1089 | 11979 |   73   (6)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| TEST | 1089 | 11979 |   73   (6)| 00:00:01 |

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

Statistics

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

      0 recursive calls

      0 db block gets

    277 consistent gets

居然是TABLE ACCESS FULL？注意COST 73 ,277的一致读

根据常识，当访问小于表15%的数据的时候，走索引会获得更好的效率。为什么这里会走FULL SCAN呢？

那么再运行一次走索引的，如下：

SQL> SELECT /*+ INDEX(test TEST_ID)*/* FROM test WHERE iid=84;



23 rows selected.

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

|   0 | SELECT STATEMENT         |       | 1089 | 11979 |   269   (0)| 00:00:04 |

|   1 | TABLE ACCESS BY INDEX ROWID| TEST   | 1089 | 11979 |   269   (0)| 00:00:04 |

|* 2 |   INDEX RANGE SCAN       | TEST_ID | 1089 |     |   3   (0)| 00:00:01 |

Statistics

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

      1 recursive calls

      0 db block gets

      13 consistent gets

COST 269,13的一致读。



这样就很清楚，优化器认为全表扫描的成本较小，所以它选择FULL SCAN,虽然效率不一定会优于走索引。

--下面我来简单说明一下COST是如何计算的呢

从索引访问说起，先收集一些基本信息

SQL> SELECT t.blevel,t.leaf_blocks FROM User_Indexes t WHERE index_name='TEST_ID';



  BLEVEL LEAF_BLOCKS

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

1                     213

2级索引，有213个叶子块。

根据公式：