My Oracle Support Banner

Upgrading to 11g Slow Performance When Compiling Tables With Created Without an Owner Prefix For Column Type For Example Sdo_Geometry (Doc ID 1313743.1)

Last updated on FEBRUARY 02, 2022

Applies to:

Oracle Spatial and Graph - Version to [Release 11.1 to 11.2]
Oracle Database - Enterprise Edition - Version to [Release 11.1 to 11.2]
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.


When upgrading a database from 10.2.x to there is a performance problem caused by tables created with column types which do not specify the owner of the object datatype.

The problem was found with tables that had columns of the SDO_GEOMETRY datatype, but the column type was not prefixed with its owner MDSYS.  The problem was not specific to SDO_GEOMETRY, but could occur for any object data type used without prefixing with the owner data type.  Use of a public synonym for the object type could lead to the same problem.

Using the example with SDO_GEOMETRY:

create table roads (geom sdo_geometry);

For this table we have a slowed performance on the upgrade because an update is issued to every row in the table.

If the same table was created using the MDSYS prefix,

create table roads (geom MDSYS.sdo_geometry);

The performance problem is not encountered during the upgrade.




To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.