Re: How to INSERT efficiently? When to COMMIT, When to CREATE INDEXES ..

Adam,

You load will progress very much faster if you drop your indexes before the load. However, against this you need to allow for the time taken to rebuild indexes. You may get some advantages here from using the parallel index build facility and using a large SORT_AREA_SIZE. If you were at 7.2 you could use the UNRECOVERABLE option which speeds big index builds quite remarkably.

Committing every 500 records or so will be just as fast as committing at the end and will avoid the risk of exceeding rollback segment resources.

As for other suggestions:

If you have multiple processes inserting into the same table, make sure the table has multiple freelists.

If you are inserting from a flat file, consider using SQL*LOADER in direct path mode.

Try to use the array insert facility. How you do this depends on the tool you are using.

-- 
Guy Harrison

gharriso_at_werple.net.au || http://werple.net.au/~gharriso || 613 419377964



Adam Tadj <vahidt_at_dbsun.vitek.com> wrote in article
<3385B5DC.6F30_at_dbsun.vitek.com>...


> I will be INSERTing a sizable chunck of data into a table with 45


> columns and I wanna do it as fast and as efficient as I can.  These

> questions came to my mind and I would like to hear your opinions too.

> 

> I may add that I am running ORACLE 7.1 in a AIX 3.2 environment.  The

> target table is gonna be realtively stable with few INSERTs now and then

> and it will be READ ONLY for everybody other than the DBA.  The data

> going into this table are already cleansed and do not need CONSTRAINT

> check or TRIGGERs.  The INDEXes are mainly for quick record retievals 

> 

> - Should I populate the bare table first then CREATE INDEXes?

> - Should I COMMIT periodically (eg every 100 or 1000, 2222, .. records)

>   if so, 

>   - is there a written rule or at leat a rule of thumb?

>   - or should I COMMIT at the very end?

> - Also, are there other stuff I need to consider too?

> 

> Your hints and brain stroms will be very welcome!  Thanx in ad.

> 

> Adam Tadj.

> vahidt_at_dbsun.vitek.com

>