Re: query crashing oracle

I would like to try this. Could you please post here the definition of the table and the exact query?

On 11 Apr 2003 18:10:55 -0700, richchri_at_erols.com (richie) wrote:

>We are running oracle 8i on linux.
>We have a query that resembles this:
>
>select field0 from table where ( (field1 >= value1 and field2 <=
>value2) or (field1 >= value3 and field2 <= value4) etc... )
>
>where there are around 36 field/value "OR" pairs. There is an index
>on the "field" in the where clause they are all of type integer.
>There is about 5,000 rows in the table.
>
>When this query runs it was running up the oracle process to 98% of
>the CPU and I couldn't kill the session with alter session kill...and
>oracle stop responding to other requests and hung up forever, the only
>way i could bring it back was to shutdown abort and then restart the
>instance. It tried it multiple times and eachg and every time it
>happened.
>
>While testing I found a solution that worked. We change the query to:
>
>select field0 from (
> select field0 from table where ( (field1 >= value1 and field2 <=
>value2) or (field1 >= value3 and field2 <= value4) etc... )
> union
> select field0 from table where ( (field1 >= value1 and field2 <=
>value2) or (field1 >= value3 and field2 <= value4) etc... )
>)
>
>and limited the number of field/value "OR" pairs to 15 in each sub
>query and union them together and it returns under a second.
>
>I tested the execution plan for each and they both use the index in
>fact the second one with the unions hits the index multple times (once
>for each sub query) and the 1st one only once.
>
>The result set returned for the query is only 36 rows of 1 field of an
>interger type.
>
>I'm just curious as to the dramatic difference. Anyone????
Received on Fri Apr 11 2003 - 20:32:14 CDT