| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle 8.1.7 Concatenate Key
Ah, my example works as you describe if I had analyzed it. You'd think
I'd have learned, given my question about a trigger to analyze a
table.
SQL> analyze table emp2 compute statistics;
Table analyzed.
SQL> SQL> SQL> select empno from emp2
EMPNO
7844
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=8) 1 0 INDEX (FULL SCAN) OF 'EMP2_IDX1' (NON-UNIQUE) (Cost=1 Card
=1 Bytes=8)
"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message
news:9mqnth01m2l_at_drn.newsguy.com...
> 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_at_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
>
Received on Sat Sep 01 2001 - 09:11:41 CDT
 |
 |