Database Reference
In-Depth Information
Assuming the same table and seed data as used with the prior
SUBSTR
examples, here we add a virtual column to
the table using
STANDARD_HASH
, create an index, and generate statistics:
EODA@O12CE> alter table t add (xv as (standard_hash(x)));
Table altered.
EODA@O12CE> create index te on t(xv);
Index created.
EODA@O12CE> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
The
STANDARD_HASH
works well when using equality predicates in the
WHERE
clause. For example:
EODA@O12CE> set autotrace traceonly explain
EODA@O12CE> select count(*) from t where x='300';
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5025 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5025 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 5025 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TE | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
The index on a
STANDARD_HASH
-based virtual column allows for efficient equality-based searches, but does not
work for range-based searches, as the data is stored in an index based on the randomized hash value; for example:
EODA@O12CE> select count(*) from t where x >'800' and x<'900';
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5004 | 6 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5004 | | |
|* 2 | TABLE ACCESS FULL| T | 239 | 1167K| 6 (0)| 00:00:01 |
---------------------------------------------------------------------------
Function-Based Index Solution
The concept here is that you're building an index and applying a function to it in a way that limits the length of the
index key and also results in a usable index. Here I use the same code (as in the prior section) to create a table with an
extended column and populate it with test data:
EODA@O12CE> create table t(x varchar2(32767));
Table created.
