Disadvantages of Indexes in Oracle Database

    

Whenever we face a SQL performance issue in Oracle, one of the first things we normally check is whether the query is using the right index. 
 
Indexes are definitely important. In many cases, creating the right index can bring down the execution time of a query significantly. But that doesn't mean every slow query needs a new index. 
 
From a DBA point of view, an index always comes with some cost. If we keep adding indexes just to improve individual SQL statements, at some point those indexes themselves can become an overhead.
Let's look at some of the practical disadvantages we need to keep in mind.

Indexes Take Additional Space 

An index is a separate database object and it requires storage. 

 

For example: 

CREATE INDEX idx_orders_customer
ON orders(customer_id);

Once we create this index, Oracle has to maintain storage for both the table and the index. 

 

For one small table, this may not matter much. But in production databases, especially where tables contain millions or billions of rows, index size can become quite large. 

 

We can quickly check the space occupied by indexes from DBA_SEGMENTS.

SELECT segment_name,
       bytes/1024/1024 size_mb
FROM dba_segments
WHERE segment_type LIKE 'INDEX%'
ORDER BY bytes DESC;

This is something worth checking occasionally because I have seen databases where some indexes occupy a surprisingly large amount of space compared to what people expect.

DML Becomes More Expensive

This is probably the biggest downside of having too many indexes. 

 

Let's say we have an `ORDERS` table and insert a new order.

INSERT INTO orders
(order_id, customer_id, order_date, status)
VALUES
(10001, 501, SYSDATE, 'NEW');

Oracle doesn't only have to insert the row into the table. 

 

If we have indexes on `ORDER_ID`, `CUSTOMER_ID`, `ORDER_DATE` and `STATUS`, Oracle has to maintain those index structures as well. 

 

The same applies to DELETE. 

 

With an UPDATE, the overhead becomes important when we change an indexed column.  

 

UPDATE orders
SET status = 'SHIPPED'
WHERE order_id = 10001;

If `STATUS` is indexed, Oracle has additional index work to perform. 

 

This may not be noticeable for a few transactions. But on a busy OLTP database processing a large number of transactions, unnecessary indexes can definitely add overhead. 

 

This is why I normally think of indexing as a trade-off. 

 

We are making reads cheaper by making writes a little more expensive.

More Indexes Also Mean More Redo 

Another thing that is easy to forget is redo generation.  

 

When Oracle modifies index blocks as part of a transaction, those changes can also generate redo. 

 

Now imagine a batch job inserting a few million rows into a table with eight or ten indexes. 

 

Oracle isn't just inserting those rows. It is also doing the work required to maintain all those indexes. 

 

That can mean more redo, more archive logs and more work for the database overall. 

 

This becomes even more relevant when we have Data Guard because the generated redo also needs to be transported and applied on the standby. 

 

So when a bulk load is taking longer than expected, I wouldn't look only at the table. I would also check how many indexes Oracle is maintaining during that load. 

An Index Doesn't Mean Oracle Will Use It 

This is another common misunderstanding. We create an index and expect Oracle to use it. But Oracle's optimizer doesn't work that way. 

 

Consider this query: 

SELECT *
FROM orders
WHERE status = 'COMPLETED';

Assume the table has 10 million rows and 8 million of those rows have a status of `COMPLETED`. 

 

Even if we create: 

CREATE INDEX idx_orders_status
ON orders(status);

Oracle may still choose a full table scan. 

 

And that may actually be the correct plan. 

 

If Oracle needs most of the rows from the table, going through the index and then visiting a large number of table blocks may cost more than simply scanning the table. 

 

So when I see a full table scan in an execution plan, I don't immediately consider it a problem.
The first question should be: 

 

How much of the table are we actually reading?

Selectivity Matters 

 Suppose we have these two columns:

ORDER_ID
STATUS

`ORDER_ID` may contain millions of different values. 

 

`STATUS` may contain only: 

NEW
PROCESSING
COMPLETED
CANCELLED

Searching for a particular `ORDER_ID` could return one row. 

 

Searching for `STATUS = 'COMPLETED'` could return millions. 

 

That's a big difference. 

 

This is why simply saying "the column is used in the WHERE clause, so let's index it" isn't enough. 

 

I would rather check the data distribution first.  

 

For example:  

 

SELECT column_name,
       num_distinct,
       num_nulls,
       density
FROM dba_tab_col_statistics
WHERE owner = 'SALES'
AND table_name = 'ORDERS';

Of course, `NUM_DISTINCT` alone doesn't tell the whole story. We also need to understand the actual SQL and how much data it normally retrieves. 

Clustering Factor Is Worth Checking 

When troubleshooting why Oracle is not choosing an index, clustering factor is another thing I like to check. 

SELECT index_name,
       blevel,
       leaf_blocks,
       distinct_keys,
       clustering_factor
FROM dba_indexes
WHERE table_owner = 'SALES'
AND table_name = 'ORDERS';

The clustering factor gives us an idea of how the order of the index keys relates to the way rows are stored in the table blocks. 

If rows for nearby index values are scattered across many table blocks, an index range scan may require a lot of table block visits. 

That can make the index less attractive to the optimizer. 

So sometimes the question isn't: 

Why is Oracle ignoring my index? 

It is : 

Would using this index actually require more work?

That's an important difference.

Too Many Indexes Can Become a Maintenance Problem 

This is something we often see in databases that have been running for several years.

One performance issue comes up and somebody creates an index. 

 

A few months later, another index gets created. 

 

Then another. 

 

Eventually, we find something like:

IDX_ORDERS_CUSTOMER
IDX_ORDERS_CUSTOMER_DATE
IDX_ORDERS_DATE
IDX_ORDERS_STATUS
IDX_ORDERS_STATUS_DATE
IDX_ORDERS_CUSTOMER_STATUS

Maybe all of them are required.

Maybe not. 

 

There can also be overlapping indexes where one existing index could already support some of the queries for which another index was created. 

 

That's why, before creating a new index, I always think it is worth checking what is already available.

Bitmap Indexes Need Extra Care 

Bitmap indexes are useful, especially in reporting and data warehouse environments. 

 

For example, columns such as status, category or region can sometimes be good candidates depending on the workload. 

 

But I would be very careful about using bitmap indexes in a busy OLTP system. 

 

Frequent concurrent DML and bitmap indexes are generally not a great combination because of how modifications and locking work with bitmap index entries. 

 

That doesn't make bitmap indexes bad. 

 

They are simply designed for a different type of workload. 

 

This is one of those areas where understanding the application workload is more important than following a general indexing rule.

Don't Rebuild Indexes Just Because You Can 

Another thing I have seen is index rebuilds being included in regular maintenance jobs. 

 

For example:

ALTER INDEX idx_orders_customer REBUILD;

There are situations where rebuilding an index makes sense. 

 

But rebuilding every index every week because we assume indexes become "fragmented" is not something I would do without a reason. 

 

Oracle B-tree indexes are designed to deal with normal inserts, updates and deletes. 

 

Before rebuilding an index, I would want to know what problem I am trying to fix. 

 

Is there an actual space issue? 

 

Is there evidence of a performance problem? 

 

Did something unusual happen to the data? 

 

If we can't answer that, rebuilding the index may simply create unnecessary work. 

Always Check the Execution Plan 

Before creating another index, check what Oracle is doing. 

 

A simple starting point is: 

EXPLAIN PLAN FOR
SELECT *
FROM orders
WHERE customer_id = 501;

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY); 

When troubleshooting an SQL statement that has already executed, actual runtime statistics are even more useful when they are available. 

SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        NULL,
        NULL,
        'ALLSTATS LAST'
    )
);

Instead of only checking whether we have an `INDEX RANGE SCAN` or `TABLE ACCESS FULL`, look at the bigger picture. 

 

How many rows did Oracle expect? 

 

How many rows were actually returned? 

 

How many buffers were read? 

 

Are the statistics correct? 

 

How frequently does this SQL execute? 

 

Only after understanding these things would I decide whether another index is really required. 

 

One Simple Example 

 

Suppose this query is frequently executed:

SELECT order_id,
       customer_id,
       order_date,
       status
FROM orders
WHERE customer_id = 501
AND order_date >= SYSDATE - 30;

We could immediately create one index on `CUSTOMER_ID` and another on `ORDER_DATE`.

But that may not be the best solution.

Depending on the data and workload, something like this might make more sense: 

CREATE INDEX idx_orders_cust_date
ON orders(customer_id, order_date);

But even then, I wouldn't create it directly in production. 

 

First I would check the existing indexes, look at the execution plan, understand how many rows the query normally returns and see how much DML happens on the table. 

 

Then we can decide whether the performance benefit is worth the additional index.