COLLECT STATISTICS

Database
TS
Enthusiast

COLLECT STATISTICS

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.
4 REPLIES
Enthusiast

Re: COLLECT STATISTICS

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
TS
Enthusiast

Re: COLLECT STATISTICS

Thanks a lot!

will get back in case of any other queries.

Regards,
TS.
Enthusiast

Re: COLLECT STATISTICS

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
Enthusiast

Re: COLLECT STATISTICS

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.