1 2 3 4 Previous Next 49 Replies Latest reply: Jan 21, 2013 1:08 PM by 864103 Go to original post RSS
      • 45. Re: nologging
        krishan
        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
          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
            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
              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
                thanks i will check it .
                1 2 3 4 Previous Next