Let's break down why your local index ReadTime_test_idx isn't being picked up by the optimizer, and walk through actionable fixes for your scenario.

First, let's recap your setup to align on context:

• You have a massive range-interval partitioned table eip.Meter_Read_Alert_test (6 trillion rows annually) partitioned monthly by Read_time.
• You created three local indexes, including one on the Read_time column.
• When querying a single day's worth of data in November 2019, the optimizer chooses a full table scan instead of using the Read_time index.

Your Schema & Query Details #

Create Table Statement:

CREATE TABLE eip.Meter_Read_Alert_test ( 
    Mfg_serial_num VARCHAR2(50 BYTE) , 
    Channel_id NUMBER NOT NULL, 
    Read_time TIMESTAMP(0), 
    CONSTRAINT pk_Alert_test PRIMARY KEY (ID,channel_id,Read_time) 
) PARTITION BY RANGE (Read_time) 
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) 
( PARTITION p1 VALUES less than('01-09-19 12:00:00.000000000 AM') ) ;

Local Indexes:

CREATE INDEX mfg_SNo_test_idx on eip.Meter_Read_Alert_test ( Mfg_serial_num ) tablespace SPRING_METER_READ Local ;
CREATE INDEX channel_ID_test_idx on eip.Meter_Read_Alert_test (Channel_ID) tablespace SPRING_METER_READ Local ;
CREATE INDEX ReadTime_test_idx on eip.Meter_Read_Alert_test (Read_Time) tablespace SPRING_METER_READ Local ;

Problem Query:

select * from meter_read_alert_test where read_time between '19-11-2019 12:00:00 AM' and '19-11-2019 11:00:00 PM';

Execution Plan:

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9090K| 728M| 411K (6)| 00:00:17 | | |
| 1 | PARTITION RANGE ITERATOR| | 9090K| 728M| 411K (6)| 00:00:17 | KEY | KEY |
|* 2 | TABLE ACCESS FULL | METER_READ_ALERT_TEST | 9090K| 728M| 411K (6)| 00:00:17 | KEY | KEY |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("READ_TIME">=TO_TIMESTAMP('19-11-2019 12:00:00 AM') AND "READ_TIME"<=TO_TIMESTAMP('19-11-2019 11:00:00 PM'))

Why the Index Isn't Being Used #

1. Your Query Returns a Large Chunk of the Partition #

Looking at the execution plan, Oracle estimates you're fetching ~90.9 million rows from the target November 2019 partition. The optimizer prioritizes the lowest-cost access path:

• If you're retrieving 10-20% or more of the partition's total data, a full table scan (sequential I/O) is cheaper than an index scan (random I/O to read index entries, plus additional random I/O to fetch table rows via ROWID).
• In this case, the full scan might actually be the optimal choice—we'll verify this first.

2. Outdated or Inaccurate Statistics #

Oracle relies on up-to-date statistics to calculate costs. If:

• Table/partition stats are stale (critical for a rapidly growing 6T-row table),
• Index stats for ReadTime_test_idx haven't been refreshed,
  the optimizer will miscalculate the cost of index vs full scan and pick the wrong path.

3. Implicit Data Type Conversion #

Your query uses string literals for the TIMESTAMP(0) column Read_time. Oracle has to implicitly convert these strings to timestamps. While this works functionally, it can confuse the optimizer's ability to match the predicate to the index, or skew cost calculations.

4. Index Fragmentation or Invalid State #

Local indexes can become fragmented over time as data is inserted/updated/deleted in partitions. If ReadTime_test_idx has high fragmentation or is in an invalid state, the optimizer will avoid it due to higher perceived cost.

Fixes to Try #

1. Verify if Full Scan is Actually Worse #

First, check how many total rows are in the November 2019 partition:

-- Replace P_201911 with your actual auto-generated partition name
SELECT COUNT(*) FROM eip.Meter_Read_Alert_test PARTITION (P_201911);

If your 90.9M rows are close to the partition's total, the full scan is likely the best choice—no need to force the index here.

2. Update Statistics #

Refresh table, partition, and index stats to give the optimizer accurate data:

-- Gather table stats including partitions, and cascade to indexes
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    OWNNAME => 'EIP',
    TABNAME => 'METER_READ_ALERT_TEST',
    CASCADE => TRUE,
    GRANULARITY => 'ALL',
    ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE
);

-- Optional: Gather index stats specifically for ReadTime_test_idx
EXEC DBMS_STATS.GATHER_INDEX_STATS(
    OWNNAME => 'EIP',
    INDNAME => 'READTIME_TEST_IDX'
);

Re-run your query after this to see if the execution plan changes.

3. Eliminate Implicit Conversion #

Rewrite the query to explicitly convert your string literals to TIMESTAMP(0) using the correct format mask. This removes ambiguity for the optimizer:

SELECT * 
FROM meter_read_alert_test 
WHERE read_time BETWEEN 
    TO_TIMESTAMP('19-11-2019 12:00:00 AM', 'DD-MM-YYYY HH:MI:SS AM') 
    AND TO_TIMESTAMP('19-11-2019 11:00:00 PM', 'DD-MM-YYYY HH:MI:SS AM');

4. Force the Index (Only if You're Sure It's Better) #

If you've confirmed the index should be faster (e.g., the result set is a small subset), use a query hint to force the optimizer to use ReadTime_test_idx:

SELECT /*+ INDEX(METER_READ_ALERT_TEST READTIME_TEST_IDX) */ * 
FROM meter_read_alert_test 
WHERE read_time BETWEEN '19-11-2019 12:00:00 AM' AND '19-11-2019 11:00:00 PM';

Note: Hints override the optimizer's decisions, so only use this after testing—data distribution changes could make this hint suboptimal later.

5. Check and Repair the Index #

Validate the index state and fix fragmentation:

-- Check if the index is valid
SELECT INDEX_NAME, STATUS, TABLESPACE_NAME 
FROM USER_INDEXES 
WHERE INDEX_NAME = 'READTIME_TEST_IDX';

-- Check for fragmentation (high leaf blocks, low clustering factor)
SELECT INDEX_NAME, LEAF_BLOCKS, AVG_LEAF_BLOCKS_PER_KEY, CLUSTERING_FACTOR
FROM USER_INDEXES 
WHERE INDEX_NAME = 'READTIME_TEST_IDX';

If the index is invalid or highly fragmented, rebuild it:

-- Rebuild all partitions of the local index
ALTER INDEX READTIME_TEST_IDX REBUILD PARTITION ALL;

内容的提问来源于stack exchange，提问作者Puja Shaw