Re: Oracle 8.1.7 Concatenate Key

In article <5u3k7.214167$J37.54141995_at_typhoon.southeast.rr.com>, "Buck says...
>
>I didn't think that it would even use the index at all unless the
>leading edge was in the where clause. It would use it if you say
>"Empno >=0", however.
>
>

It will use the index as a "skinnier version" of the base table. If the index has all of the columns needed and the data is deemed large enough, the optimizer will fast full scan the index (read the index not as a data structure a block at a time but rather as if it were a table using large multi-block IO)

For example:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table emp as select * from scott.emp where 1=0;

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create index emp_idx on emp(empno,ename);

Index created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> exec dbms_stats.set_table_stats( user, 'EMP', numrows => 10000000, numblks => 1000000 );

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> set autotrace on explain

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select empno from emp where ename = 'FRED';

no rows selected

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=100000 Bytes=2000000) 1 0 INDEX (FAST FULL SCAN) OF 'EMP_IDX' (NON-UNIQUE) (Cost=1 Card=100000 Bytes=2000000)

So, it won't exactly do a full index range scan, but it will do a FFS of the index.

>Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production
>With the Partitioning option
>JServer Release 8.1.6.0.0 - Production
>
>SQL> create table emp2
> 2 as select * from SCOTT.emp;
>
>Table created.
>
>SQL>
>SQL> create index emp2_idx1 on emp2(empno,ename);
>
>Index created.
>
>SQL>
>SQL> set autotrace on
>SQL>
>SQL> select empno from emp2
> 2 where eName = 'TURNER';
>
> EMPNO
>----------
> 7844
>
>
>Execution Plan
>----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 TABLE ACCESS (FULL) OF 'EMP2'
>
>
>
>
>Statistics
>----------------------------------------------------------
> 0 recursive calls
> 12 db block gets
> 6 consistent gets
> 0 physical reads
> 0 redo size
> 289 bytes sent via SQL*Net to client
> 420 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
>SQL>
>SQL> select empno from emp2
> 2 where empno >= 0
> 3 and ename = 'TURNER';
>
> EMPNO
>----------
> 7844
>
>
>Execution Plan
>----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 INDEX (RANGE SCAN) OF 'EMP2_IDX1' (NON-UNIQUE)
>
>
>
>
>Statistics
>----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 2 consistent gets
> 0 physical reads
> 0 redo size
> 289 bytes sent via SQL*Net to client
> 420 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
>
>
>
>"Howard J. Rogers" <howardjr_at_www.com> wrote in message news:3b8ea281_at_n
>ews.iprimus.com.au...
>> 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
>> >
>> >
>> >
>> >
>>
>>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp