13.2 Indexes

13.2 Indexes

In relational database management systems, searching a table without an index requires a Full Table Scan (FTS), where Oracle reads every data block allocated to the table from disk into the buffer cache. For large tables containing millions of rows, full scans introduce high I/O latency and CPU overhead.

An Index is an optional schema object stored independently from table data that provides a fast, direct path to table rows. An index contains the indexed column key values along with the corresponding ROWID—the physical address representing the exact data file, block, and slot where each row resides.

---

Anatomy of a Physical ROWID & Index Lookup

An extended ROWID in Oracle Database is an 18-character base-64 encoded string representing the exact physical storage coordinates of a row:

+-------------------------------------------------------------------------+
|                       EXTENDED ROWID STRUCTURE                          |
+-------------------------------------------------------------------------+
|                                                                         |
|   OOOOOO      FFF     BBBBBB      RRR                                   |
|  [AAAWdK]    [AAB]   [AAACVf]    [AAA]                                  |
|  Data Object  Data    Data Block  Row Slot                              |
|  Number       File    Number      in Block                              |
|                                                                         |
+-------------------------------------------------------------------------+

How Index Row Retrieval Works

When a query executes SELECT * FROM employees WHERE employee_id = 105;:

1. Oracle traverses the index structure on employee_id to locate key 105.
2. The index leaf entry provides the exact ROWID (e.g., AAAWdKAABAAACVfAAA).
3. Oracle performs a single physical/buffer read directly to that specific block and slot, retrieving the row in a fraction of a millisecond.

+-------------------------------------------------------------------------+
|                     INDEX SCAN VS. FULL TABLE SCAN                      |
+-------------------------------------------------------------------------+
|                                                                         |
|  FULL TABLE SCAN (FTS):                                                 |
|  [Block 1] -> [Block 2] -> [Block 3] -> ... -> [Block 50,000]           |
|  (Reads 100% of blocks to find 1 row)                                   |
|                                                                         |
|  INDEX LOOKUP BY ROWID:                                                 |
|  [Index Root] -> [Index Branch] -> [Index Leaf] -> ROWID -> [Block 42]  |
|  (Reads 3-4 index blocks + 1 table block = 4 I/O operations total)      |
|                                                                         |
+-------------------------------------------------------------------------+

---

B-Tree Indexes (Standard / Default Index)

By default, executing CREATE INDEX creates a B-Tree (Balanced Tree) index. A B-Tree index maintains hierarchical balance: all leaf blocks are located at the exact same tree depth.

+-------------------------------------------------------------------------+
|                       B-TREE INDEX ARCHITECTURE                         |
+-------------------------------------------------------------------------+
|                                                                         |
|                           +---------------+                             |
|                           |  ROOT BLOCK   |                             |
|                           |  Keys: 1-100  |                             |
|                           +-------+-------+                             |
|                                   |                                     |
|                  +----------------+----------------+                    |
|                  |                                 |                    |
|                  v                                 v                    |
|          +---------------+                 +---------------+            |
|          | BRANCH BLOCK  |                 | BRANCH BLOCK  |            |
|          |  Keys: 1-50   |                 |  Keys: 51-100 |            |
|          +-------+-------+                 +-------+-------+            |
|                  |                                 |                    |
|          +-------+-------+                 +-------+-------+            |
|          |               |                 |               |            |
|          v               v                 v               v            |
|     +---------+     +---------+       +---------+     +---------+       |
|     |  LEAF   |<--->|  LEAF   |<----->|  LEAF   |<--->|  LEAF   |       |
|     | BLOCK 1 |     | BLOCK 2 |       | BLOCK 3 |     | BLOCK 4 |       |
|     +---------+     +---------+       +---------+     +---------+       |
|     [10|ROWID]      [35|ROWID]        [60|ROWID]      [85|ROWID]        |
|     [20|ROWID]      [45|ROWID]        [75|ROWID]      [99|ROWID]        |
|                                                                         |
+-------------------------------------------------------------------------+

Structural Components of a B-Tree Index

• Root Block: The top-level entry point containing key ranges and pointers to branch blocks.
• Branch Blocks: Intermediate navigational nodes that guide the search down the tree hierarchy based on key values.
• Leaf Blocks: The bottom-level blocks containing the indexed key value and the corresponding table row ROWID. Leaf blocks are linked together as a bi-directional double-linked list (prev and next pointers), allowing extremely efficient sequential range scans (WHERE salary BETWEEN 5000 AND 10000) without re-traversing the root.

When to Use B-Tree Indexes

• Columns with high cardinality (columns containing a large number of distinct, unique, or near-unique values, e.g., ssn, employee_id, email, phone_number, order_number).
• Columns frequently used in WHERE equality and range conditions, JOIN predicates, and ORDER BY clauses.

---

Unique vs. Non-Unique Indexes & Constraint Mechanics

Indexes are classified as Unique or Non-Unique based on data uniqueness enforcement:

Syntax

-- Create a non-unique B-tree index on department_id
CREATE INDEX emp_dept_idx ON employees(department_id);

-- Create a unique B-tree index on email
CREATE UNIQUE INDEX emp_email_uk_idx ON employees(email);

Automatic Index Creation for Constraints

When you define a PRIMARY KEY or UNIQUE constraint on a table (either during CREATE TABLE or via ALTER TABLE ADD CONSTRAINT):

1. Oracle checks if an existing index (unique or non-unique) is already available on the target column(s). If found, Oracle reuses that index.
2. If no matching index exists, Oracle automatically creates a unique B-Tree index with the same name as the constraint.

+-------------------------------------------------------------------------+
|             CONSTRAINT AND AUTOMATIC INDEX LIFECYCLE                    |
+-------------------------------------------------------------------------+
|                                                                         |
|  1. ALTER TABLE employees ADD CONSTRAINT emp_pk PRIMARY KEY (emp_id);   |
|     --> Oracle automatically creates UNIQUE INDEX 'EMP_PK'.             |
|                                                                         |
|  2. ALTER TABLE employees DROP CONSTRAINT emp_pk;                       |
|     --> Oracle automatically drops UNIQUE INDEX 'EMP_PK'.               |
|                                                                         |
|  3. ALTER TABLE employees DROP CONSTRAINT emp_pk KEEP INDEX;            |
|     --> Constraint is dropped; UNIQUE INDEX 'EMP_PK' is PRESERVED!      |
|                                                                         |
|  4. Manually create index first: CREATE INDEX my_idx ON emp(emp_id);    |
|     Add constraint: ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY...|
|     Drop constraint: ALTER TABLE emp DROP CONSTRAINT emp_pk;            |
|     --> Manually created index 'MY_IDX' is RETAINED!                    |
|                                                                         |
+-------------------------------------------------------------------------+

---

Composite (Concatenated) Indexes & Leading Column Rules

A Composite Index (or concatenated index) is an index created on multiple columns of a single table (up to a maximum of 32 columns in Oracle SQL).

CREATE INDEX emp_names_comp_idx ON employees(last_name, first_name, department_id);

The Leading Column Rule

The order of columns in a composite index definition is critical:

• The leading column is the first column listed in the index definition (last_name).
• A standard Index Range Scan is utilized if the query's WHERE clause specifies the leading column (e.g., WHERE last_name = 'King' or WHERE last_name = 'King' AND first_name = 'Steven').
• If a query omits the leading column (e.g., WHERE first_name = 'Steven'), Oracle typically cannot perform a standard index range scan and defaults to a Full Table Scan (or an Index Skip Scan if the leading column has very low cardinality).

Exam Rule of Thumb: Always place the most frequently filtered, high-cardinality column as the leading column in a composite index.

---

Function-Based Indexes (FBI)

In standard SQL queries, applying a single-row function or mathematical expression to an indexed column prevents Oracle from using standard B-Tree indexes on that column.

The Problem: Suppressed Indexes

-- Index exists on LAST_NAME
CREATE INDEX emp_last_idx ON employees(last_name);

-- The UPPER function suppresses the index; Oracle performs a FULL TABLE SCAN!
SELECT employee_id, last_name, salary
FROM employees
WHERE UPPER(last_name) = 'KING';

The Solution: Function-Based Index

A Function-Based Index (FBI) calculates and stores the pre-computed evaluation of a deterministic expression or function in the index leaf blocks.

-- Create a Function-Based Index
CREATE INDEX emp_upper_last_fbi ON employees(UPPER(last_name));

-- Oracle Cost-Based Optimizer (CBO) now uses an Index Range Scan on EMP_UPPER_LAST_FBI!
SELECT employee_id, last_name, salary
FROM employees
WHERE UPPER(last_name) = 'KING';

Critical Requirements for Function-Based Indexes

1. Deterministic Functions: The function or expression used in the FBI must be strictly deterministic (it must always return the exact same output for the same input). Non-deterministic functions (such as SYSDATE, CURRENT_TIMESTAMP, USER, or DBMS_RANDOM.VALUE) are strictly prohibited.
2. Exact Expression Match: The query WHERE clause must match the exact expression defined in the index. If the index is defined on UPPER(last_name), a query using LOWER(last_name) will not use the index.

---

Bitmap Indexes & Data Warehousing

A Bitmap Index uses compact binary strings (bit vectors) to represent the presence or absence of a key value across rows in a table.

Structure of a Bitmap Index

For each distinct value in the indexed column, Oracle creates a bitmap string where each bit corresponds to a physical row slot. A bit of 1 indicates the row contains that value; a bit of 0 indicates it does not.

+-------------------------------------------------------------------------+
|                        BITMAP INDEX ARCHITECTURE                        |
+-------------------------------------------------------------------------+
|                                                                         |
|  TABLE: CUSTOMERS                                                       |
|  Row 1: MARITAL_STATUS = 'S', GENDER = 'M', REGION = 'EAST'             |
|  Row 2: MARITAL_STATUS = 'M', GENDER = 'F', REGION = 'WEST'             |
|  Row 3: MARITAL_STATUS = 'M', GENDER = 'M', REGION = 'EAST'             |
|  Row 4: MARITAL_STATUS = 'S', GENDER = 'F', REGION = 'NORTH'            |
|                                                                         |
|  BITMAP INDEX ON MARITAL_STATUS:                                        |
|  Key Value 'S': [ 1, 0, 0, 1 ]                                          |
|  Key Value 'M': [ 0, 1, 1, 0 ]                                          |
|                                                                         |
|  BITMAP INDEX ON GENDER:                                                |
|  Key Value 'M': [ 1, 0, 1, 0 ]                                          |
|  Key Value 'F': [ 0, 1, 0, 1 ]                                          |
|                                                                         |
|  QUERY: WHERE MARITAL_STATUS = 'S' AND GENDER = 'M'                     |
|  Boolean Bitwise AND Operation:                                         |
|    'S' Bit vector:  1  0  0  1                                          |
|    'M' Bit vector:  1  0  1  0                                          |
|    AND Result:      1  0  0  0  --> Matches Row 1 ONLY!                 |
|                                                                         |
+-------------------------------------------------------------------------+

Syntax

CREATE BITMAP INDEX cust_marital_bidx ON customers(marital_status);
CREATE BITMAP INDEX cust_gender_bidx ON customers(gender);

The DML Locking Bottleneck in OLTP

In a bitmap index, a single index block stores bitmap vectors covering hundreds of table rows. When an INSERT, UPDATE, or DELETE statement executes on a single row, Oracle locks the entire bitmap index segment/block. This effectively locks hundreds of unrelated rows, causing severe transaction serialization and deadlocks.

Exam Rule: Bitmap indexes are intended exclusively for read-heavy Data Warehouses / Decision Support Systems (DSS). They should never be used in high-concurrency Online Transaction Processing (OLTP) applications.

---

Exhaustive Comparison: B-Tree vs. Bitmap Indexes

---

1Z0-071 Guidelines: When to Create vs. Avoid Indexes

+-------------------------------------------------------------------------+
|                    INDEX DESIGN DECISION GUIDELINES                     |
+-------------------------------------------------------------------------+
|                                                                         |
|  CREATE AN INDEX WHEN:                                                  |
|  [✓] Column contains a wide range of values (high cardinality).         |
|  [✓] Column is frequently used in WHERE clauses or JOIN conditions.     |
|  [✓] Table is large and queries retrieve < 2% to 4% of total rows.      |
|  [✓] Column contains a high percentage of NULL values and queries      |
|      filter for non-null values.                                        |
|                                                                         |
|  AVOID CREATING AN INDEX WHEN:                                          |
|  [X] Table is small (full table scan is faster and requires fewer I/Os).|
|  [X] Columns are rarely used as predicates in queries.                  |
|  [X] Queries regularly retrieve more than 15% - 20% of total rows.      |
|  [X] Table undergoes intensive, high-volume DML (INSERT/UPDATE/DELETE). |
|  [X] Columns are indexed as part of an existing composite index.        |
|                                                                         |
+-------------------------------------------------------------------------+

---

Dropping Indexes

An index is removed using the DROP INDEX statement:

DROP INDEX emp_dept_idx;

Operational Rules for Dropping Indexes:

• Dropping an index removes the index storage structure from the database; it has zero effect on the physical data rows stored in the base table.
• You cannot modify the column structure of an index with ALTER INDEX. To add, remove, or reorder columns, the index must be dropped and recreated.
• You cannot directly drop an index that was automatically generated by Oracle to enforce a PRIMARY KEY or UNIQUE constraint; you must drop or disable the constraint itself (unless using KEEP INDEX).