Home » RDBMS Server » Performance Tuning » Need to delete partition from a large database
Need to delete partition from a large database [message #65380] Wed, 01 September 2004 00:34 Go to next message
James Attard
Messages: 1
Registered: September 2004
Junior Member
I currently administer a database which is quite large. One particular schema has a table which contains around 75,000,000 records and this table is multi-partitioned to increase performance. I have also implemented the data on a RAID 1+0 disk array. I also parallelized the table for multi-threading queries.

The problem is that now I need to delete an entire partition using something like:

delete from my_table partition my_partition

The partition contains around 4,500,000 rows. When I execute the above statement to delete the partition, any queries on this table (but different partition) are tremendously slow (take more than 30 minutes to return). How can I improve the situation?

Thanks in advance,

James
Re: Need to delete partition from a large database [message #65388 is a reply to message #65380] Fri, 03 September 2004 10:35 Go to previous messageGo to next message
billh
Messages: 35
Registered: August 1999
Member
alter table table_name truncate partition partition_name ;
Re: Need to delete partition from a large database [message #65413 is a reply to message #65380] Tue, 14 September 2004 20:52 Go to previous messageGo to next message
Deepa
Messages: 269
Registered: November 2000
Senior Member
U can truncate the partition instead of deletion for faster performance.

But if there are global indexes on ur table, then after truncation they will get disabled and insertions/updations will fail.

So make sure u have local index on the table
Need to delete partition from a dataspace [message #65698 is a reply to message #65388] Thu, 09 December 2004 08:32 Go to previous message
Bui An Loc
Messages: 1
Registered: December 2004
Junior Member
I created a partition from a dataspace with command:

alter table Cdr_call add Partition DATA20040301 Values less than (To_Date('2004-03-02','SYYYY-MM-DD','NLS_CALENDAR=GREGORIAN')) Tablespace DATA200403;

Now, I need to delete the partition that was created. Thank!
Previous Topic: Again Execution Plan after Analyzing the table
Next Topic: Understanding ODCIStatsIndexCost results in 10053 trace
Goto Forum:
  


Current Time: Tue Sep 29 06:23:34 CDT 2020