Re: Index build order
- From: Yong Huang <yong321@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 28 Jan 2009 08:42:03 -0800 (PST)
Martin,
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
