Re: Indexing Question.

DavidJRoth (davidjroth_at_aol.com) wrote:
: From: tvangod_at_ecst.csuchico.edu (Tyler Van Gorder)
: >Date: 4 Feb 1995 22:17:48 GMT
: >Message-ID: <3h0uec$lb_at_charnel.ecst.CSUChico.EDU>
 

: >Hi, I had a question about indexes in Oracle:
 

: >If you have a 4 part primary key do you need to define an index on each
: >column or can you define an index on the entire key. If the second is
 t>he case, how is performance when say 2 of the 4 columns are supplied in
: >a query?

... text deleted ...

: As far as queries using the index;
:
: If the columns in the index are A,B,C,D (in that order),
:
: queies with where clauses for
:
: A or
: A,B or
: A,B,C or
: A,B,C,D
:
: can use the index.
:
: David Roth

SO, if your clause(s) only use:

B or

B,C  or
B,C,D or
B,D

or
C or
C,D

or
D

, then the index will not be used. If you need to improve performance of queries using any of these potential clauses, build separate indexes.

Problems:

        o Additional indexes need disk storage.

	o Update/Insert/Delete will possibly take longer
          because of index maintenance.

This is basic relational database concept stuff, look in a DBA (text?) book or the Oracle Admin manuals?

Good luck to my fellow CSU folks!

EP
Data Janitor
Student Services
CSU, Sacapotatoes Received on Mon Feb 06 1995 - 20:29:20 CET