Re: query crashing oracle

With your query expressed in the original fashion, it seems quite likely that Oracle is trying to optimize 36 queries one after the other - which may slow things down a bit - but not have quite the dramatic effect you describe.

However, there is a feature called 'common subexpresssion elimination' which has only been enabled in very recent versions of 8.1, and did have some nasty bug whose detail I don't recall.

Possibly you are hitting some variant of this bug as Oracle cycles through your 36 conditions trying to determine if any of them can be folded into each other.

It is possible, if you haven't already tried it, that you could simply include a /*+ full(table) */ to make the problem go away.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____UK_______April 22nd
____Denmark__May 21-23rd
____USA_(FL)_May 2nd

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK_(Manchester)_May
____Estonia___June (provisional)
____Australia_June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"richie" <richchri_at_erols.com> wrote in message
news:9a436674.0304111710.42f92843_at_posting.google.com...


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