All Forums Database
TS 12 posts Joined 02/06
10 May 2006

Hi,I have few queries realted to COLLECT STATISTICS:1) Do we need STATISTICS for small table?2) After how many records or percentage of changes before we should run new STATS or refresh STATS?3) Which & how many columns are the best choice for collecting STATS?4) Is it possible to run STATS on a portion of a table?Thanks and Regards,TS.

TDUser-781 35 posts Joined 12/04
10 May 2006

Hi TS,I would answer like this to your questions:1. when writing about small tables you are thinking about lookup tables, aren't you? if so, thinking about a 3NF db, I would collect stats on the pk of these tables. Anyway, you have to check the explain of the most common queries in order to run the best way.2. the manual recommends recollecting stats whenever 5% - 10% of the rows have changed. If they are not too resource-consuming, I would collect them after every batch job (parhaps the most important of them)3. you have to run explain of your queries and trying tuning it. or you can use "diagnostic helpstats on for session" command if you desire hints for the RDMBS4. once you have collected stats on one table, you may refresh only some stats on your table, by using the "collect statistics on COLUMN " option.Hope this helps,Bye,TDUser

TS 12 posts Joined 02/06
10 May 2006

Thanks a lot!will get back in case of any other queries.Regards,TS.

BBR2 96 posts Joined 12/04
14 May 2006

This is a comment on the 4th answerActually you can collect statistics on a selected % of tables rows.You may use "USING SAMPLE" qualifier for collecting statistics on % of table rows.There are some restrictions that apply.You may want to look up SQL Reference Guide: DDL for more information.Vinay

Phani 15 posts Joined 07/04
19 Jun 2006

I recommend collecting stats on at least on Primary Index column. In the absence of stats, we may encounter soome bad plans. At times, a larg table may be assumed smaller to a small table. This leads to a poor plan.

You must sign in to leave a comment.