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@(protected) 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
