Problem: Users querying SDO_Geometry data owned by a different user may encounter Oracle bugs 4716096 and 5031836
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"
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.
alter system set optimizer_secure_view_merging = FALSE
- Grant the MERGE ANY VIEW permission to the user who is selecting the data.
grant merge any view to <the users selecting the data>