[索引相关] 不使用索引的情况 - 5

-- 对于小表，不要盲目的使用索引查询，可能会比全表扫描慢

go@XE> create table t (x,y,primary key (x)) as select rownum x,object_name from all_objects;

Table created.

go@XE> begin
dbms_stats.gather_table_stats
(user,'T',cascade=>true);
end;
/

PL/SQL procedure successfully completed.

go@XE> set autotrace traceonly explain
go@XE> select count(*) from t where x<50;

-- 小数据量时，索引效率高
Execution Plan
----------------------------------------------------------
Plan hash value: 2928502066

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 |     5 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |             |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| SYS_C004326 |    49 |   245 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("X"<50)

-- 数据量大时，可能走全表扫描会更合适
go@XE> select count(*) from t where x<15000;

Execution Plan
----------------------------------------------------------
Plan hash value: 141882580

-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |     5 |     8   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |             |     1 |     5 |            |          |
|*  2 |   INDEX FAST FULL SCAN| SYS_C004326 | 12125 | 60625 |     8   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"<15000)