0

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.

  • Why the downvote and no answer? – user3641702 Apr 24 '17 at 13:32
  • I don't understatnd, why do you not use date_time instead of trunc(date_time)? – miracle173 Apr 24 '17 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 Apr 25 '17 at 13:48
  • @miracle173 I was trying to use the TRUNC(date_time) to avoid a near unique (if not indeed unique) index. – user3641702 Apr 27 '17 at 8:46
  • @unleashed I forgot to mention that the ID field is a foreign key and not the primary key. – user3641702 Apr 27 '17 at 8:47
2

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_TABLE_OUTPUT
-------------------------------------------------------
Plan hash value: 852675278

-------------------------------------------------------
| Id  | Operation                           | Name    |
-------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TABLE_1 |
|*  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? – ypercubeᵀᴹ Apr 24 '17 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 Apr 24 '17 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. – Balazs Papp Apr 24 '17 at 17:39
  • @BalazsPapp so basically it doesn't matter if DATE_TIME has index, because it will never be used for this field? – user3641702 Apr 27 '17 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). – Balazs Papp Apr 27 '17 at 9:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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