Forum Stats

  • 3,851,735 Users
  • 2,264,018 Discussions
  • 7,904,831 Comments

Discussions

Need to delete rows fast

2»

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,479 Red Diamond

    Hi, @Dave_VZ

    In my outmoded ways I still think of the compound condition (A AND B) as requiring two passes against the result set.

    No, only one pass is required. (By the way, it's not a pass through the result set, it's a pass through the data to produce the resu;lt set.)

    If that's still true, I speculate that the condition drops to one I/O pass if the condition becomes "x BETWEEN trunc(sysdate) AND trunc(sysdate) + 1". If I recall correctly, the BETWEEN conditions are also inclusive, which eliminates the extra I/O required for the GE qualifier. 

    I'm not sure what you mean by " the extra I/O required for the GE qualifier". You're absolutely correct that x BETWEEN y AND z includes both y and z, so

    x >= TRUNC (SYSDATE)  AND  x < TRUNC (SYSDATE) + 1
    

    is NOT equivalent to

    x   BETWEEN  TRUNC (SYSDATE)  AND  TRUNC (SYSDATE) + 1.
    

    If x is exactly 00:00:00 tomorrow, then the former is FALSE, but the latter is TRUE.

  • Uday_N
    Uday_N Member Posts: 320 Bronze Badge

    Hi All,

    Thanks for all your wonderful analysis. It was really enriching me . Thanks once again . I use the below query which was given by paul which takes time . There is no lock as well . I have used the below query .

    delete from target_table 
    where create_date >= trunc(sysdate) and 
          create_date < trunc(sysdate) + 1 and 
          id in (select id from source_table) ;
    

    Plan:


    -------------------------------------------------------------------------------------------------------------------------

    | Id | Operation               | Name              | Rows | Bytes | Cost (%CPU)| Time   |

    -------------------------------------------------------------------------------------------------------------------------

    |  0 | DELETE STATEMENT            |                | 12215 |  644K|  360  (1)| 00:00:01 |

    |  1 | DELETE                | TARGET_TABLE           |    |    |      |     |

    |* 2 |  FILTER                |                |    |    |      |     |

    |* 3 |  HASH JOIN SEMI           |                | 12215 |  644K|  360  (1)| 00:00:01 |

    |  4 |   TABLE ACCESS BY INDEX ROWID BATCHED| TARGET_TABLE          | 12215 |  536K|   5  (0)| 00:00:01 |

    |* 5 |   INDEX RANGE SCAN         | I_TARGET_TABLE_CREATE_DATE   |   1 |    |   4  (0)| 00:00:01 |

    |  6 |   TABLE ACCESS FULL         | SOURCE_TABLE          | 109K|  960K |  354  (1)| 00:00:01 |

    -------------------------------------------------------------------------------------------------------------------------

     

    Predicate Information (identified by operation id):

    ---------------------------------------------------

     

      2 - filter(TRUNC([email protected]!)+1>TRUNC([email protected]!))

      3 - access("C"."ID"="B"."ID")

      5 - access(TRUNC(INTERNAL_FUNCTION("CREATE_DT"))>=TRUNC([email protected]!) AND 

           TRUNC(INTERNAL_FUNCTION("CREATE_DT"))<TRUNC([email protected]!)+1)

    Is there anything i should look into on what causing the performance issue in delete ? Please advise   

  • Paulzip
    Paulzip Member Posts: 8,748 Blue Diamond

    How long is "takes time"?

  • Uday_N
    Uday_N Member Posts: 320 Bronze Badge

    Hi Paul,

    It takes more than 2 hours

  • Paulzip
    Paulzip Member Posts: 8,748 Blue Diamond

    Are your table stats up to date?

    Is there an index on source_table.id?

    Are other processes updating / deleting on this table?

    Is the row size large on target_table?

    Try exclusively locking the target_table before the delete.

  • Uday_N
    Uday_N Member Posts: 320 Bronze Badge

    Hi Paul ,

    thanks for the answers . There is no index on the source table and stats are upto date . No process are on the table . Will exclusively lock the table as per your advice . Really Thanks for the advice you have given . It was really helpful . Thanks once again

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,721 Red Diamond

    @Uday_N There is no lock as well 

    OK. Then statement level write consistency (a.k.a. mini-rollback) could be the cause. If DELETE runs into a row that changed since DELETE started the DELETE will rollback and start over. So lock table exclusively, as you mentioned, and test again.

    SY.

  • Uday_N
    Uday_N Member Posts: 320 Bronze Badge

    Hi Solomon ,


    Sure . I will try . Thanks for your valuable advise . It’s always helps me . Thanks once again