Results 1 to 13 of 13
  1. #1
    Join Date
    May 2006
    Posts
    18

    Unanswered: How can I force DB2 to use indexes ? Plz give some trick

    Hi,
    I am having the follwoing query :
    /* Select * FROM S1, S2
    WHERE S1.SOURCE_ID = S2.SOURCE_ID and
    S1.PARTITION_FLAG = '0' AND S2.PARTITION_FLAG = '0' */
    Both the tables are having indexs on source id and partition flag. But Db2 is only using index for partition flag (as seen in explain plan) and not for source_id which is joining field.I dont know how I tell Db2 to use that index also so that it will improve perf drastically . Currently there is full table scan on these two tables.

    Any help would be great..really great.

    Thanks
    Kunal

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    First, try to do a runstants on the table (and use "with indexes all" option). Do "distribution on key columns".

    If that does not work, alter the table to "volatile", but I cannot guarantee that it will run faster.
    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
    May 2006
    Posts
    18

    Unhappy

    Hi Marcus,

    Thanks for reply.
    I have already tried Re org- runstats . But it still does not work. I am not getting why is it using only index? Isnt there some kind of hint I give to DB2 that please use the other index also.

    Waiting for ans.

    Thanks

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by kunal
    Both the tables are having indexs on source id and partition flag. But Db2 is only using index for partition flag (as seen in explain plan)
    So, each table has two indexes: on source_id and, separately, on partition_flag? Could you possibly post index definitions and the execution plan?
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If DB2 needs to access at least one row on each page, then it is almost always faster to not use an index. A merge scan join (if that is what is being used) instead of a nested loop join is not necessarily a slower access plan given the query you have.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    May 2006
    Posts
    18
    Hi
    Thanks for reply.
    I am need not proecssing each of the row but just 6.5 % of the total. i.e in the query /* Select * FROM STG_INCY_HIST S1, STG_INCY_HIST_EFCV_DT S2
    WHERE S1.PARTITION_FLAG = '0'
    AND S2.PARTITION_FLAG = '0' and S1.SOURCE_ID = S2.SOURCE_ID */
    For the " PARTITION_FLAG = '0'" I have only 6.5 % of the total data.
    The indexes are :-
    1. S1.SOURCE_ID = S_INCY_HIST_SRC_ID
    2. S2.SOURCE_ID = S_INCY_EF_SRC_ID
    3. S1.PARTITION_FLAG = S_INCY_HIST_IND
    4. S2.PARTITION_FLAG = S_INCY_HIST_EF_IND

    I have attached the plan from which one can see that DB2 is using ONLY the source id index on S1 and ONLY partition flg index on S2.
    This is stupidity coz to acess the rows DB2 is going for Table scan.
    God knows why it is not using both the indexs?Without them it makes this query run for marathon!!!!

    Plz help.
    Thanks Kunal
    Attached Thumbnails Attached Thumbnails explain plan.JPG  

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by kunal
    I have attached the plan from which one can see that DB2 is using ONLY the source id index on S1 and ONLY partition flg index on S2.
    This is stupidity coz to acess the rows DB2 is going for Table scan.
    I don't see anything wrong with the access plan, given your choice of indexes.

    Try creating indexes on PARTITION_FLAG, SOURCE_ID (in that order) for both tables, you may have better luck. On top of that, you may also want to rewrite the WHERE clause: "WHERE S1.PARTITION_FLAG = '0' AND S2.PARTITION_FLAG = '0' AND S1.PARTITION_FLAG = S2.PARTITION_FLAG".

    By the way, to obtain index definitions you would normally use DESCRIBE INDEXES FOR TABLE ... SHOW DETAIL.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Jun 2006
    Posts
    471

    index

    the plan is using ixscan to access the base tables
    sorting the second result - probably merge scan
    the tbscan is from the result table after the ixscan-fetch-sort..
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  9. #9
    Join Date
    May 2006
    Posts
    18
    hi,

    Thanks for the replies.
    I have tried creating the composite index on PARTITION_FLAG, SOURCE_ID in this ordr and then manipulating the join condition. But it had worse impact as now it used index only for one table and other is full acessed. The performance worsen much more.

    Guy- Correct me if I am wrong ..but I see only one type of join here and that is excecuted as Nested loop..Also initially looking closely at explain plan I see a table being IX scan only for attribute i.e S2 is IXscan on Partition flag but to get Src id it is going for Complete table scan which is bad.

    I have given the index details and It has looks strange. They are very diff for two tables even though they holds almost very similer data.
    (note:first is table S1 and second is table S2).

    Thanks for replies . I am still waiting for help.)

    Thnaks Kunal
    Attached Thumbnails Attached Thumbnails index details.JPG  

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by kunal
    I have tried creating the composite index on PARTITION_FLAG, SOURCE_ID in this ordr and then manipulating the join condition. But it had worse impact as now it used index only for one table and other is full acessed. The performance worsen much more.
    Have you updated table statistics after creating the indexes?

    Quote Originally Posted by kunal
    I am still waiting for help.)
    That's nice, but it's kinda difficult to help when you show us some funny pictures instead of the index DDL scripts and db2exfmt output...
    ---
    "It does not work" is not a valid problem statement.

  11. #11
    Join Date
    May 2006
    Posts
    18
    Hi
    I did reorg and runstat after creating the new indexes. The index DDL is
    ************************************************** *******
    -- Create Index RIDQADM.S_INCY_HIST_IND
    --------------------------------------------------
    create Index RIDQADM.S_INCY_HIST_IND
    on RIDQADM.STG_INCY_HIST
    (PARTITION_FLAG) Allow Reverse Scans;

    --------------------------------------------------
    -- Create Index RIDQADM.S_INCY_HIST_SRC_ID
    --------------------------------------------------
    create Index RIDQADM.S_INCY_HIST_SRC_ID
    on RIDQADM.STG_INCY_HIST
    (SOURCE_ID) Cluster Allow Reverse Scans;

    AND

    --------------------------------------------------
    -- Create Index RIDQADM.S_INCY_EF_SRC_ID
    --------------------------------------------------
    create Index RIDQADM.S_INCY_EF_SRC_ID
    on RIDQADM.STG_INCY_HIST_EFCV_DT
    (SOURCE_ID) Cluster Allow Reverse Scans;

    --------------------------------------------------
    -- Create Index RIDQADM.S_INCY_HIST_EF_IND
    --------------------------------------------------
    create Index RIDQADM.S_INCY_HIST_EF_IND
    on RIDQADM.STG_INCY_HIST_EFCV_DT
    (PARTITION_FLAG) Allow Reverse Scans;
    ************************************************** ********

    Let me know for anything else .

    Kunal ..

  12. #12
    Join Date
    Aug 2004
    Posts
    330
    Have you tried using the optimization hint?

  13. #13
    Join Date
    May 2006
    Posts
    18
    Hi
    I did run the SQL optimiser for DB2 which gave hints like coalesce etc . which did not work ...however I did not find any "HINTS" in the manner we obtain in ORACLE like /*+ format. In fact I am very eagrly looking for how to supply a hint which will tell DB2 to use Index .
    Plz let me know if anyone knows.

Posting Permissions

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