Details
-
Type:
Bug
-
Status: Resolved
-
Priority:
Major
-
Resolution: Won't Fix
-
Affects Version/s: 2.5.0, 2.5.2, 2.5.3, 2.6.0
-
Fix Version/s: None
-
Component/s: Content, Content service (Pre-K1/2.6), Database service (Pre-K1/2.6)
-
Labels:None
-
Environment:Oracle 10.1.0.5 on IBM AIX 5300
Description
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.