Testing an index during a SQL*LOADER direct path load

[weberm]

I am using SQL*LOADER to append data to a table with a primary index. I am attempting to perform a direct path route with the SINGLEROW option so that it doesn't load a data file twice, but it's still loading all the records. Am I using the parameter incorrectly or misunderstanding how it works? Here is the Control file:

--control file for remitter
OPTIONS(DIRECT=TRUE,ERRORS=5)
LOAD DATA
INFILE "/u003/users/ardr/ARDRS01AA.dat"

APPEND INTO TABLE arch_remitter SINGLEROW (
REC_DATE                     CHAR(6),
REC_NUM                      CHAR(6),
REC_TREAS_NUM                CHAR(6),
REC_REM_NAME                 CHAR(40),
REC_CC_LOC                   CHAR(1),
REC_CHECK_AMT                CHAR(9),
REC_BILL_TYPE                CHAR(1),
REC_INV_NUM                  CHAR(9),
REC_PAYMENT_TYPE             CHAR(1),
REC_STATUS                   CHAR(1),
REC_BAL_FLAG                 CHAR(1))

I know that setting DIRECT to "FALSE" will prevent a load file from being appended to the table a second time, but I don't want the interminable "Commit point reached" messages (using the SILENT=FEEDBACK suppresses too much and tinkering with ROW values is tedious).

 

[Dagon]

I don't understand what you mean. Why is your data being loaded twice ? SQL*Loader will only load data once, regardless of whether you use directpath or normal mode, so you must be calling the script twice or something like that.

SINGLEROW is a technical option which determines how directpath loads update the index entries.

The SINGLEROW option is intended for use during a direct path load with APPEND on systems with limited memory, or when loading a small number of records into a large table. This option inserts each index entry directly into the index, one record at a time.

By default, SQL*Loader does not use SINGLEROW to append records to a table. Instead, index entries are put into a separate, temporary storage area and merged with the original index at the end of the load. This method achieves better performance and produces an optimal index, but it requires extra storage space. During the merge operation, the original index, the new index, and the space for new entries all simultaneously occupy storage space.

With the SINGLEROW option, storage space is not required for new index entries or for a new index. The resulting index may not be as optimal as a freshly sorted one, but it takes less space to produce. It also takes more time because additional UNDO information is generated for each index insert. This option is suggested for use when either of the following situations exists:

Available storage is limited.

The number of records to be loaded is small compared to the size of the table (a ratio of 1:20 or less is recommended).

This has nothing to do with what data is loaded, just how it is loaded. The final results should look the same whether you use the option or not.

 

[weberm]

Sorry about that, a little more background should help. I have a script which is loading an archival table with records purged from production and written to a flat file. I perform a direct path load because the two tables are identical so there will never be a problem with the PK and it's faster. However, if the load file isn't renewed (the job which creates the load file fails or the load job is manually run without checking), I end up with a mess to clean up (along with lots of red tape and hoops through which I must jump). What I am seeking is a way to use the directpath load but attempt to update the index before it loads all the records, or use the conventional method but supress the pages of "Commit point reached" messages (the load files are large). Does that make sense?

 

[Dagon]

So what does SILENT=FEEDBACK suppress that you want to see ? I haven't tried it but the manual says:

FEEDBACK - Suppresses the "commit point reached" feedback messages that normally appear on the screen.

It doesn't mention anything else.

 

[weberm]

I already tried SILENT=FEEDBACK and it suppressed all feedback messages, not just the "commit point reached" ones. Not much help.

{.ardr}/global/apps/home/ardr==> sqlldr userid = ardr/ardr  control = $HOME/ctl/remitter.ctl

SQL*Loader: Release 10.2.0.3.0 - Production on Tue Feb 10 09:26:45 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

{.ardr}/global/apps/home/ardr==>

 

[weberm]

On second thought (and a couple cups of coffee later ), I realized that I can use the SILENT=FEEDBACK option and grep the SQL*LOADER log file to generate a load report with what I want.
Problem solved!