To demonstrate the collection and nested table types, i will create three different users table. First table will use a COLLECTION/VARRAY TYPE as the column type, the second one will use the NESTED TABLE and the third table will use built-in VARCHAR type to hold the demo users phone numbers. After all, i will insert some random data to query and to examine the execution plans and statistics information.
--create the test array type create type TYP_PHONE_VARRAY as varray(5) of varchar2(15); --create the test table type create type TYP_PHONE_TABLE as table of varchar2(15); --create table which uses the varray type drop table T_USER_VARRAY; create table T_USER_VARRAY ( username varchar2(25), fullname varchar2(25), phone TYP_PHONE_VARRAY default null ); --create table which uses the nested table drop table T_USER_TABLE; create table T_USER_TABLE ( username varchar2(25), fullname varchar2(25), phone TYP_PHONE_TABLE default null ) NESTED TABLE phone STORE AS nt_t_user_table_phone; --create table which uses the built-in varchar as column type drop table U_EPEKER.T_USER_STR; create table U_EPEKER.T_USER_STR ( username varchar2(25), fullname varchar2(25), phone1 varchar2(15) default null, phone2 varchar2(15) default null, phone3 varchar2(15) default null, phone4 varchar2(15) default null, phone5 varchar2(15) default null );
After creating the types and the tables, some random data would be very useful to query and examine the execution plans of the queries. And of course i should not forget to gather statistics, also on the nested table.
--fill the varray typed table with the test data.
declare
i number;
v_name varchar2(10);
v_surname varchar2(10);
v_username varchar2(25);
v_phone varchar2(15);
begin
execute immediate ('truncate table T_USER_VARRAY');
i:=0;
while i<10 loop
v_name := DBMS_RANDOM.STRING('u', 10);
v_surname := DBMS_RANDOM.STRING('u', 10);
v_username := substr(v_name,1,1) || '_' || v_surname;
v_phone := '+31' || round(DBMS_RANDOM.VALUE(60,70)) ||
round(DBMS_RANDOM.VALUE(1000000,9999999));
insert into t_user_varray
values
(
v_username,
v_name || ' ' || v_surname,
TYP_PHONE_VARRAY(v_phone)
);
i:=i+1;
end loop;
commit;
end;
--fill the nested table with the test data.
declare
i number;
v_name varchar2(10);
v_surname varchar2(10);
v_username varchar2(25);
v_phone varchar2(15);
begin
execute immediate ('truncate table T_USER_TABLE');
i:=0;
while i<10 loop
v_name := DBMS_RANDOM.STRING('u', 10);
v_surname := DBMS_RANDOM.STRING('u', 10);
v_username := substr(v_name,1,1) || '_' || v_surname;
v_phone := '+31' || round(DBMS_RANDOM.VALUE(60,70)) ||
round(DBMS_RANDOM.VALUE(1000000,9999999));
insert into t_user_table
values
(
v_username,
v_name || ' ' || v_surname,
TYP_PHONE_TABLE(v_phone)
);
i:=i+1;
end loop;
commit;
end;
--fill the conventional table with the test data.
declare
i number;
v_name varchar2(10);
v_surname varchar2(10);
v_username varchar2(25);
v_phone varchar2(15);
begin
execute immediate ('truncate table T_USER_STR');
i:=0;
while i<10 loop
v_name := DBMS_RANDOM.STRING('u', 10);
v_surname := DBMS_RANDOM.STRING('u', 10);
v_username := substr(v_name,1,1) || '_' || v_surname;
v_phone := '+31' || round(DBMS_RANDOM.VALUE(60,70)) ||
round(DBMS_RANDOM.VALUE(1000000,9999999));
insert into t_user_str
(
username,
fullname,
phone1
)
values
(
v_username,
v_name || ' ' || v_surname,
v_phone
);
i:=i+1;
end loop;
commit;
end;
--gather the statistics of the filled tables
exec sys.dbms_stats.gather_table_stats(ownname=>'U_EPEKER', tabname=>'T_USER_STR', estimate_percent=>100, cascade=>true);
exec sys.dbms_stats.gather_table_stats(ownname=>'U_EPEKER', tabname=>'T_USER_VARRAY', estimate_percent=>100, cascade=>true);
exec sys.dbms_stats.gather_table_stats(ownname=>'U_EPEKER', tabname=>'T_USER_TABLE', estimate_percent=>100, cascade=>true);
exec sys.dbms_stats.gather_table_stats(ownname=>'U_EPEKER', tabname=>'NT_T_USER_TABLE_PHONE', estimate_percent=>100, cascade=>true);
After the preparation of the tables and filling them with some test data we can examine the costs of the same identical queries on these tables.
Selecting all five of the columns and only the one column has both 8 bytes of "consistent gets" but the returned amount of data differs as expected. As the developer cannot guess how many phone number exists for an individual user, probably in the code all columns of the phones be selected to be sure of it.
But when selecting from the table which has the VARRAY type as the "phone" column there is an unexpected amount of bytes and "consistent gets" in the first look. As it is also explained in the Oracle Documentation columns and variables which are defined as VARRAY types are objects which should be instantiated once in the memory. This is the most probable reason of this excessive consistent gets which is almost 5 times more than the conventinal VARCHAR column. Even there is only one phone number stored in the VARRAY, read operation results as if there are five values in the list because the object instantiated as it is defined before.
On the other hand, when i examine the query on the table which uses the NESTED TABLE type for the phone column there is a considerable difference in the "consistent gets" and "bytes read" when comparing with the VARRAY type. Most probably this result indicates that NESTED TABLE types are not instantiated as objects in the memory. They are real tables in the database which are nested in another table and returns the results which they store physically in the database. If you investigate you will find the index and the table segments in the tablespace.
EXPLAIN PLAN FOR
SELECT
phone1, phone2, phone3, phone4, phone5
FROM t_user_str
where username='M_VKFHWFQOKL'; --1 row 48 bytes
/*
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
810 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
*/
EXPLAIN PLAN FOR
SELECT
phone1
FROM t_user_str
where username='M_VKFHWFQOKL'; --1 row 26 bytes
/*
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
534 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
*/
EXPLAIN PLAN FOR
SELECT
phone
FROM t_user_varray
where username='V_RLQDMEPGBJ'; --1 row 35 bytes
/*
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
4619 bytes sent via SQL*Net to client
1865 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
*/
EXPLAIN PLAN FOR
SELECT
phone
FROM t_user_table
where username='N_RNJASPGQOS'; --1 row 30 bytes
/*
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
1675 bytes sent via SQL*Net to client
800 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
*/
As a conclusion; if you are not sure that the VARRAY typed column will not get filled properly then, instead of using VARRAY type using NESTED TABLES may be more convenient for the performance of the application. These small decreases of IO and consistent gets may be very valuable in a busy application for an enterprise environment.
Resources:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/objects.htm
http://docs.oracle.com/cd/B28359_01/appdev.111/b28371/adobjdes.htm#i448939
http://docs.oracle.com/cd/B28359_01/appdev.111/b28371/adobjcol.htm#i454908
