SQL & PL/SQL

SCENARIO

• We have an intensive load process (100s of tables involved, multiple hours of execution). We are gathering schema stats only on stale objects during execution in order to minimize load on the system while stats are gathering at intervals (see the below command).

• There exists a table with an index on column X
• When the table is inserted into during a load process column X is null and the index is empty.
• When the table becomes stale (and column X is remains null) , stats get generated using the gather_schema_stats package with the ‘GATHER STALE’ and 'CASCADE=TRUE options.

• Afterward all records for the table are loaded and the gathering of statistics has occurred, column X happens to get updated with values so then the index gets inserted into. No more records are inserted into the table.


QUESTION

After the load no more records are getting inserted and the table never gets flagged as stale so new stats are not generated (no surprise there). However, what is the impact on the index that now has rows in it (due to updates that occurred after the table loaded was loaded)? When stats were run on the table (with cased=true) the index was empty. Do I need to run a separate gather_stats job on the index or will it somehow inherit the optimizer stats values associated with the fully populated table?_

Here’s the command I'm using:
exec dbms_stats.gather_schema_stats(ownname=>'UICONNECT',-
options=>'GATHER STALE',-
estimate_percent =>dbms_stats.auto_sample_size,-
method_opt=>'for all columns size auto',-
degree=>dbms_stats.auto_degree,-
cascade=>TRUE);

This issue is forcing me to understand the optimizer (viz a viz the statistics) more than I had hoped would be necessasry! But alas....the developers are asking me so I need to respond (appropriately)!.

Thanks in advance for any insight into this issue (or references to good material...it's hard to google this one).

Edited by: TonyG on Jul 14, 2010 2:10 PM

Edited by: TonyG on Jul 14, 2010 2:11 PM