Re: Function Based Index Oddities

See my testing at
http://hemantoracledba.blogspot.com/2007/08/nulls-are-not-indexed-right-not.html

I encountered errors if I used "1" and, worse, disconnection if I used "2". I then used "A" as my "extra character". I referenced Note#5005939.8 I hadn't tested my FBI with an NVL function.

Hemant

At 08:50 PM Monday, Taylor, Chris David wrote:
>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>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

Hemant K Chitale
http://hemantoracledba.blogspot.com

"There is more to life than increasing its speed." Mohandas Gandhi Quotes
: http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html

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