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..

Posted by Arun Srini at 7:06 PM  

43 comments:

分享的朋友,在精不在多,分享的幸福就在下一秒..............................

明白 said...
2/08/2010 7:19 AM  

keep sharing with us, please....I will waiting your up date everyday!! Have a nice and relax day!........................................

便當 said...
3/04/2010 7:33 AM  

逛到您的部落格讓我忍不住停下來!期待您的新文章!!........................................

傻眼 said...
3/04/2010 7:34 AM  
玉鳳 said...
3/31/2010 9:03 PM  

nice job! waiting for your new artical. ........................................

明偉誠秋 said...
4/09/2010 6:57 PM  

Lets cross the bridge when we come to it......................................................

童紫勳 said...
4/19/2010 12:40 AM  

Nice Post~!!!. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

逸凡逸凡 said...
4/26/2010 12:48 PM  

與其爭取不可能得到的東西,不如善自珍惜運用自己所擁有的........................................

茂恒 said...
5/01/2010 10:45 PM  
宛蓉 said...
5/07/2010 12:05 AM  
彥鈺璇洋 said...
5/13/2010 12:48 AM  

嘿,你的部落格不錯耶~~只是想跟您問聲好!!........................................

韋于倫成 said...
5/23/2010 3:12 PM  
竹青 said...
5/31/2010 1:27 AM  
HaroldM22 said...
6/05/2010 1:11 AM  

人不能像動物一樣活著,而應該追求知識和美德..................................................

于名于名 said...
6/08/2010 10:36 AM  
峻君 said...
6/12/2010 12:08 AM  
暴枝盈 said...
6/15/2010 9:40 AM  

路過看到好的blog,不推對不起自己.................................................................

胡虹 said...
6/24/2010 6:53 AM  

在莫非定律中有項笨蛋定律:「一個組織中的笨蛋,恆大於等於三分之二。」......................................................................

葉婷 said...
6/27/2010 10:08 PM  

卡爾.桑得柏:「除非先有夢,否則一切皆不成。」共勉!.................................................................                           

江婷 said...
7/01/2010 6:30 AM  

成熟,就是有能力適應生活中的模糊。.................................................................

子帆子帆 said...
7/04/2010 11:14 PM  

人有兩眼一舌,是為了觀察倍於說話的緣故。............................................................

王名仁 said...
7/08/2010 4:36 PM  

不只BLOG內容很棒留言也很精采 XDDDD..................................................................

芸茂芸茂 said...
7/10/2010 9:06 PM  

來給你加油,幫你推一下喔~期待你的下一個更新,謝謝..................................................................

蕙春蕙春 said...
7/12/2010 10:21 PM  

累了嗎?來杯咖啡休息一下吧!............................................................

李冠文 said...
7/15/2010 4:27 AM  

好文章就值得回響,如果可以常常看到您的更新,應該是件很幸福的事情~~............................................................

7/15/2010 4:27 AM  

All roads lead to Rome. 堅持自己所選!..................................................................

7/15/2010 4:27 AM  

People throw stones only at trees with fruit on them.............................................................

楊儀卉 said...
7/17/2010 8:02 PM  

堅持是為著某種目的或目標,而持續不斷朝向既定方向努力的一種意念。..................................................

原秋原秋 said...
7/20/2010 4:04 AM  

不論做什麼事,相信自己,別讓別人的一句話,把你擊倒。..................................................

曾法幸 said...
7/22/2010 10:16 PM  

一棵樹除非在春天開了花,否則難望在秋天結果。..................................................

佳瑩佳瑩 said...
7/25/2010 7:03 PM  

所有的資產,在不被諒解時,都成了負債.................................................................

7/28/2010 8:39 AM  

打擾你實在不好意思, 你的blog很好看呢!!!!..................................................................

7/31/2010 6:13 PM  

快樂,是享受工作過程的結果..................................................

7/31/2010 6:13 PM  

真是太猛了,請受小弟一拜Orz(>O<)......................................................

建邱勳 said...
8/03/2010 9:54 PM  

與人相處不妨多用眼睛說話,多用嘴巴思考. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

凱v胡倫 said...
8/06/2010 8:25 AM  
凱v胡倫 said...
8/09/2010 3:33 AM  

人因夢想而偉大,要堅持自己的理想哦!............................................................

智黃黃黃元 said...
8/11/2010 3:29 PM  
偉曹琬 said...
8/11/2010 3:31 PM  

這不過是滑一跤,並不是死掉而爬不起來了。..................................................

/798 said...
8/14/2010 12:38 AM  
蕾蕾 said...
8/16/2010 1:17 AM  

文章是心情的反應~~祝妳天天寫的都是讓人開心的好文章哦!!...............................................................

瑰潼 said...
8/18/2010 6:40 AM  

你文章很棒的~繼續分享給大家~~~~..................................................

8468 said...
8/20/2010 6:49 PM  
志涛 said...
8/23/2010 2:50 AM  

Post a Comment