| 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: Concatenated Index
The following script proves that (in this case) the 2nd and 3rd column are used IF column statistics
are available
I have created a table Test
(col1 varchar2(10)
,col2 number ,col3 number ,col4 number)
In the table Test are 4097 records
col4 => is empty col3 => 4096 records have value 1, 1 record has value 2 col2 => all records have value 1 col1 => 'AB'||rownum
There are 111 records in which col1 has a value that starts with AB6 There is an index on col1, col2 and col3
SQL> execute dbms_stats.delete_table_stats(ownname=>'RICHARD',tabname=>'TEST');
PL/SQL procedure successfully completed.
SQL> select col1,col2,col3 from test where col1 like 'AB6%' and col2=1 and col3=2;
COL1 COL2 COL3 ------------------------------ ---------- ---------- AB65 1 2
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 INDEX (RANGE SCAN) OF 'ITEST1' (NON-UNIQUE) SQL> execute dbms_stats.gather_table_stats(ownname=>'RICHARD'
,tabname=>'TEST');
PL/SQL procedure successfully completed.
SQL> select col1,col2,col3 from test where col1 like 'AB6%' and col2=1 and col3=2;
COL1 COL2 COL3 ------------------------------ ---------- ---------- AB65 1 2
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=250 Bytes=3000) 1 0 INDEX (RANGE SCAN) OF 'ITEST1' (NON-UNIQUE) (Cost=5 Card=250 Bytes=3000)
CARDINALITY=250 SQL> execute dbms_stats.gather_table_stats(ownname=>'RICHARD'
,tabname=>'TEST'
,method_opt=>'FOR ALL COLUMNS');
PL/SQL procedure successfully completed.
SQL> select col1,col2,col3 from test where col1 like 'AB6%' and col2=1 and col3=2;
COL1 COL2 COL3 ------------------------------ ---------- ---------- AB65 1 2
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=10) 1 0 INDEX (RANGE SCAN) OF 'ITEST1' (NON-UNIQUE) (Cost=2 Card=1 Bytes=10)
CARDINALITY=1
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
news:ufqf9kgbo7hmb3_at_corp.supernews.com...
>
> "Steve Johnson" <johnst_at_ncs.com> wrote in message
> news:ufq8ipkavuqn63_at_corp.supernews.com...
> > I don't think I asked the question very good.
> >
> > I can see the explain plan fine and have verified that it's using the
> index
> > as I want.
> >
> > My question was will the 2nd and 3rd columns of the index be used or just
> > the leading edge. The leading edge is the most restrictive but it has a
> > like clause on it. I don't know if the like clause will stop the rest of
> > the index from being used.
> >
> > Thanks...
> >
> > "Richard Spee" <rhpspee_at_wxs.nl> wrote in message
> > news:adj7dc$i39$1_at_reader05.wxs.nl...
> > > log into sqlplus
> > > Run the script ora8i/rdbms/admin/utlxplan.sql
> > > (creates the plan_table)
> > > set autotrace on
> > > run your query and .....
> > > start tuning
> > >
> > > "Steve Johnson" <johnst_at_ncs.com> wrote in message
> > news:ufq5u6am03kmcb_at_corp.supernews.com...
> > > > If I have a concatenated index on tablea columns (a, b, c) and a
> query:
> > > >
> > > > SELECT count(*)
> > > > FROM tablea,
> > > > tableb
> > > > WHERE tablea.a like 'AB%'
> > > > AND tablea.b=tableb.b
> > > > AND tablea.c=tableb.c
> > > >
> > > > Will columns b and c of the index on tablea be used in the query? It
> > uses
> > > > the index like I wanted but I don't get the speed I think I should
> which
> > > > leads me to believe that it doesn't.
> > > >
> > > > Any help would be appreciated.
> > > >
> > > > Thanks...
> > > > Steve
> > > >
> > > >
> > >
> > >
> >
> >
>
> The 2nd and 3rd columns will NOT be used.
> You can verify this by looking at the cardinality for each step provided
> with
> set autotrace on explain stat
> The cardinality will match the cardinality of the leading column.
>
> Hth
>
>
> --
> Sybrand Bakker
> Senior Oracle DBA
>
> to reply remove '-verwijderdit' from my e-mail address
>
>
>
Received on Wed Jun 05 2002 - 03:17:11 CDT
 |
 |