Re: TRUNCATE v DROP TABLE question

Of the first two choices, I would do the truncate. When you truncate a table, all rows of data is just dropped. The advantage here is that the data does not go through the rollback so there is a performance boost.  However, this also means the data is non-recoverable and immediate (it's DDL). The truncate also leaves all the table structures (columns, indexes, extents, etc..) behind (I believe this includes the high water mark too). Please keep in mind, whether you delete or truncate, you periodically have to rebuild your indexes. I use the truncate method and afterwards I always rebuild the indexes. Another thing you might have to do is disable the constraints.

Now doing the drop table would be very close to the truncate (DDL also), except it will do one other thing, it will remove all the structures.  That means it will release all the extents that are allocated to the table as well as the indexes, and the data will go the same route as the truncate (non-recoverable & immediate). The bad thing with this that everything in the tablespace will be release, and when you go redefining the table, you'll get extents that are available and not necessarily what was used last. This could lead to a fragmentation problem in your tablespace. This plus the fact you're forcing the DB to re-define the table everytime (causing extra overhead), could lead to some performance issues in the future.

Good luck,
jogret.....

harry wrote:

>Using Oracle 8i on NT Server 4 (sp6a)
>
>I have a table that gets populated from scratch every week or so using a "CREATE TABLE tablename as
>SELECT ..." statement.
>
>As the table is very large its a bit tricky to try all different speed tests on it so I was
>wondering if somebody could tell me if this is the quickest way to do this?
>
>Should I -
>
>1. use a "TRUNCATE TABLE" statement & use a "INSERT INTO" instead? - will the indexes automatically
>be dropped aswell? - I don't want them active when doing an INSERT - do I have to recreate them
>after?
>
>2. Use "DROP TABLE tablename cascade constraints" statement? - does this remove indexes aswell?
>
>3. any other ideas?
>
>Many thanks
>
>HArry
>
>
Received on Fri Feb 01 2002 - 22:14:16 CST