Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    2

    Unanswered: Optimized querry

    I am using this querry which works fine;

    Please advise me as to get an optimized queery as this scans the entire table for every update of a row.

    UPDATE SRCHUPDATE_MAIN A
    SET A.FULL_NAME =(SELECT A.FIRST_NAME || B.MIDDLE_NAME || C.LAST_NAME
    FROM SRCHUPDATE_MID B,
    SRCHUPDATE_LST C
    WHERE A.EMP_ID = B.EMP_ID
    AND A.EMP_ID = C.EMP_ID);

    Thank you very much in anticipation of a solution

    Regards

    Padmanabhan

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    First, I assume that you have an index on EMP_ID in all three tables. If not, create one.

    You will always do a tablespace scan on SRCHUPDATE_MAIN since you are updating every row in that table (with data from the other tables). So it is more efficient for DB2 to do a tablespace scan on this table.

    If you have the indexes suggested above, there will be no tablespace scan on the subquery part of the SQL statement.

    Make sure you do runstats command on all tables involved, and also on the indexes for these tables.
    Last edited by Marcus_A; 01-20-05 at 01:27.
    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
    Jan 2005
    Posts
    2

    Optimized Query

    Thanks for your prompt reply.

    Does DB2 support Update based on table join like the one given below?
    If no, correlated sub query is the only solution?

    MS SQL Sever:

    UPDATE SRCHUPDATE_MAIN
    SET FULL_NAME = A.FIRST_NAME + B.MIDDLE_NAME + C.LAST_NAME
    FROM SRCHUPDATE_MAIN A, SRCHUPDATE_MID B, SRCHUPDATE_LST C
    WHERE A.EMP_ID = B.EMP_ID
    AND A.EMP_ID = C.EMP_ID

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    UPDATE SRCHUPDATE_MAIN E
    SET FULL_NAME =
    (SELECT A.FIRST_NAME || B.MIDDLE_NAME || C.LAST_NAME
    FROM SRCHUPDATE_MAIN A, SRCHUPDATE_MID B, SRCHUPDATE_LST C
    WHERE A.EMP_ID = B.EMP_ID
    AND A.EMP_ID = C.EMP_ID
    AND E.EMP_ID = A.EMP_ID)

    The last line was added by me. Not sure if this fits your data correctly, but you need some way correlate the joined rows with the table you are updating.
    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
  •