|
|
|
Junior Member
 
Group: Forum Members
Last Login: 3/13/2008 3:39:40 AM
Posts: 12,
Visits: 2
|
|
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.
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: 10/12/2006 2:51:00 PM
Posts: 68,
Visits: 1
|
|
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 RDMBS
4. 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
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 3/13/2008 3:39:40 AM
Posts: 12,
Visits: 2
|
|
Thanks a lot!
will get back in case of any other queries.
Regards,
TS.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 7/5/2009 2:58:22 PM
Posts: 134,
Visits: 13
|
|
This is a comment on the 4th answer
Actually 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
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 5/2/2008 2:28:55 AM
Posts: 15,
Visits: 2
|
|
|
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.
|
|
|
|