一个小实验：找到优化器选择全表扫描和索引范围扫描的临界点
首先创建一张测试表：
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 |
-----------------------------------------------------------------------------