Antwort: Re: Index build order

From: Martin Klier <>
Date: Wed, 28 Jan 2009 18:18:28 +0100
Message-ID: <>

Hi Yong Huan,

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

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
> 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,
> about the CBO's choice of which index is used when new SQLs are parsed
> the gather stats job is completely finished. It may have great impact
> the first parse fixes the plan for later executions due to bind peeking.
> can't conclude on what the order is in cascaded gathering of index stats
> 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
> DDL (grant, comment, etc.) on the base table.
> Similarly, when you build or rebuild indexes, choose the order sensibly
> 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.
Received on Wed Jan 28 2009 - 11:18:28 CST

Original text of this message