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

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.

This is the reason I said you should coorect ORDER BY clause as necessary for example:

SELECT user_name, user_id
FROM (
  SELECT user_name, user_id,
    row_number() OVER (PARTITION BY user_id ORDER BY decode ('first_username', '1', 'second_usrename', '2', '3')) rn   FROM union_test
)
WHERE rn = 1

> 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)

Yea it seems you are doing kind of (record count) * (record count) full scans of this table. No surprise that it is slow :)

>
> 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'))
Probably SQLServer somehow is able to rewrite this select although what does mean this "Nested Loops"? Probably you have some index in SQLServer and not in Oracle?

BTW what does this row "TABLE ACCESS (FULL), MARS_IN_ACCOUNTS (SYS)" mean?
Have you created all your object under the sys account? If yes then don't do that! That is very very bad idea. Create your own user and play with it but not with the built in accounts.

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