Table of Contents
Oracle Database - Indexes
About
Indexing is a crucial aspect of the application design. Too many indexes and the performance of DML will suffer. Too few indexes and the performance of queries (including inserts, updates, and deletes) will suffer. Finding the right mix is critical to your application's performance.
When to index and what column to index are things you need to pay attention to in your design. An index does not always mean faster access, in fact, you will find that indexes will decrease performance in many case if Oracle uses them. See this example with B*Tree
- B*Tree indexes : Conventional indexes. The most common indexes in use in Oracle and most other databases.
- Bitmap index : Normally in a B*Tree indexes, there is a one-to-one relationship between an index entry and a row: an entry points to a row. With bitmap index, a single entry uses a bitmap to point to many rows simultaneously (One-to-Many Relationship). They are appropriate for highly repetitive data (data with a few distinct values relative to the total number of rows in the table)
Oracle can use an index on a column to count the number of rows in the table only when the column has been declared NOT NULL
Articles Related
Properties about index
Indexes, by default, are stored in ascending order.
DataWarehouse
Basic indexes are created and provided out of the box. These indexes are created based on the kind of reports. Heap and Bitmap indexes are created out of the box.
- Bitmap indexes are typically created when the cardinality of rows are low.
- All indexes are created on the Aggregated and Central FACT tables only
- No additional indexes are created on OFSA Tables
- If the tables are partitioned then 'LOCAL' indexes are created in case of BITMAP indexes
- All Indexes are dropped/disabled before load and enabled/created post loading
