Database Reference
In-Depth Information
Now verify that an equality-based search uses the index:
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 | 5004 | 4 (0)| 00:00:01|
| 1 | SORT AGGREGATE | | 1 | 5004 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 5004 | 4 (0)| 00:00:01|
|* 3 | INDEX RANGE SCAN | TE | 8 | | 1 (0)| 00:00:01|
--------------------------------------------------------------------------------------------
This 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.
Frequently Asked Questions and Myths About Indexes
As I said in the introduction to this topic, I field lots of questions about Oracle. I am the Tom behind the “Ask Tom”
column in Oracle Magazine and at http://asktom.oracle.com , where I answer people's questions about the Oracle
database and tools. In my experience, the topic of indexes attracts the most questions. In this section, I answer some
of the most frequently asked questions. Some of the answers may seem like common sense, while other answers
might surprise you. Suffice it to say, there are lots of myths and misunderstandings surrounding indexes.
Do Indexes Work on Views?
A related question is, “How can I index a view?” Well, the fact is that a view is nothing more than a stored query.
Oracle will replace the text of the query that accesses the view with the view definition itself. Views are for the
convenience of the end user or programmer—the optimizer works with the query against the base tables. Any and all
indexes that could have been used if the query had been written against the base tables will be considered when you
use the view. To index a view, you simply index the base tables.
Do Nulls and Indexes Work Together?
B*Tree indexes, except in the special case of cluster B*Tree indexes, do not store completely Null entries, but bitmap
and cluster indexes do. This side effect can be a point of confusion, but it can actually be used to your advantage when
you understand what not storing entirely null keys implies.
To see the effect of the fact that Null values are not stored, consider this example:
EODA@ORA12CR1> create table t ( x int, y int );
Table created.
EODA@ORA12CR1> create unique index t_idx on t(x,y);
Index created.
EODA@ORA12CR1> insert into t values ( 1, 1 );
1 row created.
 
Search WWH ::




Custom Search