Optimize Oracle Between Date Statement

Question

I got an oracle SQL query that selects entries of the current day like so:

SELECT   [fields] 
FROM     MY_TABLE T 
WHERE    T.EVT_END BETWEEN TRUNC(SYSDATE) 
                       AND TRUNC(SYSDATE) + 86399/86400
  AND    T.TYPE = 123

Whereas the EVT_END field is of type DATE and T.TYPE is a NUMBER(15,0).

Im sure with increasing size of the table data (and ongoing time), the date constraint will decrease the result set by a much larger factor than the type constraint. (Since there are a very limited number of types)

So the basic question arising is, what's the best index to choose to make the selection on the current date faster. I especially wonder what the advantages and disadvantages of a functional index on TRUNC(T.EVT_END) to a normal index on T.EVT_END would be. When using a functional index the query would look something like that:

SELECT   [fields] 
FROM     MY_TABLE T 
WHERE    TRUNC(T.EVT_END) = TRUNC(SYSDATE) 
  AND    T.TYPE = 123

Because other queries use the mentioned date constraints without the additional type selection (or maybe with some other fields), multicolumn indexes wouldn't help me a lot.

Thanks, I'd appreciate your hints.

Answer 1

Your index should be TYPE, EVT_END.

CREATE INDEX PIndex
ON MY_TABLE (TYPE, EVT_END)

The optimizer plan will first go through this index to find the TYPE=123 section. Then under TYPE=123, it will have the EVT_END timestamps sorted, so it can search the b-tree for the first date in the range, and go through the dates sequentially until a data is out of the range.

Answer 2

Based on the query above the functional index will provide no value. For a functional index to be used the predicate in the query would need to be written as follows:

SELECT [fields] 
FROM MY_TABLE T 
WHERE TRUNC(T.EVT_END) BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 86399/86400
  AND T.TYPE = 123

The functional index on the column EVT_END, is being ignored. It would be better to have a normal index on the EVT_END date. For a functional index to be used the left hand of the condition must match the declaration of the functional index. I would probably write the query as:

SELECT [fields] 
FROM MY_TABLE T 
WHERE T.EVT_END BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE+1)
  AND T.TYPE = 123

And I would create the following index:

CREATE INDEX bla on MY_TABLE( EVT_END )

This is assuming you are trying to find the events that ended within a day.