Re: how to use function-based indexes?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 12 Dec 2001 05:46:04 -0800
Message-ID: <9v7n2s022d8@drn.newsguy.com>

In article <3C172BD9.547FF1C2_at_crpa.it>, Cristian says...
>
>Niall Litchfield wrote:
>
>> how many rows does your query return?
>
>5 rows from 10904
>
>> how many blocks in the table?
>
>64
>
>> have you analyzed the table (sorry can't find if you've posted this inf=
>o or
>> not).
>
>Yes, I did:
>
>SQL> analyze table acquirenti compute statistics ;
>
>Table analyzed.
>
>> if this is performance critical - I read your posts as just investigati=
>on -
>> you could of course hint the statement.
>
>I tried, with the same result:
>
>SQL> explain plan for
> 2 select * from acquirenti /*+ INDEX(acquirenti xie1acquirenti) */
> 3 where UPPER(ACQUIRENTE_RAG_SOC) like 'VERO%';
>
>Explained.
>
>SQL> select * from plan_view ;
>
>OPERATION OPTIONS OBJECT_NAME POSITION
>-------------------- -------------------- -------------------- --------
>SELECT STATEMENT 10
> TABLE ACCESS FULL ACQUIRENTI 1
>

read the posting at:

http://osi.oracle.com/~tkyte/article1/index.html

it'll help you get your example going.

your index hint isn't a hint, its in the wrong place.

 select /*+ INDEX(acquirenti xie1acquirenti) */ *    from acquirenti
  where UPPER(ACQUIRENTE_RAG_SOC) like 'VERO%';

using ALL_ROWS will cause the CBO to skip indexes EASILY. ALL_ROWS will favor full scans over index accesses.

Here is a full example showing this in action (with no hints, nothing). I would urge you to use autotrace (that link above shows how to set it up). Its much easier and less error prone then doing the explain plan yourself.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table acquirenti ( ACQUIRENTE_RAG_SOC varchar2(30),

2                                                    data              

char(500) default 'x' );

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into acquirenti

  2 select object_name, 'x' from all_objects where rownum < 10900;

10899 rows created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into acquirenti values ( 'vero1', 'x' );

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into acquirenti values ( 'vero2', 'x' );

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into acquirenti values ( 'vero3', 'x' );

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into acquirenti values ( 'vero4', 'x' );

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into acquirenti values ( 'vero5', 'x' );

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create index xie1acquirenti on

acquirenti(upper(ACQUIRENTE_RAG_SOC));

Index created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> analyze table acquirenti compute statistics;

Table analyzed.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> alter session set QUERY_REWRITE_ENABLED=TRUE;

Session altered.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> alter session set QUERY_REWRITE_INTEGRITY=TRUSTED; Session altered.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> set autotrace on
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select ACQUIRENTE_RAG_SOC, substr(data,1,20)

from acquirenti
  2 where upper(ACQUIRENTE_RAG_SOC) like 'VERO%';

ACQUIRENTE_RAG_SOC SUBSTR(DATA,1,20)

------------------------------ --------------------
vero1                          x
vero2                          x
vero3                          x
vero4                          x
vero5                          x

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=2 Bytes=1048) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'ACQUIRENTI' (Cost=3 Card=2 Bytes=1048)
2 1 INDEX (RANGE SCAN) OF 'XIE1ACQUIRENTI' (NON-UNIQUE) (Cost=2 Card=2)

Statistics

---

          5  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
        634  bytes sent via SQL*Net to client
        430  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          5  rows processed

>Thanks for your help.
>
>Best regards, Cristian
>
>-- =
>
>Cristian Veronesi ><((((=BA> http://www.crpa.it
>
>There are no good wars, with the following exceptions: The American =
>
>Revolution, World War II, and the Star Wars Trilogy. (Bart Simpson)

--
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 

Received on Wed Dec 12 2001 - 07:46:04 CST