Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230

    Unanswered: Why optimizer does not use index in my query?

    Hi.
    I am working with db2v7.2 fixpack 6 aix 4.3
    my query is like

    SELECT DISTINCT min(bstrm.account_coid)
    ,sp.defining_prod_coid as Product_COID
    ,char(bstrm.TrmDate_Start, ISO) as PP_Date
    ,char(bstrm.TrmDate_End, ISO) as MM_Date
    ,char(bsidx.Part_Adj) as Paa_Adj
    ,char(bsidx.Part_Level) as Paa_Lvl
    FROM bsidx
    INNER JOIN bstrm
    ON (bsidx.account_coid = bstrm.account_coid
    AND bsidx.product_seq = bstrm.product_seq
    AND char(bstrm.trmdate_start, ISO) <= '2004-02-14')
    group by ....
    I did runstats and bind .
    I have indexes on both table bsidx and bstrm but optimizer does not use those indexes
    Thank you for your help

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    What indexes? Please give index DDL.

    Are the tables disorganized? Did you run runstats with full stats after reorg? If not, please do so and retest.

  3. #3
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230
    Originally posted by Marcus_A
    What indexes? Please give index DDL.

    Are the tables disorganized? Did you run runstats with full stats after reorg? If not, please do so and retest.
    Hi Marcus
    we have unique index for both tables like

    CREATE UNIQUE INDEX ON bsidx (account_coid,product_seq,feature_id),
    1- I reorg entire database this weekend and runstats like
    runstats on table $schema.$tab with distribution and indexes all shrlevel change.
    2- even I treid and added
    AND bsidx.feature_id = bstrm.feature_id in the join cluse, still problem

    Thank you for time Marcus.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Hard to say for sure, but it looks to me like this is the issue:

    AND char(bstrm.trmdate_start, ISO) <= '2004-02-14')

    If this would select a significant number of rows on the table, then all pages need to be accessed, and a table space scan (hopefully with prefetch) is the best way to access the data. DB2 probably reads and sorts both tables and then joins them.

    But I cannot be certain without seeing the explain output.

    BTW, isn’t ISO your default date format?

  5. #5
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230
    Originally posted by Marcus_A
    Hard to say for sure, but it looks to me like this is the issue:

    AND char(bstrm.trmdate_start, ISO) <= '2004-02-14')

    If this would select a significant number of rows on the table, then all pages need to be accessed, and a table space scan (hopefully with prefetch) is the best way to access the data. DB2 probably reads and sorts both tables and then joins them.

    But I cannot be certain without seeing the explain output.

    BTW, isn’t ISO your default date format?
    Hi Marcus,
    Even after I changed to AND bstrm.trmdate_start <='2004-02-14' it does not work, but you are right because:
    bsidx has 300000 records and bstrm has 3000000 records and all bsidx.account_coid (the number is 300000) are selected in
    bsidx.account_coid = bstrm.account_coid so optimizer use TBSCAN for bsidx table , I think we should change the application design, anyway thank you again for spending your valuable time.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Yes, without the date column in the where clause you were selecting all rows of the table, so a tablespace scan is usually more efficient for reading all the rows. Even with the date column, using the date value supplied, a large percentage of the table will need to be read.

    DB2 will use an index if it can avoid reading at least some 4K pages in the tablespace (which means that none of the rows on that page will be read). This is because all physical reads are done in 4K page (or larger page if defined) increments.

    If DB2 has to read at least one row on every 4K page, then it will do a tablespace scan. Even in some cases where not every 4K page needs to be read by DB2, it will do a tablespace scan anyway because sequential prefetch can be enabled, which improves efficiency over the same number of random reads from disk.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    One more thing. If you created a new index with bstrm.trmdate_start as the only column or the first column of the index, and the index was the clustering index, then that index might be used to limit the rows selected, and the other indexes might be used for the join.

    But if you created such an index, you would need to ensure that the table is easy to keep in clustered sequence as the result of new rows inserted into the table.

Posting Permissions

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