Oracle – Resolving Slow Truncate on Large Table

oracle-9iperformancetruncate

Problem

I'm truncating a very large table ( 210+ million rows ).
It's been hours since it started and I have no idea when it will be done.
There're several tables like that, that I want to truncate.
The database is Ora9i.

Question

How can I do to make truncates faster ?
Also, Is there a way to see the progress of the truncate operation ?

Best Answer

I assume you are using dictionary managed tablespaces. You have to wait until all extents are deallocated. The following statement should report the number of extents that are to deallocate:

select extents 
  from dba_segments 
  where owner='TABLEOWNER' 
  and segment_name='TABLENAME'

Index segments and materialized view logs have to be deallocated too, if they exist.

If you drop and recreate the table you will have to wait, too. Dropping the table will take as long as truncation of the table because the number of extents to release is almost the same.

Maybe the following will help: instead of drop and recreate you

1) rename the table TABLENAME to OLDTABLE
2) create a new table with the original name TABLENAME. Now you have an empty table TABLENAME
3) you drop the table OLDTABLE

The advantage of this approach is you can continue your work with table TABLENAME after step 2. But the space occupied by the former table TABLENAME will be release during the execution of step 3. In Linux you can start the job that does step 3 in the background with nohup so that you must not leave your terminal window open. Use nohup sqlplus user/password@tnsname @dropscript.sql &.

Even if you want to restart the database during execution of step 3 this is not a problem because Oracle can handle such situations. As far as I remember it does this in the following way: After the restart of the instance (or if the sessions that does the drop of the table dies for some other reasons) the segment of the table is converted to a temporary segments. Its extents are deallocated by a background process (I think it is smon). The table is removed from the dictionary after the death of session so the tablename OLDTABLE is not visible in the dictionary anymore.

Related Solutions

SQL Server – Methods to Speed Up a Huge DELETE FROM Table with No Clauses

What you can do is batch deletes like this:

SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
    DELETE TOP (xxx) MyTable

Where xxx is, say, 50000

A modification of this, if you want to remove a very high percentage of rows...

SELECT col1, col2, ... INTO #Holdingtable
           FROM MyTable WHERE ..some condition..

SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
    DELETE TOP (xxx) MyTable WHERE ...

INSERT MyTable (col1, col2, ...)
           SELECT col1, col2, ... FROM #Holdingtable

Sql-server – Maintenance Plan Takes Too Long – Locks Tables – Indexing To Blame

Why are you rebuilding the indexes? Do you have any evidence that the rebuild is required/needed/beneficial? Have you considered when to use Reorganize vs. Rebuild?

Consider using one of the many index maintenance scripts like the ones from Ola Hallengren or Michelle Ufford. These scrips have intelligence in them to do appropriate maintenance action according to the index fragmentation and will reduce the offline duration by rebuilding only when truly required (hopefully never).

IF a rebuild is needed then there is nothing you can do w/o going to an Enterprise Edition and using online index rebuild.

Oh, one more thing: do not shrink the log. You are only causing it to take more time when it grows back.