update stats on a temp table with a fresh index?

[WiccaChic]

Hello. I have a process that drops and creates a table and then loads data from a text file. After its done, it creates a indexs.

My question is, will updating statistics for each of the columns that head an index help? Keeping in mind that at this point, the table is brand new and the index is brand new.

Sorry, I am no infomix jedi like my father before me!

 

[vpshriyan]

Hi,

No, updating statistics for each of the columns that head an index will not help for the temporary table, since both the data and index are new or fresh. Being fugacious in nature, the temp table statistics are not gathered at systables and sysdistrib catalog (metadata)tables.

For non-permanent tables the optimizer bypasses the metadata objects, and considers existing indexes, if any, for the calculation of an efficient and least cost method to get into the data.

The question would be then, why update statistics syntax is permitted on a temp table?

In my view, (I may be wrong), when the existing data in a temp table being manipulated (insert,update,delete) to an exceedingly great extent or degree, we need to update the statistics, which supposed to push up performance for all the subsequent SQL queies on that temp table, otherwise we may have to drop and re-create the indexes, to give the same effect to the optimizer.

Regards,
Shriyan