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:


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.

  • Why the downvote and no answer? Commented Apr 24, 2017 at 13:32
  • I don't understatnd, why do you not use date_time instead of trunc(date_time)?
    – miracle173
    Commented Apr 24, 2017 at 16:15
  • I upvoted the answer below because I feel it is correct. But, you have a column, ID, which implies either uniqueness, or near uniqueness. If so, why have multiple columns when you have high selectivity on the ID column?
    – unleashed
    Commented Apr 25, 2017 at 13:48
  • @miracle173 I was trying to use the TRUNC(date_time) to avoid a near unique (if not indeed unique) index. Commented Apr 27, 2017 at 8:46
  • @unleashed I forgot to mention that the ID field is a foreign key and not the primary key. Commented Apr 27, 2017 at 8:47

1 Answer 1


Depends on the data distribution. I can easily construct examples for both cases (index vs table scan).

Just think about the case when the columns are unique or nearly unique (index), or when all the rows are the same (table scan).

Technically your index can be used for the above query.

Given the general nature of a column named ID, I would say the index will be chosen, but it is not guaraanteed.

The predicate DATE_TIME is not null will be processed at the table level, not the index level, even if you make sure that the NULL rows are indexed as well:

create index index_1 on table_1(id, name, trunc(date_time), 1);

Plan hash value: 852675278

| Id  | Operation                           | Name    |
|   0 | SELECT STATEMENT                    |         |
|*  2 |   INDEX RANGE SCAN                  | INDEX_1 |

Predicate Information (identified by operation id):

   1 - filter("DATE_TIME" IS NOT NULL)
   2 - access("ID"=1 AND "NAME"='Oracle')
  • 1
    Do they need to change the query to and TRUNC(DATE_TIME) is not null to align with the index? Or is Oracle optimizer smart to identify that column is (not) null is equivalent to TRUNC(column) is (not) null? Commented Apr 24, 2017 at 15:44
  • Balasz, I think '1- filter("DATE_TIME" IS NOT NULL)' means that Oracle access the row by rowid from the table to check if the value of "DATE_TIME" is not null. so the fact that the trunc(date_time) field is not null is not used.
    – miracle173
    Commented Apr 24, 2017 at 16:14
  • @miracle173 Exactly what I said. The original query contains DATE_TIME is not null, and not TRUNC(DATE_TIME) is not null. Commented Apr 24, 2017 at 17:39
  • @BalazsPapp so basically it doesn't matter if DATE_TIME has index, because it will never be used for this field? Commented Apr 27, 2017 at 9:03
  • @user3641702 Your predicate contains DATE_TIME is not null. But you have TRUNC(DATE_TIME) in your index. Even though we know that TRUNC(DATE_TIME) returns NULL when DATE_TIME is NULL, the database does not have this optimization. If you want an index to be used, include the column DATE_TIME in the index, and not TRUNC(DATE_TIME). Commented Apr 27, 2017 at 9:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.