Re: Index build order

Since you're on 10g, RBO is (almost) not relevant. But this is still interesting reading:

"What the RBO rules don't tell you #3" at http://oreilly.com/catalog/orsqltunpr/chapter/excerpt.html

"If multiple indexes can be applied to a WHERE clause, and they all have an equal number of columns specified, only the index created last will be used."

Mark Gurry's book is still classic when you need to deal with an old database or one running with RBO.

Other people have talked about the alphabetic order of index names (or their object ID's?). I think you're concerned about the creation date of the indexes instead. But if the DBA's script to create or rebuild indexes is based on the alphabetic order of index names, then it's the same issue here.

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.

Yong Huang

> does the index build order matter in any way? There's rumor that the order
> in which indexes are built does affect query performance.
> We are using 10gR2, but the "problem" is interesting in any way. I can't
> find any topic on that, could you clarify that, please?
>
> Thanks in advance
> Martin
      

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