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

Just found out this

"¦Note Partitioning is an extra cost option to the Enterprise Edition of the Oracle database. It is not available in the Standard Edition."

Is there any other solution which can be is avaiable on standard edition as well?

Faisal

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