Re: Function based indexes?

9.2.0.6

SQL> create index i2 on t2(case n1 when 1 then 1 end);

Index created.

SQL> execute dbms_stats.gather_table_stats(user,'t2',cascade=>true);

PL/SQL procedure successfully completed.

SQL> select count(*) from t2 where n1 = 1;

  COUNT(*)

        15

1 row selected.

SQL> select num_rows from user_indexes where index_name = 'I2';

  NUM_ROWS

        15

1 row selected.

SQL set autotrace traconly explain
SQL> select * from t2 where
  2 case n1 when 1 then 1 end = 1;

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=15 Bytes=3345)    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=15 Bytes=3345)    2 1 INDEX (RANGE SCAN) OF 'I2' (NON-UNIQUE) (Cost=1 Card=15)

SQL> Not quite what you might want.
You could create a view over the table
to give the case expression an alias that make using it a little easier.

You need to use this version of the case expression in your version of Oracle, as there is a cunning optimisation in some
versions of Oracle that unfortunately
converts the alternative style, viz:
  case when n1 = 1 then 1 end
into the style in my examaple when you
create the index, but NOT when you try to use the index - with the effect that the index is ignored.

Regards

Jonathan Lewis

RStephenson_at_Ovid.com wrote:
> Is there a way to create an index on a column and only index those where
> the value meets a certain expression? For example, if I have a numeric
> column, can I just have the index built for those values that equal 1?
> I don't query on any other value, so I don't want to consume the space
> for the other values. I am running EE 9.2.0.3.
> =20
> Thanks,
> =20
> Rick Stephenson
> =20

>
>

--
http://www.freelists.org/webpage/oracle-l