Re: Index Across Tables

"Thomas Kyte" <tkyte_at_oracle.com> wrote in message news:ag7lhk018ui_at_drn.newsguy.com...
> So, as far an analogies go, there are tons of things in common between
MV's and
> indexes. Both are used to tune. Both are maintained by the system, not
the
> application. Both will be used automagically when appropriate (when the
> optimizer deems it is appropriate ;). Their existence or lack thereof
won't
> affect an application (from an error standpoint -- performance, that'll be
> something totally different)

Hmmm. I do see your argument but I can't really buy it. My mental image of an index is that of the traditional list at the back of a book (i.e it contains key information and a locator for that information in a larger set of data). MV's don't fit with this. My image of them is of pre computed summary tables. I also can't think of any circumstances in which you would do what you *traditionally* do with an index which is visit the materialised view and then go look in the source table for the actual data.

There *is* a storage structure that sort of does what the original poster asked and that is the cluster, and associated cluster index. It'd be interesting to know if no-one has suggested this so far because it was the weekend or if its because no-one actually uses them.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************