Indexing not working with column using range operations in oracle

Question

I have created index on timestamp column for my table, but when I am querying and checking the explain plan in oracle it is doing the full table scan rather that range scan

Below is the DDL script for the table

CREATE TABLE EVENT (
   event_id     VARCHAR2(100) NOT NULL,
   status       VARCHAR2(50) NOT NULL,
   timestamp    NUMBER NOT NULL,
   action       VARCHAR2(50) NOT NULL
);

ALTER TABLE EVENT ADD CONSTRAINT PK_EVENT PRIMARY KEY ( event_id ) ;
CREATE INDEX IX_EVENT$timestamp ON EVENT (timestamp);

Below is the explain plan query used to get the explain plan -

EXPLAIN PLAN SET STATEMENT_ID = 'test3' for select * from EVENT where timestamp between 1620741600000 and 1621900800000 and status = 'CANC';

SELECT * FROM PLAN_TABLE WHERE STATEMENT_ID = 'test3';

Here is the explain plan that oracle returned -

I am not sure why the index is not working here, rather it is still doing the full table scan even after creating the index on the timestamp column.

Can someone please help me with this.

Oracle has decided that the condition is not restrictive enough. An index on (status, timestamp) would probably fare better. — Gordon Linoff
How many rows are in the event table? How many of them have a timestamp value in the range specified by your query (as an aside, timestamp is a reserved word so it's generally not a great idea to use that as a column name particularly when the data type is not timestamp)? How many of them have a status of "CANC"? How many have both the status you're looking for and are in the timestamp range? What are Oracle's cardinality estimates? — Justin Cave

O. Jones O. Jones · Accepted Answer · 2021-05-26T09:48:47

Gordon is correct. You need this index to speed up the query you showed us.

CREATE INDEX IX_EVENT$timestamp ON EVENT (status, timestamp);

Why? Your query requires an equality match on status and then a range scan on timestamp. Without the possibility of using the index for the equality match, Oracle's optimizer seems to have decided it's cheaper to scan the table than the index.

Why did it decide that?

Who knows? Hundreds of programmers have been working on the optimizer for many decades.

Who cares? Just use the right index for the query.

Indexing not working with column using range operations in oracle

2 Answers