Re: TRUNCATE v DROP TABLE question

in my opinion the question is :
is your db running in archivelog-modus or not

if running in archivelog-modus, the writing of of redo and archivlog is the speed-factor
so i would switch of writing redo and archive log

to activate table for nologging :

alter Table nologging

then truncate table (with drop storage or reuse storage) - NO drop-statement( this forces loging)

load table with this insert-statement
'INSERT /*+ APPEND*/ INTO tablename NOLOGGING SELECT * FROM sourcename

then a explicit COMMIT is nesesery

this is a kind of Direct load from a queri

"harry" <a_at_abc.com> schrieb im Newsbeitrag news: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 thanks
>
> HArry
>
>
Received on Tue Feb 05 2002 - 08:56:42 CET