Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Concatenated Index

Re: Concatenated Index

From: Steve Johnson <johnst_at_ncs.com>
Date: Wed, 5 Jun 2002 08:40:28 -0500
Message-ID: <ufs56ega4c7b4f@corp.supernews.com>


Thanks for the answers...

"Richard Spee" <rhpspee_at_wxs.nl> wrote in message news:adkhrg$20o$1_at_reader06.wxs.nl...
> 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 - 08:40:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US