query crashing oracle

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:10:55 CDT