Re: Problems with OR in Oracle 8i

I quess I wasn't very precise with my question, sorry. In my database there is a problem with OR and I think outer joins.

Here is the scenario exactly:

TABLE: SO (Sales Order) has 2,000,000 rows TABLE:CUST (Customer) has 1,500,000 rows

The tables join on CUST_CD (customer code). The (SO) sales order table has 2 salesmen fields SO_EMP_SLSP_CD1 and SO_EMP_SLSP_CD2.

Query 1 takes 1/2 a second or less:

SELECT * FROM SO, CUST WHERE (SO.CUST_CD=CUST.CUST_CD(+)) and (SO.SO_EMP_SLSP_CD1='PERSON1') Query 2 takes 1/2 a second or less:

SELECT * FROM SO, CUST WHERE (SO.CUST_CD=CUST.CUST_CD(+)) and (SO.SO_EMP_SLSP_CD2='PERSON1') Query 3 takes 1 second or less:

SELECT * FROM SO, CUST WHERE (SO.CUST_CD=CUST.CUST_CD(+)) and (SO.SO_EMP_SLSP_CD2='PERSON1') UNION
SELECT * FROM SO, CUST WHERE (SO.CUST_CD=CUST.CUST_CD(+)) and (SO.SO_EMP_SLSP_CD1='PERSON1') Query 4 takes 5 minutes:

SELECT * FROM SO, CUST WHERE (SO.CUST_CD=CUST.CUST_CD(+)) and (SO_EMP_SLSP_CD2='PERSON1' OR SO_EMP_SLSP_CD1='PERSON1') It isn't just this query. Whenever I do an outer join with an OR in additional filter criteria the query runs really slow.

Are there any issues with Oracle 8i optomizer, outer joins and ORs?

Thanks

"Andy Hassall" <andy_at_andyh.co.uk> wrote in message news:oqvnr15epi5hnisetvue160u4mo8jqfa5s_at_4ax.com...
> 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
Received on Wed Jan 04 2006 - 12:12:27 CST