Re: Performance question - query with OR

From: Peter Schneider <peter.schneider_at_okay.net>
Date: Thu, 09 Apr 1998 23:45:20 GMT
Message-ID: <352d59ad.22972963@news.ipf.net>

On 9 Apr 1998 20:21:50 GMT, "KenParis" <KenParis_at_prodigy.net> wrote:

>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.

Hi Ken,

queries with or-expressions often give bad performance because the optimized chose to do a full table scan. You can replace such constructs with an equivalent union-all statement:

 SELECT columns

   FROM	table
  WHERE (     column_1 = value1
          AND column_2 = value2
          AND column_3 = value3 )

UNION ALL
 SELECT columns

   FROM	table
  WHERE (     column_1 = value1
          AND column_2 > value2 )

UNION ALL
 SELECT columns

   FROM	table
  WHERE (     column_1 > value1 )

 ORDER BY 1, 2, 3 Note that an order-by clause in a union query requires that you specify the column positions by number rather than by name (so you must have column_1, column_2 and column_3 in your select list).

This query should give better performance. If not, don't blame it on me :-) - instead check the following:

What is your optimizer mode ? If it is CBO you need to have recent statistics for your tables and indexes. Is your sort_area_size configured suffiently large ? Have you distributed data and indexes properly to different physical disk drives ?

HTH
Peter

--
Peter Schneider
peter.schneider_at_okay.net Received on Thu Apr 09 1998 - 18:45:20 CDT