Re: Function Based Index Oddities
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: Chris.Taylor@xxxxxxxxxxxxxxx, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 15 Oct 2007 23:14:21 +0800
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
