Need help with query which runs faster in SQL than ORACLE

From: <faisal.mansoor_at_gmail.com>
Date: 9 Aug 2006 00:43:40 -0700
Message-ID: <1155109420.475267.318950@h48g2000cwc.googlegroups.com>

Hello All

I am having performance problems with the following query. For the following data

TABLE: UNION_TEST
user_name, user_id
A 1
A 2
A 3
B 2
B 4

For retrieving all rows for user A and those rows of user B for which user A does not have a user_id. I have written following query for this.

select * from UNION_TEST WHERE USER_NAME = 'A' UNION
select * from UNION_TEST WHERE USER_NAME = 'B' AND USER_ID NOT IN

                            (SELECT USER_ID FROM UNION_TEST WHERE

USER_NAME = 'A') which produces the following desired output.

A 1
A 2
A 3
B 4

But for large data set this query is running very slow.

//Create table

create table UNION_TEST
(

    USER_NAME VARCHAR2(50),
    USER_ID NUMBER(22)
);

//insert 30000 rows for A

BEGIN
    FOR i in 1 .. 30000
    LOOP
    insert into UNION_TEST VALUES('A', i);     end LOOP;
END;
//RUN

select * from UNION_TEST WHERE USER_NAME = 'A' UNION
select * from UNION_TEST WHERE USER_NAME = 'B' AND USER_ID NOT IN

                            (SELECT USER_ID FROM UNION_TEST WHERE

USER_NAME = 'A') Even now the query runs in resonable time. But if I ran this query

UPDATE UNION_TEST SET USER_NAME = 'B' Now this query takes more than 30 min to execute.

For SQL Server both scenarios take alomst same time.

Can any please explain this problem and recommend how can rewrite the query to optimize it.

Thanks
Faisal Received on Wed Aug 09 2006 - 02:43:40 CDT