Feb 132013
 
select how_to_write_this_properly
from   index_breaking_predictates
where  trunc(i_want_all_the_times) =
         to_date (:on_this_date, 'dd/mm/yyyy')
and    the_numbers + 1 = :one_less_than_this
and    instr(text, :starting_with_this) = 1;

HOW_TO_WRITE_THIS_PROPERLY
---------------------------------------------
where i_want_all_the_times >= 
    to_date (:on_this_date, 'dd/mm/yyyy')
and i_want_all_the_times < 
    to_date (:on_this_date, 'dd/mm/yyyy') + 1 
and the_numbers = :one_less_than_this - 1
and text like :with_this_at_the_start || '%'
While you could create function-based indexes to satisfy these conditions, they will be less reusable.
trunc(date) is a common offender in my experience, with indexes like this preventing "time-of-day" query indexing

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>