Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2010
    Posts
    8

    Unanswered: Need help in optmizing DELETE statement

    Here is the query:

    DELETE
    FROM A
    WHERE SEGMENT_ID IN
    (SELECT DISTINCT PARTY_ID
    FROM B)

    Any inputs would be deeply appreciated. Thanks in advance.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Not sure what you want to do, but try this instead:

    DELETE FROM TABLE_1 A
    WHERE EXISTS
    (SELECT 1 FROM TABLE_2 B where B.SEGMENT_ID = A.SEGMENT_ID)

    Obviously, you will want an index on SEGMENT_ID for TABLE_2
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Apr 2010
    Posts
    8
    Thanks for the reply. Actually SEGMENT_ID is joined with PARTY_ID and not with SEGMENT_ID of the second table. I modified the query according to your suggestion and did not find any improvement in performance.

    I ran db2expln utility on the query I pasted for optimization and your version of the query but the Estimated cost (=1171144.250000) remained the same.

    Is there any other way apart from what you suggested earlier. Thanks once again

  4. #4
    Join Date
    Apr 2010
    Posts
    8
    Thanks for the reply. Actually SEGMENT_ID is joined with PARTY_ID and not with SEGMENT_ID of the second table. I modified the query according to your suggestion and did not find any improvement in performance.

    I ran db2expln utility on the query I pasted for optimization and your version of the query but the Estimated cost (=1171144.250000) remained the same.

    Is there any other way apart from what you suggested earlier. Thanks once again

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How about posting the access plan. The number does nothing for us. How many rows are in table_1 and table_2? How many rows do you expect to be deleted? Do you have an index on party_id in table_2? Do you have an index on segment_id in table_1?

    Andy

  6. #6
    Join Date
    Apr 2010
    Posts
    8
    DELETE
    FROM ADDRESS
    WHERE SEGMENT_ID IN
    (SELECT DISTINCT PARTY_ID
    FROM SUBSCRIBER_DATA)

    Section Code Page = 1208

    Estimated Cost = 1171144.250000
    Estimated Cardinality = 153268.000000

    Access Table Name = EXTRACT.SUBSCRIBER_DATA ID = 4,57
    | #Columns = 1
    | Relation Scan
    | | Prefetch: Eligible
    | Lock Intents
    | | Table: Intent Share
    | | Row : Next Key Share
    | Sargable Predicate(s)
    | | Process Build Table for Hash Join
    Hash Join
    | Early Out: Single Match Per Outer Row
    | Estimated Build Size: 10320000
    | Estimated Probe Size: 4928000
    | Access Table Name = EXTRACT.ADDRESS ID = 4,13
    | | #Columns = 1
    | | Relation Scan
    | | | Prefetch: Eligible
    Isolation Level: Read Stability
    | | Lock Intents
    | | | Table: Intent Exclusive
    | | | Row : Exclusive
    | | Sargable Predicate(s)
    | | | Process Probe Table for Hash Join
    Establish Row Position
    | Access Table Name = EXTRACT.ADDRESS ID = 4,13
    Delete: Table Name = EXTRACT.ADDRESS ID = 4,13

    End of section


    Optimizer Plan:

    DELETE
    ( 2)
    /----/ \
    FETCH Table:
    ( 3) EXTRACT
    /---/ \ ADDRESS
    HSJOIN Table:
    ( 4) EXTRACT
    /-/ \ ADDRESS
    TBSCAN TBSCAN
    ( 5) ( 6)
    | |
    Table: Table:
    EXTRACT EXTRACT
    ADDRESS SUBSCRIBER_DATA

    FYI, there is no index on either of the tables.

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Without indexes, the best you can get are tablescans, which is what you are getting. If you want to speed it up, you need indexes.

    Andy

  8. #8
    Join Date
    Apr 2010
    Posts
    8
    Thanks Andy. I understand that indexes are needed but I feel it might hamper the performance of other queries. Anyway I will see what I can do. Thanks very much.

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Your tables have no indexes. All current queries will be doing tablescans--the worst possible access plan. How can adding index hamper the performance of other queries?

    Andy

  10. #10
    Join Date
    Apr 2010
    Posts
    8
    I am sorry. You're right. I got mixed up with another table of the same sort.

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by diwakarmdba View Post
    Thanks Andy. I understand that indexes are needed but I feel it might hamper the performance of other queries. Anyway I will see what I can do. Thanks very much.
    I specfically told you that my example needed an index to work efficiently.

    Adding an index will not adversely affect other selects, but will add slight overhead to inserts and deletes.

    I suggest that you add the index and test my query (with the correct column names), and also test inserts and deletes with and without the index.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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