1 2 Previous Next 18 Replies Latest reply: Oct 4, 2012 2:17 AM by jeneesh RSS

    DELETE with NOLOGGING?

    574641
      Hi All,

      Using Oracle 10g version

      I have a table A and want to delete almost 15 million rows. My DBA was suggesting to use delete statement with no logging option. My questions
      1) does a nologging work for delete option?
      2) if yes then what is the syntax?
      Delete from A nologging where col1="hello" ?

      3) Does the table need to be created with NOLOGGING option for this?

      I could find articles regarding NOLOGGING for inserts in asktom etc... but couldnt find for DELETE ...

      Please help

      Thanks,
      Saff
        • 1. Re: DELETE with NOLOGGING?
          Anurag Tibrewal
          Hi,

          No nologging has no effect on any dml in oracle

          Regards
          Anurag
          • 2. Re: DELETE with NOLOGGING?
            574641
            Hi Anurag,

            Then what did my DBA mean by saying the folowing:

            "NOLOGGING works with DELETE DML … again, keep in mind that nologging=no rollback capabilities." ?

            Thought of taking the forum opinion before going back to him.

            Thanks,
            Saff
            • 3. Re: DELETE with NOLOGGING?
              699346
              I have seen the NOLOGGING hint used with statements. Like /*+ NOLOGGING */.
              However, I don't think that this is hint is documented or correct. I wouldn't hurt to try.

              You could also attempt to switch the tablespace to nologging. This will make all newly created object with nologging.
              Or you could alter the table with nologging, Such as: ALTER TABLE <TABLENAME> NOLOGGING;

              GOOD LUCK.
              • 4. Re: DELETE with NOLOGGING?
                Hoek
                http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:421219463156#227219400346833034
                • 5. Re: DELETE with NOLOGGING?
                  Anurag Tibrewal
                  Hi,

                  You may need to ask him
                  NOLOGGING works with DELETE DML … again, keep in mind that nologging=no rollback capabilities." ?
                  {code}
                  
                  What he meant by this?
                  Did he meant you cannot rollback the delete dml. Then tell him to show it practically.
                  
                  Regards
                  Anurag                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                  • 6. Re: DELETE with NOLOGGING?
                    6363
                    saffron wrote:

                    Then what did my DBA mean by saying the folowing:

                    "NOLOGGING works with DELETE DML … again, keep in mind that nologging=no rollback capabilities." ?
                    It means you need a new DBA.
                    • 7. Re: DELETE with NOLOGGING?
                      574641
                      Hi Guys,

                      Thanks for your suggestions and the link to Tomkyte.

                      Even I tried creating a table with nologging option, then delete from the table and tried rollback. The rollback actually did happen.
                      1. create table mytab NOLOGGING as select * from od_order
                      
                      2. select ord_id from mytab where ord_id=165
                      165
                      
                      3. delete from mytab where ord_id=165 
                      1 rows deleted
                      
                      4. rollback
                      Rollback executed
                      
                      5. select ord_id from mytab where ord_id=165
                      165
                      Then why did rollback did not happen? with the table created using NOLOGGING option, there should have been no REDO and hence no rollback right?

                      Thanks,
                      Saff
                      • 8. Re: DELETE with NOLOGGING?
                        Anurag Tibrewal
                        saffron wrote:
                        Hi Guys,

                        Thanks for your suggestions and the link to Tomkyte.

                        Even I tried creating a table with nologging option, then delete from the table and tried rollback. The rollback actually did happen.
                        1. create table mytab NOLOGGING as select * from od_order
                        
                        2. select ord_id from mytab where ord_id=165
                        165
                        
                        3. delete from mytab where ord_id=165 
                        1 rows deleted
                        
                        4. rollback
                        Rollback executed
                        
                        5. select ord_id from mytab where ord_id=165
                        165
                        Then why did rollback did not happen? with the table created using NOLOGGING option, there should have been no REDO and hence no rollback right?

                        Thanks,
                        Saff
                        Sorry am getting confused.
                        Did you ask why did rollback did not happen.
                        I thought rollback happen and thats why 165 did not get deleted even though you fired a delete statement.

                        Regards
                        Anurag
                        • 9. Re: DELETE with NOLOGGING?
                          574641
                          Oops my bad, what I wanted to ask was why did the rollback actually happened. Ideally it should NOT have happened since the table is in nologging mode?

                          Thanks,
                          Saff
                          • 10. Re: DELETE with NOLOGGING?
                            Anurag Tibrewal
                            saffron wrote:
                            Oops my bad, what I wanted to ask was why did the rollback actually happened. Ideally it should NOT have happened since the table is in nologging mode?

                            Thanks,
                            Saff
                            As already said nologging as no effect on DML. Delete is a dml statement.
                            Ideally it should rollback even if the table is logging or nologging mode. This is the way Oracle is made.

                            Regards
                            Anurag
                            • 11. Re: DELETE with NOLOGGING?
                              574641
                              Ok, I think I am getting it now!!. So basically REDO segment is used only for "Recovery" purpose is what I understand. When a table A is created for the first time using CTAS from table B, at that time if we use NOLOGGING option, then the table wont write any records in the REDO segments. This is what I understand.

                              But with DML, whatever be the table properties, REDO segment doesnt play any role here, but only ROLLBACK segment plays a role and will always be written with existing recod before the change was made.

                              Is that right?

                              Thanks,
                              Saff
                              • 12. Re: DELETE with NOLOGGING?
                                Anurag Tibrewal
                                saffron wrote:
                                Ok, I think I am getting it now!!. So basically REDO segment is used only for "Recovery" purpose is what I understand. When a table A is created for the first time using CTAS from table B, at that time if we use NOLOGGING option, then the table wont write any records in the REDO segments. This is what I understand.

                                But with DML, whatever be the table properties, REDO segment doesnt play any role here, but only ROLLBACK segment plays a role and will always be written with existing recod before the change was made.

                                Is that right?

                                Thanks,
                                Saff
                                When you say nologging for CTAS, yes records are not returned to the redo logs (only create statement is written).

                                With dml too redo and rollback segment both plays a role and records are written at both place.

                                Regards
                                Anurag
                                • 13. Re: DELETE with NOLOGGING?
                                  6363
                                  saffron wrote:

                                  Ideally it should NOT have happened since the table is in nologging mode?
                                  NOLOGGING affects REDO generation, UNDO is used for rollback.

                                  NOLOGGING affects the following object creation processes, and direct path inserts only.

                                  http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/clauses005.htm#SQLRF30009

                                  http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_9014.htm#SQLRF01604

                                  So NOLOGGING has no effect on UNDO / rollback and no effect on delete statements.
                                  • 14. Re: DELETE with NOLOGGING?
                                    574641
                                    Thanks for the replies folks... let me now go confidently to my DBA....

                                    I think for pointless's comment (Need a new DBA), let me forward that to my manager ;-)

                                    Thanks,
                                    Saff
                                    1 2 Previous Next