Re: Problems with OR in Oracle 8i

On Wed, 4 Jan 2006 10:46:28 -0500, "Dan Dubinsky" <sofia_at_salmonllc.com> wrote:
>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.

 The two queries are not equivalent, due to operator precedence. They may happen to return the same data in some cases, but the first one can't be transformed to the second pair of queries.

 You have:

WHERE customer.customer_code=sales_order.customer_code AND salesmen1='person1'
OR salesmen2='person1'

 AND is higher precedence than OR, so it's actually:

WHERE (customer.customer_code=sales_order.customer_code AND salesmen1='person1')
OR salesmen2='person1'

 What you want is:

WHERE customer.customer_code=sales_order.customer_code AND (salesmen1='person1' OR salesmen2='person1')

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool