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....
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 .
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.
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.