Re: Performance question - query with OR

Although I have not tested it in Oracle (I have in DB2), there is a concept called the 'exclusion cursor'. This cursor may provide the optimizer with a better plan. Try this:

select * from tabx where

 key1 > :key1 andnot
(key1 = :key1 and 
 key2 < :key2) and not 
(key1 = :key1 and 
 key2 = :key2 and 
 key2 < :key3); 

This executes the same thing as below, without the 'OR'.

'Or' Predicates cause either table scans or concatenations. The TS scans is almost always bad, the concatennation could be O.K.. (the concatenation is the term used to describe the UNION implmenation explained in your previous reply) However, concatenation could be a killer (as in select * from tab where key1 > :key1). You might consider trying the numrows option (it may screwup your query, if you have an order by clause).

                                Art

KenParis <KenParis_at_prodigy.net> wrote in article <01bd63f5$29333100$0c549cd1_at_kenp0007>...
> I need help on Oracle performance. I don't have direct access to
> Oracle - it's all being done by a remote associate.
>
> We are executing a query of the form:
>
> SELECT columns
> FROM table
>
> WHERE (column_1 = value1
> AND column_2 = value2
> AND column_3 = value3)
>
> OR (column_1 = value1
> AND column_2 > value2)
>
> OR (column_1 > value1)
>
> ORDER BY column_1, column_2, column_3
>
> Even though we have an index on column_1, column_2 and column_3, Oracle
> still
> does a sort (I'm told it doesn't like the OR). We tried rewriting this
> using the more
> complicated but equivalent version replacing the ORs with AND NOTs (among
> other
> changes), but this was worse.
>
> Any suggestions on how to deal with queries of this form (this solves the
> general problem
> of seeing a set of columns in order and wanting to get the next set of
> values after those
> with values: value1, value2 and value3).
>
> For instance, does Oracle support a WHERE clause of the form:
> WHERE column_1, column_2, column_3 > value1, value2, value3 ?
>
Received on Tue Apr 14 1998 - 10:17:03 CDT