English

Problem: Users querying SDO_Geometry data owned by a different user may encounter Oracle bugs 4716096 and 5031836

Description

SDE users querying SDO_Geometry data owned by a different user may encounter Oracle bugs 4716096 and 5031836. This can cause the spatial index on the SDO_Geometry column to not be used.

For more information consult Oracle documentation on the following:

"5031836 - SECURE VIEW MERGING ON 10.2 PREVENTS DOMAIN INDEX USAGE"
"4716096 - DOMAIN INDEX NOT PERFORMED IN QUERY ON VIEW BY A DIFFERENT USER"

Cause

This issue is caused by changes that were made to view security in Oracle 10gR2.

Solution or Workaround

To resolve this problem, do one of the following:

  • Upgrade to Oracle 10.2.0.4.
  • Alter the database system to set the optimizer_secure_view_merging parameter to false.

    Code:
    alter system set optimizer_secure_view_merging = FALSE

  • Grant the MERGE ANY VIEW permission to the user who is selecting the data.

    Code:
    grant merge any view to <the users selecting the data>