can you use hints in SQLLoader

[Horace]

Hi,

Whilst I'm reading up on this, can you use hints in SQLLoader,

i.e.,

Load Data
Insert /*+ append */ into table MY_TABLE
etc........

Just that the current load script uses "Append into...." and there's a lot of logging going on.

Alternatively, is the UNRECOVERABLE option still valid??

[anandkl]

Hi,
You can use this

To start SQL*Loader in direct load mode, set the parameter DIRECT to TRUE on the command line or in the parameter file, if used, in the format:

DIRECT=TRUE

regards
anandkl

[Horace]

Yes, but that just speeds up the load,

By default, a direct path load is RECOVERABLE, true??

So still lots of REDO.......

[anandkl]

Hi,
I dont think DIRECT PATH does generate REDO LOGS, Please correct me if i am wrong.

regards
anandkl

[jmodic]

Originally posted by Horace
Yes, but that just speeds up the load,

By default, a direct path load is RECOVERABLE, true??

So still lots of REDO.......

No, not true. As anandkl says, DIRECT PATH loads are not recoverable. In fact, SQL*Loader's DIRECT PATH load and INSERT /*+ APPEND */ are one and the same thing under the cover. The redo generation during DIRECT PATH load is next to nothing (only data dictionary changes are logged) as far as *table data* is concerned. But if your table is indexed, then you'll see lot or redo information generated - but that is because of indexes, you can't avoid redo generation for index during DIRECT PATH.

[Horace]

Hmm, Interesting.....
Here's an extract from the 9i documentation :-

---------------------------------------------------------------------

Data Recovery During Direct Path Loads
SQL*Loader provides full support for data recovery when using the direct path load method. There are two main types of recovery:

Media recovery - recovery from the loss of a database file. You must be operating in ARCHIVELOG mode to recover after you lose a database file.
Instance recovery - recovery from a system failure in which in-memory data was changed but lost due to the failure before it was written to disk. The Oracle database server can always recover from instance failures, even when redo logs are not archived

---------------------------------------------------------------------

This appears to be saying that direct path loads are recoverable. Comments??

[jmodic]

That's true if your table is in LOGGING mode. But then again, the same is true with INSERT /*+ APPEND*/ too - the two are in fact the same thing.

If you realy want to reduce redolog generation, put the table in NOLOGGING mode and then perform a directh path operation (wether that is SQL*Loader's direct path or insert with APPEND hint). And in both cases, if you need to recover from a backup taken prior to direct path operation, you'll find out that it can't be done. I mean, the recovery will suceed, of course, but the table that has been loaded directly will not be reloaded during the recovery, and furthermore you'll find out that it is corrupted. You'll get something like:

SQL> select * from blahblah;
ERROR:ORA-01578: ORACLE data block corrupted (file # .., block # ..)
ORA-01110: data file 11: '.....'
ORA-26040: Data block was loaded using the NOLOGGING option