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

From: <faisal.mansoor_at_gmail.com>
Date: 9 Aug 2006 06:22:53 -0700
Message-ID: <1155129773.791786.198220@h48g2000cwc.googlegroups.com>

Thanks Gints

This is great the query ran instantaneously for 30,000 rows all of user B.

But I want to order each partition such that first user (which i specify in the first union clause ) comes to the top, if it does not exists for a particluar partion then the second user's row come up. Thats is the effect I get with this query.

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') By the way I wonder why this query ran slower on Oracle than SQL. In SQL this problem is not noticible.

This is Oracle's Query Plan

7 	border=0 	SELECT STATEMENT
6 	border=0 	SORT (UNIQUE)
5 	border=0 	UNION-ALL
1 	border=0 	TABLE ACCESS (FULL), MARS_IN_ACCOUNTS (SYS)
4 	border=0 	FILTER
2 	border=0 	TABLE ACCESS (FULL), MARS_IN_ACCOUNTS (SYS)
3 	border=0 	TABLE ACCESS (FULL), MARS_IN_ACCOUNTS (SYS)

And this is SQL Server query plan

  |--Hash Match(Union)

       |--Table Scan(OBJECT:([MARS_QA].[dbo].[UNION_TEST]), WHERE:([UNION_TEST].[USER_NAME]='A'))
       |--Hash Match(Aggregate, HASH:([UNION_TEST].[USER_ID]) DEFINE:([UNION_TEST].[USER_NAME]=ANY([UNION_TEST].[USER_NAME])))             |--Nested Loops(Left Anti Semi Join, WHERE:([UNION_TEST].[USER_ID]=[UNION_TEST].[USER_ID]))

                 |--Table Scan(OBJECT:([MARS_QA].[dbo].[UNION_TEST]),
WHERE:([UNION_TEST].[USER_NAME]='B'))
                 |--Table Spool
                      |--Table

Scan(OBJECT:([MARS_QA].[dbo].[UNION_TEST]), WHERE:([UNION_TEST].[USER_NAME]='A')) Thanks once again Gints.

Faisal Mansoor

Gints Plivna wrote:
> 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 - 08:22:53 CDT