Mohamed Houri’s Oracle Notes

December 21, 2008

On the use of nested table

Filed under: Oracle — hourim @ 6:22 pm

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

Create a free website or blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: