At work I am building an interface using SQL object types and SQL collection types. I noticed a difference between using the COLLECT aggregate function in combination with a CAST function, versus the CAST-MULTISET method. I started out using CAST-COLLECT, but switched to CAST-MULTISET. Here is a simulation:
rwijk@ORA11GR1> create table customers
2 ( id number(6) not null primary key
3 , name varchar2(30) not null
4 , birthdate date not null
5 )
6 /
Table created.
rwijk@ORA11GR1> create table bankaccounts
2 ( nr number(10) not null primary key
3 , customer_id number(6) not null
4 , current_balance number(14,2) not null
5 )
6 /
Table created.
rwijk@ORA11GR1> insert into customers
2 select 1, 'Jeffrey Lebowski', date '1964-01-01' from dual union all
3 select 2, 'Walter Sobchak', date '1961-01-01' from dual
4 /
2 rows created.
rwijk@ORA11GR1> create sequence customers_seq start with 3
2 /
Sequence created.
rwijk@ORA11GR1> insert into bankaccounts
2 select 123456789, 1, 10 from dual union all
3 select 987654321, 1, 100 from dual union all
4 select 234567890, 2, 2000 from dual
5 /
3 rows created.
rwijk@ORA11GR1> select * from customers
2 /
ID NAME BIRTHDATE
---------- ------------------------------ -------------------
1 Jeffrey Lebowski 01-01-1964 00:00:00
2 Walter Sobchak 01-01-1961 00:00:00
2 rows selected.
rwijk@ORA11GR1> select * from bankaccounts
2 /
NR CUSTOMER_ID CURRENT_BALANCE
---------- ----------- ---------------
123456789 1 10
987654321 1 100
234567890 2 2000
3 rows selected.
Two simple tables with a master-detail relationship. To be able to communicate (not to store, except for simple auditing) a customer, SQL types come in handy. Three types, two object types and a collection type:
rwijk@ORA11GR1> create type to_bankaccount is object
2 ( nr number(10)
3 , current_balance number(14,2)
4 );
5 /
Type created.
rwijk@ORA11GR1> create type ta_bankaccounts is table of to_bankaccount;
2 /
Type created.
rwijk@ORA11GR1> create type to_customer is object
2 ( name varchar2(30)
3 , birthdate date
4 , bankaccounts ta_bankaccounts
5 );
6 /
Type created.
rwijk@ORA11GR1> select type_name
2 , typecode
3 , attributes
4 from user_types
5 /
TYPE_NAME TYPECODE ATTRIBUTES
------------------------------ ------------------------------ ----------
TO_BANKACCOUNT OBJECT 2
TA_BANKACCOUNTS COLLECTION 0
TO_CUSTOMER OBJECT 3
3 rows selected.
The external system can call a function to get the customer information. The function below simulates that function. I used the CAST-COLLECT method:
rwijk@ORA11GR1> create function customer_with_collect
2 ( p_customer_id in customers.id%type
3 ) return to_customer
4 is
5 o_customer to_customer;
6 begin
7 select to_customer
8 ( c.name
9 , c.birthdate
10 , ( select cast
11 ( collect(to_bankaccount(ba.nr,ba.current_balance))
12 as ta_bankaccounts
13 )
14 from bankaccounts ba
15 where ba.customer_id = c.id
16 )
17 )
18 into o_customer
19 from customers c
20 where c.id = p_customer_id
21 ;
22 return o_customer;
23 end customer_with_collect;
24 /
Function created.
rwijk@ORA11GR1> select type_name
2 , typecode
3 , attributes
4 from user_types
5 /
TYPE_NAME TYPECODE ATTRIBUTES
------------------------------ ------------------------------ ----------
TO_BANKACCOUNT OBJECT 2
TA_BANKACCOUNTS COLLECTION 0
TO_CUSTOMER OBJECT 3
3 rows selected.
rwijk@ORA11GR1> select customer_with_collect(1)
2 from dual
3 /
CUSTOMER_WITH_COLLECT(1)(NAME, BIRTHDATE, BANKACCOUNTS(NR, CURRENT_BALANCE))
------------------------------------------------------------------------------
TO_CUSTOMER('Jeffrey Lebowski', '01-01-1964 00:00:00', TA_BANKACCOUNTS(TO_BANK
ACCOUNT(123456789, 10), TO_BANKACCOUNT(987654321, 100)))
1 row selected.
rwijk@ORA11GR1> select type_name
2 , typecode
3 , attributes
4 from user_types
5 /
TYPE_NAME TYPECODE ATTRIBUTES
------------------------------ ------------------------------ ----------
TO_BANKACCOUNT OBJECT 2
TA_BANKACCOUNTS COLLECTION 0
TO_CUSTOMER OBJECT 3
SYSTPuS41SrC7Q9G6/OfQT4VHPA== COLLECTION 0
4 rows selected.
But as you can see, when you use the function, an internal type has been persistently created. This is the internal type that is used to store the result of the COLLECT-function in:
rwijk@ORA11GR1> select collect(to_bankaccount(nr,current_balance))
2 from bankaccounts
3 /
COLLECT(TO_BANKACCOUNT(NR,CURRENT_BALANCE))(NR, CURRENT_BALANCE)
------------------------------------------------------------------------------
SYSTPuS41SrC7Q9G6/OfQT4VHPA==(TO_BANKACCOUNT(123456789, 10), TO_BANKACCOUNT(98
7654321, 100), TO_BANKACCOUNT(234567890, 2000))
1 row selected.
Note that the name of the type is the same as listed earlier.
If you don't query the user_types view as I did, then you might be surprised that the following sequence doesn't work:
rwijk@ORA11GR1> drop type to_customer
2 /
Type dropped.
rwijk@ORA11GR1> drop type ta_bankaccounts
2 /
Type dropped.
rwijk@ORA11GR1> drop type to_bankaccount
2 /
drop type to_bankaccount
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents
So I have to drop the internal type first, before I can drop type TO_BANKACCOUNT:
rwijk@ORA11GR1> column type_name new_value systype
rwijk@ORA11GR1> select type_name
2 from user_types
3 where type_name like 'SYS%'
4 /
TYPE_NAME
------------------------------
SYSTPuS41SrC7Q9G6/OfQT4VHPA==
1 row selected.
rwijk@ORA11GR1> drop type "&systype"
2 /
old 1: drop type "&systype"
new 1: drop type "SYSTPuS41SrC7Q9G6/OfQT4VHPA=="
Type dropped.
rwijk@ORA11GR1> drop type to_bankaccount
2 /
Type dropped.
This behaviour annoyed me slightly, so I checked out another way using CAST-MULTISET:
rwijk@ORA11GR1> create type to_bankaccount is object
2 ( nr number(10)
3 , current_balance number(14,2)
4 );
5 /
Type created.
rwijk@ORA11GR1> create type ta_bankaccounts is table of to_bankaccount;
2 /
Type created.
rwijk@ORA11GR1> create type to_customer is object
2 ( name varchar2(30)
3 , birthdate date
4 , bankaccounts ta_bankaccounts
5 );
6 /
Type created.
rwijk@ORA11GR1> create function customer_with_multiset
2 ( p_customer_id in customers.id%type
3 ) return to_customer
4 is
5 o_customer to_customer;
6 begin
7 select to_customer
8 ( c.name
9 , c.birthdate
10 , cast
11 ( multiset
12 ( select to_bankaccount(ba.nr,ba.current_balance)
13 from bankaccounts ba
14 where ba.customer_id = c.id
15 )
16 as ta_bankaccounts
17 )
18 )
19 into o_customer
20 from customers c
21 where c.id = p_customer_id
22 ;
23 return o_customer;
24 end customer_with_multiset;
25 /
Function created.
And with CAST-MULTISET, there are no additional internal types created:
rwijk@ORA11GR1> select customer_with_multiset(1)
2 from dual
3 /
CUSTOMER_WITH_MULTISET(1)(NAME, BIRTHDATE, BANKACCOUNTS(NR, CURRENT_BALANCE))
------------------------------------------------------------------------------
TO_CUSTOMER('Jeffrey Lebowski', '01-01-1964 00:00:00', TA_BANKACCOUNTS(TO_BANK
ACCOUNT(123456789, 10), TO_BANKACCOUNT(987654321, 100)))
1 row selected.
rwijk@ORA11GR1> select type_name
2 , typecode
3 , attributes
4 from user_types
5 /
TYPE_NAME TYPECODE ATTRIBUTES
------------------------------ ------------------------------ ----------
TA_BANKACCOUNTS COLLECTION 0
TO_BANKACCOUNT OBJECT 2
TO_CUSTOMER OBJECT 3
3 rows selected.
So that's 0-1 in favour of CAST-MULTISET. Let's check performance:
rwijk@ORA11GR1> declare
2 o_customer to_customer;
3 begin
4 runstats_pkg.rs_start;
5 for i in 1..10000
6 loop
7 o_customer := customer_with_collect(1);
8 end loop;
9 runstats_pkg.rs_middle;
10 for i in 1..10000
11 loop
12 o_customer := customer_with_multiset(1);
13 end loop;
14 runstats_pkg.rs_stop(1000);
15 end;
16 /
Run1 draaide in 171 hsecs
Run2 draaide in 130 hsecs
Run1 draaide in 131.54% van de tijd
Naam Run1 Run2 Verschil
STAT.undo change vector size 10,264 2,844 -7,420
LATCH.shared pool 10,236 8 -10,228
STAT.buffer is pinned count 0 20,000 20,000
STAT.redo size 30,784 3,780 -27,004
Run1 latches totaal versus run2 -- verschil en percentage
Run1 Run2 Verschil Pct
183,334 170,551 -12,783 107.50%
PL/SQL procedure successfully completed.
That's 0-2.
Excellent & informative article. Nicely done.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteYou can drop the to_bankaccount object despite dependencies the following way:
ReplyDeletedrop type to_bankaccount force
So still 0-1 ;-)
True. But using "force" still leaves an invalid internal type behind in my schema. So I still consider it 0-2 :-).
ReplyDeleteIf by using Cast Multisect return a resultset of 50 records how much cursor it will open 1 or 51.
ReplyDeleteAny idea if things have changed in 12.1?
ReplyDeleteNo real change:
ReplyDeleteRun1 ran in 105 hsecs
Run2 ran in 78 hsecs
run 1 ran in 134.62% of the time
Name Run1 Run2 Diff
STAT...undo change vector size 12,632 4,604 -8,028
STAT...physical read bytes 8,192 0 -8,192
STAT...cell physical IO interc 8,192 0 -8,192
STAT...physical read total byt 8,192 0 -8,192
LATCH.shared pool 10,269 8 -10,261
STAT...file io wait time 13,793 0 -13,793
STAT...buffer is pinned count 0 20,000 20,000
STAT...redo size 36,156 6,352 -29,804
STAT...session uga memory 65,488 0 -65,488
STAT...session pga memory -327,680 0 327,680
STAT...logical read bytes from 412,942,336 410,165,248 -2,777,088
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
112,640 100,742 -11,898 111.81%
PL/SQL procedure successfully completed.
Nice article, but it's important to realize that this did NOT prove that the use of MULTISET is faster than COLLECT. In your example it was, but this might not be true to all circumstances (ref: http://www.oracle-developer.net/display.php?id=514).
ReplyDeleteVery helpful article. First one I could find which explained how to map a row to an object. Rather simple actually - once you know how. Thanks
ReplyDelete