Database Reference
In-Depth Information
EODA@O12CE> insert into t
2 select to_char(level)|| rpad('abc',10000,'xyz')
3 from dual connect by level < 1001
4 union
5 select to_char(level)
6 from dual connect by level < 1001;
2000 rows created.
Now suppose you're familiar with the data and know that the first ten characters of the extended columns are
usually sufficient for identifying a row; therefore, you create an index on the substring of the first ten characters and
generate statistics for the table:
EODA@O12CE> create index te on t(substr(x,1,10));
Index created.
EODA@O12CE> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
The optimizer can use an index like this when there are equality and range predicates in the
WHERE
clause.
Some examples will illustrate this:
EODA@O12CE> set autotrace traceonly explain
EODA@O12CE> select count(*) from t where x = '800';
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16407 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 16407 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 16407 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TE | 8 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
This example uses a range predicate:
EODA@O12CE> select count(*) from t where x>'200' and x<'400';
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5011 | 6 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5011 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 477 | 2334K| 6 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TE | 479 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Assuming the same table and seed data as used with the prior
SUBSTR
examples, here we add a function-based
index using
STANDARD_HASH
:
EODA@O12CE> create index te on t(standard_hash(x));
