Re: Compound Indexes...

Perhaps you should refresh your read of the Oracle Tuing manual. Indexes are there to speed access. Concatenated indexes are often created because by themselves the individual columns are not selective. By concatenating columns in an index, you can increase selectivity. Further, if all the columns needed to satisfy a query are in an index, this can really speed access.

You should have leading columns in your concatenated indexes which are also the most frequently found in the where clause of queries. If possible, order the columns in the index so the most selective column is first, the next most second, and so on.

Oracle 8i will, under certain circumstances, combine indexes to satisfy a query, rather than scan the table.

You need to look at what queries are run, and how often, to determine which index to make and keep. How expensive it is to maintain the indexes for DML vs. selects depends, obviously, on how often updates which affect an index, or inserts/deletes are made on the table vs. selects.

• Original Message ----- From: Rocky Welch <rockyw_99_at_yahoo.com> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Sunday, August 06, 2000 1:08 PM Subject: Compound Indexes...

> Hi All!
> Hope your weekend is good. I have a large table (over a million rows) that
> has multiple compound indexes. These indexes contain 2 common columns.
> Should i put those 2 cloumns in their own index? I think this would
> increase performance on inserts without hurting reads. Any thoughts?
>
> TIA,
>
> =====
> Rocky Welch
> Oracle DBA Consultant
>
> rockyw_99_at_yahoo.com
>
> __________________________________________________
> Do You Yahoo!?
> Kick off your party with Yahoo! Invites.
> http://invites.yahoo.com/
> --
> Author: Rocky Welch
> INET: rockyw_99_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Sun Aug 06 2000 - 15:17:53 CDT