can you use hints in SQLLoader
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: can you use hints in SQLLoader

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    can you use hints in SQLLoader

    Hi,

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

    i.e.,

    Code:
    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??

  2. #2
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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
    anandkl

  3. #3
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Yes, but that just speeds up the load,

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

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

  4. #4
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi,
    I dont think DIRECT PATH does generate REDO LOGS, Please correct me if i am wrong.

    regards
    anandkl
    anandkl

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    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??

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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:
    Code:
    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
    Last edited by jmodic; 06-10-2005 at 05:35 AM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width