Oracle Bug on Function-Based Indexes that use aconstant as a parmeter
GENERAL
TESTING
GENERAL
TESTING
Description
Activity
Show:
Neal Caidin August 14, 2017 at 1:59 PM
Bulk closing issues that have not been updated since 2014 and earlier. Please reopen if this is still an issue and you have new information.
Matthew Jones March 26, 2013 at 6:47 PM
Is this still an issue with more recent versions of Oracle?
David Horwitz January 25, 2010 at 4:57 AM
MAINTANCE TEAM: Unassigned so these get reviewed by the Maintance Team
Ian Boston July 28, 2008 at 8:30 AM
Jim,
One for you I think ?
Ian
Lance Speelmon July 28, 2008 at 8:01 AM
Ian, do you know anything about this function based index for Oracle? Or was this something Jim Eng worked on? Thanks! L
Oracle Bug: Function-Based Index with a constant
Oracle Metalink Doc 359241.1
IU is currently running 10.1.0.5 on AIX.
IU has come across an Oracle bug with Function-Based Indexes
having a constant as as a parameter. It causes our nightly
statistic refresh to not stop once the bug has been encountered.
Therefore statistics on tables that follow CONTENT_RESOURCE are
not checked to see if a stats refresh is needed. Over time,
this could impact performance since without refreshed stats
the cost based optimizer may not use the most
appropriate execution plan.
The function-based index in question is on the CONTENT_RESOURCE table.
CREATE INDEX ONC.CONTENT_RESOURCE_FSI ON ONC.CONTENT_RESOURCE (FILE_SIZE, 0)
It appear that the app uses this index to return a count of row
where where file_size is null.
Normal indexes do not contain nulls but this function-based index does.
Unfortunately, the zero (constant) "trips" the bug.
Work Arounds:
BEGIN
DBMS_STATS.lock_table_stats('MY_SCHEMA','LOAD_TABLE');
END;
/
The allows any gathering of stats (refresh) to skip the
referenced table and therefore avoid the bug. The stats on Content_Resource
could be refreshed on an "as needed basis" by dropping the
function-based index, then gathering stats, then recreating the index.
Another approach would be to set the file_size default value to be -1
when no file is attached. This then avoids the need to use a
function-based index. However, it would require the code in the app
to be changed to reference -1 instead of null as well as updating any null
in file_size to be -1.