query for trunc(date) on a view that already filters for date

We’re accessing a view from another team’s database and to make it a lot simpler, the view looks a bit like this:

create view x_view as
select
  x.exec_time,
  ...
from
  stuff x
where
  x.exec_time > SYSDATE -2
 and
  ...
;

and when accessing the view, we further filter on the same column:

select
  *
from
  x_view x
where
  trunc(x.exec_time) = %1
 and
  ...
;

since I’d rather not change the view, but still get our query done fast and with a stable execution plan, I want to tell them what Indices would be beneficial. But how do I deal with those 2 predicates on the date field? I have those 3 options:

• add exec_time AND trunc(exec_time) to the index
• only exec_time in the index
• only trunc(exec_time) in the index

or is this construct so problematic that we should rather make a different view?

EDIT: I believe it’s oracle 11.2

Answer

Change your query to be:

where x.exec_time >= %1
and   x.exec_time < %1 + 1

and then a single index on EXEC_TIME should do the trick (obviously we are not considering other predicates you might have here)