Re: TRUNCATE v DROP TABLE question

From: A.Liggins <A.Liggins_at_hotmail.com>
Date: Sat, 18 May 2002 16:32:50 +0000 (UTC)
Message-ID: <ac5vng$guh$1@paris.btinternet.com>

WRONG WRONG GL! Christian is quite right, in 8i (8.1.6+ definitely), the APPEND hint in an insert uses direct load, but only under certain conditions..

From Oracle documentation :--

When you use the APPEND hint for INSERT, data is simply appended to a table. Existing free space in the blocks currently allocated to the table is not used.
The append operation is performed in LOGGING or NOLOGGING mode, depending on whether the [NO] option is set for the table in question. Use the ALTER TABLE... [NO]LOGGING statement to set the appropriate value.

Look at the Tunning and concepts manuals as this is a new(ish) feature.

This means basically --

Make sure the table is in nologging mode You dont have ANY indexes/constraints enabled on the take (you can try setting them to nologging but it didnt work for me..)

YOU MUST COMMIT (or ROLLBACK if it goes pants) IMMEDIATELY after the insert finishes.

Some small amount redo will be generated (you can't avoid it) There are recovery issues with this, so once done build the indexes (use nologging to build them quickly) and

take the table (and indexes!) out of nologging mode and do a backup afterwards.

Obviously you need to do some tracing/explain planning to make sure it is actually taking affect. But we did get
it going quicker.

Alun

"gl" <jogret_at_hotmail.com> wrote in message
news:3C649E73.4070504_at_hotmail.com...
Sorry, but alter table nologging refers only to nologging when the table is loaded via Direct Loader (SQL*Loader only). There is no escape from going through redo log when doing any type of insert or update!

Christian Effler wrote:

in my opinion the question is :is your db running in archivelog-modus or notif running in archivelog-modus, the writing of of redo and archivlog is thespeed-factorso i would switch of writing redo and archive logto activate table for nologging :alter Table nologgingthen truncate table (with drop storage or reuse storage) - NOdrop-statement( this forces loging)load table with this insert-statement'INSERT /*+ APPEND*/ INTO tablename NOLOGGING SELECT * FROM sourcenamethen a explicit COMMIT is neseserythis is a kind of Direct load from a queri"harry" <a_at_abc.com> schrieb im Newsbeitragnews:nhtv4u0koeahbec0jl0gbarhs9e41k98s4_at_4ax.com... 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 thanksHArry Received on Sat May 18 2002 - 11:32:50 CDT