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

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

Anonymous said...
4/09/2010 6:57 PM  

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

Anonymous said...
4/19/2010 12:40 AM  

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

Anonymous said...
4/26/2010 12:48 PM  

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

Anonymous said...
5/01/2010 10:45 PM  
Anonymous said...
5/07/2010 12:05 AM  
Anonymous said...
5/13/2010 12:48 AM  

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

Anonymous said...
5/23/2010 3:12 PM  
Anonymous said...
5/31/2010 1:27 AM  
Anonymous said...
6/05/2010 1:11 AM  

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

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

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

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

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

Anonymous said...
6/27/2010 10:08 PM  

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

Anonymous said...
7/01/2010 6:30 AM  

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

Anonymous said...
7/04/2010 11:14 PM  

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

Anonymous said...
7/08/2010 4:36 PM  

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

Anonymous said...
7/10/2010 9:06 PM  

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

Anonymous said...
7/12/2010 10:21 PM  

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

Anonymous said...
7/15/2010 4:27 AM  

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

Anonymous said...
7/15/2010 4:27 AM  

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

Anonymous said...
7/15/2010 4:27 AM  

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

Anonymous said...
7/17/2010 8:02 PM  

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

Anonymous said...
7/20/2010 4:04 AM  

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

Anonymous said...
7/22/2010 10:16 PM  

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

Anonymous said...
7/25/2010 7:03 PM  

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

Anonymous said...
7/28/2010 8:39 AM  

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

Anonymous said...
7/31/2010 6:13 PM  

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

Anonymous said...
7/31/2010 6:13 PM  

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

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

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

Anonymous said...
8/06/2010 8:25 AM  
Anonymous said...
8/09/2010 3:33 AM  

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

Anonymous said...
8/11/2010 3:29 PM  
Anonymous said...
8/11/2010 3:31 PM  

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

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

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

Anonymous said...
8/18/2010 6:40 AM  

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

Anonymous said...
8/20/2010 6:49 PM  
Anonymous said...
8/23/2010 2:50 AM  

Post a Comment