Home » SQL & PL/SQL » SQL & PL/SQL » which query will be having better performance
which query will be having better performance [message #295397] Tue, 22 January 2008 04:37 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi,

I want to delete 200.000 records from the table.
Those records are based on the one condition.

e.g
Delete from TAB_TEST where db_id = 10;


Deleted record count will be 200.000 records.

So My question is do i delete those records with above query or do i apply a loop?
i.e.

DECLARE
 i NUMBER:=0;
 BEGIN
    FOR i IN (SELECT id FROM TAB_TEST WHERE db_id=10) LOOP
        i:=i+1;
        DELETE FROM TAB_TEST WHERE id = i.id;
        IF (MOD(i, 10000) = 0) THEN 
            COMMIT;
        END IF;
    END LOOP;
 END;


Which will be better?

Please suggest me.

Thanks in advance.

[Updated on: Tue, 22 January 2008 04:38]

Report message to a moderator

Re: which query will be having better performance [message #295404 is a reply to message #295397] Tue, 22 January 2008 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Guess! or measure.
I think we already answered many times to this question.

Regards
Michel

[Updated on: Tue, 22 January 2008 04:59]

Report message to a moderator

Re: which query will be having better performance [message #295409 is a reply to message #295397] Tue, 22 January 2008 05:03 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
I think the static sql query(delete) is better than the pl/sql block.
Re: which query will be having better performance [message #295412 is a reply to message #295409] Tue, 22 January 2008 05:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
One thing is thinking, another one is proving.

Regards
Michel
Re: which query will be having better performance [message #295452 is a reply to message #295412] Tue, 22 January 2008 06:32 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Nobody is having any thaughts the same? Smile
Re: which query will be having better performance [message #295455 is a reply to message #295397] Tue, 22 January 2008 06:36 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Why not run both doing a rollback after each to measure performance ?
Re: which query will be having better performance [message #295457 is a reply to message #295452] Tue, 22 January 2008 06:38 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
How about this one?
select * from scott.emp;

delete from scott.emp where rownum <= 3;
Take the count and loop it for count/10000 and delete using rownum <= 10000 and commit in the loop.

By
Vamsi

[Updated on: Tue, 22 January 2008 06:40]

Report message to a moderator

Re: which query will be having better performance [message #295470 is a reply to message #295457] Tue, 22 January 2008 07:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is one of the worst solutions (just after the posted PL/SQL block). Thank for posting it.

Regards
Michel

[Updated on: Tue, 22 January 2008 07:30]

Report message to a moderator

Re: which query will be having better performance [message #295476 is a reply to message #295470] Tue, 22 January 2008 07:22 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Huh! I think I always post worst solutions Sad
Yet to learn a lot on performance.
@Michel,
Can you please explain me why this is worse than the pl/sql block in the OP?

By
Vamsi

[Updated on: Tue, 22 January 2008 07:29]

Report message to a moderator

Re: which query will be having better performance [message #295478 is a reply to message #295476] Tue, 22 January 2008 07:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Actually there is a solution between the posted one and yours: bulk selecting instead of selecting row by row. Smile

Regards
Michel
Re: which query will be having better performance [message #295487 is a reply to message #295397] Tue, 22 January 2008 08:04 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Experts,
Can anyone please tell the solution instead of creating puzzles? Smile
Re: which query will be having better performance [message #295490 is a reply to message #295487] Tue, 22 January 2008 08:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you want to try to find it by yourself instead of constantly asking others?
By the way, the answer was given.

Regards
Michel
Re: which query will be having better performance [message #295545 is a reply to message #295397] Tue, 22 January 2008 12:05 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Have you even thought about what you're asking? Or, looked at the code you provided and what it does?

You state that you want to delete 200,000 records from the table, based on one condition...

delete from TAB_TEST where db_id = 10;


So, how did you get the 200,000 number? Did you do something like...

select count(*) from TAB_TEST where db_id = 10;


You also don't define what ID is in your SQL statement. Is that the record ID you assigned to the records in the TAB_TEST table? Does each record in TAB_TEST contain a value in the ID field? Are those values uniuqe for each record?

I suspect that both queries will delete the records you want deleted. Are you asking which is the BEST method, which is the RIGHT method or will EITHER method work?

Based on my personal experience, the "delete from TAB_TEST..." statement is much more direct and readable and allows the system to perform the delete in the best manner. It also allows you to rollback the deletion if necessary and will work whether you have 20 records, 200,000 records or 2.5 million records. Your looping through the records will also work, but it's a lot harder to understand and does not permit a rollback if you make a mistake (because of your COMMIT for every 10,000 records). As to efficiency and log files, I'll have to let others speak on that topic.

For me, typing a seven word SQL statement that will do the same work as an eleven line pl/sql procedure is a no-brainer decision. Your mileage on this topic may vary.

HTH,
Ron
Re: which query will be having better performance [message #295548 is a reply to message #295490] Tue, 22 January 2008 12:24 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Have you even thought about what you're asking?

I think you didn't read his previous posts. Grin

Regards
Michel
Previous Topic: Why is the same query returning the output differently on different databases.
Next Topic: Using Instr and substring
Goto Forum:
  


Current Time: Sun Dec 04 04:44:51 CST 2016

Total time taken to generate the page: 0.08050 seconds