oracle ındex

What is an Oracle Index?

When it comes to database performance, the first and most effective optimization method that comes to mind is indexing. Whether an SQL query runs in milliseconds or minutes often depends on the right index strategy. In this guide, we'll thoroughly examine the types of indexes in Oracle databases, their logic, and when to use them and when not to use them.

What is Oracle Index and Why is it Important?

The simplest analogy is the index page at the end of a thick book. Imagine you're looking for a specific topic in the book. Instead of flipping through each page (a full table scan), you go to the index page, find the page on which that topic appears, and then go directly to that page (an index scan).

In Oracle architecture, indexes reduce I/O (Input/Output) costs and dramatically improve query performance by shortening the access path to data. However, not every index is suitable for every table; choosing the wrong index can even slow down the system.

Basic Concepts: Cardinality and Selectivity

Before we move on to index types, we need to understand two critical terms:

Cardinality: The number of unique values in a column. (Example: TR ID Number has a high cardinality, Gender column has a low cardinality).
Selectivity: This is how few rows a query returns. Indexes perform best on queries with high selectivity (few rows returned).

Oracle Index Types and Usage Scenarios

Below you can find the most commonly used index types and code examples in the Oracle world.

1. B-Tree (Balanced Tree) Index

It is Oracle's default index type. If you don't specify a specific type (when you use CREATE INDEX), Oracle creates a B-Tree index in the background.

Structure: Similar to a tree structure. It consists of Root, Branch and Leaf blocks.
When to Use?
- High cardinality (wide variety) data (Customer ID, Phone Number, Email, etc.).
- In OLTP (Online Transaction Processing) systems.
- In queries using operators such as =, , BETWEEN.

-- A standard B-Tree Index
CREATE INDEX idx_customer_surname
ON customers(surname);

- Composite B-Tree Index (Order matters!)
CREATE INDEX idx_customer_name_surname
ON customers(first name, last name);

2. Bitmap İndex

It operates on the opposite logic of B-Tree. It stores data not in a tree structure, but as bit maps (0s and 1s).

When to Use?
- In Low Cardinality (Low Cardinality) columns (Gender, Marital Status, Yes/No fields).
- In Data Warehouse and OLAP systems.
- In reporting queries that use a lot of logical operators such as AND and OR.
When NOT to use ?
- In tables that are subject to intensive INSERT, UPDATE, DELETE operations. (Bitmap indexes lock a very large area while being updated – locking issue).

-- Bitmap Index for the Gender column (Only 'E' and 'F' values are present)
CREATE BITMAP INDEX idx_bm_gender
ON employee(gender);

3. Function-Based İndex (FBI)

If you query WHERE UPPER(ad) = 'ORACLE' while the data is stored as "Oracle" in the database, standard indexes are disabled. This is because a function has been run on the column. This is where FBI comes into play.

When to Use?
- If mathematical operations or functions (UPPER, LOWER, NVL, etc.) are used on columns in queries.
- For case-insensitive searches.

-- Creating a function-based index
CREATE INDEX idx_upper_ad
ON employee(UPPER(ad));

- The following query will now use the index:
SELECT * FROM employee WHERE UPPER(ad) = 'ORACLE';

4. Unique İndex

This index type provides both performance and data integrity. It prevents duplicate records from being inserted into the indexed column. When you define a primary key, Oracle automatically creates a unique index.

-- For fields that must be unique, such as ID No.
CREATE UNIQUE INDEX idx_uniq_id
ON citizenship (id_no);

5. Reverse Key İndex

It is typically used in Oracle RAC (Real Application Clusters) environments. For sequentially increasing values (such as IDs that increase with a sequence), the B-Tree stores the data in reverse order (e.g., 123 -> 321) to prevent it from being written to the same block of the index (Right-Hand Growth).

Advantage: Prevents I/O bottleneck (Hot block contention)
Disadvantage: It cannot be used in range scan (BETWEEN, >, <) queries, it only works in equality (=) queries.

-- Creating a Reverse Key Index
CREATE INDEX idx_siparis_rev
ON orders(order id) REVERSE;

6. Composite İndex

It keeps multiple columns in a single index.

Critical Rule (Leading Column): The most frequently used and most selective column in the WHERE condition should be written at the beginning of the index definition.
Skip Scan: Oracle can sometimes use this index even if the first column is not in the query (but it is costly).

-- First Last Name, then First Name
CREATE INDEX idx first_name ON personal(lastname, first name);

7. Index-Organized Tables (IOT)

Normally (Heap Table), data and index reside in separate locations. In IoT, the table itself is a B-Tree index. Data is physically stored in an ordered manner based on the primary key.

Area of Use: Narrow tables (Lookup tables) that are accessed only by the Primary Key

CREATE TABLE country_kodlari ( code_id NUMBER PRIMARY KEY, country name VARCHAR2(50)
) ORGANIZATION INDEX;

8. Partitioned Indexes

It divides large indexes across table partitions. Partitioning provides management and performance advantages (critical for large amounts of data).

Local Index: Separate index part for each partition (partition-aligned). Partition maintenance is easy (DROP/EXCHANGE).
Global Index: A single index for the entire table, but it can be managed by partition. Global indexes can become unavailable during partition operations.

CREATE TABLE SALES (
    SALE_ID      NUMBER,
    SALE_DATE    DATE,
    AMOUNT       NUMBER
)
PARTITION BY RANGE (SALE_DATE) (
    PARTITION p2023 VALUES LESS THAN (DATE '2024-01-01'),
    PARTITION p2024 VALUES LESS THAN (DATE '2025-01-01')
);

9. Cluster Indexes (Hash & B-Tree Cluster)

It is used in special structures where tables are created with CREATE CLUSTER.

Index Cluster: Data from different tables with the same cluster key are physically stored side by side. This improves join performance.
Hash Cluster: Data is accessed by going to an address calculated using a hash algorithm. Physical I/O is minimized. (It is the fastest method for equality queries).

CREATE CLUSTER CUST_CLUSTER (CUSTOMER_ID NUMBER)
SIZE 1024;

10. Domain (Custom) Indexler — Oracle Text, Spatial vb.

They are custom index types or user-defined index engines offered by Oracle (such as index type = CTXSYS.CONTEXT).

CREATE INDEX idx_doc_text ON documents(text) INDEXTYPE IS CTXSYS.CONTEXT;

11. Invisible, Compressed, Online/Offline ve Unusable Index’ler

Invisible Index

Invisible to the optimizer; used for testing purposes or to test the effect of the new index.

ALTER INDEX idx_emp_lastname INVISIBLE;

Compressed Index

It reduces disk usage by compressing indexes. Prefix compression can be particularly useful:

CREATE INDEX idx_emp_compr ON employees(last_name) COMPRESS 1;

Online Rebuild

It performs operations without closing access to the table during index rebuild:

ALTER INDEX idx_emp_lastname REBUILD ONLINE;

Unusable Index

After partition operations, the index may become UNUSABLE and must be rebuilt.