Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Deepankar.

Asked: June 03, 2021 - 3:59 am UTC

Last updated: June 03, 2021 - 4:02 pm UTC

Version: 12c

Viewed 1000+ times

You Asked

We have a scenario where we are considering using partitions on a control table. The control table will be hit with regular CRUD operations coming from triggers on transaction tables hence we want to address fragmentation by running regular TRUNCATE table sql. My questions are -

(1) What is a good strategy to handle fragmentation in high usage tables?

(2) Is it possible to lock a partition in a table and execute a TRUNCATE on that partitions.

Thanks
Deepankar


and Chris said...

1. I don't understand what fragmentation you think is happening, why this is bad, and how truncating partitions will solve it.

Please give more background on why you're trying to do this. What is the business problem you're trying to solve here?

2. You can lock a partition with:
lock table t 
  partition ( p0 ) 
  in exclusive mode;


This will block DML & DDL operations on that partition in other sessions. You can then truncate the partition with:

alter table t
  truncate partition p0;


But beware! Oracle Database issues a commit before running DDL. So truncating the partition will release the lock on it.

If there are any blocked transactions with uncommitted DML these will then proceed. Which can then cause the truncate to fail with ORA-00054. This is because uncommitted DML stops truncate getting the lock it needs on the partition.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Product Development

Deepankar, June 03, 2021 - 1:24 pm UTC

Thanks for the response!

I will try to explain the business problem in issue (1) - we have a scrub_tbl that is used to do post processing of transactional data. This table gets constantly slammed with CRUD operations. We noticed that the high water mark on the table moves higher and higher as we process large datasets. We also noticed that the sql reads, inserts, deletes and updates start to take more time. This deterioration was attributed to index fragmentation. My question is - what are some strategies to tackle index fragmentation in such scenarios.

I believe Truncating the table is one option.
Chris Saxon
June 03, 2021 - 4:02 pm UTC

So is the high water mark growing faster than the actual data size? If you're processing lots of data the high water mark will rise - as will space you need to store the data!

If you're making extensive use of direct path inserts (which write above the high water mark) and regularly delete data you may end up with lots of wasted space. In which case you may want to reclaim some by shrinking/moving the table partitions

https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9527343800346989243

This deterioration was attributed to index fragmentation

How have you determined this?

Again, index fragmentation is only really an issue there is lots of free space that will never get reused. If this is an issue, like space in the table you can reclaim it with coalesce or a rebuild:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9533003900346051914

Truncating a partition is a rather extreme solution (unless you genuinely want to remove all the data!)

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database