Re: Need help with query which runs faster in SQL than ORACLE

faisal.mansoor_at_gmail.com wrote:
> 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

Your query with that UNION should be equivalent to this one for at least users A and B. If their actual usernames differ you should correct ORDER BY clause accordingly.

SELECT user_name, user_id
FROM (
  SELECT user_name, user_id,
    row_number() OVER (PARTITION BY user_id ORDER BY user_name) rn   FROM union_test
)
WHERE rn = 1

Speaking of your update - how many rows do you have? Is there a possibility that someone has locked any row (i.e. tried to update or delete them simultaneously)?

Gints Plivna
http://www.gplivna.eu Received on Wed Aug 09 2006 - 06:57:06 CDT