Re: Oracle 8.1.7 Concatenate Key

A concatenated key is one that comprises two or more columns. Hence: create index on emp(empno,ename) results in a concatenated key.

Useful? Well, it depends. Do I often query using that combination? Even if I query by only one or other at different times, there may be benefit in having a single index on both columns, rather than two on each one separately. If I have a primary key on a table, I get an index created for me whether I want it or not -how about combining that index with one on a column I actually query against extremely regularly?

Just be aware that the order of concatenation is signficant. In my earlier example, a query 'select * from emp where empno=999' will be able to use index access easily, because empno is the leading column. But a 'select * from emp where ename='Fred'' would require a complete index range scan, since not all our Freds will be sorted together, and could hence lie anywhere within the index.

So the index still gets used, but not necessarily in the way you (or your application) would prefer.

Regards
HJR "Werner Faßbender" <LUP_at_edv3.de> wrote in message news:3B8A5B77.6AFF2A2E_at_edv3.de...
> Hi,
>
> Oracle DB Release 8.1.7
>
> what is a concatenate Key ?
>
> what are the beniftis using such a key where are the diffrents?
>
> when is it usefull?
>
> with regards
> Werner Fassbender
>
>
>
>
Received on Thu Aug 30 2001 - 15:35:45 CDT