REPOST: TRUNCATE v DROP TABLE question

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Thu, 24 Jan 2002 14:23:01 -0000
Message-ID: <9$--$%%%_$$_%-%-$$@news.noc.cabal.int>

Truncate table simply drops the high water mark right down to the 'bottom' of the data in the table, so the table is effectively, empty. There is minmal redo generated. Indexes remain but are emptied. If there was data in the table, Oracle marks as UNUSABLE any and all non-partitioned indexes and all partitions of global partitioned indexes on the table - if there were any.

Drop table cascade constrainst drops the table, deallocates all the extents, removes the indexes and deallocates their extents and effectively removes the table from the database. Much redo is generated.

Delete from table removes the data rows from the table, and can be rolled back until such time as you commit (or you carry out a DDL command which implicitly commits !)

Under Oracle 817, there is ALTER INDEX blah DISABLE and ALTER INDEX blah ENABLE - that might help.

If so, I'd truncate the table and disable the indexes. If there is a lot of data to be re-loaded subsequently, I'd also use REUSE STORAGE on the truncate to save dynamic allocation/de-allocation.

HTH Regards,
Norman.

---

---

Norman Dunbar			EMail:	Norman.Dunbar_at_LFS.co.uk
Database/Unix administrator	Phone:	0113 289 6265
				Fax:	0113 289 3146
Lynx Financial Systems Ltd.	URL:	http://www.Lynx-FS.com


------------------------------------------------------------------------

---

-----Original Message-----

From: harry [mailto:a_at_abc.com]
Posted At: Thursday, January 24, 2002 12:08 PM Posted To: server
Conversation: TRUNCATE v DROP TABLE question Subject: TRUNCATE v DROP TABLE question

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

• WAS CANCELLED BY =======: From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk> Control: cancel <E2F6A70FE45242488C865C3BC1245DA79DA45C_at_lnewton.leeds.lfs.co.uk> Subject: cmsg cancel <E2F6A70FE45242488C865C3BC1245DA79DA45C_at_lnewton.leeds.lfs.co.uk> Date: Mon, 28 Jan 2002 02:42:47 GMT Message-ID: <cancel.E2F6A70FE45242488C865C3BC1245DA79DA45C_at_lnewton.leeds.lfs.co.uk> X-No-Archive: yes Newsgroups: microsoft.test,alt.flame.niggers,comp.databases.oracle.server NNTP-Posting-Host: w088.z064003087.lax-ca.dsl.cnc.net 64.3.87.88 Lines: 1 Path: news.uni-stuttgart.de!cert.uni-stuttgart.de!news.belwue.de!news.uni-ulm.de!rz.uni-karlsruhe.de!blackbush.xlink.net!blackbush.de.kpnqwest.net!netnews.web.de!newsfeed01.sul.t-online.de!t-online.de!fr.clara.net!heighliner.fr.clara.net!opentransit.net!news.stealth.net!msrtrans1!msrnewsc1!cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp04!u&n&a&c&anceller Xref: news.uni-stuttgart.de control:40725909

This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers. Received on Thu Jan 24 2002 - 08:23:01 CST