 |
|
Varray Tables
Oracle Tips by Burleson Consulting |
Before Oracle8, we would need to represent
repeating groups in a table in a very clumsy and non-elegant
fashion. Below is the syntax we use to create the table in Oracle7.
create table
employee (
full_name full_mailing_address_type,
last_name varchar(40),
previous_employer_one varchar(40),
previous_employer_two varchar(40),
previous_employer_three varchar(40)
);
We begin by creating a Oracle type to hold the
repeating group of prior employers.
CREATE OR REPLACE
TYPE
employer_name
AS OBJECT
(e_name varchar(40))
;
CREATE OR REPLACE
TYPE
prior_employer_name_arr
AS
VARRAY(10) OF employer_name;
Next, we create the employee type, embedding
our varray of prior employers.
CREATE OR REPLACE
TYPE employee AS OBJECT
(
last_name
varchar(40),
full_address
full_mailing_address_type,
prior_employers
prior_employer_name_arr
);
Next, we create the emp table, using the
employee type.
SQL> create table
emp of employee;
Table Created.
Now we insert rows into the object table. Note
the use of the full_mailing_address_type reference for the
ADT and the specification of the repeating groups of previous
employers.
insert into emp
values
(
'Burleson',
full_mailing_address_type('7474 Airplane Ave.','Rocky Ford','NC','27445'),
prior_employer_name_arr(
employer_name('IBM'),
employer_name('ATT'),
employer_name('CNN')
)
);
insert into emp
values
(
'Lavender',
full_mailing_address_type('7474 Bearpond Ave.','Big Lick','NC','17545'),
prior_employer_name_arr(
employer_name('Oracle'),
employer_name('Sybase'),
employer_name('Computer Associates')
)
);
Next, we perform the select SQL. Note that we
can select all of the repeating groups with a single reference to
the prior_employers column.
select
p.prior_employers
from
emp p
where
p.last_name = 'Burleson';
PRIOR_EMPLOYERS(E_NAME)
--------------------------------------------------------------------------------
PRIOR_EMPLOYER_NAME_ARR(EMPLOYER_NAME('IBM'), EMPLOYER_NAME('ATT'),
EMPLOYER_NAM
E('CNN'))
This output can be difficult to interpret because of
the nature of the repeating groups. In the example below, we use a
new BIF called table that will flatten-out the repeating groups,
re-displaying the information.
column l_name
heading "Last Name" format a20;
SELECT
emp.last_name l_name,
prior_emps.*
FROM
emp emp,
table(p.prior_employers) prior_emps
WHERE
p.last_name = 'Burleson';
Here we see a
flattened output from the query, and the single information is
replicated onto each table row.
Last
Name E_NAME
-------------------- ----------------------------------------
Burleson IBM
Burleson ATT
Burleson CNN
Execution plans for varray tables
The execution plans for the simple query
above reveals some of the new access methods that were added to
Oracle SQL to manage objects. Note the pickler fetch and
collection iterator operations. The pickler code is very new in
Oracle8 and there are several reported memory leak issues with
pickler fetches. As for performance, the collection iterator
operation is used to extract the embedded repeating group from the
row. Remember, rows with varray items are stored in-line, so
that a fetch for the data block that contains the row will also
contain the repeating groups. Hence, the performance of tables with
varray columns is comparable to standard row select
statements.
OPERATION
--------------------------------------------------------------------------------
OPTIONS OBJECT_NAME
POSITION
------------------------------ ------------------------------
----------
SELECT STATEMENT
33
NESTED LOOPS
1
TABLE ACCESS
FULL EMP
1
COLLECTION ITERATOR
PICKLER
FETCH 2
Next, let’s continue with our discussion of repeating
groups and look at a unique object-oriented concept that is
exclusive to Oracle8. A nested table is a special construct that
allows for another table to be embedded within a master table.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
