Database Reference
In-Depth Information
Case 5
The index, if used, would actually be slower. I see this often—people assume that, of course, an index will always
make a query go faster. So, they set up a small table, analyze it, and find that the optimizer doesn't use the index.
The optimizer is doing exactly the right thing in this case. Oracle (under the CBO) will use an index only when it
makes sense to do so. Consider this example:
EODA@ORA12CR1> create table t(x int);
Table created.
EODA@ORA12CR1> insert into t select rownum from dual connect by level < 1000000;
999999 rows created.
EODA@ORA12CR1> create index ti on t(x);
Index created.
EODA@ORA12CR1> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
If we run a query that needs a relatively small percentage of the table, as follows
EODA@ORA12CR1> set autotrace on explain
EODA@ORA12CR1> select count(*) from t where x < 50;
COUNT(*)
----------
49
Execution Plan
...
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| TI | 49 | 245 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
it will happily use the index; however, we'll find that when the estimated number of rows to be retrieved via the index
crosses a threshold (which varies depending on various optimizer settings, physical statistics, version, and so on),
we'll start to observe a full table scan:
EODA@ORA12CR1> select count(*) from t where x < 1000000;
COUNT(*)
----------
999999
