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

Adam Tadj wrote:
>
> 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

If you will insert the data using SQL*Loader, you should use the Direct Path (bypassing conventional SQL-operations), which is fast (you specify Direct=true on the SQLLOAD command line and there is no explicit commit needed as far as I know) and yes, creating indexes after populating the table will enhance the insert-operations's performance.

Without SQL*Loader's direct path, a commit can occur any time you want, as far as your rollback segments are capable of it (large enough. You can create a large rollback segment and put the smaller ones offline during insert)

-- 
**************************************
Toine van Beckhoven
Oracle applicatie ontwikkelaar
Dedicate Information Technology Services, Woerden, The Netherlands
(http://www.dedicate.com)

Email   : A.A.M.v.Beckhoven_at_inter.nl.net
WWW     : http://infolabwww.kub.nl:2080/infolab/people/toine/toine.htm
Zie ook : http://stuwww.kub.nl:2080/sport/parcival/
**************************************