All,
We regularly use the function “Create Script”.
When generating scripts for indexes, we noticed that the “Compute Statistics” clause does not get generated.
For performance reasons we would like to see this clause included.
Would it be possible to retrieve this from the catalog/directory, or would it take an option that we might turn on?
John,
thanks a bunch! We’ve missed that one.
You’re welcome, Abe. You too.
John,
We’ve just given it a try. It works fine for a stand-alone script for creating an index.
However, we normally generate the indexes with the table definition.
In this case the option appears to have no effect at all 
Are we missing something obvious?
Hm, no, there’s not a way to add it there - but if the script creates the table and the index, then the table is going to be empty since it was just created. Do you want to compute statistics even on an index of an empty table?
John,
I think you’re right. I’ve forwarded you reply to the colleagues who actually work with it. I expect to have an answer by Monday.
John,
Apologies for the delay in answering your last remark.
I’ve discussed the options with my colleagues. We think it is beneficial to have compute statistics even when the table is still empty.
That way, Oracle has statistics. If and when the table is filled with more than a handful of data rows, it remains the programmer’s (or DBA’s)
responsibility to make sure stats are updated appropriately.
Otherwise, we’d have to include a call to gather_stats in each create script,
which would beat the purpose of having them generated in the first place…
Hi,
Which Oracle version are you using? (On 11 and maybe on 10 create index explicitly generate statistic for that index…not sure for lower versions).
If you are dealing with partition tables and it’s indexes as well, then you might find GLOBAL_STATS problem (all the time “NO”) but this can be changed only by gather table stat on global level.
Damir,
most of our systems are on 10 or 11. One very important system, however, is still at 9.
I would think changes to better support ORacle 9 are out of scope for Dell.
So, we’ll solve this ourselves. Wuold you please close this case?
FYI, these forum threads aren’t really “cases” that get opened or closed. They are just informal conversations. If you want that kind of formality, go through support.
John,
Thanks for explaining the difference. And no, I’m not interested in formalities and the like.
I just picked the wrong word. Sorry about that.
Kind regards,
