Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11

    Unanswered: optimizer question

    strange behavior and I don't see why
    db2 ese 10.1 fp4 on p/linux
    simple table (not partitioned) with different indexes - about 3m rows
    indexes on these cols :
    +QUAL+TYPE+STEP+STATUS
    +LINK
    +STEP+STATUS
    +SEQ
    +FORWARDLINK
    query with left outer join to same table and predicates : result =300rows
    select .....colnames.....
    from biip.MailboxStatus batchfilei0_ left outer join
    biip.MailboxStatus batchfilei1_ on batchfilei0_.link= batchfilei1_.seq
    where batchfilei0_.step=4 and (batchfilei0_.status<4 or
    batchfilei0_.status=5)or batchfilei0_.step<4
    according db2expln : tablescan
    Access Table Name = BIIP.MAILBOXSTATUS ID = 14,10
    | Relation Scan
    db2advis indicates no indexes recommended
    offline reorg-runstats have been done but still tablescan
    would there be any reason, why db2 would not filter the table before as the predicates reduce the answer-set to 300rows and only these rows would have a potential equivalent in the join..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Look at the db2exfmt output.
    Optimizer probably does wrong output rows number estimation from this predicate:

    batchfilei0_.step=4 and (batchfilei0_.status<4 or batchfilei0_.status=5)
    or batchfilei0_.step<4

    If this is the case and you use namely these constants in your query you can try to use a statistical view like this:

    select .....colnames.....
    from biip.MailboxStatus batchfilei0_
    where batchfilei0_.step=4 and (batchfilei0_.status<4 or
    batchfilei0_.status=5)or batchfilei0_.step<4
    Regards,
    Mark.

  3. #3
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Do you have distribution statistics? Uniform distribution assumption might lead to tablescan.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    thanks for the update
    yes we runstats with distribution and detailed indexes all
    the 300rows is the actual value, as I executed the query...
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as indicated - created the stats_view and tried again
    now the access is through the complete index on 1: QUAL TYPE STEP 4: STATUS (Ascending)
    but just to find data pages with use of any key-col
    the cost is about 10% of original cost - but we have to see for elapsed time....

    did the run again
    before the nbr of rows read was 6m and returned 300
    now the event monitor indicates 600 read and 300 returned...
    Last edited by przytula_guy; 05-27-15 at 06:39.
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  6. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Another option is to create MQT (even refresh immediate if you have an unique key for this table) using the same query as for the statistical view.
    Regards,
    Mark.

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I was thinking it may have been due to the where clause here as well with the ORs

    where batchfilei0_.step=4 and (batchfilei0_.status<4 or
    batchfilei0_.status=5)or batchfilei0_.step<4
    Shouldn't that be:
    Code:
    where (batchfilei0_.step=4
     and (batchfilei0_.status<4
       or batchfilei0_.status=5))
       or batchfilei0_.step<4
    One of the items I have suggested for years on the mainframe with that type of query was to use UNION, like:
    Code:
    select .....colnames.....
     from biip.MailboxStatus batchfilei0_ 
    
    left outer join biip.MailboxStatus batchfilei1_
       on batchfilei0_.link= batchfilei1_.seq
    where batchfilei0_.step=4
     and (batchfilei0_.status<4
       or batchfilei0_.status=5)
    union
    select .....colnames.....
     from biip.MailboxStatus batchfilei0_ 
    
    left outer join biip.MailboxStatus batchfilei1_
       on batchfilei0_.link= batchfilei1_.seq
    where batchfilei0_.step<4
    With either of those solutions you shouldn't have to worry about additional stats.
    Dave

Posting Permissions

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