Re: performance using inequality query

Stephan Langer wrote in message <3A816EE0.747B5FC6_at_dixi-wc.de>...
>hi,
>
>have you tried:
>
>SELECT *
>FROM METER_READ
>WHERE
> START_DATE < :end_date AND
>END_DATE > :start_date;
>
>AFAIK Indexes can only be used for columns standing on the left side of an
>expression.
>

Not true. The manuals only say that "indexes can only be used for columns standing on the left side of an expression" because _in their examples_ the condition is written so that database column name is on the LHS (which, putting my head above the parapet, is what most people find easier to read).

The reason a FTS is being used is that, at the time of parsing, Oracle doesn't know what the values of the bind variables will be, and so it is quite likely that the conditions will result in a large proportion of the table being read. FTS is therefore a sensible choice.

I think it will be difficult to write this query to use an index without the use of a hint.

Even using an index, this query will still be inefficient. Suppose an index on START_DATE is used. The query will have to read every row with START_DATE less than the bind variable and then test the value of END_DATE for each one. A similar problem occurs if an index on END_DATE is used.

If the rows in your table represent non-overlapping ranges then you can write this *much* more efficiently using PL/SQL or application logic: Open a cursor which does a

    SELECT ...

      FROM METER_READ
     WHERE END_DATE > :end_date

(use a hint to force the use of the index on END_DATE) and read *one* row from the table. Check if START_DATE < :start_date. If it satisfies, you've got your row. If not, stop, no other row can match.

If possible, restructure your application code to do this first then feed the rowid or primary key of the returned row into the piece of SQL which does all your other joins etc.

HTH, Dave.

--
If you reply to this newsgroup posting by email, remove the "nospam"
from my email address first.