- Nested tables may be modified (inserted/updated/deleted) from, using SQL.
2 street_address1 varchar2(40),
3 street_address2 varchar2(40),
4 city varchar2(40),
5 state varchar2(40),
6 zip number
7 );
8 /
Type created.
Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2> create type person_typ is table of person_add;
2 /
Type created.
Elapsed: 00:00:00.12
rajesh@10GR2>
rajesh@10GR2> create table person(
2 person_id number,
3 first_name varchar2(30),
4 last_name varchar2(30),
5 address person_typ
6 ) nested table address store as address_ntt;
Table created.
Elapsed: 00:00:00.17
rajesh@10GR2>
rajesh@10GR2> insert into person values (1,'Test1','Test1',person_typ(
2 person_add('123 main1',null,'CA','MN',123),
3 person_add('245 main1',null,'NJ','MN',245))
4 );
1 row created.
Elapsed: 00:00:00.01
rajesh@10GR2> commit;
Commit complete.
Elapsed: 00:00:00.01
rajesh@10GR2> select p_add.street_address1,p_add.street_address2
2 from person p,table(p.address) p_add;
STREET_ADDRESS1 STREET_ADDRESS2
---------------------------------------- ----------------------------------------
123 main1
245 main1
Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2> update table (select address from person)
2 set street_address2 = street_address1;
2 rows updated.
Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2> select p_add.street_address1,p_add.street_address2
2 from person p,table(p.address) p_add;
STREET_ADDRESS1 STREET_ADDRESS2
---------------------------------------- ----------------------------------------
123 main1 123 main1
245 main1 245 main1
Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2> delete from table (select address from person);
2 rows deleted.
Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2> select p_add.street_address1,p_add.street_address2
2 from person p,table(p.address) p_add;
no rows selected
Elapsed: 00:00:00.00
rajesh@10GR2>
rajesh@10GR2>
- VARRAYs must be procedurally modified. You cannot: UPDATE , DELETE as you could with a nested table
2 street_address1 varchar2(40),
3 street_address2 varchar2(40),
4 city varchar2(40),
5 state varchar2(40),
6 zip number
7 );
8 /
Type created.
Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2> create type person_typ is Varray(3) of person_add;
2 /
Type created.
Elapsed: 00:00:00.04
rajesh@10GR2> create table person(
2 person_id number,
3 first_name varchar2(30),
4 last_name varchar2(30),
5 address person_typ
6 );
Table created.
Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2> insert into person values (1,'Test1','Test1',person_typ(
2 person_add('123 main1',null,'CA','MN',123),
3 person_add('245 main1',null,'NJ','MN',245))
4 );
1 row created.
Elapsed: 00:00:00.01
rajesh@10GR2> commit;
Commit complete.
Elapsed: 00:00:00.03
rajesh@10GR2> select p_add.street_address1,p_add.street_address2
2 from person p,table(p.address) p_add;
STREET_ADDRESS1 STREET_ADDRESS2
---------------------------------------- ----------------------------------------
123 main1
245 main1
Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2> update table (select address from person)
2 set street_address2 = street_address1;
set street_address2 = street_address1
*
ERROR at line 2:
ORA-25015: cannot perform DML on this nested table view column
Elapsed: 00:00:00.00
rajesh@10GR2> delete from table (select address from person);
delete from table (select address from person)
*
ERROR at line 1:
ORA-22906: cannot perform DML on expression or on nested table view column
Elapsed: 00:00:00.01
rajesh@10GR2>
Piece-wise updates of a varray value are not supported. Thus, when a varray is updated, the entire old collection is replaced by the new collection
- Nested tables will perform a relational JOIN to bring the collect back with the row. For small collections, this may be expensive
2 street_address1 varchar2(40),
3 street_address2 varchar2(40),
4 city varchar2(40),
5 state varchar2(40),
6 zip number
7 );
8 /
Type created.
Elapsed: 00:00:00.10
rajesh@10GR2> create type person_typ_ntt is table of person_add;
2 /
Type created.
Elapsed: 00:00:00.06
rajesh@10GR2>
rajesh@10GR2> create table person_ntt(
2 person_id number,
3 first_name varchar2(30),
4 last_name varchar2(30),
5 address person_typ_ntt
6 )nested table address store as person_ntt_nest;
Table created.
Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2> insert into person_ntt
2 select object_id,object_name,object_name,person_typ_ntt(
3 person_add('123 main1',null,'CA','MN',123),
4 person_add('245 main1',null,'NJ','MN',245))
5 from all_objects;
56175 rows created.
Elapsed: 00:00:20.32
rajesh@10GR2> commit;
Commit complete.
Elapsed: 00:00:00.03
rajesh@10GR2> begin
2 dbms_stats.gather_table_stats(ownname=>user,tabname=>'PERSON_NTT',estimate_percent=>100,method_opt=>'for all columns size 254');
3 end;
4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.65
select p.person_id,p_add.street_address1,p_add.street_address2
from person_ntt p,table(p.address) p_add
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.31 0 1 0 0
Execute 1 0.00 0.02 0 0 0 0
Fetch 750 0.23 0.27 0 2007 0 112350
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 752 0.25 0.60 0 2008 0 112350
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100
Rows Row Source Operation
------- ---------------------------------------------------
112350 HASH JOIN (cr=2007 pr=0 pw=0 time=486462 us)
56175 TABLE ACCESS FULL PERSON_NTT (cr=581 pr=0 pw=0 time=71 us)
112350 TABLE ACCESS FULL PERSON_NTT_NEST (cr=1426 pr=0 pw=0 time=112409 us)
- VARRAYs do not join. The data is accessed inline for small collections, and as a LOB segment for large collections. In general, there will be less overhead associated with accessing a VARRAY compared to a nested table. There is potentially more overhead associated with updating a VARRAY as compared to a nested table however, since the entire VARRAY must be replaced ‐ not just an element of it.
rajesh@10GR2> create type person_varr is Varray(60000) of person_add;
2 /
Type created.
Elapsed: 00:00:00.07
rajesh@10GR2> create table person_varr_tab(
2 person_id number,
3 first_name varchar2(30),
4 last_name varchar2(30),
5 address person_varr
6 );
Table created.
Elapsed: 00:00:00.54
rajesh@10GR2>
rajesh@10GR2> insert into person_varr_tab
2 select object_id,object_name,object_name,person_varr(
3 person_add('123 main1',null,'CA','MN',123),
4 person_add('245 main1',null,'NJ','MN',245))
5 from all_objects;
56177 rows created.
Elapsed: 00:00:02.73
rajesh@10GR2> commit;
Commit complete.
Elapsed: 00:00:00.07
rajesh@10GR2> begin
2 dbms_stats.gather_table_stats(ownname=>user,tabname=>'PERSON_VARR_TAB',estimate_percent=>100,method_opt=>'for all columns size 254');
3 end;
4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.06
select p.person_id,p_add.street_address1,p_add.street_address2
from person_varr_tab p,table(p.address) p_add
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 751 0.95 0.79 0 1914 0 112354
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 753 0.95 0.79 0 1914 0 112354
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100
Rows Row Source Operation
------- ---------------------------------------------------
112354 NESTED LOOPS (cr=1914 pr=0 pw=0 time=1011273 us)
56177 TABLE ACCESS FULL PERSON_VARR_TAB (cr=1914 pr=0 pw=0 time=112428 us)
112354 COLLECTION ITERATOR PICKLER FETCH (cr=0 pr=0 pw=0 time=275780 us)
- Nested tables are physically stored as a parent child table with surrogate keys.
2 street_address1 varchar2(40),
3 street_address2 varchar2(40),
4 city varchar2(40),
5 state varchar2(40),
6 zip number
7 );
8 /
Type created.
Elapsed: 00:00:00.10
test_user@10GR2> create type person_typ_ntt is table of person_add;
2 /
Type created.
Elapsed: 00:00:00.06
test_user@10GR2> create table person_ntt(
2 person_id number,
3 first_name varchar2(30),
4 last_name varchar2(30),
5 address person_typ_ntt
6 )nested table address store as person_ntt_nest;
Table created.
Elapsed: 00:00:00.15
test_user@10GR2>
test_user@10GR2>
test_user@10GR2> column obj# format 99999999;
test_user@10GR2> column col# format 99999;
test_user@10GR2> column name format a30;
test_user@10GR2>
test_user@10GR2> SELECT obj#, col#, name
2 FROM sys.col$
3 WHERE obj# IN
4 (SELECT object_id
5 FROM user_objects
6 WHERE object_name IN ('PERSON_NTT','PERSON_NTT_NEST'))
7 order by 1,2
8 /
OBJ# COL# NAME
--------- ------ ------------------------------
146579 1 PERSON_ID
146579 2 FIRST_NAME
146579 3 LAST_NAME
146579 4 ADDRESS
146579 4 SYS_NC0000400005$
146580 0 NESTED_TABLE_ID
146580 0 SYS_NC_ROWINFO$
146580 1 STREET_ADDRESS1
146580 2 STREET_ADDRESS2
146580 3 CITY
146580 4 STATE
146580 5 ZIP
12 rows selected.
Elapsed: 00:00:00.03
test_user@10GR2>
VARRAYs are stored as a RAW column or as a LOB. There is minimal overhead introduced for the functionality.
test_user@10GR2> create type person_add as object(
2 street_address1 varchar2(40),
3 street_address2 varchar2(40),
4 city varchar2(40),
5 state varchar2(40),
6 zip number
7 );
8 /
Type created.
Elapsed: 00:00:00.04
test_user@10GR2> create type person_varr is Varray(15) of person_add;
2 /
Type created.
Elapsed: 00:00:00.04
test_user@10GR2> create table PERSON_VARR_TAB(
2 person_id number,
3 first_name varchar2(30),
4 last_name varchar2(30),
5 address person_varr
6 );
Table created.
Elapsed: 00:00:00.04
test_user@10GR2>
test_user@10GR2> SELECT col#, name,length
2 FROM sys.col$
3 WHERE obj# =
4 (SELECT object_id
5 FROM user_objects
6 WHERE object_name = 'PERSON_VARR_TAB')
7 order by 1,2;
COL# NAME LENGTH
---------- ------------------------------ ----------
1 PERSON_ID 22
2 FIRST_NAME 30
3 LAST_NAME 30
4 ADDRESS 3002
Elapsed: 00:00:00.01
test_user@10GR2>
test_user@10GR2> Select object_type, object_name,
2 decode(status,'INVALID','*','') status,
3 tablespace_name
4 from user_objects a, user_segments b
5 where a.object_name = b.segment_name (+)
6 order by object_type, object_name;
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME
------------------- ------------------------------ - ---------------------
TABLE PERSON_VARR_TAB USERS
TYPE PERSON_ADD
TYPE PERSON_VARR
Elapsed: 00:00:00.12
test_user@10GR2>
Oracle has added a 3000 byte column to support our VARRAY implementation. The data for our VARRAY will be stored inline (in the row itself). This raises an interesting question; what will happen if our array could exceed 4,000 bytes ?
test_user@10GR2> alter table PERSON_VARR_TAB drop column address;
Table altered.
Elapsed: 00:00:00.15
test_user@10GR2> create or replace type person_varr is Varray(25) of person_add;
2 /
Type created.
Elapsed: 00:00:00.14
test_user@10GR2> alter table person_varr_tab add address person_varr;
Table altered.
Elapsed: 00:00:00.06
test_user@10GR2>
test_user@10GR2> Select object_type, object_name,
2 decode(status,'INVALID','*','') status,
3 tablespace_name
4 from user_objects a, user_segments b
5 where a.object_name = b.segment_name (+)
6 order by object_type, object_name;
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME
------------------- ------------------------------ - ------------------------------
LOB SYS_LOB0000146626C00004$$ USERS
TABLE PERSON_VARR_TAB USERS
TYPE PERSON_ADD
TYPE PERSON_VARR
Elapsed: 00:00:00.09
test_user@10GR2>
- Nested table ʹArrayʹ elements have no specific order. The data in the collection may be returned in a very different order than it was in when you inserted it.
rajesh@10GR2> create type too as object(
2 id number,
3 dt date,
4 data varchar2(20)
5 );
6 /
Type created.
Elapsed: 00:00:00.15
rajesh@10GR2> create type foo is table of too;
2 /
Type created.
Elapsed: 00:00:00.18
rajesh@10GR2>
rajesh@10GR2> create table t(
2 x number,
3 y foo)
4 nested table y store as t_y;
Table created.
Elapsed: 00:00:00.11
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> INSERT INTO t
2 (
3 x,
4 y
5 )
6 SELECT level ,
7 CAST(multiset
8 (SELECT too(level,sysdate,rpad('*',20,'*'))
9 FROM dual
10 CONNECT BY level <= 1000
11 ) AS foo)
12 FROM dual
13 CONNECT BY level <= 5;
5 rows created.
Elapsed: 00:00:00.07
rajesh@10GR2>
rajesh@10GR2> commit;
Commit complete.
Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2> SELECT x,
2 tab.id
3 FROM t ,
4 TABLE(t.y) tab
5 WHERE t.x = 2
6 AND rownum <= 5;
X ID
---------- ----------
2 191
2 192
2 193
2 194
2 195
Elapsed: 00:00:00.01rajesh@10GR2> rajesh@10GR2>
- VARRAYs are true arrays. The data will remain inherently ordered as you left it
rajesh@10GR2> create type too as object(
2 id number,
3 dt date,
4 data varchar2(20)
5 );
6 /
Type created.
Elapsed: 00:00:00.12
rajesh@10GR2> create type foo is Varray(1000) of too;
2 /
Type created.
Elapsed: 00:00:00.12
rajesh@10GR2>
rajesh@10GR2> create table t(
2 x number,
3 y foo);
Table created.
Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2> declare
2 l_foo foo;
3 begin
4 for k in 1..5
5 loop
6 l_foo := foo();
7 for i in 1..1000
8 loop
9 l_foo.extend;
10 l_foo(l_foo.last) := too(i,sysdate,rpad('*',20,'*'));
11 end loop;
12 insert into t values (k,l_foo);
13 end loop;
14 end;
15 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> commit;
Commit complete.
Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2> SELECT p.x,
2 t_tab.id
3 FROM t p,
4 TABLE(p.y) t_tab
5 where p.x = 2
6 and rownum <= 5;
X ID
---------- ----------
2 1
2 2
2 3
2 4
2 5
Elapsed: 00:00:00.00
rajesh@10GR2>
rajesh@10GR2>
No comments:
Post a Comment