Oracle Database Indexes b-tree index
Example
CREATE INDEX ord_customer_ix ON orders (customer_id);
By default, if we do not mention anything, oracle creates an index as a b-tree index. But we should know when to use it. B-tree index stores data as binary tree format. As we know that, index is a schema object which stores some sort of entry for each value for the indexed column. So, whenever any search happens on those columns, it checks in the index for the exact location of that record to access fast. Few points about indexing:
- To search for entry in the index, some sort of binary search algorithm used.
- When data cardinality is high, b-tree index is perfect to use.
- Index makes DML slow, as for each record, there should be one entry in the index for indexed column.
- So, if not necessary, we should avoid creating index.
