Re: Individual Indexes v.s. One "combinded" Index

In article <CxD08t.957_at_netnews.whoi.edu> dhiltz_at_whsun2.whoi.edu (David Hiltz) writes:
> I just read in the "Oracle 7 Server - SQL Language Reference Manual",
> pg. 4-162 ("Index Columns") that if you have a "combined index" of
> column A,B and C on a table and you reference column B; C; or B,C - the
> index is not used. You would have to reference from left to right (ie.
> A; A,B; or A,B,C).
>
> I created six individual indexes on a table and the indexes used a total
> of 7.5Megs. One index that contained the six columns used 2.3Megs.
>
> So its a trade off of disk space v.s. use of index. Also, with

My understanding of indexing is that if you want to access something based on BOTH A and B in your example above, then you need a composite index. In other words, if your where clause has something like: "where A = 5 and B = 6," then individual indexes will not work; a partial tablescan will be performed.

Run an explain plan and see for yerself.

> Thanks.

Anytime,
Marty Received on Tue Oct 11 1994 - 06:32:20 CET