Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2008
    Posts
    88

    Unanswered: optimizing the Delete SQL

    delete from tab1 a where cast(a.col as BIGINT) not in (select cast(s.col as bigint) from tab2 s).

    Does anyone has any idea to optimize this delete. I want to delete the data from tab1 on the basis of column which doesnt have any equivalent row in tab2.

    Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You do have indexes on TAB2.COL and TAB1.COL? Other than that, there is not much to optimize.

    The main issue here will be logging anyway, i.e. if there are many rows to be deleted, the before image of each row must be written to the log. So you may want to consider turning logging off with ALTER TABLE ... NOT LOGGED INITIALLY.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Using the CAST function on a predicate may reduce (or eliminate) the chances of DB2 using an index on that column.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    Quote Originally Posted by azs0309
    delete from tab1 a where cast(a.col as BIGINT) not in (select cast(s.col as bigint) from tab2 s).
    Thanks

    make sure, that s.col is NOT NULL in tab2. If a NULL-value occures, the DELETE fails.

    For performance reasons, omit the CAST() funcion ( at least in the WHERE-clause of the outer statement )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •