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
> individual indexes I can drop any of the six indexes without effecting
> the other indexes (that's not such a big deal). With individual indexes
> you also have more flexibility in adjusting storage parameters.
>
> Just curious what other people are doing or have to say.

If your table is there for 'adhoc querries' where you do not know what fields will be in the WHERE clause or what order they will be in then you are probably better off with many single field indexes. You may want to check how unique your field contents are as it's really pointless indexing a field which has only 2 or 3 values. Anyway, index them and let the cost based optimiser sort it out for you (once they get it working properly).

If you are writing a report that gets run regularly or an interactive user interface then build your indexes specificly to make this run fast. Using your example above you may need to create 2 indexes one on A-B-C and another on just B-C (actually, throw in a B-C-A and you might be able to drop the A-B-C index).

I would be interested in hearing about any other comments on index usage you receive.

-- 

Bruce...        pihlab_at_cbr.hhcs.gov.au

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Human Services and Health            *
* Canberra, ACT, Australia                        (W) 06-289-7056 *
*=================================================================*
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************

"The more complex the argument gets, the easier it is to refute."
"Killing is wrong!"  -- Trent 'The Uncatchable' Castanaveras