| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: index help
A copy of this was sent to "Matt Larson" <mattlarson_at_usa.net>
(if that email address didn't require changing)
On Tue, 22 Dec 1998 09:38:42 -0700, you wrote:
>I'm working with an application that needs help with indexing. Does anyone
>know of a good whitepaper or book that deals with indexing in Oracle? For
>example, we have some indexes that look like this.
>
>Table: col1, col2, col3, col4
>
>Index1: col1
>Index2: col1, col2
>
>It seems like the first index is not needed since all queries that would use
>the first index could also use the second index. Would it be correct to
>recommend eliminating all indexes that are subsets of another index
>(assuming they have the same leading columns)?
>
not necessarily (but usually).
What if:
Index1: was a unique index on col1.
Index2: was an index intended to make things go faster (avoids a table acess by rowid for queries that go after col1 and col2 and nothing else)...
dropping index1 would change the rules in the database -- index1 is the primary key constraint perhaps.
In this example, dropping index2 would be safe and only affect performance -- dropping index1 would be a disaster.
You'll have to look at why each index is there -- there might be a reason and if so, it'll have to stay....
>I realize that indexes with the same columns that do not start out the same
>way cannot be eliminated. For example, if the same table had these two
>indexes. Neither index could necessarily be eliminated because the leading
>columns are not the same.
>
>Index3: col1, col2, col3
>Index4: col2, col3
>
>Please email answers to mattlarson_at_usa.net
>
>Thanks in advance,
>Matt
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Dec 22 1998 - 14:29:44 CST
 |
 |