Re: Problems with OR in Oracle 8i

"Dan Dubinsky" <sofia_at_salmonllc.com> a écrit dans le message de news: KmSuf.3012$DY3.224_at_fe09.lga...
| Hi All,
|
| I have a fairly big Oracle 8i database (2 million order rows in the largest
| table) and I'm having trouble with the OR operator in a query. For some
| reason the OR is causing queries to run dog slow. It's so slow that I have
| to take out the OR and use a UNION instead. For example:
|
| this takes 5 minutes to run:
|
| select * from sales_order, customer where
| customer.customer_code=sales_order.customer_code and salesmen1='person1' or
| salesmen2='person1'
|
| this takes a few seconds to run and returns the same result:
|
| select * from sales_order, customer where
| customer.customer_code=sales_order.customer_code and salesmen1='person1'
| union
| select * from sales_order, customer where
| customer.customer_code=sales_order.customer_code and salesmen2='person1'
|
| Almost every time I use an OR on a moderately complex query on a big table
| the thing takes forever. Is there a problem with the 8i optomizer? These
| unions get cumbersome, especially if there are lot's of OR conditions to
| account for.
|
| Thanks,
| Dan
|

There is a problem with developper brain.

Your 2 queries are not equivalent.
The first one means:

select *
from sales_order, customer
where
( customer.customer_code=sales_order.customer_code and salesmen1='person1' )  or
 salesmen2='person1'

This does not happen when queries are correctly parenthesized.

Regards
Michel Cadot Received on Wed Jan 04 2006 - 10:43:27 CST