Re: Oralce - best index ?

"Jens Meier" <jens.meier_at_ddr.de> wrote in message news:b18ueu$10ed2i$1_at_ID-109621.news.dfncis.de...
> Thanks for helping,
>
> if I create a function based index using the substr function (last 3
> characters), match this Index on '%3456'?

No you need to rewrite your sql so it doesn't use like.

SQL> select count(*)
  2 from large_tab where substr(object_name,1,3) ='SYS';

  COUNT(*)

      2952

Elapsed: 00:00:00.09

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=306 Card=1 Bytes=23)    1 0 SORT (AGGREGATE)

   2    1     TABLE ACCESS (FULL) OF 'LARGE_TAB' (Cost=306 Card=2326 B
          ytes=53498)

SQL> CREATE INDEX F_IDX ON LARGE_TAB(SUBSTR(OBJECT_NAME,1,3)); Index created.

Elapsed: 00:00:14.04
SQL> select count(*)
  2 from large_tab where substr(object_name,1,3) ='SYS';

  COUNT(*)

      2952

Elapsed: 00:00:00.02

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=23)    1 0 SORT (AGGREGATE)

   2    1     INDEX (RANGE SCAN) OF 'F_IDX' (NON-UNIQUE) (Cost=1 Card=
          2326 Bytes=53498)

SQL>

--
Niall Litchfield
Oracle DBA
Audit Commission UK