一个小实验：找到优化器选择全表扫描和索引范围扫描的临界点
首先创建一张测试表：CREATE TABLE FOO( USERID NUMBER(20) NULL, USERNAME VARCHAR2(100), USERDEPT NUMBER(20), USERDEPTNAME VARCHAR2(100))/模拟上10万条数据进去：BEGIN FOR I IN 0..100000 LOOP INSERT INTO FOO VALUES (I,'ZIWEN'||i,MOD(I,30),'USERDEPTNAME'||MOD(I,30)); IF MOD(I,5000) =0 THEN COMMIT; END IF; END LOOP;END;/在上面创建一个复合索引,也可以创建其他的索引:CREATE INDEX FOO_INDEX ON FOO(USERID,USERNAME,USERDEPT);
下面的SQL应该会走INDEX RANGE SCAN:ChenZw> SELECT * FROM FOO WHERE USERID = 1000;
已选择 1 行。已用时间: 00: 00: 00.01执行计划----------------------Plan hash value: 1755106210-----------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 34 | 3 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| FOO | 1 | 34 | 3 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | FOO_INDEX | 1 | | 2 (0)| 00:00:01 |-----------------------------------------------------
下面的也应该会走INDEX RANGE SCAN:ChenZw> SELECT * FROM FOO WHERE USERID <1000;已选择1000行。已用时间: 00: 00: 00.10执行计划----------------------Plan hash value: 1755106210-----------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------| 0 | SELECT STATEMENT | | 1000 | 34000 | 12 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| FOO | 1000 | 34000 | 12 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | FOO_INDEX | 1000 | | 6 (0)| 00:00:01 |-----------------------------------------------------
但是我们发现，下面的这个SQL居然走了TABLE ACCESS FULL!ChenZw> SELECT * FROM FOO WHERE USERID <20000;已选择20000行。已用时间: 00: 00: 01.89执行计划----------------------Plan hash value: 1245013993--------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------| 0 | SELECT STATEMENT | | 20000 | 664K| 171 (1)| 00:00:03 ||* 1 | TABLE ACCESS FULL| FOO | 20000 | 664K| 171 (1)| 00:00:03 |--------------------------------------
我们去找临界点是多少呢？虽然这个是与数据块大小，以及各种其他因素有关的，下面的结果仅仅代表在我本地环境中的Oracle:ChenZw> SELECT * FROM FOO WHERE USERID <18079;已选择18079行。已用时间: 00: 00: 01.70执行计划----------------------Plan hash value: 1755106210-----------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------| 0 | SELECT STATEMENT | | 18079 | 600K| 170 (0)| 00:00:03 || 1 | TABLE ACCESS BY INDEX ROWID| FOO | 18079 | 600K| 170 (0)| 00:00:03 ||* 2 | INDEX RANGE SCAN | FOO_INDEX | 18079 | | 74 (0)| 00:00:01 |-----------------------------------------------------
下面的是全表扫描,仅仅在上面的谓词条件上+1:ChenZw> SELECT * FROM FOO WHERE USERID <18080;已选择18080行。已用时间: 00: 00: 01.66执行计划----------------------Plan hash value: 1245013993--------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------| 0 | SELECT STATEMENT | | 18080 | 600K| 171 (1)| 00:00:03 ||* 1 | TABLE ACCESS FULL| FOO | 18080 | 600K| 171 (1)| 00:00:03 |--------------------------------------
可以看到，在我的本地，当Oracle的优化器认为只要超过下面的这个比例数据的提取就会走全表扫描！真是一个聪明的家伙。ChenZw> select 18080/count(1) from foo;18080/COUNT(1)-------------- 0.180798192已选择 1 行。
当然，如果你在本地运行这个SQL的时候，里面的数据不能太少，如果Oracle一次抓取BLOCK就可以把所有数据抓到的话，当然会走全表扫描了。