Home » SQL & PL/SQL » SQL & PL/SQL » Difference on Delete (9i, 10g)
Difference on Delete [message #332428] Tue, 08 July 2008 09:06 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi,
We have some tables, some are partitioned some are not. Usually we load to this tables by day, and sometimes we have to re-run the loading for the past days. The chunks of data are grouped by day, so the bigger ones are partitioned by day. When we re-run it we usually just do a

        delete from partitionedtable where date = (date)

   


I just noticed the format


       delete from table partition (partitionname)...
     
   


Does this have any difference or performance issues? Or which is the best way of deleting those records. Thanks.

Regards,
Rhani
Re: Difference on Delete [message #332438 is a reply to message #332428] Tue, 08 July 2008 09:19 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Quote:

Does this have any difference or performance issues? Or which is the best way of deleting those records. Thanks.



What do your benchmark test show?
Re: Difference on Delete [message #332441 is a reply to message #332438] Tue, 08 July 2008 09:25 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi,
yes, that's what i would like to have an advice on how to look for that benchmark thing, could you give some pointers on what things or views i need to look at for me to produce a statistics. thanks.
Re: Difference on Delete [message #332442 is a reply to message #332441] Tue, 08 July 2008 09:32 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link for how to delete records from a partitioned table. Remember this is similar to your truncate table. So be aware of that.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_3001.htm#i2133032

Regards

Raj
Re: Difference on Delete [message #332445 is a reply to message #332442] Tue, 08 July 2008 09:34 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thanks =) I'll check on this.
Re: Difference on Delete [message #332450 is a reply to message #332441] Tue, 08 July 2008 09:49 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
ehegagoka wrote on Tue, 08 July 2008 07:25
hi,
yes, that's what i would like to have an advice on how to look for that benchmark thing, could you give some pointers on what things or views i need to look at for me to produce a statistics. thanks.


If you could reduce the elapsed time for either delete (or both), would anyone but you know or care that this occurred?

Are you wasting time trying to optimize something that is already "good enough"?

Do you suffer from Compulsive Tuning Disorder?
Re: Difference on Delete [message #332462 is a reply to message #332450] Tue, 08 July 2008 10:37 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi,
i don't know that much about tuning. i just read something about the "delete" command is wasting space or something, that's why i asked in this forum to get a much more clearer explanation. which i haven't got that much yet, all i got is an answer saying my question is too much of a newbie.
Re: Difference on Delete [message #332507 is a reply to message #332462] Tue, 08 July 2008 14:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you read about reclaiming/waisting space, but you ask about performance, do you think it strange that your real question is not answered?
Re: Difference on Delete [message #332516 is a reply to message #332507] Tue, 08 July 2008 15:39 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi,
sorry for my english if it may sound confusing on my terms, what i mean on performance, is about all possible issues or consequence of using those two "method" of deletion. that's why i asked if i could gain anything on using the other methods. i know i should be doing my own testing or something, but all i asked was any info or tips that might add some help for me to be in the right path if that wont be too much of a burden. thanks.
Re: Difference on Delete [message #332852 is a reply to message #332516] Wed, 09 July 2008 23:34 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The purpose of the 2 queries are different. They can be compared only knowing the final and table partitioning.

Regards
Michel
Previous Topic: ORA-14155
Next Topic: Logic help required
Goto Forum:
  


Current Time: Fri Dec 09 13:47:44 CST 2016

Total time taken to generate the page: 0.06029 seconds