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
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.
prev_addrs as object
Next, we created the nested object.
nested_address as table of prev_addrs;
Now, we create the parent table with the nested table.
nested table prev_address store as nested_prev_address return as
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.