Nologging, index and direct-load insert
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Nologging, index and direct-load insert

  1. #1
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Hi all,

    I posted this question to usenet last week but no response so far :-(.
    I'm sure somebody here tells me what's wrong.


    From Oracle doco8.1.7:

    TUNING PARALLEL EXECUTION:
    "When a table or index has NOLOGGING set, neither parallel nor serial
    direct-load INSERT operations generate undo or redo logs."

    ALTER INDEX NOLOGGING:
    "Use LOGGING or NOLOGGING to specify whether subsequent
    Direct Loader (SQL*Loader) and direct-load INSERT operations
    against a nonpartitioned index, a range or hash index partition, or
    all partitions or subpartitions of a composite-partitioned index
    will be logged (LOGGING) or not logged (NOLOGGING) in the redo
    log file."

    BUT ...
    when I create a table and set it as nologging, subsequent direct-load INSERT
    makes reasonable amount of redo.
    After truncating the table I create an ordinary index on that table, set it
    nologging as well and make direct-load INSERT again. The redo is huge.
    It seems that NOLOGGING status applies to tables only despite of manual.

    Is that bug or feature?

    My Oracle is 8.1.7 EE on AIX

    Thanks in advance,
    Ales

    ***********************************************************************
    Testing report follows:
    ***********************************************************************

    SQL>create table t nologging as select * from all_objects where 1=0;

    Table created.

    SQL>set autotrace on statistics
    SQL>insert /*+ APPEND */ into t select * from all_objects;

    21734 rows created.

    Statistics
    ----------------------------------------------------------
    786 recursive calls
    154 db block gets
    83190 consistent gets
    0 physical reads
    19616 redo size
    493 bytes sent via SQL*Net to client
    466 bytes received via SQL*Net from client
    4 SQL*Net roundtrips to/from client
    3 sorts (memory)
    0 sorts (disk)
    21734 rows processed

    SQL>commit;

    Commit complete.

    SQL>truncate table t;

    Table truncated.

    SQL>create index t_ind on t (object_id) nologging;

    Index created.

    SQL>select table_name, logging from tabs where table_name='T'
    2 union
    3 select index_name, logging from ind where table_name='T';

    TABLE_NAME LOG
    ------------------------------ ---
    T NO
    T_IND NO


    --(Statistics omitted)

    SQL>insert /*+ APPEND */ into t select * from all_objects;

    21735 rows created.


    Statistics
    ----------------------------------------------------------
    1972 recursive calls
    1437 db block gets
    83613 consistent gets
    319 physical reads
    1484268 redo size
    494 bytes sent via SQL*Net to client
    466 bytes received via SQL*Net from client
    4 SQL*Net roundtrips to/from client
    2 sorts (memory)
    1 sorts (disk)
    21735 rows processed

    SQL>commit;

    Commit complete.

    ***********************************************************************



  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    redo are always generated, nologging minimize the amount but not to zero

    Code:
    opsc@MOVE>alter table obj nologging;
    
    Table altered.
    
    opsc@MOVE>set autotrace on
    
    opsc@MOVE>insert /*+ append */ into obj select * from dba_objects where rownum < 100
    
    99 rows created.
    
    Statistics
    ----------------------------------------------------------
            221  recursive calls
             12  db block gets
             98  consistent gets
             10  physical reads
            296  redo size
            633  bytes sent via SQL*Net to client
            503  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              5  sorts (memory)
              0  sorts (disk)
             99  rows processed
    
    opsc@MOVE>alter table obj logging;
    
    Table altered.
    
    opsc@MOVE>insert into obj select * from dba_objects where rownum < 100;
    
    99 rows created.
    
    Statistics
    ----------------------------------------------------------
            151  recursive calls
             26  db block gets
             78  consistent gets
              0  physical reads
          10456  redo size
            647  bytes sent via SQL*Net to client
            489  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              4  sorts (memory)
              0  sorts (disk)
             99  rows processed
    296 redo size with nologging vs 10456 redo size with logging


  3. #3
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Pando, thanks for reply.
    Yes, I understand there must be some redo.
    Just see again my original post.
    Populating the table with over 20,000 rows makes about 20 K redo. That's great and works as I'd expect.
    But the same count of rows inserted into the same table with NOLOGGING index makes about 1,5M redo. That is the problem. It seems that subsequent maintenance of nologgingg index after direct-load insert is fully loged in redo. But (if I understand it) it shouldn't.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    you mean that if you create an index with nologging then susquent insert shouldnt generate redo with that index...?

    AFIAK if you reverse your process, first insert then create the index with nologging then you get minimum redo but if you insert with an index then itīs pretty normal?

  5. #5
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Originally posted by pando
    you mean that if you create an index with nologging then susquent insert shouldnt generate redo with that index...?
    No. Subsequent direct-load insert shouldn't.

    If I create an index with nologging clause on populated table, then redo is small.
    If I create an index with nologging clause on empty table and then populate the table with direct-load insert, then redo is big.


  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    you mean that if you have an index with nologging attribute then the inserts afterwards should not generate redo?

    direct load works if you use insert append or create index (and a few more) that doesnt mean that after you create an index with nologging then the redo would be small later



    If I create an index with nologging clause on populated table, then redo is small.

    this is normal because the index creation process is generating minimum redo

    If I create an index with nologging clause on empty table and then populate the table with direct-load insert, then redo is big

    this is normal too because the redo generated is for both table and index

  7. #7
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Hi, I'm sorry for the delay.
    I don't want to annoy with full report of testing,
    so have a look on the following summary table:
    Operation                                 Bytes of redo
    create table nologging ........................ 39172
    direct-load insert of 22,000 rows ............. 19613
    create index nologging on populated table ..... 33456
    truncate table ................................ 22556
    direct-load insert of 22,000 rows
    with both table and index set to nologging .... 1487556


    I hope it's clear what I mean: the redo generated with the last operation should be something about 55K, not 1.5 M.
    That's why I mean something is wrong with NOLOGGING.

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    you cannot set an index to nologging, I see it as a normal behaviour, a table with an index obviously generates more redo than one table without index even the table is set to nologging

    you can create an index with no logging but not alter itīs attribute

  9. #9
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Originally posted by pando
    you cannot set an index to nologging
    Yes, I see now and that's I'm worrying about :-), but doesn't manual say the exact opposite? (see my original post)

    ALTER INDEX NOLOGGING really sets the NOLOGGING column in TABS to "NO".
    But subsequent direct-load INSERT does not care and manual says it does.

    Please kick me if I misunderstand it.

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    well I make a pardon you can set to nologging an index

    so basically you are saying that an table with nologging and its indexes with nologging is generating more redo than a table without indexes with nologging ?

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