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..
自拍 脫衣秀 a片 成人 a片 3p自拍 3y3成人色色網 3級片 45av 45av光碟 4qk 4u成人 4u成人影片 50av 520sex 520sex脫衣秀 520免費視訊 520免費影片 520聊天室 5278cc免費影片 5278成人色論 5278免費 5278貼圖區 555免費影片 5s成人色論 666成人 666成人光 666貼圖 女優 全裸 69成人 69性愛姿勢 69情色貼圖 69聊天室 鹹濕 裸圖 火辣 6g成人色論 6k情色av聊天館 6k情色淫婦聊天館 6k聊天室 視訊 6k聊天館 爆乳 6k貼圖 6k貼圖爆乳聊天館 707聊天聯盟 72p影片網 735聊天室 77p2p