Db2

 View Only

  • 1.  index usage

    Posted Tue September 29, 2020 11:28 AM
    having a strange issue  with optimizer and index usage on db2 luw on linux
    table ttt with    col1   col2  col 3 and many more   : table contains billion of rows and is partitioned on col1
    index exists on col2
    index exists on col3
    both are non-unique and partitioned
    doing
    select  col2=xxx and col3 in (xxx,yy) uses  index on col3
    select  col2=xxx and col3 in (xx,yy,zz) uses index on col2
    difference in behavior if 2 or 3 values supplied for col3 and in
    the query with index on col3 is much slower although cost is almost the same and all partitions are accessed in both cases

    has anyone already used DB2_INLIST_TO_NLJN setting for  forcing a join with inlist ? this is being done if 2 values are specified for in predicate
    according some discussions, this is a better solution, thus setting this setting
    but not in our case : the 3 values in in predicate is doing regular index access without this join on genrow and performing better

    would someone have another idea, how to favorite the usage of index on col2

    ------------------------------
    Thank for all update/help
    Best Regards, Guy Przytula
    ------------------------------

    #Db2


  • 2.  RE: index usage

    IBM Champion
    Posted Wed September 30, 2020 02:49 AM

    optimization guidelines may help here.

    try something like this:

    select * from ttt where col2=? and col3 in (?,?,?) /* <OPTGUIDELINES> <IXSCAN TABLE='TTT' INDEX='COL3IDX'/> </OPTGUIDELINES> */



    ------------------------------
    Markus Fraune
    ------------------------------

    Original Message