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.
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.