Re: Oracle 8.1.7 Concatenate Key

From: Buck Turgidson <noway_at_nohow.com>
Date: Sat, 01 Sep 2001 11:18:25 GMT
Message-ID: <5u3k7.214167$J37.54141995@typhoon.southeast.rr.com>

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.

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
> >
> >
> >
> >
>
>
Received on Sat Sep 01 2001 - 06:18:25 CDT