Purging records of a large table
Ajay, August 09, 2022 - 8:03 am UTC
Hi Chris,
That was too informative. Thank you!
I still have a few doubts though,
We have a space concern in the tablespace level, We are restricted to use either CTAS switch rows or CTAS switch tables, in both the methods we have to create a temp table, in a different tablespace say T2.(Current table is in T1).
Now If I follow,
CTAS switch rows - There will be two inserts, which will take time.
CTAS switch tables - I need to move temp table from T2 to T1 ( to do so I need to drop or truncate current table), which will take time.
I am in a tough situation right now, what do you suggest?
August 09, 2022 - 1:09 pm UTC
Why do you need to move the table back to the other tablespace? Can you defer this to some time in future?
As stated originally, you need to prioritise upgrading to 19c. Once you're on this version you can move tables online.
Purging records of a large table
Ajay, August 11, 2022 - 6:00 am UTC
Hey Chris, Thank you for the reply!
We are sticking with CTAS switch rows method, because we cannot use CTAS switch tables since we have a table with one partition.
So this is what we are doing,
1. Take a expdp table backup
2. create a temp table in T2 with required data
3. drop FK constraints of current table
4. truncate current table and make its indexes unusable(so that insert will be faster)
5. Insert from temp to current
6. create the dropped FK constraints on current table
7. Rebuild indexes online and gather stats
The only dml operation on this table in the night, so we are asking downtime to perform steps 3,4,5,6(downtime of 7 hours).
I just wanted to know, can we do anything to make this better and less time consuming?
August 11, 2022 - 1:34 pm UTC
we cannot use CTAS switch tables since we have a table with one partition.
I don't understand why this stops you using the switch tables method.
Though as you've mentioned partitions - can you use exchange partition as a way to swap the rows over?
Have you tried running these operations in parallel?
narayana tarakada, September 25, 2023 - 1:10 pm UTC
If I have a table with partition 400 million records and I need to delete 30 million records which of these is best option
1) filtered move table
2) CTAS table switch
3) drop partition/truncate partition
Consider 2 cases a) partitioned table b) non partitioned table
September 26, 2023 - 11:04 am UTC
"Best" is subjective; read the linked blog post above for the full ins and outs of these methods.
If you're asking which is fastest, drop/truncate partition is quickest - assuming the table is already partitioned with all the rows you want to remove in the same partition(s). If it's unpartitioned there (potentially lots) of work checking how partitioning affects the rest of the app.