Re: function index creation syntax

From: Ted <theo_reh_at_yahoo.com>
Date: 30 Sep 2003 11:40:48 -0700
Message-ID: <1d9d3087.0309301040.ff473f9@posting.google.com>

roger <xrsr_at_rogerware.com> wrote in message news:<Xns94057AFE77AAArsrrogerwarecom_at_204.127.199.17>...
> theo_reh_at_yahoo.com (Ted) wrote in
> news:1d9d3087.0309251522.3ec01f39_at_posting.google.com:
>
> > Use a substr() in your index DDL.
> >
> > Ted-
>
>
> This works, insofar as the syntax of creating the FBI goes:
>
> create index foo on some_table ( substr(some_func(some_col), 1, 255) );
>
> Now, my question is whether by using the substr here, I have to
> also use it when I query the table using some_func, in order for the
> index to be recognized and used?
>
> That is, can I do this:
>
> select * from some_table
> where some_func(some_col) = 'some value'
>
> or would I need to do this:
>
> select * from some_table
> where substr(some_func(some_col), 1, 255) = 'some value'
>
>
> Thanks.

Test it.

CREATE INDEX MCGDBA.IDX_TEST_NAME
    ON MCGDBA.TEST(SUBSTR(MCGDBA.NAME_TRIM(NAME),1,50)); mcgdba_at_MCGDVL> select * from test where name_trim(name) = 'christian';

NAME

---

christian

1 row selected.

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=321 Card=5696 Bytes=
          125312)

   1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=321 Card=5696 Bytes=12
          5312)

No index....

mcgdba_at_MCGDVL> select * from test where substr(name_trim(name),1,49) = 'christian';

NAME

---

christian

1 row selected.

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=321 Card=5696 Bytes=
          125312)

   1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=321 Card=5696 Bytes=12
          5312)

No index... Index DDL defined substr(column,1,50)

mcgdba_at_MCGDVL> select * from test where substr(name_trim(name),1,50) = 'christian';

NAME

---

christian

1 row selected.

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=35 Bytes=770
          )

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=38 Card=35 B
          ytes=770)

   2    1     INDEX (RANGE SCAN) OF 'IDX_TEST_NAME' (NON-UNIQUE) (Cost
          =3 Card=35)

Index used.

HTH Ted- Received on Tue Sep 30 2003 - 13:40:48 CDT