Sakai
  1. Sakai
  2. SAK-14095

Oracle Bug on Function-Based Indexes that use aconstant as a parmeter

    Details

    • Previous Issue Keys:

      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.

        Activity

        Hide
        Lance Speelmon added a comment -
        Ian, do you know anything about this function based index for Oracle? Or was this something Jim Eng worked on? Thanks! L
        Show
        Lance Speelmon added a comment - Ian, do you know anything about this function based index for Oracle? Or was this something Jim Eng worked on? Thanks! L
        Hide
        Ian Boston added a comment -
        Jim,
        One for you I think ?
        Ian
        Show
        Ian Boston added a comment - Jim, One for you I think ? Ian
        Hide
        David Horwitz added a comment -
        MAINTANCE TEAM: Unassigned so these get reviewed by the Maintance Team
        Show
        David Horwitz added a comment - MAINTANCE TEAM: Unassigned so these get reviewed by the Maintance Team
        Hide
        Matthew Jones added a comment -
        Is this still an issue with more recent versions of Oracle?
        Show
        Matthew Jones added a comment - Is this still an issue with more recent versions of Oracle?

          People

          • Assignee:
            Unassigned
            Reporter:
            Dan M McKee
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: