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

Thanks once again Grin.

Sorry pasted wrong oracle query plan. This is the right one

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

I was using SYS only for testing purpose.

I think "Nested Loops" is some what like FILTER in oracle. For each row in the top input (outer) scan the bottom input (ineer) and return matching rows.

One more performance related issue.

How can I imporve performance of deleting large number of rows > 500000 from a table with the following constraints.

1. Table does not have indexes (This should help deletion -- Hope I am right)
2. Multiple clients can issue delete quries for deleting subset of table data. (For example table might contain 50,0000 rows for product
3. If a user loading fresh data for product A he will first delete the previous data of product A and then upload the new data, similarly another user might be working with product B etc and they might be running the delete query simulataneouly)
4. User might not have DDL rights so creating a new table and deleting the old one is not possible.

Currently we are using delete quries which is taking a lot of time.

Faisal Mansoor

Gints Plivna wrote:
> 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 Thu Aug 10 2006 - 02:18:10 CDT