Nested Tables Wrap-Up-Database Tables

I do not use nested tables as a permanent storage mechanism myself, for the following reasons:

•\ The unnecessary storage overhead of the RAW(16) columns that are added. Both the parent and the child table will have this extra column. The parent table will have an extra 16-byte RAW for each nested table column it has. Since the parent table typically already has a primary key (DEPTNO in my examples), it makes sense to use this key in the child tables, not a system-generated key.

•\ The unnecessary overhead of the additional unique constraint on theparent table, when it typically already has a unique constraint.

•\ The nested table is not easily used by itself, without usingunsupported constructs (NESTED_TABLE_GET_REFS). It can beunnested for queries, but not mass updates. I have yet to find a tablein real life that isn’t queried “by itself.”

I do use nested tables heavily as a programming construct and in views. This is where I believe they are in their element. As a storage mechanism, I much prefer creating the parent/child tables myself. After creating the parent/child tables, we can, in fact, create a view that makes it appear as if we had a real nested table. That is, we can achieve all of the advantages of the nested table construct without incurring the overhead.

If you do use a nested table as a storage mechanism, be sure to make it an IOT to avoid the overhead of an index on the NESTED_TABLE_ID and the nested table itself. See the previous section on IOTs for advice on setting them up with overflow segments and other options. If you do not use an IOT, make sure to create an index on the NESTED_ TABLE_ID column in the nested table to avoid full scanning it to find the child rows.

Temporary Tables

Oracle comes with two types of temporary tables: global and private. Global temporary tables have been a feature within Oracle for a long time. Global temporary tables are permanent database objects that persist on disk and are visible to all sessions. They are named temporary tables because the data within them only persists for the duration of the transaction or session.

Starting with Oracle 18c, you can create a private temporary table. Unlike a global temporary table, a private temporary table exists only in memory and is only visible to the session that created it. You can define a private temporary table to persist per transaction or session duration, after which the private temporary table is automatically dropped.

Note If you’ve worked with other database technologies such as SQL Server or MySQL, a private temporary table aligns more with what you’ve used in those environments.

In the following sections, I’ll start by first describing how global temporary tables work, and then move on to the newer private temporary table technology.