Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Force a table to use the index

    Hi,

    I have a stored procedure that returns monthly sum of reading data and seems to be a bit slow. I have created an index on the temp tables but the number of records returned isn't huge so I assume it's just doing a table scan. How can I confirm this? Also, is there a way to force it to use the index no matter what so I can tell if there is any difference in the speed? Not sure what else I can do to make it go faster..Thanks!!

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by db2user24 View Post
    I assume it's just doing a table scan. How can I confirm this?
    Use db2exfmt if you can extract the sql and execute it from the command line.


    Quote Originally Posted by db2user24 View Post
    is there a way to force it to use the index no matter what so I can tell if there is any difference in the speed?
    Read about optimization profiles.

  3. #3
    Join Date
    Oct 2010
    Posts
    14

    force the scan by..

    Guess this is not relevant now, since it's an old post, but for other users, it might help. You could force the index scan by adding the following predicate:

    AND COALESCE(INDX_COL, INDX_COL) = INDX_COL

    But this won't always guarantee a reduced run-time - ensure you check the plan and execute the statement before finally using this.

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by Anand.Kaushal View Post
    Guess this is not relevant now, since it's an old post
    It's only one day old



    Quote Originally Posted by Anand.Kaushal View Post
    You could force the index scan by adding the following predicate:

    AND COALESCE(INDX_COL, INDX_COL) = INDX_COL
    I have a table with only a few rows so db2 will do a table scan for sure, but I can still force it to use the index with the opt profile (if I really want to). By adding coalesce to my simple query, db2 is still doing a table scan (as expected). Can you please explain why adding COALESCE(INDX_COL, INDX_COL) = INDX_COL can force the index scan? It looks like it doesn't do anything.

  5. #5
    Join Date
    Oct 2010
    Posts
    14

    it depends..

    i'm sorry.. i guess i read the date wrong..

    well, i totally agree with you.. but it does depend on the nature of my sql.. using this doesn't or rather wouldn't always result in an indx scan.. and it might not be required at times.. but it does work.. has worked successfully at times with some complex queries.. but not always

    here's a sample script.. try it yourself..

    create table temp.tab1(c1 int, c2 int, c3 int, c4 int, c5 int);
    create index temp.ind1 on temp.tab1(c1);
    insert into temp.tab1 with t(val) as (values (1) union all select val + 1 from t where val <100) select val, val, val, val, val from t;
    runstats on table temp.tab1 with distribution and indexes all;

    select * from temp.tab1; --table scan
    select * from temp.tab1 where c1 = coalesce(c1, c1); --table scan
    select * from temp.tab1 where 10 in (c1,c2); --table scan
    select * from temp.tab1 where (c1 = 10 or c2 = 10); --table scan
    select * from temp.tab1 where 10 in (c1,c2) and coalesce(c1, c1) = c1; --index scan

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I compared:

    #1
    select *
    from temp.tab1
    where (c1 = 10 or c2 = 10)


    #2
    select *
    from temp.tab1
    where (c1 = 10 or c2 = 10) and coalesce(c1, c1) = c1


    #2 is using an index and the total cost is almost half of #1. Do you know what is it about this coalesce predicate that is making db2 to use the index?

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    #1
    select *
    from temp.tab1
    where (c1 = 10 or c2 = 10)


    #2
    select *
    from temp.tab1
    where (c1 = 10 or c2 = 10) and coalesce(c1, c1) = c1
    The result of #1 and #2 may be different, if c1 is nullable.
    Because, a row with c1 is null and c2 = 10 will be included in #1 and not included in #2.

    Anand.Kaushal created column c1 of the table as nullable.
    create table temp.tab1(c1 int, c2 int, c3 int, c4 int, c5 int);
    If adding "or c1 is null" to make the query #2 beeing equivalent to #1,
    access path might be changed.

    #3
    select *
    from temp.tab1
    where (c1 = 10 or c2 = 10)
    and (coalesce(c1, c1) = c1 or c1 is null)

  9. #9
    Join Date
    Oct 2010
    Posts
    14

    you're right..

    Tonkuma, you're absolutely right.. i'd use this when i'm sure there aren't any nulls in there..

    db2girl, that's an excellent find.. thank you.

  10. #10
    Join Date
    Nov 2007
    Posts
    265
    Quote Originally Posted by Anand.Kaushal View Post
    i'm sorry.. i guess i read the date wrong..

    create table temp.tab1(c1 int, c2 int, c3 int, c4 int, c5 int);
    create index temp.ind1 on temp.tab1(c1);
    insert into temp.tab1 with t(val) as (values (1) union all select val + 1 from t where val <100) select val, val, val, val, val from t;
    runstats on table temp.tab1 with distribution and indexes all;

    select * from temp.tab1; --table scan
    select * from temp.tab1 where c1 = coalesce(c1, c1); --table scan
    select * from temp.tab1 where 10 in (c1,c2); --table scan
    select * from temp.tab1 where (c1 = 10 or c2 = 10); --table scan
    select * from temp.tab1 where 10 in (c1,c2) and coalesce(c1, c1) = c1; --index scan
    Hi Anand, can you tell me where you are getting the output that says whether it used a table scan or an index scan? It is from the db2expln command?

  11. #11
    Join Date
    Nov 2007
    Posts
    265
    Can I use the -stmtfile sql-statement-file option of the db2expln command to check execution of a stored procedure? Or is there another way to do that? Basically I have something like this and want to check how it performs and see if it uses any indexes ( table scan vs index scan) ... thanks!!

    db2 "call COLLECT_TOTAL (1, 'Name', 1, 2, '2009-04-01-04.00.00.000000', '2009-05-01-04.00.00.000000', 8, 1, 'SESSION.tmpdata', 0)"

  12. #12
    Join Date
    Nov 2007
    Posts
    265
    Hmm, seems like I can't use db2expln on global temp tables... basically I want to optimize my temp tables and the index on it so that the sproc returns the results fast..

  13. #13
    Join Date
    Nov 2007
    Posts
    265
    So after doing some research, I found this link to run explain on stored procedures :


    How to create Explain Plan of DB2 Stored Procedures? | db2ude


    Has anyone ever tried this? Does it work? Thanks!!

Posting Permissions

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