This discussion is archived
1 2 3 4 Previous Next 49 Replies Latest reply: Jan 21, 2013 11:08 AM by 864103 Go to original post RSS
  • 45. Re: nologging
    krishan Explorer
    Currently Being Moderated
    Sir,

    But, we use /*+ append */ hint to make use of NOLOGGING mode.

    insert /*+ append */ into A select * from B;

    Please correct me if I am wrong.

    Regards,
    Krishan
  • 46. Re: nologging
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    krishan wrote:

    But, we use /*+ append */ hint to make use of NOLOGGING mode.

    insert /*+ append */ into A select * from B;

    Please correct me if I am wrong.
    I've given you the 4 combinations above: logging/nologging with or without append.
    It happens that the combination of append and nologging is particularly effective (within the limits I mentioned) - but the two features do not have to be used together

    Regards
    Jonathan Lewis
  • 47. Re: nologging
    864103 Newbie
    Currently Being Moderated
    thanks for stopping by , just please see my comments :

    If you are running the database, or the tablespace, with FORCE LOGGING then nologging doesn't apply :
    my database force_loggin is false

    If the table appears at either end of a referential integrity constraint, or has a trigger on it, then nologging doesn't apply:
    my tables don't have FK or trigger
    (there may be a couple of other reasons that I don't recall at present) :
    i will very happy to share with my all the reason .


    Nologging applies only for direct path loading, or such things as Create table as select, alter table move, alter index rebuild, create index:
    NAME                                                                  VALUE
    ---------------------------------------------------------------- ----------
    redo size                                                               796
    
    SQL> create table jo nologging
      2  as
      3  select * from employees;
    
    Table created.
    
    NAME                                                                  VALUE
    ---------------------------------------------------------------- ----------
    redo size                                                             35556
    
    SQL> select force_logging from v$database;
    
    FOR
    ---
    NO
    concern your second ex: it generate redo but it's too small and also generate undo block
    but it will be speed that the first one without append and logging , please provide us ex:
    of these , due to really i want to clear this ???


    your cooperation with us will be a high appreciated.

    thanks Sir .
  • 48. Re: nologging
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    861100 wrote:

    concern your second ex: it generate redo but it's too small and also generate undo block
    but it will be speed that the first one without append and logging , please provide us ex:
    of these , due to really i want to clear this ???
    You're 90% of the way to sorting it all out yourself - do some checks based on the following
    create table t1 nologging
    as
    select * from all_objects where rownum <= 50000
    ;
    
    create index t1_i1 nologging on t1(object_owner, object_name);
    
    insert /*+ append */ into t1 select * from t1;
    Run the code with and without the APPEND, with and without the nologging, with and without the index.


    Regards
    Jonathan Lewis
  • 49. Re: nologging
    864103 Newbie
    Currently Being Moderated
    thanks i will check it .
1 2 3 4 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points