Stats Collection

Database

Stats Collection

Sorry if this has been asked for, but is there a definitive answer to the question:

Is it better to collect stats individually on a table post a large maintenance, or collect stats on all columns in one go (assuming they have been collected before).

So:

COLLECT STATS ON TABLE_1 INDEX (C1);
COLLECT STATS ON TABLE_1 COLUMN C2;
COLLECT STATS ON TABLE_1 COLUMN C3;

or

COLLECT STATS ON TABLE_1;

Thanks.
2 REPLIES
Enthusiast

Re: Stats Collection

Hello Terry

Individual collect stats is required initailly at the time of defining table statistics for the 1st time. With that done, ideally 'COLLECT STATS ON TABLE' is used for refreshing the existing stats on the table (defined initally).
Also, later if you want to collect stats on additional column of a existing table then you can execute an indivivdual COLLECT STATS ON TABLE (NEW_COLUMN) statement.
The following table level stats statement will collect stats on this column in its subsequent run.
Enthusiast

Re: Stats Collection

The old rule was to collect stats on the columns while the table was empty; collect stats on the table when it is populated and it will repopulate them all at once (in a single pass).

Later releases (V2R5 on - I think) dont work like this. If you collect stats on the table, and there are stats on more than one column (or column combination), result is a pass for every column combination. The explanation I got was that there is much more frequency information in current releases and it is too complicated to collate this for more than one column set in the same pass. If you set up a table with stats on three columns and then do an explain on a collect stats for the table, you can clearly see the three passes

There is a very real danger that if you collect stats on one column (or column combination) and you do not refresh those, you will get a bad query strategy. So collect them on the table after table refreshes.