Function Based Index Oddities

I came across a couple of Tom Kyte's blog entries from 2006: "Mull about Null" and "Something about nothing" and using a Function Based Index (FBI) by placing a '0' (zero) in the create index script such as:  

Create index blah_idx01 on blah (col1, col2, col3, 0);  

To assist the optimizer in choosing an index scan when using a "IS NULL" predicate. However I discovered a rather nasty bug in 10.2.0.3 where if you use an FBI with a NVL function in an outer join query, the query will return "NO ROWS FOUND" even though there are rows to be returned.  

After playing around with it, I discovered that you can use a "1" instead of a "0" and the rows will be returned. I "assumed" that a 1 performs the same function as the 0, basically forcing every row to be in the index, even though all 3 columns may in fact be null. However, when using DBMS_STATS.GATHER_TABLE_STATS with CASCADE=> TRUE, it returns an "ORA-03001: unimplemented feature" error.  

So my question is this:  

1.) Is a FBI using a '1' as a pseudo-column different than using a '0' as a pseudo-column?    

Here is the bug info from metalink:

https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_ database_id=NOT&p_id=4621590.8  

Note: 4621590.8  

I find it interesting that this bug affects so many versions and doesn't have a fix yet and affects a multitude of function based indexes including DESC indexes.      

Chris Taylor

Sr. Oracle DBA

Ingram Barge Company

Nashville, TN 37205

Office: 615-517-3355

Cell: 615-354-4799

Email: chris.taylor_at_ingrambarge.com  

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