Antwort: Re: Index build order

thanks a lot for the explanation, I will have to do tests on that, the parse-while-rebuild stuff sounds very feasible. I think I will create a small procedure commenting all tables in the DB to force a general re-parse after gather_schema_stats. That's a great hint!

Last but not least, thanks a lot to all the other posters as well, all of your opinions will help me in furture!

--
Mit freundlichem Gruß


Martin Klier
------------------------------------------------------------------------------

Klug GmbH integrierte Systeme
Lindenweg 13, D-92552 Teunz
Tel.:  +49 9671/9216-245
Fax.: +49 9671/9216-112
mailto: martin.klier_at_klug-is.de
www.klug-is.de
------------------------------------------------------------------------------

Geschäftsführer: Johann Klug, Roman Sorgenfrei
Sitz der Gesellschaft: Teunz, USt-ID-Nr. DE175481608,
HRB Nr. 2037, Amtsgericht Amberg



> Now, let's forget about RBO and back to modern versions. You may consider

the


> timing issue with dbms_stats.gather_(schema|table)_stats

> (...,cascade=>true). If a big

> table has multiple big indexes, in a moderately busy OLTP environment,

think


> about the CBO's choice of which index is used when new SQLs are parsed

before


> the gather stats job is completely finished. It may have great impact

because


> the first parse fixes the plan for later executions due to bind peeking.

I


> can't conclude on what the order is in cascaded gathering of index stats

(if


> not random, has to be one of name, object_id, data_object_id, or

> creation time,

> but I can't reproduce). With this in mind, you may as well set

> no_invalidate to

> true and after the gather stats job is done, force reparsing with a

harmless


> DDL (grant, comment, etc.) on the base table.

>

> Similarly, when you build or rebuild indexes, choose the order sensibly

while


> new SQLs are parsed. Again, this has nothing to do with RBO's Rule

> #3 in Mark's

> summary; it's just what CBO sees at the moment of parsing. If

> needed, turn off

> _optimizer_compute_index_stats so you can separate the two tasks,

> (re)building

> indexes and providing stats for CBO.


--
http://www.freelists.org/webpage/oracle-l