Re: Multiple indexes on a column

In article <2k70e4$p5s_at_blkbox.blkbox.com>, Brett May <bmay_at_blkbox.COM> wrote:
>We have a large table which has a primary key of 5 columns (say, a, b, c,
>d and e). When I define a primary key constraint over the table, my
>understanding is that a single concatenated index is created over all five
>columns. Is this correct?

Try this for the syntax for an out of line constraint... This will create a unique index on the 3 columns and automatically make them not null. Note I have specified the tablespace and storage using the "USING INDEX" clause.

CREATE TABLE REF_CALENDAR

	<stuff deleted>
	CONSTRAINT ref_calendar_pk PRIMARY KEY 
		( cal_ty_cd, cal_rng_cd, cal_strt_dt )
      		USING INDEX TABLESPACE ref_index
		STORAGE ( initial 50K next 20K pctincrease 0 )
		PCTFREE 0)
        TABLESPACE ref_data
        STORAGE ( initial 100K next 50K pctincrease 0 )
	PCTFREE 0

If so, is there any benefit to creating additional
>individual indexes over the same columns. For example, lets say I knew the
>users would be often doing something like:
>SELECT whatever FROM my_Table
>WHERE a=something AND b=something_else;
 

In the example you give ... NO.
The reason is that Oracle can use the leading columns on the index. If you had said "WHERE B= and C=", the index would not be used. If you had said "WHERE A= and C=", the index would be used to find the values for column "A".

>If I created another additional index over a and b alone, would it buy me
>anything?

YES! - Another disk and no performance improvement.

Or would the query get as much benefit from the original index
>of which a and b are only a part?

Correct.

Also, ensure the index is ordered from MOST SELECTIVE to LEAST SELECTIVE. If your column "A" only has 1 value, it is useless in an index and will give you poor performance. If columns "E" has 500,000 unique values, put it first in the index if you can. Remember though, that all queries must then at least specify column "E" in the predicate.

P.S. We're not related!

-- 
--------------------------------------*---------------------------------------
N. Andrew May.                        | Database Administrator
Coles-Myer Ltd                        | These are my humble opinions alone.
53 Hoddle Street, Collingwood,        | Tel: +61 3 483 7389.  Fax: 483 7381