Index - Performance

> (Data|State|Operand) Management and Processing > (Data Type|Data Structure) > (Relation|Table) - Tabular data > Relation - Index (Indices)

1 - About

The impact of index on performance can be resumed as:

  • 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 the database uses them. See this example with B*Tree

  • btree : Conventional indexes. The most common indexes in use in Oracle and most other databases.
  • Bitmap index : Normally in a btree, 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)

Database 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