All Forums UDA
gururaj 13 posts Joined 02/07
28 Mar 2008
Collect Stats difference

Is there any difference in performance when -(1) We collect stats on Column(2) We collect stats on IndexSay table...COLLECT STATS ON INDEX A (ACCNO);COLLECT STATS IN COLUMN A (ACCNO);A is a table with UPI as ACCNO.Thanks

Fred 1096 posts Joined 08/04
29 Mar 2008

Other than syntax, there is no difference between collecting stats on an index or on the same column(s). The same is true of the "CREATE INDEX form" versus "Legacy form" of COLLECT STATS statement.The actual collection process (which you can EXPLAIN), the data stored in the dictionary tables, and the subsequent use of the stats for query optimization are all the same.

carrie 21 posts Joined 04/08
28 Apr 2008

There is a small difference in how the statistics collection process will read the data, depending on whether the column is indexed or not. If there is a NUSI defined on the column, the statistics collection process will scan the index subtables, instead of the base table. Depending on the number of distinct values in the NUSI, this could contribute to a reduced time to collect the statistics because fewer rows and fewer data blocks will need to be read.

You must sign in to leave a comment.