impact on table of partition truncate/deletehttp://www.orafaq.com/forum/./mv/msg/184600/569135/#msg_569135
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]]>kashifchughtai2012-10-21T10:22:45-00:00Re: impact on table of partition truncate/deletehttp://www.orafaq.com/forum/./mv/msg/184600/569137/#msg_569137
Regards
Michel
]]>Michel Cadot2012-10-21T10:49:35-00:00Re: impact on table of partition truncate/deletehttp://www.orafaq.com/forum/./mv/msg/184600/569139/#msg_569139
kashifchughtai2012-10-21T11:00:11-00:00Re: impact on table of partition truncate/deletehttp://www.orafaq.com/forum/./mv/msg/184600/569140/#msg_569140
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
]]>Michel Cadot2012-10-21T11:11:54-00:00Re: impact on table of partition truncate/deletehttp://www.orafaq.com/forum/./mv/msg/184600/569141/#msg_569141
how a drop parition will work? will it have a similar impact?]]>kashifchughtai2012-10-21T11:25:14-00:00Re: impact on table of partition truncate/deletehttp://www.orafaq.com/forum/./mv/msg/184600/569144/#msg_569144
Regards
Michel
]]>Michel Cadot2012-10-21T12:46:15-00:00Re: impact on table of partition truncate/deletehttp://www.orafaq.com/forum/./mv/msg/184600/569166/#msg_569166
kashifchughtai2012-10-22T04:22:16-00:00Re: impact on table of partition truncate/deletehttp://www.orafaq.com/forum/./mv/msg/184600/569167/#msg_569167
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.]]>BlackSwan2012-10-22T04:28:53-00:00Re: impact on table of partition truncate/deletehttp://www.orafaq.com/forum/./mv/msg/184600/569168/#msg_569168
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).
]]>kashifchughtai2012-10-22T05:02:43-00:00Re: impact on table of partition truncate/deletehttp://www.orafaq.com/forum/./mv/msg/184600/569169/#msg_569169
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?
]]>BlackSwan2012-10-22T05:06:52-00:00Re: impact on table of partition truncate/deletehttp://www.orafaq.com/forum/./mv/msg/184600/569171/#msg_569171
kashifchughtai2012-10-22T06:21:21-00:00