Oracle Indexes
The degree to which indexes help performance depends partly on the selectivity of the data.High selectivity – Few rows match the index valueLow selectivity – like Country name returns many rows from the index
If data is selective but the rows are not stored near each other in the table, many blocks may have to be read which will reduce the indexes effectiveness.
If a high percentage of blocks need to be read, a full table scan may be quicker. Oracle uses multi block read on full table scans.
Index block reads are single block reads
The goal of an index should be to reduce the number of single blocks needed to be read to solve the querry.
Indexes generally benefit performance on:
- SELECT
- WHERE clause of UPDATE
- WHERE clause of DELETE (where few rows are selected)
Decrease performance for:
- INSERT statements
- UPDATES
- DELETES (of large numbers of rows)
SQL for indexes:
Get all indexes:
select * from dba_ind_columns;
Other tables:
- all_ind_columns (all you have access to)
- user_ind_columns
- dba_indexes
- user_indexes
- all_indexes
Oracle Index Types.
Btree indexes
- The default index type
- Balanced tree
- Good with High Cardinality
- Null value are not indexed
- Most common type
- Each value should take aprox the same time to return
Btree cluster indexes
- These are B*tree index defined for clusters. Clusters are two or more tables with one or more common columns and are usually accessed together (via a join).
- CREATE INDEX product_orders_ix ON CLUSTER product_orders;