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.