Re: Newbie question on indexes

There's a wealth of index management information in the RevealNet Oracle Administration Knowledge Base. You can download a copy and try it out at http://www.revealnet.com

Bur here's some general info:

Determining Index Use Guidelines

• Indexes speed the search for queries when approximately 10 to 15% of the table or less is being retrieved.
• For proper table design each table must have a unique identifier. The primary key and unique key constraints automatically generate the required indexes.
• Put the column expected to be accessed most often first. The leading portion of the index is used to speed queries. The way columns are indexed will effect their efficiency. The order in which columns are specified should reflect the way a SELECT will retrieve them. A composite index can be created on up to 16 columns.

*If a column is frequently used to sort selected data (used in a WHERE clause) it should be indexed.

• All unique columns should be indexed.
• All foreign keys should be indexed to avoid excessive lock contention. If a column is a foreign key and there is no index for it, excessive table locking will occur in order to concur with requirements for data integrity.

Best wishes,

Cam White
RevealNet
(202) 234-8557

Steve18888 <steve18888_at_aol.com> wrote in article <19971018165600.MAA22626_at_ladder02.news.aol.com>...
> What exactly do indexes do and why do I want to use them? From my reading
 I
> gather that it improves query performances somehow. Digging through my
 manuals
> tells me how to create indexes, but can't find anything on the strategy
 behind
> it. Also, if I create an index, are there any other settings that I need
 to
> make, or do the indexes get used automatically somehow? Thanks - SS
>
Received on Mon Oct 20 1997 - 00:00:00 CDT