Re: Need oracular advice on indexes.

In general it is a good idea to have at least one index that is minimal. That is, it contains the minimum amount of information needed for uniqueness.
You can have several indexes to support different applications. Oracle can use partial indexes. If an index includes 3 columns a,b,c then Oracle can use (a) or (a,b) or (a,b,c). If you have where clauses for b and/or c but not for a, the index cannot be used.

-- 
David J Roth
(My own opinions)

Bruce Dodds <bruce_at_juniper.com> wrote in article
<3275488C.61BD_at_juniper.com>...


> I'm writing the client side of a C/S application that connects to Oracle


> 7.1 tables.  One of the Oracle tables is a dog, and not for my

> application alone. 

> 

> This table has 200,000 rows and a 104 byte, six field primary key.  It

> holds active and inactive records.  A third of the records are active,

> but they are responsible for 95% of the system activity, and all of my

> application's activity.  The table is quite volatile.

> 

> The first and sixth of the fields in the primary key, totalling nine

> bytes, could be used to uniquely identify active records.  This first

> field is the field used for almost all of the joins against the table. 

> 

> Would we be better off adding an index based on these two fields?  If

> so, how much better off?  

> 

> I'm not an Oracle expert, so I would like some advice before making

> suggestions.

> 

> Thanks,

> 

> Bruce Dodds

>