| Need to delete partition from a large database [message #65380] |
Wed, 01 September 2004 00:34  |
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 #65413 is a reply to message #65380] |
Tue, 14 September 2004 20:52  |
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 |
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!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
