CODEWITHSUNDEEP
sqloracle-databaseindexing
Thắng Nguyễn
Thắng Nguyễn

Reputation: 13

Oracle Query Ignores Local Index on Partitioned Table

I have a partitioned table SHARED_RAW_RE_GL78 in Oracle, partitioned by the column MONTHID. The table has around 300 million rows, with about 2 million new rows inserted daily. I created a local index on the CUST_DIVISION column in each partition, expecting Oracle to use this index in my query. However, when I run the query, Oracle performs a full table scan instead of using the index.

CREATE INDEX IDX_CUST_DIVISION_1 ON "KTC"."SHARED_RAW_RE_GL78" (CUST_DIVISION)
LOCAL
(
    PARTITION MONTHID_01,
    PARTITION MONTHID_02,
    PARTITION MONTHID_03,
    PARTITION MONTHID_04,
    PARTITION MONTHID_05,
    PARTITION MONTHID_06,
    PARTITION MONTHID_07,
    PARTITION MONTHID_08,
    PARTITION MONTHID_09,
    PARTITION MONTHID_10,
    PARTITION MONTHID_11,
    PARTITION MONTHID_12
);

----the query-----
SELECT GL_CODE, SUM(NET_LCY) 
FROM 
(
  SELECT GL_CODE, NET_LCY 
  FROM "KTC"."SHARED_RAW_RE_GL78"
  WHERE MONTHID=202408 AND CUST_DIVISION = 'KHCN'
) a
GROUP BY GL_CODE;

THIS is my execution plan of the code

Id Operation Name Rows Bytes Cost (CPU) Time Pstart Pstop
0 SELECT STATEMENT 279 7533 665K (1) 00:00:27
1 HASH GROUP BY 279 7533 665K (1) 00:00:27
2 PARTITION LIST SINGLE 59M 1520M 664K (1) 00:00:26 8 8
3 TABLE ACCESS FULL SHARED_RAW_RE_GL78 59M 1520M 664K (1) 00:00:26 8 8

From this execution plan, it’s clear that Oracle is performing a full table scan on partition 8, even though the query filters on MONTHID (partition key) and CUST_DIVISION (indexed column). I expected it to use the local index on CUST_DIVISION.

  1. Why is Oracle ignoring the local index on CUST_DIVISION?
  2. Is there a specific reason why the optimizer prefers a full table scan in this case?
  3. Are there any optimizer hints or adjustments that could force Oracle to use the index?

Any advice on how to improve this indexing strategy would be greatly appreciated! Thank you!

Upvotes: 0

Views: 73

Answers (2)

Jan Suchanek
Jan Suchanek

Reputation: 191

The Oracle Optimizer's decision to favour a full table scan over the local index on CUST_DIVISION typically stems from a combination of cost estimates and the selectivity of the index compared to scanning the entire partition. Here’s a summary of the probable causes:

Higher Cost of Index Access
Compared to Full Scan The Optimizer may have determined that using the index incurs a greater cost than a full scan. For each row matching the MONTHID, Oracle would first need to access the index, then fetch the actual data from the table—potentially a substantial I/O operation. Given that there are around 59 million rows in the partition, if a large portion of these match CUST_DIVISION = 'KHCN', Oracle may consider a full scan to be more efficient.

Low Selectivity on CUST_DIVISION
If CUST_DIVISION has a large number of distinct values but also a high frequency of the value 'KHCN', the Optimizer may view the index as suboptimal. Indexes tend to perform best when they narrow down results to a smaller subset of rows; hence, low selectivity may lead Oracle to forego the index.

Data Skew and Incomplete Statistics
Outdated or insufficient statistics on the table or index could lead the Optimizer to misjudge the cost of using the index. If the statistics do not accurately reflect the current data distribution in CUST_DIVISION, Oracle might deem the index ineffective.

Preference for Parallel Execution
Oracle might bypass the index if it determines that a parallel full scan would provide faster results. When the Optimizer senses that parallel execution will reduce overall time, it may favour a full table scan.

These are some of the common factors that could be influencing Oracle’s choice, particularly with large datasets or when data distribution is uneven.

You can try to force Oracle to use the index with the INDEX hint:

SELECT /*+ INDEX(SHARED_RAW_RE_GL78 IDX_CUST_DIVISION_1) */ GL_CODE, SUM(NET_LCY)
FROM

Upvotes: 1

Sudipto Bhattacharya
Sudipto Bhattacharya

Reputation: 88

Use partition name to make it faster

SELECT GL_CODE, SUM(NET_LCY) FROM ( SELECT GL_CODE, NET_LCY FROM "KTC"."SHARED_RAW_RE_GL78" PARTITION (MONTHID_08) WHERE CUST_DIVISION = 'KHCN' ) a GROUP BY GL_CODE;

Upvotes: -3

Related Questions