Re: faster than SQLLOADER
Date: Fri, 6 Jan 1995 19:33:19 +0000
Message-ID: <789420799snz_at_jlcomp.demon.co.uk>
In article <3e3uhb$1q8_at_portal.gmu.edu>
mghous1_at_mason1.gmu.edu "Mohammed Ghouse" writes:
: Is there any other method to load 3 million records of 100 reclength
: into a table already having 60 million records. We need to have an
: index of a key of length 17. What I need is a method
:
I was going to wait for an answer to my original question about triggers and constraints on the table before giving an answer to this question, but since 2 people have now advised dropping the index I want to put my USD 0.10 straight away.
If all other circumstances allow for it, DIRECT LOAD will, when tuned properly, be the fastest way of loading the data, and it is NOT always a good idea to drop the index when using the direct load method.
If you can use direct load then the 3,000,000 rows will be loaded by Oracle formatting them directly into Oracle blocks, bypassing all SQL calls: if you are running in NOARCHIVELOG, (or in 7.1 if you are running in ARECHIVELOG but specify the UNRECOVERABLE option then) there will be no redo log (and no archived redo log) caused by the TABLE load.
When the 3,000,000 row are in the table, Oracle will do a sort operation ONLY against those 3,000,000 key values into a temporary segment. It will then merge the sorted 3,000,000 rows with the original index to create a new index. This will probably be a lot quicker than reading the 7 Gb (estimate) of table and sorting the 1.2 Gb (estimate) of raw information that has to go into the index.
Special Notes:
Irrespective of whether the new index is created by the merge, or by re-creation, it will be protected in the redo log, so there is a 2+ Gb redo that will be generated by this process, (ditto archived redo if you run in ARCHIVELOG mode).
If there are any constraints (other than non-null, or primary/unique key) then you cannot use the direct load.
If there are any triggers, then it will probably not be sensible to do direct load, as you will have to work around the fact that the direct loaded rows have not fired their triggers.
Watch out that your Operating System sort collates in the same order as the Oracle sort (which it does not on my HP, the '_' comes out in a different place.)
If you try direct load and your index in supposed to be unique, but your load produces a duplicate value, you are in trouble: possibly an O/S restore would be the optimum fix.
Space: because the old index will be merged with the temporary sort segment to produce a new index, your INDEX tablespace will have to have enough free space available in it to hold the final index. (The merge is NOT done in situ, a whole new index segment is created). This is not as bad as it seems: after all if you recreated the index your temporary tablespace would nominally have to be twice the size of the final index to allow for worst case sorting.
Since the data in the table has NOT been protected by redo, then you should do a backup of the database a.s.a.p.
The Answer to your Original Query:
Direct loader is the fastest option if it can be used at all. Pre-sort the data and use the 'sorted' option Run in NOARCHIVELOG mode
Set the 'rows' parameter to sqlload to something very large Tune for the large volume of redo log, e.g.
Large redo logs Large log buffer size
Backup after completion.
P.S. I forgot to check: are you running Oracle 7 ?
-- Jonathan LewisReceived on Fri Jan 06 1995 - 20:33:19 CET