Re: Indexing Question.

In article <3h0uec$lb_at_charnel.ecst.CSUChico.EDU>, tvangod_at_ecst.csuchico.edu says...
>
>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
>the case, how is performance when say 2 of the 4 columns are supplied in
>a query?
>
>Our table will contain 1,000,000 + records, and we are trying to determine
>the best way build indexes on that table.
>
>Thx.
>
>Tyler Van Gorder
>Systems Programmer
>Landa Management Systems.
>

-- 
Ian Hariott                     ihariott_at_caritas.ab.ca
The best way to ensure good performance when working with indexes, is by 
putting your indexes and tables in  separate tablespaces, and on separate 
disks.  By doing this, you allow for concurrent reads from multiple devices.
To answer your question, it depends on how you perform your searches.
You should determine your primary search keys, and form separate indexes for 
each. This method will require you to have single column indexes and 
concatenated indexes.  If your search frequently requires you to bring back 
data only from the four key columns, then you should form a concatenated 
index encompassing all four columns; this way, oracle will be able to 
satisfy its search requirement from the index data, thus will not have to 
read from your table data.  But if you search frequently for example on
Deptno, and sometimes on Ename, you should form one index on deptno, and 
another on Ename.

peace.