Choosing the correct index in Oracle

How to choose the correct index

I have an index in an Oracle table:

CREATE INDEX "I_TBL_XYZ_COLA"
      ON "TBL_XYZ" ("COLA") LOCAL;

And the query is:

SELECT  
sum(COLB) from tbl_xyz partition (TBL_XYZ_3) 
where MAP_ID='XXXXX' COLA >= to_date('2023/04/21 17:00:00', 'YYYY/MM/DD HH24:MI:SS') 
AND COLA < to_date('2025/05/21 20:00:00', 'YYYY/MM/DD HH24:MI:SS');

It is scanning about 4 million of data.

It is not using the index, instead, it is doing the scan, taking 9 secs.

Now, let's try to use the index forcefully in Oracle.

SELECT /*+ INDEX(TBL_XYZ I_TBL_XYZ_COLA) */
  COLB, COLA
FROM TBL_XYZ PARTITION (TBL_XYZ_4)
WHERE COLA >= TO_DATE('2025/05/22 10:00:00', 'YYYY/MM/DD HH24:MI:SS')
  AND COLA < TO_DATE('2025/05/23 00:00:00', 'YYYY/MM/DD HH24:MI:SS');

❌ When Forcing Index Hurts

I observed: Forcing index = 57s, vs full scan = 9s.

I explained the query, which is using the index. But still it is taking this much time. So, Oracle knows the index and purposefully avoids it.

🧠 Why Oracle Chooses Full Scan

Oracle's optimizer is not ignoring the index — it's making a cost-based decision:

Our query is aggregating (SUM) over a large range.

The index would require:

Scanning many index blocks. It is supposed to use the index for doing this.
But, then visiting table blocks to get COLB (since COLB is not in the index)
A full scan reads the partition sequentially and can do aggregation efficiently.
In this case, full scan is actually faster.

✅ So What Can we Do?

✅ Option 1: Create a Covering Index (Best when query is frequent)

If this query (or similar) is run often on this partition:

CREATE INDEX I_TBL_XYZ_NEW_INDEX
ON TBL_XYZ (COLB, COLA)
LOCAL;

This allows Oracle to:

Do index-only range scan

Get both COLA and COLB from the index

Do the SUM without table access

✅ Now I can avoid the 57s table lookup penalty.

✅ Option 2: Use Materialized View or Precomputed Stats

If you always run SUM over long ranges:

Consider precomputing aggregates by hour/day and storing them

Use a materialized view to keep the results updated. I will not go into those details.

✅ Option 3: Parallelism

For huge partitions, enable parallel query:

ALTER SESSION ENABLE PARALLEL QUERY;

SELECT /*+ parallel(tbl_xyz, 4) */
  SUM(COLB)
FROM tbl_xyz PARTITION (tbl_xyz_3)
WHERE COLA BETWEEN ...

I have gone with Option 1.

But the result is not impressive. It is still doing the full scan, and the new index is not even used.

The reason is our index is for COLB + COLA combination. In Oracle indexing it actually matters.
COLB + COLA != COLA + COLB in this case.

Since in the query, it is looking up with COLA, if the index leading column is that, it would be helpful for Oracle.

So I dropped the index and created a new index

CREATE INDEX I_TBL_XYZ_NEW_INDEX
ON TBL_XYZ (COLA, COLB)
LOCAL;

In this time, I ran the query, 9 secs got reduced to 1.5 secs. I explained the query. And this time it is straightforward, no more scanning, but it is using the index only for looking up the data it wants to sum.

So yes — in this case, the full scan is better unless you cover the query with a better index.

And our query is also now more robust since