Re: Function based indexes?
- From: jonathan@xxxxxxxxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 06 Dec 2005 19:14:56 +0000
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@xxxxxxxx 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
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
