Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103

    Unanswered: Update Statement Tuning - Plan attached

    A simple update involving two small tables takes nearly 8 hours to complete. I would appreciate if you can kindly help me to understand the root cause of this slowness.

    Environment:
    DB2 V8.1 FP 12 / AIX 5.3
    Both are non-partitioned tables..residing on a logically partitioned database

    Table1: STG.TB_FIN_BRANCH has 34,658 records
    Table2: STG.TB_STATE has 108 records

    Update SQL :
    update stg.tb_fin_branch aa set (aa.branch_state) = ( select coalesce state,' ') from stg.tb_fin_branch br left outer join stg.tb_state st on r.branch_state = st.state where aa.branch_sk = br.branch_sk)
    Both tables has matching indexes and are in good shape as per db2reorgchk

    Table DDL:
    $ db2 describe table STG.TB_FIN_BRANCH

    Column Type Type
    name schema name Length Scale Nulls
    ------------------------------ --------- ------------------ -------- ----- ------
    BRANCH_SK SYSIBM DECIMAL 10 0 No
    BRANCH SYSIBM VARCHAR 6 0 No
    BRANCH_ADDRESS SYSIBM VARCHAR 30 0 Yes
    BRANCH_CITY SYSIBM VARCHAR 25 0 Yes
    BRANCH_STATE SYSIBM VARCHAR 2 0 Yes
    BRANCH_ZIPCODE SYSIBM VARCHAR 9 0 Yes
    BRANCH_COUNTRY SYSIBM VARCHAR 2 0 Yes
    CLOSED_DATE SYSIBM TIMESTAMP 10 0 Yes
    CONTACT_SK SYSIBM DECIMAL 10 0 No
    CONTACT_AGENCY SYSIBM VARCHAR 35 0 Yes

    10 record(s) selected.

    $ db2 describe indexes for table STG.TB_FIN_BRANCH show detail

    Index Index Unique Number of
    schema name rule columns Column names
    ------------------------------- ------------------ -------------- -------------- ------------------------------------------------------------
    STG WMX1 D 2 +BRANCH_STATE+BRANCH_SK
    STG WMX2 D 10 +CONTACT_SK+CONTACT_AGENCY+CLOSED_DATE+BRANCH_ZIPC ODE+BRANCH_CITY+BRANCH_ADDRESS+BRANCH+BRANCH_SK+BR ANCH_COUNTRY+BRANCH_STATE


    Table 2 DDL:
    $ db2 describe table STG.TB_STATE

    Column Type Type
    name schema name Length Scale Nulls
    ------------------------------ --------- ------------------ -------- ----- ------
    COUNTRY SYSIBM VARCHAR 2 0 No
    STATE SYSIBM VARCHAR 2 0 No
    STATE_NAME SYSIBM VARCHAR 100 0 No
    CTRY_STATE SYSIBM VARCHAR 100 0 No
    STATE_ORDER SYSIBM DECIMAL 10 0 No

    5 record(s) selected.

    $ db2 describe indexes for table STG.TB_STATE show detail

    Index Index Unique Number of
    schema name rule columns Column names
    ------------------------------- ------------------ -------------- -------------- ------------------------------------------------------------
    STG IX1 D 1 +STATE

    1 record(s) selected.
    I have attached the explain plan collected using db2exfmt for your reference
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I would speculate that an index on STG.TB_FIN_BRANCH (BRANCH_SK, BRANCH_STATE) could be a better choice. However, unless I'm missing something, you could achieve the same goal by doing
    Code:
    update stg.tb_fin_branch aa set aa.branch_state = ' ' where not exists 
    (select 1 from stg.tb_state st where aa.branch_state = st.state)
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    Thanks for your suggestion. I generated the explain plan with the new SQL and it was very good, when I executed the update statement it completed within a second.

    But when I tried to validate using select statement it failed.

    Old SQL : Returned 34,658 rows
    select count(*) from STG.TB_FIN_BRANCH AA where (AA.branch_state) = ( select COALESCE(state,' ') from STG.TB_FIN_BRANCH BR LEFT OUTER JOIN STG.TB_STATE ST on BR.branch_state = ST.state where AA.branch_sk = BR.branch_sk ) with ur
    New SQL : Returned 0 rows
    select count(*) from stg.tb_fin_branch aa where not exists ( select 1 from stg.tb_state st where aa.branch_state = st.state )
    Could you please provide your thoughts on this? Thanks

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Well, if you attempt to explain what your SQL statement is doing in plain words, it's really quite simple. Your original statement can be described as this: fetch the STATE column value from TB_STATE if it equals the value in the BRANCH_STATE column in TB_FIN_BRANCH; update BRANCH_STATE with the STATE value. If the matching STATE value cannot be found, put a single space into BRANCH_STATE. Update every line in TB_FIN_BRANCH in this manner.

    Now, if you think about it, why would you need to update BRANCH_STATE if it already has the same value as STATE? Clearly, you wouldn't, and only the second part of the original statement would possibly do anything useful. Therefore, we can reduce the description of work to this: find BRANCH_STATE values that do not have matching values in STATE and set them to the single blank value.

    As you can probably see, the original statement updates all records in TB_FIN_BRANCH, while the new one only updates those that need to be updated. It appears though that none need to be: they all have matching values in TB_STATE.

    From your question I have a feeling that it may not have been you who wrote the original statement...
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    Thanks for the quick and detailed reply. That helped me understand exactly what is happening on that statement.

    And as you suspected I did not code the SQL and infact this Update SQL would be automatically triggered by a third party tool.

Posting Permissions

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