BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation










Oracle Nesting of Tables

Oracle Tips by Burleson Consulting

Now lets take this concept one step further and consider how Oracle implements a nested table. Unlike a table with embedded repeating groups, the nested table creates a subordinate table structure to give the appearance of embedding. (Figure 5)  Internally, we see embedded pointers that are used to navigate between the master table and the nested table.

Figure 5: A nested table with pointer links

In this example we will use a nested table to represent repeating group for previous addresses. Whereas a person is likely to have a small number of previous employers, most people have a larger number of previous addresses. First, we create a type using our full_mailing_address_type.

create type prev_addrs as object
   prior_address       full_mailing_address_type

Next, we created the nested object.

create type nested_address as table of prev_addrs;

Now, we create the parent table with the nested table.

create table
   last_name         char(40),
   current_address   full_mailing_address_type,
   prev_address      nested_address
   nested table prev_address store as nested_prev_address return as locator

Although a nested table appears as a part of the master table, internally, it is a separate table. Hence, we see the store as clause to allow the Remote DBA to give the nested table a specific name. In this example, the nested_prev_address subordinate table can be indexed just like any other Oracle table. Also note the use of the “return as locator” SQL syntax. In many cases, returning the entire nested table at query time can be time-consuming. The use of the locator enables Oracle to use the pointer structures to de-reference [what is meant by de-reference?? added] the location of the nested rows.  Any you may know from programming, a pointer de-reference happens when you take a pointer to an object and ask the program to display the data that the pointer is pointing to. In other words, if we have a pointer to a customer row, we can de-reference the object ID and see the data for that customer. The link to the nested tables uses an Oracle object ID (OID) instead of a traditional foreign key value.

Performance of SQL object extensions

Since this book is focused on SQL tuning, we need to take a look at the SQL performance ramifications of using object extensions. Overall, the performance of ADT tables is the same as any other Oracle table, but we do see significant performance differences when implementing varray tables and nested tables. There is a summary of the significant features of the Oracle SQL object extensions.

  • Tables with Abstract Data Types – Creating user defined datatypes can greatly simplify the design of an Oracle database while also provide uniform data definitions for common data items. There is no downside for SQL performance, and the only downside for SQL syntax is the requirements that all references to ADTs be fully qualified.
  • Varray tables - Varray tables have the benefit of avoiding costly SQL joins, and the ability to 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 table 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 table also allow for an infinite number of repeating groups. However, it sometimes takes longer to de-reference [as on the previous page – not sure what is meant here] the OID to get to the nested table entries than ordinary SQL tables join operations, and most Oracle experts see no compelling benefit of using nested tables over traditional table joins.


Oracle has provided a wealth of extensions to SQL in order to improve the productivity of the language and improve the ability to model complex objects. The main point of this chapter is that these extensions can often change the execution plan for an SQL statement and special care must be taken to index Oracle tables so that SQL with BIFs and other extension will be able to quickly access the table rows.

Next, we are going to go deeper into Oracle SQL internals and investigate the workings of the SQL optimizer. It is only with an in-depth knowledge of the SQL optimizer that you will be able to successfully tune an SQL statement.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter