Thanks for the question, Rohan.
Asked: August 12, 2016 - 1:44 pm UTC
Last updated: August 12, 2016 - 2:25 pm UTC
Version: 11G
Viewed 1000+ times
You Asked
Hi,
We have a table with 120 partitions and we create around 3500 sub partitions in total every month. Not all subpartitions are having data in it eg (weekends, holidays). But over the period these subpartitions are increased to 70000. And now this parition creation job takes more than 22hrs to create the subpartition. We decided to purge old partitions from this table so to improve performance. But purging old empty subpartitions is very slow job around 80 subpartitions are purged in 1 hr.
Any suggestions what could be the issue or how we can improve the performance of table during purge?
One option we are trying is create another table with same structure but which will have only subpartitions with data in it excluding empty subpartitions from original table. Then droping the old table and moving data in new table and making it primary table by renaming it.
Any other solution which is more accurate and feasible.
Thanks.
Rohan.
and Chris said...
There is a bug which affects DDL on tables with "many" subpartitions. This is on some 11g versions, so may be the cause of your woes. See MOS note 1285491.1 for more details.
If this isn't the problem affecting you, I'd still contact support. 22hrs to add a partition doesn't sound right...
Your plan of creating a new table with fewer subpartitions is a good workaround in the meantime. I would also think about changing the subpartitioning scheme at the same time.
With ~3500 subpartitions for 120 partitions/month, I'm guessing you've got daily partitions. Given that several of these are empty, why do you need this level of granularity? Can you get away with weekly or monthly subpartitions instead?
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment