Concepts - Index

Oracle provides:
- B-tree indexes
- B-tree cluster indexes
- Hash cluster indexes
- Reverse key indexes
- Bitmap indexes
- Bitmap Join indexes

Performance:

However, the presence of many indexes on a table decreases the performance of updates, deletes, and inserts, because Oracle must also update the indexes associated with the table

Function-based index

- A function-based index computes the value of the function or expression and stores it in the index. You can create a function-based index as either a B-tree or a bitmap index.

Example 1

CREATE INDEX uppercase_idx ON employees (UPPER(first_name));

can facilitate processing queries such as this:SELECT * FROM employees WHERE UPPER(first_name) = 'RICHARD';

Example 2

For example, if you create the following index:CREATE INDEX idx ON table_1 (a + b * (c - 1), a, b);

then Oracle can use it when processing queries such as this:
SELECT a FROM table_1 WHERE a + b * (c - 1) <>The B-tree structure has the following advantages:
- All leaf blocks of the tree are at the same depth, so retrieval of any record from anywhere in the index takes approximately the same amount of time.
- B-tree indexes automatically stay balanced.
- All blocks of the B-tree are three-quarters full on the average.
- B-trees provide excellent retrieval performance for a wide range of queries, including exact match and range searches.
- Inserts, updates, and deletes are efficient, maintaining key order for fast retrieval.
- B-tree performance is good for both small and large tables and does not degrade as the size of a table grows.


Index Unique Scan
- used for returning the data from B-tree indexes.
- The optimizer chooses a unique scan when all columns of a unique (B-tree) index are specified with equality conditions.

Reverse Key Indexes

- reverses the bytes of each column indexed (except the rowid) while keeping the column order.

- Using the reverse key arrangement eliminates the ability to run an index range scanning query on the index. Because lexically adjacent keys are not stored next to each other in a reverse-key index,

- only fetch-by-key or full-index (table) scans can be performed.

Bitmap index

- Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then it means that the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid

- Not suitable for OLTP applications with large numbers of concurrent transactions modifying the data. Intended for decision support in data warehousing applications where users typically query the data rather than update it.

- Not suitable for columns that are primarily queried with less than or greater than comparisons. Useful for AND, OR, NOT, or equality queries.

- The advantages of using bitmap indexes are greatest for low cardinality columns: that is, columns in which the number of distinct values is small compared to the number of rows in the table.