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
   emp1
(
   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.

Conclusion

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.