Oracle Indexes

Selectivity :-
It can be determined by comparing the number of distinct keys to the number of rows in the table.
select DISTINCT_KEYS from USER_INDEXES.
Greater the selectivity , then the index would return small records so the index will be used.

Clustering Factor :-
Low clustering factor means that the data /record is not clustered - in which case the number of blocks to read is less. Higher the clustering factor means that data is clustered so it has to read more data blocks.
select CLUSTERING_FACTOR from USER_INDEXES
(If the value of CLUSTERING_FACTOR is close to the number of leaf blcoks in the index then it means the data is well ordered. If it is close to the number of rows int he table then it means that the table is not well ordered)

Binary Height :-
Determines the amount of i/o that needs to be performed to return the ROWID.
Each level in the binary height adds an extra block that needs to be reas and since they are not read sequentially it requires separte i/o
select BLEVEL from USER_INDEXES
It increases in size bcas of the table size and range of values in the indexes columns is narrow.
If you DELETE large number of records, it will also increase the height
REBUILDING the index can help

INDEX_STATS - view.
will be populated after u run >analyze index indexname validate structure;

HASH Indexes
- The number of distinct values need to be know before u create the hash index.
- (this determines the number of hashkeys). Fastest way of gettinga record
- u cannot change the num of hashkeys so u have to preallocte....etc...

Index Organized Table
- Whole table is index.