Drop partition (big tables) elapsed time
I was wondering if you could help me with this free space issue.
Currently we have some big partitioned tables, that have started to being recycled. Each partition has +2GB/+0.5GB of data/index.
When we started to recycle partitions three weeks ago, the elapsed time to drop one was 15 to 20 minutes. But, since we have continued recycling space, now its taken more than 5 hours.
The big problem is, since this is taking that long, it has started to affect other processes because of RBS or TEMP segment.
I have noticed that it's not particular of an specific tablespace, because it's now affecting the whole database. Also, it does seem to affect more to index tablespaces than data's.
Do you have any ideas about what can I do to solve this?
Where do you think we might have the first issue to solve?
Where could be the main problem? tablespaces, smon or dictionary?
Last edited by orajoe; 02-12-2003 at 03:36 AM.
So drop partition is now taking 5 hours? Do you have any global indexes on the table?
None. It has 21 bitmaps a 1 unique index, but all of them are local to each partition.
I have also noticed that the delete of the index space is much slower than the needed for the data.
Last edited by orajoe; 02-12-2003 at 01:06 PM.
Unique index mostly like is a global index. Please double check
One, who thinks that the other one who thinks that know and does not know, does not know either!
Double checked. All of them are local. The table is subpartitioned, and the index is partitioned by time (range) and a dimension (hash).
And none of all index subpartitions have become unusable.
are you using the "alter table ... drop partition ..." syntax?
also, do you have any foreign keys defined against the pk of that table?
No foreign keys.
And the complete syntax I'm using is:
"alter table mytable drop partition mypartition1;"
Jus FYI, I'm altering the sesion to the enable parallel ddl, each time I drop (or create) a partition for that table., in order to use more than 1 cpu to exec these statements.
well why dont you try
alter table mytable disable constraint my_constraint_fk
--disable all constraints on these tables like foreign key
alter table mytable truncate partition mypartition1
--truncate the partition
alter table mytable enable constraint my_constraint_pk
instead and let us know
Last edited by hrishy; 02-13-2003 at 02:21 AM.
dropping a partition is nothing to do with parallelism.
Click Here to Expand Forum to Full Width