Donald K. Burleson Oracle Tips

Performance of Oracle object extensions

To fully understand Oracle advanced design with object-oriented extensions we need to take a look at the performance ramifications of using object extensions. While object-oriented database are elegant, we must keep performance our foremost concern. Here is a summary of the performance of object-oriented constructs within Oracle:

Tables with ADTs

Creating user-defined datatypes greatly simplifies Oracle database design. Doing so also provides uniform data definitions for common data items. There is no downside for SQL performance, and the only downside for SQL syntax is the requirement that all references to ADTs be fully qualified.

Varray tables

Varray tables have the benefit of avoiding costly SQL joins, and they can maintain the order of the varray items based upon the sequence when they were stored. However, the longer row length of varray tables causes full-table scans to run longer, and the items inside the varray cannot be indexed. More importantly, varrays cannot be used when the number of repeating items is unknown or very large.

Nested tables

Nested tables have the advantage of being indexed, and the repeating groups are separated into another table so as not to degrade the performance of full-table scans. Nested tables allow for an infinite number of repeating groups. However, it sometimes takes longer to dereference the OID to access the nested table entries as opposed to ordinary SQL tables join operations. Most Oracle experts see no compelling benefit of using nested tables over traditional table joins.

The evolution of Oracle9i has provided a huge number of extensions to standard relational database modeling. It is the challenge of all Oracle design professionals to use these Oracle extensions to improve both the performance and maintainability of Oracle databases. Relational professionals can no longer be content with understanding basic relational systems: Successful Oracle designers must master object-oriented concepts, including abstract data typing, nested tables, array tables, and those unique data structure extensions that make Oracle clearly one of the fastest and most robust databases in the marketplace.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: