On the use of nested table

I just embarked on a new project where use of object types, collections and nested tables are very frequent.  And as always when I need valuable and definite idea about such a kind of oracle feature, I immediately go to read Tom Kyte’s book “Expert Oracle Data Base architecture”.

There I found what made me very confident about my feeling: in your PLSQL code, use object types and collections as widely as you want. However, when it comes to persisting data then avoid using nested tables and prefer to them classical relational ones. Why? Because of the hidden work Oracle is doing when you create nested or object tables. Let’s reproduce the examples given by Tom.

First we will create a simple object type representing the emp table structure

create or replace type emp_type

as object

(empno number(4)

,ename varchar2(10)

,job varchar2(9)

,mgr number(4)

,hiredate date

,sal number(7,2)

,comm number(7,2)

);

Then we create a collection on that defined oracle type

create type emp_tab_type as table of emp_type;

Now we want to create a table which resembles the dept table and in which we want to store the emp table records as a nested table of emp_tab_type data type

create table dept_and_emp(deptno number(2) primary key,

dname varchar2(14),

loc varchar2(13),

emps  emp_tab_type

)

nested table emps store as emps_nt

Let’s insert few data in this new table

insert into dept_and_emp

select dept.*,

cast (multiset(select empno,ename,job,mgr,hiredate,sal,comm

from emp

where emp.deptno = dept.deptno) as emp_tab_type) from dept

Let’s now issue a select from this table with autotrace on

select * from dept_and_emp;
--------------------------------------------------------------------------------
 Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |     4 |   160 |           3
|*  1 |  TABLE ACCESS FULL| EMPS_NT      |     1 |    84 |     3   (0)
|   2 |  TABLE ACCESS FULL| DEPT_AND_EMP |     4 |   160 |     3   (0)
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 1 - filter("NESTED_TABLE_ID"=:B1)
- dynamic sampling used for this statement

What’s this predicate NESTED_TABLE_ID”=:B1 on a column that exists neither in dept_and_emp table nor in the emps_nt nested table????

This is one of the hidden parts Oracle is doing behind our knowledge. It seems that it creates in the emps_nt nested table a column named NESTED_TABLE_ID in order to use it when joining the dept_and_emp table with emps_nt nested table. We can observe this using the following select

select
NESTED_TABLE_ID                   SYS_NC_ROWINFO$(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM)
1386BA31403C4925B35B85978382E69C    EMP_TYPE(7782, 'CLARK', 'MANAGER', 7839, '09/06/81', 2450, NULL)
1386BA31403C4925B35B85978382E69C    EMP_TYPE(7839, 'KING', 'PRESIDENT', NULL, '17/11/81', 5000, NULL)
1386BA31403C4925B35B85978382E69C    EMP_TYPE(7934, 'MILLER', 'CLERK', 7782, '23/01/82', 1300, NULL)
57C6453378A04A84A03893F53C9D0567    EMP_TYPE(7499, 'ALLEN', 'SALESMAN', 7698, '20/02/81', 1600, 300)
57C6453378A04A84A03893F53C9D0567    EMP_TYPE(7521, 'WARD', 'SALESMAN', 7698, '22/02/81', 1250, 500)
57C6453378A04A84A03893F53C9D0567    EMP_TYPE(7654, 'MARTIN', 'SALESMAN', 7698, '28/09/81', 1250, 1400)
57C6453378A04A84A03893F53C9D0567    EMP_TYPE(7698, 'BLAKE', 'MANAGER', 7839, '01/05/81', 2850, NULL
57C6453378A04A84A03893F53C9D0567    EMP_TYPE(7844, 'TURNER', 'SALESMAN', 7698, '08/09/81', 1500, 0)
57C6453378A04A84A03893F53C9D0567    EMP_TYPE(7900, 'JAMES', 'CLERK', 7698, '03/12/81', 950, NULL)
5A62BD5FD95E46AFB3078ABEF1AED5B2    EMP_TYPE(7369, 'SMITH', 'CLERK', 7902, '17/12/80', 800, NULL)
5A62BD5FD95E46AFB3078ABEF1AED5B2    EMP_TYPE(7566, 'JONES', 'MANAGER', 7839, '02/04/81', 2975, NULL)
5A62BD5FD95E46AFB3078ABEF1AED5B2    EMP_TYPE(7788, 'SCOTT', 'ANALYST', 7566, '19/04/87', 3000, NULL)
5A62BD5FD95E46AFB3078ABEF1AED5B2    EMP_TYPE(7876, 'ADAMS', 'CLERK', 7788, '23/05/87', 1100, NULL)
5A62BD5FD95E46AFB3078ABEF1AED5B2    EMP_TYPE(7902, 'FORD', 'ANALYST', 7566, '03/12/81', 3000, NULL)

This nested_table_id column is used in the join between emps_nt nested table and dept_and_emp one. But what is the joining column in the dept_and_emp table?

It is also a hidden column created by Oracle. Its name is SYS_NC0000400005$

dept_and_emp

SYS_NC0000400005$

1386BA31403C4925B35B85978382E69C
5A62BD5FD95E46AFB3078ABEF1AED5B2
57C6453378A04A84A03893F53C9D0567
7686297E1004451DB666B7FEA4F8FAB2

Put it simply, oracle creates two hidden columns, SYS_NC0000400005$ in dept_and_emp and NESTED_TABLE_ID in emps_nt.  But why then it did a full table scan on emps_nt?

Oracle did a full table scan because there is no index on the foreign key column NESTED_TABLE_ID. The hidden work done by Oracle not only creates an invisible nested_table_id to “Foreign key it” with the parent table but it forgets to create a index on this foreign key.  In case of delete, merge or update of the parent table in absence of an index on this hidden column then we are candidate to deadlocks and performance problems.

Hence, we need absolutely to index this foreign key as follows

(nested_table_id)

select * from dept_and_emp;

| Id  | Operation                   | Name           | Rows  | Bytes |Cost (%CP
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     4 |   160 |3
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPS_NT        |     1 |    84 |2
|*  2 |   INDEX RANGE SCAN          | EMPS_DEPT_FK_I |     1 |       | 1
|   3 |  TABLE ACCESS FULL          | DEPT_AND_EMP   |     4 |   160 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
 2 - access("NESTED_TABLE_ID"=:B1)</span>
Note
- dynamic sampling used for this statement</span>

Now it is joining the two tables using an index range scan of the newly created index EMPS_DEPT_FK_I. But according to Tom it is better than to consider the creation of the nested table as an index organized table as show below:

emps emp_tab_type
emps_nt
Insert few data in this new table
dept_and_emp
comm
emp
emp_tab_type

and finally let’s see what happen with the select * from dept_and_emps

 select * from dept_and_emp;
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |     4 |   160 |
|*  1 |  INDEX RANGE SCAN | SYS_IOT_TOP_74530 |     1 |    84 |
|   2 |  TABLE ACCESS FULL| DEPT_AND_EMP      |     4 |   160 |

1 - access("NESTED_TABLE_ID"=:B1)
Note
- dynamic sampling used for this statement</span>

Conclusion: you have better not to use nested tables (and object table) to persist data. But use object types, collections and nested tables as widely as you want in your plsql code.

Update 26-02-2011 :  formatting the code made this blog article not correct. I will update it as soon as possible

Tom Kyte Seminar : Paris 3-4 december 2008

For the first time of my life, I attended a technical seminar given by one of my favorite oracle experts: Tom Kyte

Tom Kyte

If I have one thing to emphasize about this seminar it will be the modesty of Tom Kyte. He is so humble and modest so that he can make your stupid question an interesting issue to explore. I have pointed out that he never refused answering a question during the break time despite that he would much appreciate to take this break time to send e-mails or to answer other urgent questions coming from another place of the world.

This is another proof that a fundamental skill of a great specialist regardless of its domain specialty is its modesty.

 It was about bind variables and cursor sharing, storage techniques, indexing, reorganizing objects, materialized views and cashing, and Q&A as always.