APAR NOTE - DB2 - bad access path when using 'FETCH FIRST N ROWS ONLY' (n - small value)

Sunday, January 10, 2010

APAR NOTE :
http://www-01.ibm.com/support/docview.wss?uid=swg1IZ49936

IZ49936: UNDER CERTAIN CONDITIONS, OPTIMIZER MAY PICK LESS THAN OPTIMAL ACCESS PLAN DUE TO OVER-ESTIMATED CARDINALITY

Our team ran into a problem of using the 'fetch first 1 row only' (functional requirement of just finding whether atleast one row matched a criteria), and the return time for the query was 100 times longer than the one when used without the row limiting clause.
On viewing the explain data, we found it was using a table scan, and when used 'without' the clause, it used an appropriate index scan.
So what was the reason?? Because of the row-limiting clause, DB2 'thinks' (sometimes it doesn't align with the world) that the cardinality in the table formed due to a subquery with this clause is very low and hence uses a tablescan.

This has been fixed now, and all is well, but before this was put in place, I was able to 'tell the optimizer' about the cardinality using the runstats with distribution on the predicates, yes, RUNSTATS is that powerful..