Re: Index Across Tables

In article <3d275053$0$232$cc9e4d1f_at_news.dial.pipex.com>, "Niall says...
>
>"Mark Townsend" <markbtownsend_at_attbi.com> wrote in message
>news:B94BA0FD.38A91%markbtownsend_at_attbi.com...
>> in article 3d2603e8$0$8510$cc9e4d1f_at_news.dial.pipex.com, Niall Litchfield
>at
>> niall.litchfield_at_dial.pipex.com wrote on 7/5/02 1:39 PM:
>>
>> > "Donna Yorukoglu" <dym7_at_outreach.psu.edu> wrote in message
>> > news:8ede3df2.0207051122.7529f90d_at_posting.google.com...
>> >> I have one multi-column index (using a concatenated datastore). Works
>> >> great. However, I would like to either create a new index on another
>> >> table and then join the indices or create one index across both
>> >> tables. Are either of these options possible with Oracle 8.1.7? Any
>> >> advice would be greatly appreciated.
>> >
>> > Joining indexes makes no sense. if you index the relevant columns then
>(if
>> > appropriate) oracle will use both indexes to get to the data.
>> >
>>
>> Hmm - not necessarily. A join index (aka a materialized view across two or
>> more tables) can be useful in some circumstances.
>
>Materialized views are great. I don't think of them as indexes though?

I see Mark followed up on the bitmaps -- I'll follow up on this index thing tho.

When you think of say an OLTP system with an EMPLOYEE table that has an EMPNO column -- the first thing you might think to do is create an index on EMP(EMPNO) -- to speed performance. Some of the MAJOR characteristics of the index is that

o it is self maintaining, the application doesn't have to maintain it

o it is transparent to the application. If you drop the index, the application continues to function as normal (maybe slower, maybe faster but the presence of the index or lack thereof does not cause an error)

o it is used automagically when appropriate by the application. You do not have to train the application to use it.

In short, an index is a tool we use to "tune" oltp applications (true, internally we might also use them to help in the enforcement of constraints but we don't always created indexes for that -- we can piggy back on a "good enough" index)

Now, when looking at MV's -- we'll see many of the same characteristics as indexes have. For this very reason, when I'm explaining what they are to a large group of people -- I call them "the indexes of your data warehouse".

o they are self maintaining, the application doesn't have to maintain it (all of the way to an ON COMMIT refresh MV if you want)

o it is transparent to the application. Drop them, create them -- the application will continue to function as before

o it is used automagically when appropriate by the application. You do not have to train the application to use it. This is perhaps the most important point.

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)

>
>> And the new bit mapped
>> join indexes in Oracle9i rock.
>
>care to expand. I haven't looked so this is a purely lazy question.
>
>
>--
>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
>******************************************
>
>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp