Home » RDBMS Server » Server Administration » impact on table of partition truncate/delete (oracle 10g)
impact on table of partition truncate/delete [message #569135] Sun, 21 October 2012 05:22 Go to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
hi guys,

can you please tell if any impact is there if is do the following:

ALTER TABLE MY_TABLE TRUNCATE PARTITION P1 UPDATE GLOBAL INDEXES;


will there be any lock on the table during this operation? DML operations will work without any issue or not?

regards,
kashif

Report message to a moderator

Re: impact on table of partition truncate/delete [message #569137 is a reply to message #569135] Sun, 21 October 2012 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes, there will be locks, many locks and even flush from the cache so yes there will be an impact, TRUNCATE is ALWAYS a heavy operation and more when there are update of global indexes.

Regards
Michel

Report message to a moderator

Re: impact on table of partition truncate/delete [message #569139 is a reply to message #569137] Sun, 21 October 2012 06:00 Go to previous messageGo to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
i want to purge some data from huge tables by truncating the partitions? is there anyway to avoid the locks on the table, considering that there are lot of DMLs are happening on the other partitions in the same table.? or can you suggest then any other way to purge the data?

Report message to a moderator

Re: impact on table of partition truncate/delete [message #569140 is a reply to message #569139] Sun, 21 October 2012 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If you truncate a partition (without update global indexes) there is a shared lock on the table and an exclusive only on the partition.
Updating the global indexes require a lock on the table to prevent any modification on the table during the index rebuild.
TRUNCATE is a maintenance statement and should not be used during peak hours.
A better mitgh be to exchange the partition with an empty table and then to drop exchanged table (now or later).

Regards
Michel

Report message to a moderator

Re: impact on table of partition truncate/delete [message #569141 is a reply to message #569135] Sun, 21 October 2012 06:25 Go to previous messageGo to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
without update global indexes, indexes will become unusable?

how a drop parition will work? will it have a similar impact?

Report message to a moderator

Re: impact on table of partition truncate/delete [message #569144 is a reply to message #569141] Sun, 21 October 2012 07:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes, similar way, yes.

Regards
Michel

Report message to a moderator

Re: impact on table of partition truncate/delete [message #569166 is a reply to message #569144] Sun, 21 October 2012 23:22 Go to previous messageGo to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
Thankx Michel, if i delete the records and then drop/truncate the partition to release the storage, will it reduce the time and impact?

Report message to a moderator

Re: impact on table of partition truncate/delete [message #569167 is a reply to message #569166] Sun, 21 October 2012 23:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>will it reduce the time and impact?
I would expect performance to stay the same.
If partition pruning is actually used, then SQL will never look at the drop/truncated partition; whether it exists or not.

Report message to a moderator

Re: impact on table of partition truncate/delete [message #569168 is a reply to message #569167] Mon, 22 October 2012 00:02 Go to previous messageGo to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
so i am stuck Sad i have a situation where, i have a large table with partitions on date (month wise)
and canot drop/truncate the paritions without having a impact or other users(locking).

Report message to a moderator

Re: impact on table of partition truncate/delete [message #569169 is a reply to message #569168] Mon, 22 October 2012 00:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>and canot drop/truncate the paritions without having a impact or other users(locking).
why are some user trying to access data that is deemed to be worthless & expendable?
do you have a design problem or business rule problem?

Report message to a moderator

Re: impact on table of partition truncate/delete [message #569171 is a reply to message #569169] Mon, 22 October 2012 01:21 Go to previous message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
no, user will not access that particular parition which we are going to purge but will access the same table and there willl be continious insert/selects will be happening on the same table.

Report message to a moderator

Previous Topic: Database 9i to 10g
Next Topic: about oracle memory
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Jan 12 23:17:38 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.