Suppose I have a table (TABLE_1) with some fields, including ID (number), NAME (varchar2) and DATE_TIME (date). There was a need to create a index on those 3 fields to optimize queries like:
select * from TABLE_1 where ID = [SOME_VALUE] and NAME = [SOME_VALUE] and DATE_TIME is not null;
Because DATE_TIME can have a multitude of different datetime values, I decided to create the index as such:
CREATE INDEX IDX_TABLE_1 ON TABLE_1 (ID,NAME,TRUNC(DATE_TIME));
My question is: will Oracle use this index on the select query or will it ignore it and perform a full table scan on TABLE_1?
EDIT1: I forgot to mention that the ID field is a foreign key.