Summary
Very recently i had a strange case where i have to truncate a large table. The system was a DWH so the case was to
truncate serial multiple big tables. The table sizes varied from 5 to 30GB. For some reason the truncate every night take 10-30min plus for every table. This was very slow and strange.
Starting experiments with one table which i copied to a new test table, with same data but without indexes the truncate took less than a minute, when to the original table took 12 min. After tests and experiments i manage to find what was going on.
The table i was trying to truncate was
partitioned with a large number of
local indexes on it. The table had 41 partitions and 15 indexes, all local partitioned! So the total number of segment that i am actually truncating was 41 + 41x15 = 656 segments! That explained why it takes so long.
Solutions
1. One solution that came to my mind is to
reduce the number of partitions thus and the total number of local index segments. Recreating the table with a new structure of only 10 partitions and the same local indexes the truncate of the same data finished in 75% faster, from 12mins went down to 3mins!
2. A
faster solution without changing the partitioning structure of the table is:
a. Save the DDL of the table indexes.
b. Drop all indexes (very fast, less than 1 minute)
c. Truncate table (very fast, less than 1 minute)
d. Create indexes again
3. THE
fastest solutions is to
Partition Exchange the partitioned table with an empty table
