collect stats prob

Database
Enthusiast

collect stats prob

Hi,

Is there any performance improvement if statistics is collected first and then the table is populated?
i.e
create table tbl_a
(col1 int,col2 char(4) ....);
collect stats on a column (col1);
THEN
insert into tbl_a
sel * from tbl_b....

if yes please help me out ...

6 REPLIES
Enthusiast

Re: collect stats prob

Hi goutham,
I strongly suggest if your table is heavily used.perform a collect stats atleast @ the index after creating the table and then do a insert select after that perform a collect stats on heavily used columns +index .
this will surely improve your perfomance .
Enthusiast

Re: collect stats prob

hi ,
But why is it reqrd to collect the stats before loading into the table?

Re: collect stats prob

Hi Goutham,

Normally stats are recommended when your data changes by 10% or more. So after a create table statement ,stats may not be required. But after you insert data into the table its strongly recommended to collect stats. Another important criteria is that the stats should always be up-to-date, which means you should collect stats as and when your data changes (10% threshold). No stats being defined (leading to random AMP sampling) is always better than stale/obsolete stats. So define stats only when you plan to keep them up-to-date.
Enthusiast

Re: collect stats prob

Got it .. Thanks a lot
Enthusiast

Re: collect stats prob

Collecting statistics on empty table would populate the dictionary tables with the statistics information whatever is available at the time of collection.However, you might see high confidence steps in explains since the statistics are available for the columns.

Parser would still generate an execution plan based on the available statistics.However if you donot refresh/re-collect the statistics once the data is populated into the table, you might end up with a bad execution plan which would impact your work.
Enthusiast

Re: collect stats prob

Thanks a lot Issac.

You made things more clear.