Thanks a lot for your fast response.

I am not getting your statement

"I tend not to use the physical structures -- I
use plain old tables with plain old columns. I will use OR views to give me easier to use data structures in my code however "

Here what do you mean by plain tables? In fact, I am talking about Object tables through out the database design.

Thank You.

Mahendra Jadav.

Tom,
That was a good response.
What I understand from your response is that use of objects results in ease of usage but results in performance depreciation, Right???
Are there any cases where use of Object Oriented and Object relational technology can lead to performance improvement or better space management?

How can I call an overridden method inside the overriding method in PL/SQL?

e.g.
in java I'd use:

void method(int x) {
super.method(x);
...
}

in C++:
virtual void method(int x) {
BaseClass::method(x);
...
}

How to achieve this in PL/SQL?

Regards

So it really means that OO extensions in PL/SQL are of VERY, VERY limited use (at least to me and at least now)...

Sure one can use them but anything requiring non-trivial class hierarchy where one could slightly reimplement the functionality of a base class in a derived class is virtually impossible (or maybe there are some tricks to achieve this?)

I believe that lack of possibility to make such calls is a language design flaw (omission?) not a pre-meditated feature ;) Cause technically it should not be that hard to fix it basically in ~ 100 lines of source code for the PL/SQL compiler. Do you think if the next release will have this fundamental feature properly implemented?

Thank you anyway, bad information sometimes is better than no information at all...

Regards

Piotr, do you mean bad *news* is better than no news? I agree, because knowing the bad news at least lets you prepare for alternatives...

But yes I meant that I can at least stop scratching my head
and stop looking for non-existent solution...

Hey! But seems like I have found an akward, ugly and dirty solution to my problem... Small sample of what and how I do:


CREATE OR REPLACE type person_ot
is object
(
firstname varchar2(100),
lastname varchar2(100),

member procedure print,
member procedure do_print,
static function make return person_ot
) not final
/
CREATE OR REPLACE type body person_ot
is
member procedure do_print
is
begin
p('Firstname=' || firstname || ' Lastname=' || lastname);
end;

member procedure print
is
begin
do_print;
end;

static function make return person_ot
is
begin
return person_ot(null,null);
end;
end;
/


CREATE OR REPLACE type user_ot under person_ot (
name varchar2(30),
password varchar2(30),
overriding member procedure print
);
/
CREATE OR REPLACE type body user_ot
is
overriding member procedure print
is
begin
self.do_print;
p('Name=' || name || ' Password=' || password);
end;
end;
/


Ugly? Sure but it works :-)))
Right now it is a matter of habit I must simply remember to call do_XXX() instead of XXX() directly whenever I want to override XXX()... But anyway I hope that Oracle development team will fix it in the next release.

Regards

Hi Tom
Which is better and why?
1.

CREATE TYPE type_object AS OBJECT (x integer );
CREATE TABLE type_object_table OF type_object;

2. CREATE TABLE normal_relational_table( x integer );

In the first alternative, I have an object type and I am
creating a table of that object type. in the second
one, I am creating a normal relational table with
effectively the same information.

What are the pros and cons of each approach? (assume
thay in the object we are not using any methods etc.)

Thank you - hope you are having a lovely vacation!!

one disadvantage could be I cant create primary key
or fk or any other constraints on the object table (
at least I dont of a method)
You also mention in your book that object tables
(actually nested tables which are not exactly object
tables) get stored as relational underneath - with
lots of magic that can surprise you (e.g. hidden primary
keys in nested table etc) That could also be a disadvantage.

"Hi Tom
Which is better and why?
1.

CREATE TYPE type_object AS OBJECT (x integer );
CREATE TABLE type_object_table OF type_object;

2. CREATE TABLE normal_relational_table( x integer );

In the first alternative, I have an object type and I am
creating a table of that object type. in the second
one, I am creating a normal relational table with
effectively the same information.

What are the pros and cons of each approach? "


OK - I decided to run some benchmarks on the above scenario.

I created the following schema:

-------
drop table object_table;
drop table relational_table;
drop type type_object;
create type type_object as object ( x integer, y varchar2(50), z date );
/
create table object_table of type_object;
create table relational_table( x integer, y varchar2(50), z date );
alter table object_table add constraint object_table_pk primary key ( x );
alter table relational_table add constraint relational_table_pk primary key ( x );

-------

Then I ran the following benchmark
---
set echo on
declare
num_of_records int default 100000;
begin
runstats_pkg.rs_start;
insert into object_table
select rownum, 'xxxyyy'||rownum, sysdate + 20
from all_objects, all_objects
where rownum <= num_of_records;
runstats_pkg.rs_middle;
insert into relational_table
select rownum, 'xxxyyy'||rownum, sysdate + 20
from all_objects, all_objects
where rownum <= num_of_records;
runstats_pkg.rs_stop(200);
end;
/
commit;
exec show_space ( 'RELATIONAL_TABLE' )
exec show_space ( 'OBJECT_TABLE' )
-- benchmarking queries (only with primary key based )
declare
lower_value int default 100;
upper_value int default 102;
num_of_records int default 100000;
begin
runstats_pkg.rs_start;
for i in 1 .. num_of_records loop
for x in ( select * from object_table where x between lower_value and upper_value)
loop
null;
end loop;
end loop;
runstats_pkg.rs_middle;
for i in 1 .. num_of_records loop
for x in ( select * from relational_table where x between lower_value and upper_value)
loop
null;
end loop;
end loop;
runstats_pkg.rs_stop(200);
end;
/
set echo off

---

The benchmark compares inserts and space taken. I did try
to compare selects but I compared only one scenario
using the primary key.

the results of the run are:
----
benchmark@apps> @benchmark
benchmark@apps> set echo on
benchmark@apps> declare
2 num_of_records int default 100000;
3 begin
4 runstats_pkg.rs_start;
5 insert into object_table
6 select rownum, 'xxxyyy'||rownum, sysdate + 20
7 from all_objects, all_objects
8 where rownum <= num_of_records;
9 runstats_pkg.rs_middle;
10 insert into relational_table
11 select rownum, 'xxxyyy'||rownum, sysdate + 20
12 from all_objects, all_objects
13 where rownum <= num_of_records;
14 runstats_pkg.rs_stop(200);
15 end;
16 /
Run1 ran in 2784 hsecs
Run2 ran in 2258 hsecs
run 1 ran in 123.29% of the time

Name Run1 Run2 Diff
LATCH.cache buffers lru chain 455 229 -226
STAT...buffer is not pinned co 228,342 228,090 -252
STAT...change write time 384 128 -256
LATCH.redo writing 533 266 -267
STAT...recursive cpu usage 2,599 2,264 -335
STAT...leaf node splits 529 188 -341
STAT...calls to kcmgas 657 267 -390
STAT...calls to kcmgcs 1,176 601 -575
LATCH.messages 1,236 660 -576
LATCH.session allocation 641 21 -620
LATCH.shared pool 1,216 595 -621
STAT...immediate (CURRENT) blo 1,046 372 -674
STAT...commit cleanouts 1,143 431 -712
STAT...commit cleanouts succes 1,143 431 -712
LATCH.list of block allocation 1,207 430 -777
STAT...calls to get snapshot s 1,504 640 -864
LATCH.transaction allocation 1,749 635 -1,114
LATCH.row cache objects 39,453 38,315 -1,138
STAT...consistent gets - exami 137,897 136,714 -1,183
STAT...recursive calls 3,487 2,047 -1,440
LATCH.checkpoint queue latch 3,154 1,578 -1,576
STAT...free buffer requested 2,770 1,178 -1,592
LATCH.resmgr group change latc 3,852 2,046 -1,806
LATCH.resmgr:schema config 3,852 2,046 -1,806
STAT...consistent gets 427,249 425,244 -2,005
STAT...enqueue releases 4,846 2,506 -2,340
STAT...enqueue requests 4,847 2,507 -2,340
LATCH.undo global data 5,198 1,922 -3,276
STAT...session uga memory 8,472 4,116 -4,356
LATCH.enqueue hash chains 10,148 5,138 -5,010
LATCH.library cache 10,124 4,520 -5,604
STAT...redo entries 11,697 5,534 -6,163
LATCH.redo allocation 12,281 5,786 -6,495
LATCH.enqueues 18,718 9,664 -9,054
STAT...db block gets 17,610 7,723 -9,887
STAT...db block changes 21,853 10,096 -11,757
STAT...session logical reads 444,859 432,967 -11,892
LATCH.cache buffers chains 795,051 748,061 -46,990
STAT...session pga memory max 248,424 0 -248,424
STAT...session pga memory 249,136 -14,696 -263,832
STAT...redo size 22,223,108 9,440,724 -12,782,384

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
909,403 822,203 -87,200 110.61%

PL/SQL procedure successfully completed.

Elapsed: 00:00:51.02
benchmark@apps> commit;

Commit complete.

Elapsed: 00:00:00.02
benchmark@apps> exec show_space ( 'RELATIONAL_TABLE' )
Free Blocks............................. 5
Total Blocks............................ 436
Total Bytes............................. 3,571,712
Total MBytes............................ 3
Unused Blocks........................... 20
Unused Bytes............................ 163,840
Last Used Ext FileId.................... 1
Last Used Ext BlockId................... 54,389
Last Used Block......................... 120

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.35
benchmark@apps> exec show_space ( 'OBJECT_TABLE' )
Free Blocks............................. 5
Total Blocks............................ 957
Total Bytes............................. 7,839,744
Total MBytes............................ 7
Unused Blocks........................... 305
Unused Bytes............................ 2,498,560
Last Used Ext FileId.................... 1
Last Used Ext BlockId................... 53,769
Last Used Block......................... 10

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.31
benchmark@apps> -- benchmarking queries (only with primary key based )
benchmark@apps> declare
2 lower_value int default 100;
3 upper_value int default 102;
4 num_of_records int default 100000;
5 begin
6 runstats_pkg.rs_start;
7 for i in 1 .. num_of_records loop
8 for x in ( select * from object_table where x between lower_value and upper_value)
9 loop
10 null;
11 end loop;
12 end loop;
13 runstats_pkg.rs_middle;
14 for i in 1 .. num_of_records loop
15 for x in ( select * from relational_table where x between lower_value and upper_value)
16 loop
17 null;
18 end loop;
19 end loop;
20 runstats_pkg.rs_stop(200);
21 end;
22 /
Run1 ran in 5346 hsecs
Run2 ran in 5319 hsecs
run 1 ran in 100.51% of the time

Name Run1 Run2 Diff
LATCH.checkpoint queue latch 1,119 866 -253
LATCH.resmgr group change latc 975 586 -389
LATCH.resmgr:schema config 975 586 -389
LATCH.redo allocation 1,104 670 -434
LATCH.enqueue hash chains 2,544 1,569 -975
LATCH.session allocation 2,036 786 -1,250
LATCH.row cache objects 1,906 331 -1,575
LATCH.enqueues 4,598 2,846 -1,752
LATCH.library cache 212,070 206,695 -5,375
LATCH.multiblock read objects 9,342 0 -9,342
LATCH.cache buffers lru chain 32,471 10 -32,461
LATCH.cache buffers chains 1,613,024 1,505,997 -107,027

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
1,885,153 1,723,088 -162,065 109.41%

PL/SQL procedure successfully completed.

Elapsed: 00:01:47.23
benchmark@apps> set echo off
benchmark@apps> spool off

----

Following are my conclusions:
1.Insert into Object table took 23% more time (this %age was consistent for different runs ranging from 20% to 30% )
2. Insert into Object table used 11% more latches (this %age was consistent for different runs ranging from 11% to 15% )
3. Insert into object table took more than twice the amount of space as compared to insert into relational table. This increase in space is due to internal columns being generated by Oracle as shown in the select statement results below (from Tom Kyte's book "Expert one on one Oracle".) As you can see for an object table there are two additional columns SYS_NC_OID$ ( a system generated object id of type raw(16)with a unique constraint and a unique index on it) and SYS_NC_ROWINFO$ ( a virtual column used to get scalar elements as an object.) This unique index explains the extra redo generated during the inserts as shown in point "a" above for object table.
benchmark@apps> set echo on
benchmark@apps> @show_cols OBJECT_TABLE
benchmark@apps> select name, length
2 from sys.col$
3 where obj# =
4 (
5 select object_id
6 from user_objects
7 where object_name = '&1'
8 );
old 7: where object_name = '&1'
new 7: where object_name = 'OBJECT_TABLE'
SYS_NC_OID$ 16
SYS_NC_ROWINFO$ 1
X 22
Y 50
Z 7

Elapsed: 00:00:00.01
benchmark@apps> @show_cols RELATIONAL_TABLE
benchmark@apps> select name, length
2 from sys.col$
3 where obj# =
4 (
5 select object_id
6 from user_objects
7 where object_name = '&1'
8 );
old 7: where object_name = '&1'
new 7: where object_name = 'RELATIONAL_TABLE'

X 22
Y 50
Z 7

4. There does not seem to be much difference in the select's run time. I verified with tkprof where both of them generated the same plan and had the same
number of logical IOs. The differences in latches seems to be incidental - I ran just the select portion multiple times - and the time taken as well as the latches consumed were more or less the same for object table and relational table. Anyways, the select test is definitely not comprehensive and needs to be further tested with different scenarios to arrive at any conclusion.
5. In the end, we can conclude that in the above case
relational table is the way to go.

Tom, could you kindly give your comments on this benchmark
and my conclusions?

Also, I have one question:
Can you please explain a little more about what the hidden column SYS_NC_ROWINFO$ is - how is it a "virtual" column and how exactly it gets used.

Thanx!
Menon:)

Yes, I do have both your books and I know your philosophy
related to how you use object tables:)

I thought I would just run these results by you since
you have a sharp eye for mistakes and also it may be
useful for others.

Thanx for a great web site!

Menon:)

Oracle objects have come a long way since their
introduction - the most important improvement perhaps
being that of type evolution.
However, it still has some major limitations.
For example, it seems that encapsulation of data
in object types is weak in Oracle since it does not support
private attributes. In Java if you have a "set" method
that sets an attribute(declared private),
you are guaranteed that the method is the *only* mechanism of changing the attribute - this is a major feature of the object orientedness of Java (or for that matter, any OO language.)

In Oracle objects (even in 10g), the attributes
are public. So you can change them directly
without using any "set" methods. Perhaps
a mechanism by which you can declare object attributes
within the body (instad of the specification) may
be useful (kind of like packages.) currently
this is not supported (even in 10g)

Following are the reasons I think Oracle does not
support this yet (assuming you are using objects in
tables instead of object views/as extensions to pl/sql.)

1. Object data is stored in tables in the end. If
the only way to get at the data is through setter
and getter then the performance of any dmls
that retrieve/modify data would suffer due to the
extra function calls required.
2. There may be ways of modifying the table data themselves
thus overcoming the "private" data in objects.
This could perhaps be handled by some privileges
(e.g. give privilege to execute the object methods
but dont give privilege to modify the underlying
table directly?
Even though (as your book states), objects give us
a way of binding data to methods, they do not seem to
guarantee that the methods are the only way to change/see the data - which defeats to a large purpose the encapsulation mechanism.

Your thoughts?
Thank you!
Menon:)

it may be useful only for small utilities perhaps. Any
serious OO programming is hampered by the lack
of true encapsulation mechanism. Of course on this
site and in your books, you have demonstrated lot of times how useful these features are for utilities..
I do feel that if Oracle completes the OO ness
of its implementation, its range as a programming
language would increase tremendously...

Thanx!
Menon:)

"I guess that assumes that you have bought into "OO" is the "RW" (right way)"

I never said that:)
All I am saying is that Oracle claims it has support
for Object Oriented programming but this claim is hampered
by the shortcomings (some of which I pointed above.)

OO in my humble opinion, is definitely not what I would
call the "right way" all the time. There is no right way for all situations. There are times when OO is a more powerful programming paradigm that its procedural counterparts - other times it only complicates
code and procedural language is better off.

Just because most J2EE developers abuse the database
developement by their lack of knowledge of db, does
not make Java or OO any less or more powerful paradigm than it is.
And in my opinion, OO is perhaps a bit more than mere
syntactic sugar...

Thanx!:)
Menon:)

Hi Tom,

I have a set of types:

CREATE OR REPLACE
TYPE person_type AS OBJECT (
first_name VARCHAR2 (50),
last_name VARCHAR2 (50),
per_id NUMBER);

CREATE OR REPLACE
TYPE person_ref_ntabtype AS TABLE OF REF person_type;


CREATE OR REPLACE
TYPE project_type AS OBJECT (
prj_id VARCHAR2 (10),
description VARCHAR2 (100),
prj_per person_ref_ntabtype);


I'm have created tables for these type;

CREATE TABLE person
OF PERSON_TYPE
(per_id NOT NULL
,
PRIMARY KEY (per_id));

CREATE TABLE PROJECT OF PROJECT_TYPE
NESTED TABLE PRJ_PER STORE AS PRJ_PER_TAB;

I have a record in PERSON as

insert into person
values(person_type('p8','p8',008));

Now I want to innsert a record into project;
I have tried many ways but with no success.

1.

insert into project
values ( 'PM1',
'test',
person_ref_ntabtype(SELECT ref(p) FROM person_type p WHERE per_id = '008'));

2.
insert into project
values ( 'PM1',
'test',
person_ref_ntabtype());

insert into table(select p.prj_per
from project p
where prj_id='PM1')
values (person_ref_ntabtype(SELECT ref(p) FROM person_type p WHERE per_id = '008'));

3.

insert into project
values ( 'PM1'
,'test'
,person_type('p9','p9',009));

I always get ORA-00932, inconsistent data types error. Please help to figure out this.

Thanks in advance.

ops$tkyte@ORA9IR2> insert into person values ( 'tom', 'kyte', 111 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into person values ( 'frank', 'smith', 222 );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into project values
  2  ( 1, 'first project',
  3    cast( multiset( select ref(p)
  4                      from person p) as person_ref_ntabtype ) );
 
1 row created.
 
ops$tkyte@ORA9IR2> select * from project;
 
PRJ_ID
----------
DESCRIPTION
------------------------------------------------------------------------
PRJ_PER
------------------------------------------------------------------------
1
first project
PERSON_REF_NTABTYPE(0000220208D971AF5F498C9312E030A8C003006A02D971AF5F49
8A9312E030A8C003006A02, 0000220208D971AF5F498D9312E030A8C003006A02D971AF
5F498A9312E030A8C003006A02)
 
 

Tom

We have a need to add attributes to an object type. What is
the best practice for doing this? Appreciate if you can outline the
steps to be taken and the precautions to be taken. The table that has
one of the columns as an object type has lots of dependencies.
Also will 9.2 version allow us to add/modify attributes.

Dear Tom,

If i want to only manipulate the data (in plsql) and finally store the result(after so many process) in permanant table which way of using is better ie either object type tables or physical table or global temp tables? i mean for manipulating purpose. If i use physical tables i need to delete/update/insert several times on the same table to play with the data so i am confused whether to use normal table/global temp table/object type for manupilating the data.

eg: i get a set of dates i need to find slice the dates and do some process then finally store(for this ofcourse we will use physical tables) it in the database.

Thanks and Regards,
Ibrahim.

Tom,

Yes we are on 9.2 and I could get the type altered with cascade option. But when I try to describe the type, it gives out error saying "object has been evolved". I exited out and reconnected and it worked fine. Is this some sort of a bug, as I have to take care of this when we roll it out to prodn.
By the way Tom, I have to appreciate the work you do selflessly. Hope there are few more experts in other fields too who can do this type of work what you do.

ops$tkyte@ORA9IR2> !oerr ora 22337
22337, 00000, "the type of accessed object has been evolved"
// *Cause:  The type of the accessed object has been altered and the client's
//          object is based on an earlier type definition.
// *Action: The user needs to exit application and modify application to
//          accommodate the type change.
//          From SQL/PLUS, reconnect and resubmit statement.
 
"expected" behavior. 

Hi tom
Please look at the following example
where I compare nested table as IOT versus
as heap for storage saving. I dont see any
storage saving even though I used compress
option. Can you take a look? (both cases use
256 blocks)

Thanx!
------------
ttalent@ORA10G> drop table parent_nt_heap_child;

Table dropped.

talent@ORA10G> drop table parent_nt_iot_child;

Table dropped.

talent@ORA10G> drop type child_tab;

Type dropped.

talent@ORA10G> drop type child;

Type dropped.

talent@ORA10G> create or replace type child as object
2 (
3 parent_id number,
4 child_id number,
5 child_name varchar2(50),
6 child_desc varchar2(500)
7 );
8 /

Type created.

talent@ORA10G> /* create the nested table type */
talent@ORA10G> create or replace type child_tab as table of child;
2 /

Type created.

talent@ORA10G>
talent@ORA10G> create table parent_nt_iot_child
2 (
3 parent_id number primary key,
4 parent_name varchar2(50),
5 children child_tab
6 )
7 nested table children store as children_nt_iot(
8 (primary key(nested_table_id, child_name))
9 organization index compress 1);

Table created.

talent@ORA10G>
talent@ORA10G> create table parent_nt_heap_child
2 (
3 parent_id number primary key,
4 parent_name varchar2(50),
5 children child_tab
6 )
7 nested table children store as children_nt_heap
8 ( (primary key(nested_table_id, child_name) ) );

Table created.

talent@ORA10G> begin
2 for i in 1 .. 10000
3 loop
4 insert into parent_nt_heap_child values
5 ( i, 'parent '||i,
6 child_tab(
7 (child(i, i, 'child'||i, 'child'||i||' description')),
8 (child(i, i+10000, 'child'||(i+10000), 'child'||i||' description'))
9 )
10 );
11 insert into parent_nt_iot_child values
12 ( i, 'parent '||i,
13 child_tab(
14 (child(i, i, 'child'||i, 'child'||i||' description')),
15 (child(i, i+10000, 'child'||(i+10000), 'child'||i||' description'))
16 )
17 );
18 end loop;
19 end;
20 /

PL/SQL procedure successfully completed.

talent@ORA10G> exec show_iot_space( 'CHILDREN_NT_IOT' )
IOT used 256

PL/SQL procedure successfully completed.

talent@ORA10G> exec show_space ( 'CHILDREN_NT_HEAP' )
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 1
FS4 Blocks (75-100)..................... 6
Full Blocks ..................... 173
Total Blocks............................ 256
Total Bytes............................. 2,097,152
Total MBytes............................ 2
Unused Blocks........................... 64
Unused Bytes............................ 524,288
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 10,632
Last Used Block......................... 64

I added:

nested table children store as children_nt_iot(
  (<b>constraint nt_iot</b> primary key(nested_table_id, child_name))
 organization index compress 1);


and show space shows:

ops$tkyte@ORA9IR2> exec show_space( 'CHILDREN_NT_HEAP' );
Free Blocks.............................               4
Total Blocks............................             256
Total Bytes.............................       2,097,152
Total MBytes............................               2
Unused Blocks...........................              78
Unused Bytes............................         638,976
Last Used Ext FileId....................               6
Last Used Ext BlockId...................             649
Last Used Block.........................              50
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec show_space( 'NT_IOT', user, 'INDEX' );
Free Blocks.............................               3
Total Blocks............................             256
Total Bytes.............................       2,097,152
Total MBytes............................               2
Unused Blocks...........................             103
Unused Bytes............................         843,776
Last Used Ext FileId....................               6
Last Used Ext BlockId...................             905
Last Used Block.........................              25
 
PL/SQL procedure successfully completed.
 


so, the heap table took 178 blocks (allocated 256, didn't use 78 of them) and the IOT took 153 (allocated 256, didn't use 103 of them)


with only two repeated values on the leading edge, i would not expect HUGE differences.  
 

I tried the same test as above (with your modifications
though it should not have any effect on the
results)
However, I get different results - the compressed
IOT seems to take more space!:)

IOT = 384-64 = 320
HEAP = 256-64 = 192

Menon:)
-----------
scott@ORA10G> drop table parent_nt_heap_child;

Table dropped.

scott@ORA10G> drop table parent_nt_iot_child;

Table dropped.

scott@ORA10G> drop type child_tab;

Type dropped.

scott@ORA10G> drop type child;

Type dropped.

scott@ORA10G> create or replace type child as object
2 (
3 parent_id number,
4 child_id number,
5 child_name varchar2(50),
6 child_desc varchar2(500)
7 );
8 /

Type created.

scott@ORA10G> /* create the nested table type */
scott@ORA10G> create or replace type child_tab as table of child;
2 /

Type created.

scott@ORA10G>
scott@ORA10G> create table parent_nt_iot_child
2 (
3 parent_id number primary key,
4 parent_name varchar2(50),
5 children child_tab
6 )
7 nested table children store as children_nt_iot(
8 (constraint nt_iot_pk primary key(nested_table_id, child_name))
9 organization index compress 1);

Table created.

scott@ORA10G>
scott@ORA10G> create table parent_nt_heap_child
2 (
3 parent_id number primary key,
4 parent_name varchar2(50),
5 children child_tab
6 )
7 nested table children store as children_nt_heap
8 ( (primary key(nested_table_id, child_name) ) );

Table created.

scott@ORA10G> begin
2 for i in 1 .. 10000
3 loop
4 insert into parent_nt_heap_child values
5 ( i, 'parent '||i,
6 child_tab(
7 (child(i, i, 'child'||i, 'child'||i||' description')),
8 (child(i, i+10000, 'child'||(i+10000), 'child'||i||' description'))
9 )
10 );
11 insert into parent_nt_iot_child values
12 ( i, 'parent '||i,
13 child_tab(
14 (child(i, i, 'child'||i, 'child'||i||' description')),
15 (child(i, i+10000, 'child'||(i+10000), 'child'||i||' description'))
16 )
17 );
18 end loop;
19 end;
20 /

PL/SQL procedure successfully completed.

scott@ORA10G> exec show_space( 'PARENT_NT_HEAP_CHILD')
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 6
Full Blocks ..................... 52
Total Blocks............................ 64
Total Bytes............................. 524,288
Total MBytes............................ 0
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 9,472
Last Used Block......................... 8

PL/SQL procedure successfully completed.

scott@ORA10G> exec show_space( 'PARENT_NT_IOT_CHILD')
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 6
Full Blocks ..................... 52
Total Blocks............................ 64
Total Bytes............................. 524,288
Total MBytes............................ 0
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 16,136
Last Used Block......................... 8

PL/SQL procedure successfully completed.

scott@ORA10G> exec show_space( 'NT_IOT_PK', user, 'INDEX' )
Unformatted Blocks ..................... 80
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 79
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 147
Total Blocks............................ 384
Total Bytes............................. 3,145,728
Total MBytes............................ 3
Unused Blocks........................... 64
Unused Bytes............................ 524,288
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 13,832
Last Used Block......................... 64

PL/SQL procedure successfully completed.

scott@ORA10G> exec show_space ( 'CHILDREN_NT_HEAP' )
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 1
FS4 Blocks (75-100)..................... 6
Full Blocks ..................... 173
Total Blocks............................ 256
Total Bytes............................. 2,097,152
Total MBytes............................ 2
Unused Blocks........................... 64
Unused Bytes............................ 524,288
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 10,248
Last Used Block......................... 64

PL/SQL procedure successfully completed.

----------

Yes - learnt something again - always wondered
what those "FS1" etc. meant:)
btw, not sure what you meant by
"Also, please add in the primary key of the heap."?

There is a primary key in the heap as far as I can
tell..

Thanx a lot!!!

Thanx - that is definitely correct:)

Please keep up the great work - I am your fan
as most of my co workers know (sometimes to their
"annoyance")!:)

From reading your site and in my own experience, I find that scalar object types are most useful in getting multiple fields back in a scalar subquery, part of a larger query. Something like

select a,b,(cast(select c,d,e from ...) as object_t)
from ...

If I define the type as

create or replace type name_t as object
( last_name varchar2(50),
first_name varchar2(50)
)

and so on i.e. with properly defined column names.

Now, when I want to get 2 varchar2(50) columns in a unrelated query, I cant really use name_t because it has nothing to do with names.

Instead, if I define a generic type like
create or replace type varchar2_t as object
( col1 varchar2(2000),
col2 varchar2(2000)
),

I can use this varchar2_t in any query where I need a maximum of 2 varchar2 columns back from a scalar subquery.

In other words, I would define object types for the basic types like number, varchar2, date, etc with a maximum of N columns and then I can use them in any scalar subquery where I need a maximum of N columns of that type.

[Of course, if I need a mixture of columns of different types in my subquery, I cannot use this approach and then I would create a properly named type for this purpose]

Comments? Thanks

Good idea. Thanks. A couple of questions

1. Suppose I define a object type like
create or replace type mytype as object (
c1 varchar2(4000)
c2 varchar2(4000),
c3 varchar2(4000)
)

And I use it to select out a varchar2, date and number column from a table. Where exactly do I put the to_date() and to_number()?

select a,b,t.emp_t.ename,to_date(t.emp_t.hire_date,'dd-mon-yyyy'),
to_number(t.emp_t.salary) from (
select a,b,(select mytype(ename,hire_date,salary) from emp) emp_t
from ...) t

Or do I put the functions in the mytype(...) constructor itself? If both are ok, is there any difference between them? Do I have to worry about any implicit type conversions in each case?

2. Since my scalar type contains N attributes and if I need less than N in a query, I need to pass NULL for the rest of the parameters.
select mytype(c1,c2,null,null,...)

Is there a way to have the type's constructor function or something automagically default the parameters so that I can just pass in as many as I need and the rest are assumed to be null?

I tried defining the type as

create or replace type mytype as object (
c1 varchar2(4000),
c2 varchar2(4000) DEFAULT NULL
)

The type was created, but when I tried to use it I got a 'incomplete type' error.

Thanks

Hi Tom
Is this a bug in 10gr1 or am I missing something?
- the subscript 100 gives an error below..Any higher
number gives the same problem. (100 may not be the
lowest number where the exception occurs.) Same results
in 9201.

--
benchmark@ORA10G> drop type bm_type force;

Type dropped.

benchmark@ORA10G> drop type bm_type_varray force;

Type dropped.

benchmark@ORA10G>
benchmark@ORA10G> /* create an object type - bm_type */
benchmark@ORA10G> create or replace type bm_type as object
2 (
3 x_char varchar2(30),
4 y_number number,
5 z_date date
6 );
7 /

Type created.

benchmark@ORA10G> show errors;
No errors.
benchmark@ORA10G>
benchmark@ORA10G> /* create a varray of bm_types */
benchmark@ORA10G> create or replace type bm_type_varray as varray(20) of bm_type;
2 /

Type created.

benchmark@ORA10G> declare
2 l_bm_type_varray bm_type_varray ;
3 begin
4 l_bm_type_varray := bm_type_varray();
5 l_bm_type_varray.extend(5);
6 end;
7 /

PL/SQL procedure successfully completed.

benchmark@ORA10G> declare
2 l_bm_type_varray bm_type_varray ;
3 begin
4 l_bm_type_varray := bm_type_varray();
5 l_bm_type_varray.extend(10);
6 end;
7 /

PL/SQL procedure successfully completed.

benchmark@ORA10G> declare
2 l_bm_type_varray bm_type_varray ;
3 begin
4 l_bm_type_varray := bm_type_varray();
5 l_bm_type_varray.extend(100);
6 end;
7 /
declare
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at line 5


Thanx!

Sorry to have bothered you with such a trivial q!:)

I have two objects, how can I compare them ?
----
Oracle 8.1.7.4 EE on Sun Sol
----
eg.

CREATE TYPE defaulttype AS OBJECT (
code CHAR (16),
name VARCHAR2 (4000),
description varchar2(4000),
value_date DATE,
is_live CHAR (1),
site_code CHAR (11),
updated DATE
);

CREATE OR REPLACE TYPE data_array_type IS VARRAY (4000) OF defaulttype;


---------------------
create table data_value_table (
code CHAR (16),
name VARCHAR2 (4000),
description varchar2(4000),
value_date DATE,
is_live CHAR (1),
site_code CHAR (11),
updated DATE);
-------------------------
CREATE OR REPLACE PROCEDURE p_add_value (p_data_array data_array_type) AS
v_data_array data_array_type;
BEGIN
v_data_array := data_array_type ();
v_data_array.EXTEND (50);

--*** now I want to load values in my local
--*** v_data_array from cursor, and than
--*** compare v_data_array with p_data_array
-- any easy way ? examples ?
FOR x IN (SELECT *
FROM data_value_table)
LOOP
---Q1. how do I load my v_data_array ?
NULL;
END LOOP;
-- Q2, How do I **comapre** the database values in
-- v_data_array to parameter valus in p_data_array ?
END;
/

show err;

please help
thanks,

Hi Tom!

I think that I have found a very interesting bug/feature related to polymorphism... My test script looks like:

SET SERVEROUTPUT ON
SET FEEDBACK OFF

CREATE TYPE t_root
AS OBJECT(
dummy NUMBER
)
NOT FINAL
NOT INSTANTIABLE
/

CREATE TYPE t_under_root_1
UNDER t_root(
test_id NUMBER
,CONSTRUCTOR FUNCTION t_under_root_1 RETURN SELF AS RESULT
)
/

CREATE TYPE BODY t_under_root_1 AS
CONSTRUCTOR FUNCTION t_under_root_1
RETURN SELF AS RESULT IS
BEGIN
RETURN;
END;
END;
/

CREATE TYPE t_under_root_2
UNDER t_root(
test_object t_under_root_1
,CONSTRUCTOR FUNCTION t_under_root_2 RETURN SELF AS RESULT
)
/

CREATE TYPE BODY t_under_root_2 AS
CONSTRUCTOR FUNCTION t_under_root_2
RETURN SELF AS RESULT IS
BEGIN
SELF.test_object := t_under_root_1;
RETURN;
END;
END;
/

DECLARE
v_root t_root;
v_under_root_1 t_under_root_1;
v_under_root_2 t_under_root_2;

PROCEDURE extract_test IS
v_test_id NUMBER;
BEGIN
SELECT treat( v_root AS t_under_root_1 ).test_id
INTO v_test_id
FROM dual;
dbms_output.put_line( nvl( to_char(v_test_id), 'NULL' ) );
END;

BEGIN

-- 1 - simple inherited object
v_root := t_under_root_1( dummy => 1
,test_id => 1 );
extract_test;


-- inherited object as part of an other inherited object

-- 2 - explicit initialization / explicit association to supertype
v_root :=
t_under_root_2( dummy => 1
,test_object => t_under_root_1( dummy => 1
,test_id => 1 ) ).test_object;
extract_test;

-- 3 - explicit initialization / implicit association to supertype v1
v_under_root_2 :=
t_under_root_2( dummy => 1
,test_object => t_under_root_1( dummy => 1
,test_id => 1 ) );
v_root := v_under_root_2.test_object;
extract_test;

-- 4 - explicit initialization / implicit association to supertype v2
v_under_root_1 := t_under_root_1( dummy => 1
,test_id => 1 );
v_under_root_2 := t_under_root_2( dummy => 1
,test_object => v_under_root_1 );
v_root := v_under_root_2.test_object;
extract_test;

-- 5 - implicit initialization - constructors used / implicit association to supertype
v_under_root_2 := t_under_root_2();
-- v_under_root_2.test_object := t_under_root_1(); -- not really necessary
v_under_root_2.test_object.test_id := 1;
v_root := v_under_root_2.test_object;
extract_test;

-- 6 - same as 5 - except the explicit sub-object initialization
v_under_root_2 := t_under_root_2();
v_under_root_2.test_object := t_under_root_1(); -- not really necessary
v_under_root_2.test_object.test_id := 1;
v_root := v_under_root_2.test_object;
extract_test;

-- 7 - TOTALLY same as 5
v_under_root_2 := t_under_root_2();
-- v_under_root_2.test_object := t_under_root_1(); -- not really necessary
v_under_root_2.test_object.test_id := 1;
v_root := v_under_root_2.test_object;
extract_test;

END;
/

DROP TYPE t_under_root_2;
DROP TYPE t_under_root_1;
DROP TYPE t_root;

-- RESULTS on Oracle9i Enterprise Edition Release 9.2.0.6.0 for MS-Win:
-- 1 - fine
-- 1 - fine
-- 1 - fine
-- 1 - fine
-- 1 - fine
-- 1 - fine

-- RESULTS on Oracle9i Enterprise Edition Release 9.2.0.5.21 for IBM z/OS:
-- ( also the same on linux and aix/unix )
-- 1 - fine
-- 1 - fine
-- NULL - bad
-- NULL - bad
-- NULL - bad
-- 1 - fine
-- 1 - fine


I know that this one is a bit sophisticated, but I relly need these features. I will report this as a bug, but before doing that, please confirm me that this is not a feature what happening on z/OS.

I think this is related to the representation of the objects, because if I try to identify the object type with "sys_typeid(object)" or "object is of (ONLY type)" the result is bad in the problematic cases above.

Thanks a lot!

Tom,
Could you please explain me the following two terms or point me to a reference with respect to object relational databases:

- dereference
- double dot notation

Thanks,

Tom,

Thanks. My question was related to object relational features.


The note that I have with me suggests the following(I have only one slide which is not complete at all):

"In SQL-3 sub-components are not referenced by dereferencing operator but by double dot operator '..' (x..A)

ex:

select maker->president->name
from automobile
where mechanicalparts..motor='xv'

In this example the motor attribute of the sub-object mechanical parts of cars is being accessed"

I am relating dereference with Deref (provided by oracle), am I correct ?
Is there equivalent thing for double dot notation (..) in oracle?

I have made several attempts to searching for SQL-3 syntax on the net for the above but without joy. Do you know any such links?

Regards

I want to use Objects to pass large and complex data sets from the server to a forms client and then execute encapsulated methods on those objects within the client.

My plan is to create the object type on the server and have a forms package that gets the object type and places it into a variable in the Forms PL/SQL engine.

Starting thinking about this and started wondering - if I use this approach is the PL/SQL that implements the object methods automagically copied to the form?

If not, is the entire object copied down to the server so it can execute the method, thus totally destroying the whole performance rationale of my approach?

Or does something else happen?

I can't think of any way to test this - except for performance this copying if it happens should be totally transparent.

1. The object on the client will be copied to the server any time you execute any object method. Then the object will be copied back to client after the execution.

2. Therefore, it does not make sense to use object methods on clients that implement PL/SQL unless the objects are small and you are willing to accept the round trip transmission to the server.

3. However, you can still use objects on the client if all you do is access their attributes - they're still a good way to encapsulate jagged result sets, etc. - since that will not require transmission to the server to access them.

(Please tell me I'm right about #3!!!)

We have forms even if you don't. Can you suggest a test for this?

Tom,

I've got a specific example that I am having trouble in obtaining reasonable select times from an object-relational type. The collection data doesn't exist in actual tables but I want to be able to use "select" syntax over PL/SQL "for" loop syntax because I intend to embed such select statements into other encompassing select's. Trouble is, I just can't get the performance of the "select" from the basic object-relational component to be, even close, to that of a PL/SQL "for" loop. I've tried adding MAP and ORDER functions but am not sure if these are getting invoked. If you have time, could you make some suggestions regarding the "test 1" select statement in relation to performance improvements. NB. if you run the test case as-is, you'll see that the "select" on the object-relational type takes around 10 times longer than the PL/SQL "for" loop.

Regards,

Harris.

Test case follows ...


drop type mytype1_tab;
drop type mytype1;
create type mytype1 as object(f1 number(10),
f2 varchar2(100),
map member function map1 return number,
pragma restrict_references(map1,
WNDS,RNDS,WNPS,RNPS)
);
/
show errors;

create type body mytype1 as
map member function map1 return number
as
begin
return f1;
end;
end;
/
show errors;

create type mytype1_tab as table of mytype1;
/


set serverout on;
declare
vMYTYPE mytype1_tab:= mytype1_tab();
vI NUMBER;
vJ NUMBER;
vOFFSET NUMBER;
vX VARCHAR2(100);
begin
vI:= dbms_utility.get_time;
for vC in 1 .. 100000
loop
vMYTYPE.extend(1);
vMYTYPE(vMYTYPE.LAST):= mytype1(vC,'REC'||vC);
end loop;
vJ:= dbms_utility.get_time;
dbms_output.put_line('Time populating 10000 records: '||(vJ-vI)/100);

-- Test1: Running a select table() ...
vI:= dbms_utility.get_time;
select f2
into vX
from table(vMYTYPE) t
where f1 = 99000;
vJ:= dbms_utility.get_time;
dbms_output.put_line('Time checking for record 99000: '||(vJ-vI)/100);
dbms_output.put_line('Value vMYTYPE(99000) is: '||vX);

-- Test2: PL/SQL scanning of collection ...
vI:= dbms_utility.get_time;
for vC in vMYTYPE.FIRST .. vMYTYPE.LAST
loop
if vMYTYPE(vC).f1 = 99000
then
--dbms_output.put_line('99000 is scanned');
vOFFSET:= vC;
exit;
end if;
end loop;
vJ:= dbms_utility.get_time;
dbms_output.put_line('Time scanning for record 99000: '||(vJ-vI)/100);
dbms_output.put_line('Value vMYTYPE(99000) is: '||vMYTYPE(vOFFSET).f2);
end;
/

Appologies, probably should have included the results, which are;
Type dropped.


Type dropped.


Type created.

No errors.

Type body created.

No errors.

Type created.

Time populating 10000 records: .93
Time checking for record 99000: 2.99
Value vMYTYPE(99000) is: REC99000
Time scanning for record 99000: .22
Value vMYTYPE(99000) is: REC99000

PL/SQL procedure successfully completed.


Test1 and Test2 are essentially performing the same lookup function for an arbitrary record (value 99000). Notice that the time for the "select" is about 10 times worse than the PL/SQL "for" loop.

Regards,

Harris.

Hi

I have these types


CREATE OR REPLACE
TYPE "CONTACT_ARR" AS VARRAY (3) OF CONTACT_AUTO
/

CREATE OR REPLACE
TYPE "CONTACT_AUTO" AS OBJECT (
NAME VARCHAR2(65),
EMAIL VARCHAR2(65)
)
/


and a table

create table emp_contact
(
empno number,
emp_details CONTACT_ARR)

I dont have a clue how to insert a row to this table, I am reading Database Application Developer's Guide - Object-Relational Features but I cant find a way.

Can you help me?

Hi Tom,

I've read her and in your books that you don't like the idea of storing objects physically.

Overall i agree, however, I have a need to store addresses consistently across my app and i think creating an object type to represent addresses and then using that as a column type would be quite elegent?

In fact, to do it relationaly was quite ugly. If addresses are needed for different entity types (Person, Business, Factory, etc) then you ahve an issue with foreign key management - or creating seperate address tables for each entity type.

What do you think?

Hi Tom ,

I am puzzled by one pl/sql array problem.Please help me.I want to use 10g provided set operator on pl/sql table.


If I run below code, it is working fine.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> 
SQL> declare
  2  TYPE string1 IS TABLE OF VARCHAR2(100);
  3  lv_string1 string1:=string1('a','b','c');
  4  lv_string2 string1:=string1('a','x','c');
  5  lv_changed string1:=string1();
  6  begin
  7  lv_changed := lv_string2 MULTISET EXCEPT lv_string1;
  8  for i in 1..lv_changed.count
  9  loop
 10  dbms_output.put_line(lv_changed(i));
 11  end loop;
 12  end;
 13  /
x

PL/SQL procedure successfully completed.

But when I run ....

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> 
SQL> desc subha1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(100)
 SAL                                                NUMBER

SQL>  select id,name,sal  from subha1;
ID NAME     SAL    
-----------------------     
1  subha    100
1  SUBH     10


SQL> set serveroutput on
SQL> declare
  2  type str1 is record (cname varchar2(100),value VARCHAR2(1000));
  3  TYPE string1 IS TABLE of str1;
  4  lv_string1 string1:=string1();
  5  lv_string2 string1:=string1();
  6  lv_changed string1:=string1();
  7  cursor c1 is
  8  select column_name from user_tab_columns
  9  where table_name='SUBHA1';
 10  curstr varchar2(32000):='select ';
 11  cnt number:=1;
 12  begin
 13  for c1_rec in c1
 14  loop
 15  lv_string1.extend;
 16  lv_string2.extend;
 17  lv_string1(cnt).cname:=c1_rec.column_name;
 18  lv_string2(cnt).cname:=c1_rec.column_name;
 19  execute immediate curstr|| lv_string1(cnt).cname ||' from subha1 where sal=100' into lv_string1(cnt).value;
 20  execute immediate curstr|| lv_string2(cnt).cname ||' from subha1 where sal=10' into lv_string2(cnt).value;
 21  dbms_output.put_line('first array -> '||lv_string1(cnt).cname||'-'||lv_string1(cnt).value);
 22  dbms_output.put_line('second array -> '||lv_string2(cnt).cname||'-'||lv_string2(cnt).value);
 23  cnt:=cnt+1;
 24  end loop;
 25  dbms_output.put_line('-----------------------------------------------------------------');
 26  lv_changed := lv_string2 MULTISET EXCEPT lv_string1;
 27  end;
 28  /
lv_changed := lv_string2 MULTISET EXCEPT lv_string1;
              *
ERROR at line 26:
ORA-06550: line 26, column 15:
PLS-00306: wrong number or types of arguments in call to 'MULTISET_EXCEPT_ALL'
ORA-06550: line 26, column 1:
PL/SQL: Statement ignored

I tried several times in different ways, but it seems whenever I run a 'multiset except' on a record type array(i.e. an pl/sql table built on user defined type)..it is throwing same error.

Please give me suggestions or am I missing something.Offcourse I could loop throgh one array and compare the values and show the differences..but I think it will be much beneficial to use set operator from code maintainability and preformance aspect.

Waiting for your early reply as one of my implemenation is stuck on this.

Many Thanks & regards
Subhasis Gangopadhyay 

in order for two ADT instances (complex structures, not simple scalars), you need a map/order member function, eg, it could look like this:

ops$tkyte%ORA10GR2> create or replace type str1 as object
  2  ( cname varchar2(100),
  3    value varchar2(1000),
  4    map member function mapper return varchar2
  5  )
  6  /

Type created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace type body str1
  2  as
  3  map member function mapper return varchar2
  4  is
  5  begin
  6          return cname ||'/'|| value;
  7  end;
  8  end;
  9  /

Type body created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace type string1 is table of str1
  2  /

Type created.

ops$tkyte%ORA10GR2> set serveroutput on
ops$tkyte%ORA10GR2> declare
  2          lv_string1 string1:=string1( str1( 'x', 1 ), str1('y',2) );
  3          lv_string2 string1:=string1( str1( 'x', 1 ), str1('y',2), str1('z',3) );
  4          lv_changed string1;
  5  begin
  6          lv_changed := lv_string2 MULTISET EXCEPT lv_string1;
  7
  8          for i in 1 .. lv_changed.count
  9          loop
 10                  dbms_output.put_line( lv_changed(i).cname || ', ' || lv_changed(i).value );
 11          end loop;
 12  end;
 13  /
z, 3

PL/SQL procedure successfully completed.


that simple of a map function will work here - in this case - since we only really care about equality and not sorting.  We'd have to go for a order member function if you wanted to sort str1-wise (and you'd have to decide what it meant for one ADT instance to be greater than another) 

Hi Tom,

The solution that you have given me has been most usefull.Thanks a lot for this.But I suppose I have to create types(though not a very big problem) ..I can not use pl/sql types declared in code itself(as I have to create member function)..or otherwise use dynamic sqls to create types in the code itself.

Also,secondly just one thing coming to my mind from your answer.You have mentioned that if I want to sort the contents then I have to use order member function.If you are specifying that for 'multiset union' it is required, then there is no problem(please see the below code)in running the code but it is working like sql operator 'union all' but not like 'union' to show the distinct combination.Can you please cite an small example to show the use of 'order' member function to solve the above requirement.

  1  declare
  2  type str1 is record (cname varchar2(100),value VARCHAR2(1000));
  3  TYPE string_new IS TABLE of str1;
  4  lv_string_new string_new:=string_new();
  5  lv_string2 string_new:=string_new();
  6  lv_changed string_new:=string_new();
  7  cursor c1 is
  8  select column_name from user_tab_columns
  9  where table_name='SUBHA1';
 10  curstr varchar2(32000):='select ';
 11  cnt number:=1;
 12  begin
 13  for c1_rec in c1
 14  loop
 15  lv_string_new.extend;
 16  lv_string2.extend;
 17  lv_string_new(cnt).cname:=c1_rec.column_name;
 18  lv_string2(cnt).cname:=c1_rec.column_name;
 19  execute immediate curstr|| lv_string_new(cnt).cname ||' from subha1 where sal=10' into lv_string_new(cnt).value;
 20  execute immediate curstr|| lv_string2(cnt).cname ||' from subha1 where sal=10' into lv_string2(cnt).value;
 21  dbms_output.put_line('first array -> '||lv_string_new(cnt).cname||'-'||lv_string_new(cnt).value);
 22  dbms_output.put_line('second array -> '||lv_string2(cnt).cname||'-'||lv_string2(cnt).value);
 23  cnt:=cnt+1;
 24  end loop;
 25  dbms_output.put_line('-----------------------------------------------------------------');
 26  lv_changed := lv_string2 MULTISET union lv_string_new;
 27  for i in 1..lv_changed.count
 28  loop
 29  dbms_output.put_line(lv_changed(i).cname||'-'||lv_changed(i).value);
 30  end loop;
 31* end;
SQL> /
first array -> ID-1
second array -> ID-1
first array -> NAME-SUBH
second array -> NAME-SUBH
first array -> SAL-10
second array -> SAL-10
-----------------------------------------------------------------
ID-1
NAME-SUBH
SAL-10
ID-1
NAME-SUBH
SAL-10

PL/SQL procedure successfully completed.

From the above output it can be seen that it is not picking the distinct combination of values.An example will help very much like always you do.

Many thanks & Regards
Subhasis Gangopadhyay
 

Hi Tom,

For my problem, I have seen many questions, but none of the questions was matching my requirement. I thought the question I am responding to talks something about it, hence adding it here.
The assignment is very crucial for my project, please help me.
=================================
In my current assignment, we have

Object Type
CREATE OR REPLACE TYPE MESSAGEHEADER_TY AS OBJECT(
MESSAGETYPE VARCHAR2(16),
MESSAGEDATE NUMBER(14))
/

Table is based on above Object Type
CREATE TABLE MESSAGES
(
MSG_ID NUMBER NOT NULL,
MESSAGEHEADER MESSAGEHEADER_TY
)
/

Records in the Messages table are

MSG_ID MESSAGEHEADER
=========================
100320 (ichicsr, 2)
100321 (ichicsr, 2)
100322 (ichicsr, 2)
100330 (ichicsr, 2)
100331 (xyzcsr, 2)
100332 (pqrcsr, 2)

Requirement is cut first word from Messageheader column that is "ichicsr" or "xyzcsr" or "pqrcsr". And then do lookup on some other table based on this value.

Above tables are in Source schema, hence I do have only SELECT privileges. Hence I can not create Object Method to apply string functions on object type data.

Since the table has object type column, I can access it over DB Links, nor can apply CTAS.

Can you please suggest me way for this?

regards
Rupali

what deos oracle depend on ? relational model or object oriented model?

Tom,
What business problem did oracle solve using object relational model?

Hi Tom,
Can you comment following behaviour of object member methods? Why it is so slowly?
Example:

create or replace type NumberArray is table of number;

create or replace type test_object as object(
arr NumberArray,
constructor function test_object return self as result,
member function get_value(in_indx in number) return number,
member procedure set_value(in_indx in number, in_value in number),
static procedure set_value(io_obj in out nocopy test_object, in_indx in number, in_value in number)
)
/

create or replace type body test_object as

constructor function test_object return self as result
as
begin
self.arr := new NumberArray();
return;
end;

member function get_value(in_indx in number) return number
as
begin
return arr(in_indx);
end;

member procedure set_value(in_indx in number, in_value in number)
as
begin
arr(in_indx) := in_value;
end;

/* both methods set_value do the same but in different ways*/
static procedure set_value(io_obj in out nocopy test_object, in_indx in number, in_value in number)
as
begin
io_obj.arr(in_indx) := in_value;
end;

end;
/

declare
obj test_object;
t pls_integer;
l_max constant pls_integer := 10000;
begin
-- Init object
obj := test_object();
obj.arr.extend(l_max);
-- Call member method
t := dbms_utility.get_time;
for i in 1..l_max loop
obj.set_value(i,i);
end loop;
t := dbms_utility.get_time - t;
dbms_output.put_line('Member procedure execution time: '||t/100||' sec.');
-- Call static method
t := dbms_utility.get_time;
for i in 1..l_max loop
test_object.set_value(io_obj => obj, in_indx => i, in_value => i);
end loop;
t := dbms_utility.get_time - t;
dbms_output.put_line('Static procedure execution time: '||t/100||' sec.');
end;
/

Member procedure execution time: 2,75 sec.
Static procedure execution time: ,01 sec.

WHY?

Tested on Oracle 11.2 and Oracle 10.2 (10g slowler than 11g)

Thank you,
Rustam

I've found answer to my previous question by myself: when I define self parameter explicity everything starts work fine!
member procedure set_value(self in out nocopy test_object, in_indx in number, in_value in number),

This little changes in declaration have greate improvement in performance!

rajesh@10GR2> select type_name from user_types;

TYPE_NAME
------------------------------
ADDRESS
PERSON_TYPE

2 rows selected.

rajesh@10GR2> 
rajesh@10GR2> drop type person_type ;
drop type person_type
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents


rajesh@10GR2> 
rajesh@10GR2> drop type address ;
drop type address
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents


rajesh@10GR2> 
rajesh@10GR2> select NAME,TYPE
  2  from user_dependencies
  3  where referenced_name in ('ADDRESS','PERSON_TYPE')
  4  /

NAME                           TYPE
------------------------------ -----------------
PERSON_TYPE                    TYPE
ADDRESS                        TYPE BODY
PEOPLE                         TABLE
PEOPLE                         TABLE

4 rows selected.

rajesh@10GR2> 
rajesh@10GR2> SELECT table_name from user_tables;

no rows selected

rajesh@10GR2> 
rajesh@10GR2> select object_name
  2  from user_recyclebin
  3  /

no rows selected

rajesh@10GR2> 
rajesh@10GR2> drop table people purge;
drop table people purge
           *
ERROR at line 1:
ORA-01741: illegal zero-length identifier

Tom:

1) The table PEOPLE doesnot exists in user_tables data dictionary, but how this is visible in user_dependencies ?
2) what does this error ( ORA-01741: illegal zero-length identifier ) mean ?

Good Morning Tom,

I have an issue to store big volume of order data

the order entity has following attributes
(
USER_ID VARCHAR2(24)
,CARD_ID VARCHAR2(40)
,ITEM_ID INTEGER
,DTTM TIMESTAMP
)

one USER_ID+CARD_ID has average 1000 items but never go over 5000 items
the business requirement is to ask all item_ids by given (USER_ID+CARD_ID)

total USER_ID+CARD_ID is about 100 millions.

there are couple ways to store those data:
if we store as IOT table. the total rows will be 100 Billions. that maybe too much records.
if put item_ids for each USER_ID+CARD_ID into a collection or clob, the total rows will be 100 millions

here is what I did for 4 different storage methods

1: IOT with compression

2: VARRAY for item_ids

3: CLOB for item_ids

4: NESTED_TABLE for item_id

ORACLE VERSION 11gR2 on Solaris

1:IOT table with compression
CREATE TABLE ORDER_IOT
(
USER_ID VARCHAR2(24)
,CARD_ID VARCHAR2(40)
,ITEM_ID INTEGER
,DTTM TIMESTAMP
,CONSTRAINT ORDER_IOT_PK PRIMARY KEY(USER_ID,CARD_ID,ITEM_ID)
)
ORGANIZATION INDEX COMPRESS
/

2:VARRAY

CREATE OR REPLACE TYPE ITEM_ID_VAR AS VARRAY(5000) OF NUMBER(8,0)
/
CREATE TABLE ORDER_VAR
(
USER_ID VARCHAR2(24)
,CARD_ID VARCHAR2(40)
,ITEM_ID_LIST ITEM_ID_VAR
,DTTM TIMESTAMP
)
/
CREATE UNIQUE INDEX ORDER_VAR_PK ON ORDER_VAR(USER_ID, CARD_ID)
/
ALTER TABLE ORDER_VAR ADD CONSTRAINT ORDER_VAR_PK PRIMARY KEY (USER_ID, CARD_ID) USING INDEX
/

3: CLOB:
CREATE TABLE ORDER_LOB
(
USER_ID VARCHAR2(24)
,CARD_ID VARCHAR2(40)
,ITEM_ID_LIST CLOB
,DTTM TIMESTAMP
)
/
CREATE UNIQUE INDEX ORDER_LOB_PK ON ORDER_LOB(USER_ID, CARD_ID)
/
ALTER TABLE ORDER_LOB ADD CONSTRAINT ORDER_LOB_PK PRIMARY KEY (USER_ID, CARD_ID) USING INDEX
/

4: NESTED TABLE

CREATE OR REPLACE TYPE ITEM_ID_REC AS OBJECT (ITEM_ID NUMBER(8,0))

CREATE TYPE ITEM_ID_TAB AS TABLE OF ITEM_ID_REC
/
CREATE TABLE ORDER_NT
(
USER_ID VARCHAR2(24)
,CARD_ID VARCHAR2(40)
,ITEM_ID_LIST ITEM_ID_TAB
,DTTM TIMESTAMP
)
nested table ITEM_ID_LIST store as ITEM_ID_NT_IOT
( (PRIMARY KEY(NESTED_TABLE_ID, ITEM_ID ))
ORGANIZATION INDEX COMPRESS 1
)
/
CREATE UNIQUE INDEX ORDER_NT_PK ON ORDER_NT(USER_ID, CARD_ID)
/
ALTER TABLE ORDER_NT ADD CONSTRAINT ORDER_NT_PK PRIMARY KEY (USER_ID, CARD_ID) USING INDEX
/

I loaded the same data into 4 tables above
Total distinct value of USER_ID+CARD_ID IS 20,602
each distinct USER_ID+CARD_ID has 1000 items

-- ROWCOUNTS

SELECT COUNT(1) FROM ORDER_IOT
/
20602000
SELECT COUNT(1) FROM ORDER_VAR
20602
/
SELECT COUNT(1) FROM ORDER_LOB
20602
/
SELECT COUNT(1) FROM ORDER_NT
20602
/


-- SPACE
--
-- ORDER_IOT
--
SHOW_SPACE('ORDER_IOT_PK','USER', 'INDEX');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 226
Total Blocks............................ 1,024
Total Bytes............................. 8,388,608
Total MBytes............................ 8
Unused Blocks........................... 780
Unused Bytes............................ 6,389,760
Last Used Ext FileId.................... 5
Last Used Ext BlockId................... 191,488
Last Used Block......................... 244

-- ORDER_VAR
--
SHOW_SPACE('ORDER_VAR','USER');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 226
Total Blocks............................ 1,024
Total Bytes............................. 8,388,608
Total MBytes............................ 8
Unused Blocks........................... 780
Unused Bytes............................ 6,389,760
Last Used Ext FileId.................... 5
Last Used Ext BlockId................... 191,488
Last Used Block......................... 244

SHOW_SPACE('ORDER_VAR_PK','USER', 'INDEX');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 1
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 131
Total Blocks............................ 256
Total Bytes............................. 2,097,152
Total MBytes............................ 2
Unused Blocks........................... 112
Unused Bytes............................ 917,504
Last Used Ext FileId.................... 6
Last Used Ext BlockId................... 81,664
Last Used Block......................... 16

SHOW_SPACE('SYS_LOB0000090713C00003$$','USER', 'LOB');
Unformatted Blocks ..................... 565
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 42,189
Total Blocks............................ 43,008
Total Bytes............................. 352,321,536
Total MBytes............................ 336
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 5
Last Used Ext BlockId................... 535,936
Last Used Block......................... 1,024

TOTAL SPACE USED FOR VARRAY = TABLE+INDEX+LOB = 8+2+336 = 346MB

-- CLOB
--
SHOW_SPACE('ORDER_LOB','USER');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 284
Total Blocks............................ 1,024
Total Bytes............................. 8,388,608
Total MBytes............................ 8
Unused Blocks........................... 722
Unused Bytes............................ 5,914,624
Last Used Ext FileId.................... 5
Last Used Ext BlockId................... 430,720
Last Used Block......................... 302

SHOW_SPACE('ORDER_LOB_PK','USER', 'INDEX');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 17
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 227
Total Blocks............................ 256
Total Bytes............................. 2,097,152
Total MBytes............................ 2
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 6
Last Used Ext BlockId................... 82,048
Last Used Block......................... 128

SHOW_SPACE('SYS_LOB0000091265C00003$$','USER', 'LOB');
Unformatted Blocks ..................... 315
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 84,258
Total Blocks............................ 84,992
Total Bytes............................. 696,254,464
Total MBytes............................ 664
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 5
Last Used Ext BlockId................... 514,304
Last Used Block......................... 1,024

TOTAL SPACE USED FOR CLOB = TABLE+INDEX+LOB = 8+2+664 = 674 MB

--
-- NESTED TABLE AS IOT
--
SHOW_SPACE('ORDER_NT','USER');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 204
Total Blocks............................ 1,024
Total Bytes............................. 8,388,608
Total MBytes............................ 8
Unused Blocks........................... 802
Unused Bytes............................ 6,569,984
Last Used Ext FileId.................... 6
Last Used Ext BlockId................... 114,304
Last Used Block......................... 222

SHOW_SPACE('ORDER_NT_PK','USER');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 15
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 229
Total Blocks............................ 256
Total Bytes............................. 2,097,152
Total MBytes............................ 2
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 6
Last Used Ext BlockId................... 206,848
Last Used Block......................... 128


SHOW_SPACE('SYS_IOT_TOP_91716','USER', 'INDEX');
anonymous block completed
Unformatted Blocks ..................... 252
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 59
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 41,406
Total Blocks............................ 41,984
Total Bytes............................. 343,932,928
Total MBytes............................ 328
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 6
Last Used Ext BlockId................... 220,288
Last Used Block......................... 1,024

TOTAL SPACE USED FOR NESTED TABLE = TABLE+INDEX+LOB = 8+2+328 = 338 MB




NOW check the query plan

set autotrace traceonly
--
-- IOT
--
SELECT ITEM_ID
FROM ORDER_IOT a
WHERE USER_ID ='1000000000000000000'
and CARD_ID ='1111-2222-3333-4444-5555'
/

Plan hash value: 657524096

------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 4 (0)| 00:00:01 | | |
|* 1 | INDEX RANGE SCAN | ORDER_IOT_PK | 1 | 42 | 4 (0)| 00:00:01 | 1 | 1 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("USER_ID"='1000000000000000000' AND "CARD_ID"='1111-2222-3333-4444-5555')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
75 consistent gets
0 physical reads
0 redo size
18899 bytes sent via SQL*Net to client
1250 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed



--
-- VAR
--
SELECT ITEM_ID_LIST
FROM ORDER_VAR a
WHERE USER_ID ='1000000000000000000'
and CARD_ID ='1111-2222-3333-4444-5555'
/

Execution Plan
----------------------------------------------------------
Plan hash value: 3481436131

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY INDEX ROWID| ORDER_VAR | 1 | 38 | 2 (0)| 00:00:01 | 1 | 1 |
|* 2 | INDEX UNIQUE SCAN | ORDER_VAR_PK | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("USER_ID"='1000000000000000000' AND "CARD_ID"='1111-2222-3333-4444-5555')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
30 consistent gets
0 physical reads
0 redo size
7412 bytes sent via SQL*Net to client
1098 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


--
-- CLOB
--

SELECT ITEM_ID_LIST
FROM ORDER_LOB a
WHERE USER_ID ='1000000000000000000'
and CARD_ID ='1111-2222-3333-4444-5555'
/

Execution Plan
----------------------------------------------------------
Plan hash value: 2238791307

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 148 | 2 (0)| 00:00:01 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ORDER_LOB | 1 | 148 | 2 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX UNIQUE SCAN | ORDER_LOB_PK | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("USER_ID"='1000000000000000000' AND "CARD_ID"='1111-2222-3333-4444-5555')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
2 physical reads
0 redo size
1199 bytes sent via SQL*Net to client
816 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed



--
-- NESTED TABLE
--
SELECT ITEM_ID_LIST
FROM ORDER_LOB a
WHERE USER_ID ='1000000000000000000'
and CARD_ID ='1111-2222-3333-4444-5555'
/

Execution Plan
----------------------------------------------------------
Plan hash value: 2787992437

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 54 | 2 (0)| 00:00:01 | | |
|* 1 | INDEX RANGE SCAN | SYS_IOT_TOP_91716 | 999 | 21978 | 5 (0)| 00:00:01 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ORDER_NT | 1 | 54 | 2 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX UNIQUE SCAN | ORDER_NT_PK | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("NESTED_TABLE_ID"=:B1)
3 - access("USER_ID"='1000000000000000000' AND "CARD_ID"='1111-2222-3333-4444-5555')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
10746 bytes sent via SQL*Net to client
794 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed



SUMMARY :
space usage (MB):
TABLE LOB INDEX TOTAL
IOT 0 0 656 656
VAR 8 336 2 340
CLOB 8 664 2 674
NT 8 0 330 338

EXECUTION PLAN
consistent gets physical reads plan cost row(s) returned
IOT 75 0 4 1001
VAR 30 0 2 1
CLOB 5 2 2 1
NT 14 0 5 1


seems the NESTED table option has the best result on both space and execution plan.
then follow by VARRAY, IOT and CLOB.

I learned that NESTED table is not good practice to store data, is something wrong here ?javascript:apex.submit('SUBMIT_REVIEW');

What is the best approach to store the data with this volume ?

Thanks !

Thanks for quick response !

Yes, partition(hash on user_id) is going to be used in this case.

My question is why the collection(such as VARRAY, Nested Table) using much fewer storage than IOT does(using IOT Compress 2 shows the same result)?

ops$tkyte%ORA11GR2> CREATE OR REPLACE TYPE ITEM_ID_REC AS OBJECT (ITEM_ID NUMBER(8,0))
  2  /

Type created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> CREATE  TYPE ITEM_ID_TAB AS TABLE OF ITEM_ID_REC
  2  /

Type created.

ops$tkyte%ORA11GR2> CREATE TABLE ORDER_NT
  2  (
  3   USER_ID           VARCHAR2(24)
  4  ,CARD_ID           VARCHAR2(40)
  5  ,ITEM_ID_LIST      ITEM_ID_TAB
  6  ,DTTM              TIMESTAMP
  7  )
  8  nested table ITEM_ID_LIST store as ITEM_ID_NT_IOT
  9  ( (PRIMARY KEY(NESTED_TABLE_ID, ITEM_ID ))
 10     ORGANIZATION INDEX COMPRESS 1
 11  )
 12  /

Table created.

ops$tkyte%ORA11GR2> CREATE UNIQUE INDEX ORDER_NT_PK ON ORDER_NT(USER_ID, CARD_ID)
  2  /

Index created.

ops$tkyte%ORA11GR2> ALTER TABLE ORDER_NT ADD CONSTRAINT ORDER_NT_PK PRIMARY KEY (USER_ID, CARD_ID) USING INDEX
  2  /

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select segment_name, segment_type from user_segments;

SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------
ORDER_NT                       TABLE
SYS_IOT_TOP_99743              INDEX
SYS_C0023722                   INDEX
ORDER_NT_PK                    INDEX

Hi Tom,

I did some run_stats analysis on three different types(IOT,VARRAY, NESTED TABLE). Each test will run 10000 queries by given user_id and card_id and query result is a type with a list of items ids.

The result confirmed that IOT does outperform the Nested Table, but the interesting thing is that the VARRAY did outperform the IOT in my test case.

Here are the test results
--
-- IOT vs Nested Table:
--
SET SERVEROUTPUT ON
BEGIN
runStats_pkg.rs_start;
RUN_IOT;
runstats_Pkg.rs_middle;
RUN_NT;
runstats_pkg.rs_stop(1000);
END;
/
Run1 ran in 3353 hsecs
Run2 ran in 18604 hsecs
run 1 ran in 18.02% of the time

Name Run1 Run2 Diff
LATCH.JS queue state obj latch 288 1,332 1,044
LATCH.Real-time plan statistic 1,083 3,246 2,163
LATCH.messages 481 2,724 2,243
LATCH.enqueues 634 2,958 2,324
LATCH.enqueue hash chains 643 3,026 2,383
LATCH.SQL memory manager worka 740 4,157 3,417
LATCH.object queue header heap 713 4,145 3,432
LATCH.object queue header oper 713 4,480 3,767
LATCH.simulator hash latch 3,003 11,164 8,161
LATCH.shared pool 36 10,224 10,188
STAT...recursive cpu usage 3,302 18,550 15,248
STAT...CPU used by this sessio 3,357 18,607 15,250
STAT...Elapsed Time 3,357 18,608 15,251
STAT...calls to get snapshot s 10,001 29,999 19,998
STAT...no work - consistent re 50,000 30,000 -20,000
STAT...consistent gets from ca 50,009 30,009 -20,000
STAT...index fetch by key 0 29,998 29,998
STAT...table fetch by rowid 0 29,998 29,998
STAT...rows fetched via callba 0 29,998 29,998
LATCH.cache buffers chains 130,225 171,181 40,956
STAT...consistent gets from ca 80,017 140,012 59,995
STAT...consistent gets 80,017 140,012 59,995
STAT...session logical reads 80,049 140,048 59,999
LATCH.checkpoint queue latch 14,113 80,088 65,975
STAT...buffer is not pinned co 10,000 79,996 69,996
STAT...consistent gets - exami 30,008 110,003 79,995
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
153,741 304,497 150,756 50.49%


-- IOT vs VARRAY
BEGIN
runStats_pkg.rs_start;
RUN_IOT;
runstats_Pkg.rs_middle;
RUN_VAR;
runstats_pkg.rs_stop(1000);
END;
Run1 ran in 3359 hsecs
Run2 ran in 1360 hsecs
run 1 ran in 246.99% of the time

Name Run1 Run2 Diff
LATCH.Real-time plan statistic 1,084 0 -1,084
STAT...recursive cpu usage 3,278 1,329 -1,949
STAT...CPU used by this sessio 3,362 1,364 -1,998
STAT...Elapsed Time 3,362 1,364 -1,998
LATCH.simulator hash latch 3,008 5 -3,003
LATCH.checkpoint queue latch 15,201 5,217 -9,984
STAT...rows fetched via callba 0 10,000 10,000
STAT...index scans kdiixs1 10,000 0 -10,000
STAT...index fetch by key 0 10,000 10,000
STAT...table fetch by rowid 0 10,000 10,000
STAT...session logical reads 80,064 60,104 -19,960
STAT...consistent gets from ca 80,022 60,025 -19,997
STAT...consistent gets 80,022 60,025 -19,997
STAT...buffer is not pinned co 10,000 30,000 20,000
STAT...consistent gets from ca 50,009 30,008 -20,001
STAT...lob reads 0 30,000 30,000
LATCH.cache buffers chains 130,292 90,346 -39,946
STAT...no work - consistent re 50,000 0 -50,000
STAT...session pga memory 65,536 0 -65,536
STAT...session cursor cache hi 10,001 -55,535 -65,536

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
154,824 97,561 -57,263 158.69%

Good Morning Tom.

Thanks for the quick response.

Here is the test code. QUERY is used most of time.

I will use runstats package to evaluate the Transaction later.


CREATE OR REPLACE TYPE ITEM_ID_LIST AS VARRAY(5000) OF NUMBER(8,0)
/
CREATE OR REPLACE PROCEDURE RUN_IOT
IS
lv_ITEM_ID ITEM_ID_LIST ;
BEGIN

FOR i IN 1 .. 1000
LOOP
SELECT ITEM_ID BULK COLLECT INTO lv_ITEM_ID FROM ORDER_IOT
WHERE USER_ID = '000000000000000' AND CARD_ID ='1111-2222-3333-4440';
SELECT ITEM_ID BULK COLLECT INTO lv_ITEM_ID FROM ORDER_IOT
WHERE USER_ID = '111111111111111' AND CARD_ID ='1111-2222-3333-4441';
SELECT ITEM_ID BULK COLLECT INTO lv_ITEM_ID FROM ORDER_IOT
WHERE USER_ID = '222222222222222' AND CARD_ID ='1111-2222-3333-4442';
SELECT ITEM_ID BULK COLLECT INTO lv_ITEM_ID FROM ORDER_IOT
WHERE USER_ID = '333333333333333' AND CARD_ID ='1111-2222-3333-4443';
SELECT ITEM_ID BULK COLLECT INTO lv_ITEM_ID FROM ORDER_IOT
WHERE USER_ID = '444444444444444' AND CARD_ID ='1111-2222-3333-4444';
SELECT ITEM_ID BULK COLLECT INTO lv_ITEM_ID FROM ORDER_IOT
WHERE USER_ID = '555555555555555' AND CARD_ID ='1111-2222-3333-4445';
SELECT ITEM_ID BULK COLLECT INTO lv_ITEM_ID FROM ORDER_IOT
WHERE USER_ID = '666666666666666' AND CARD_ID ='1111-2222-3333-4446';
SELECT ITEM_ID BULK COLLECT INTO lv_ITEM_ID FROM ORDER_IOT
WHERE USER_ID = '777777777777777' AND CARD_ID ='1111-2222-3333-4447';
SELECT ITEM_ID BULK COLLECT INTO lv_ITEM_ID FROM ORDER_IOT
WHERE USER_ID = '888888888888888' AND CARD_ID ='1111-2222-3333-4448';
SELECT ITEM_ID BULK COLLECT INTO lv_ITEM_ID FROM ORDER_IOT
WHERE USER_ID = '999999999999999' AND CARD_ID ='1111-2222-3333-4449';
END LOOP;
END;
/
CREATE OR REPLACE PROCEDURE RUN_VAR
IS
lv_ITEM_ID ITEM_ID_LIST;
BEGIN

FOR i IN 1 .. 1000
LOOP
SELECT ITEM_ID INTO lv_ITEM_ID FROM ORDER_VAR
WHERE USER_ID = '000000000000000' AND CARD_ID ='1111-2222-3333-4440';
SELECT ITEM_ID INTO lv_ITEM_ID FROM ORDER_VAR
WHERE USER_ID = '111111111111111' AND CARD_ID ='1111-2222-3333-4441';
SELECT ITEM_ID INTO lv_ITEM_ID FROM ORDER_VAR
WHERE USER_ID = '222222222222222' AND CARD_ID ='1111-2222-3333-4442';
SELECT ITEM_ID INTO lv_ITEM_ID FROM ORDER_VAR
WHERE USER_ID = '333333333333333' AND CARD_ID ='1111-2222-3333-4443';
SELECT ITEM_ID INTO lv_ITEM_ID FROM ORDER_VAR
WHERE USER_ID = '444444444444444' AND CARD_ID ='1111-2222-3333-4444';
SELECT ITEM_ID INTO lv_ITEM_ID FROM ORDER_VAR
WHERE USER_ID = '555555555555555' AND CARD_ID ='1111-2222-3333-4445';
SELECT ITEM_ID INTO lv_ITEM_ID FROM ORDER_VAR
WHERE USER_ID = '666666666666666' AND CARD_ID ='1111-2222-3333-4446';
SELECT ITEM_ID INTO lv_ITEM_ID FROM ORDER_VAR
WHERE USER_ID = '777777777777777' AND CARD_ID ='1111-2222-3333-4447';
SELECT ITEM_ID INTO lv_ITEM_ID FROM ORDER_VAR
WHERE USER_ID = '888888888888888' AND CARD_ID ='1111-2222-3333-4448';
SELECT ITEM_ID INTO lv_ITEM_ID FROM ORDER_VAR
WHERE USER_ID = '999999999999999' AND CARD_ID ='1111-2222-3333-4449';
END LOOP;
END;
/

connect /

set echo on
/*

drop table order_iot purge;
drop table order_var purge;
drop type item_id_var purge;

define nusers=1000
define ncards=10
define nitems=5000

CREATE TABLE ORDER_IOT
(
 USER_ID          VARCHAR2(24)
,CARD_ID          VARCHAR2(40)
,ITEM_ID          INTEGER
,DTTM             TIMESTAMP
,CONSTRAINT ORDER_IOT_PK PRIMARY KEY(USER_ID,CARD_ID,ITEM_ID)
)
ORGANIZATION INDEX COMPRESS 2
/

CREATE OR REPLACE TYPE ITEM_ID_VAR AS VARRAY(5000) OF NUMBER(8,0)
/
CREATE TABLE ORDER_VAR
(
 USER_ID           VARCHAR2(24)
,CARD_ID           VARCHAR2(40)
,ITEM_ID_LIST      ITEM_ID_VAR
,DTTM              TIMESTAMP
)
/
CREATE UNIQUE INDEX ORDER_VAR_PK ON ORDER_VAR(USER_ID, CARD_ID)
/
ALTER TABLE ORDER_VAR ADD CONSTRAINT ORDER_VAR_PK PRIMARY KEY (USER_ID, CARD_ID) USING INDEX
/

insert into order_iot (user_id, card_id, item_id, dttm)
with
users as (select level user_id from dual connect by level <= &nusers),
cards as (select level card_id from dual connect by level <= &ncards),
items as (select level item_id from dual connect by level <= &nitems)
select user_id, card_id, item_id, systimestamp
  from users, cards, items;

insert into order_var (user_id, card_id, item_id_list, dttm)
with
users as (select level user_id from dual connect by level <= &nusers),
cards as (select level card_id from dual connect by level <= &ncards),
items as (select cast( multiset(select level l from dual connect by level <= &nitems) as item_id_var) item_id
            from dual)
select user_id, card_id, i.item_id, systimestamp
  from users, cards, items i;

exec dbms_stats.gather_table_stats( user, 'ORDER_IOT' );
exec dbms_stats.gather_table_stats( user, 'ORDER_VAR' );
*/


create or replace procedure run_iot
as
    l_data item_id_var;
begin
    for i in 1 .. &nusers
    loop
        for j in 1 .. &ncards
        loop
            select item_id bulk collect into l_data from order_iot
            where user_id = to_char(i) and card_id = to_char(j);
            for k in 1 .. l_data.count
            loop
                if ( l_data(i) is null ) then raise program_error; end if;
            end loop;
        end loop;
    end loop;
end;
/

create or replace procedure run_var
as
    l_data item_id_var;
begin
    for i in 1 .. &nusers
    loop
        for j in 1 .. &ncards
        loop
            select item_id_list into l_data from order_var 
            where user_id = to_char(i) and card_id = to_char(j);
            for k in 1 .. l_data.count
            loop
                if ( l_data(i) is null ) then raise program_error; end if;
            end loop;
        end loop;
    end loop;
end;
/

exec runStats_pkg.rs_start;
exec run_iot;
exec runStats_pkg.rs_middle;
exec run_var;
exec runStats_pkg.rs_stop;

Thank you very much, Tom !

I will post the result once I finish the test.

Tom,

Here is the test result:

drop table order_iot purge;
drop table order_var purge;
drop type item_id_var purge;

define nusers=5000
define ncards=10
define nitems=1000

CREATE TABLE ORDER_IOT
(
USER_ID VARCHAR2(24)
,CARD_ID VARCHAR2(40)
,ITEM_ID INTEGER
,DTTM TIMESTAMP
,CONSTRAINT ORDER_IOT_PK PRIMARY KEY(USER_ID,CARD_ID,ITEM_ID)
)
ORGANIZATION INDEX COMPRESS 2;

CREATE OR REPLACE TYPE ITEM_ID_VAR AS VARRAY(5000) OF NUMBER(8,0);

CREATE UNIQUE INDEX ORDER_VAR_PK ON ORDER_VAR(USER_ID, CARD_ID);

ALTER TABLE ORDER_VAR ADD CONSTRAINT ORDER_VAR_PK PRIMARY KEY (USER_ID, CARD_ID) USING INDEX;

--load data for ORDER_IOT
--
BEGIN
FOR i IN 1 .. &ncards
LOOP
insert /*+ NOLOGGING */ into order_iot (user_id, card_id, item_id, dttm)
with
users as (select level user_id from dual connect by level <= &nusers),
-- cards as (select level card_id from dual connect by level <= &ncards),
items as (select level item_id from dual connect by level <= &nitems)
select LPAD(user_id,20,'0'), LPAD(i, 20, '0'), item_id, systimestamp
from users, items;
COMMIT;
END LOOP;
END;
/
Elapsed: 00:44:53.62

-- load data for ORDER_VAR
BEGIN
FOR i IN 1 .. &ncards
LOOP
insert /*+ NOLOGGING */ into order_var (user_id, card_id, item_id_list, dttm)
with
users as (select level user_id from dual connect by level <= &nusers),
-- cards as (select level card_id from dual connect by level <= &ncards),
items as (select cast( multiset(select level l from dual connect by level <= &nitems)
as
item_id_var) item_id from dual)
select LPAD(user_id,20, '0'), LPAD(i, 20,'0'), item_id, systimestamp
from users, items ;
COMMIT;
END LOOP;
END;
/
Elapsed: 00:04:34.95

-- gather stats
--
--
exec dbms_stats.gather_table_stats( 'TEST', 'ORDER_IOT');
Elapsed: 00:03:27.89

exec dbms_stats.gather_table_stats( 'TEST', 'ORDER_VAR' );
Elapsed: 00:00:01.51

-- Row counts
SELECT COUNT(1) FROM ORDER_IOT;
COUNT(1)
--------------
50000000

Elapsed: 00:01:03.66

SELECT COUNT(1) FROM ORDER_VAR;
COUNT(1)
----------
50000

Elapsed: 00:00:00.22

--
-- First , compare the space usage
--
-- IOT
--
SET SERVEROUTPUT on
BEGIN show_space(
p_segname => 'ORDER_IOT_PK',
p_owner => 'TEST',
p_type => 'INDEX');
END;
Unformatted Blocks ..................... 3,808
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 112
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 151,092
Total Blocks............................ 155,648
Total Bytes............................. 1,275,068,416
Total MBytes............................ 1,216
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 5
Last Used Ext BlockId................... 755,328
Last Used Block......................... 8,192

So the total space usage for ORDER_IOT
Total MBytes: table+index+lob = 0+1,216+0 = 1,216
Total Blocks: table+index+lob = 0+155,648+0 = 155,648

--
-- VARRAY
--
-- TABLE
--
SET SERVEROUTPUT on
BEGIN show_space(
p_segname => 'ORDER_VAR',
p_owner => 'TEST',
p_type => 'TABLE' );
END;

Unformatted Blocks ..................... 62
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 1
FS4 Blocks (75-100)..................... 36
Full Blocks ..................... 649
Total Blocks............................ 768
Total Bytes............................. 6,291,456
Total MBytes............................ 6
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 5
Last Used Ext BlockId................... 705,024
Last Used Block......................... 128


-- IDNEX
BEGIN show_space(
p_segname => 'ORDER_VAR_PK',
p_owner => 'TEST',
p_type => 'INDEX' );
END;
Unformatted Blocks ..................... 48
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 54
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 520
Total Blocks............................ 640
Total Bytes............................. 5,242,880
Total MBytes............................ 5
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 5
Last Used Ext BlockId................... 700,800
Last Used Block......................... 128
Unformatted Blocks ..................... 48
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 54
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 520
Total Blocks............................ 640
Total Bytes............................. 5,242,880
Total MBytes............................ 5
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 5
Last Used Ext BlockId................... 700,800
Last Used Block......................... 128
--
-- LOB
--
SELECT a.SEGMENT_NAME
from user_segments a, user_lobs b
where a.segment_name = b.segment_name
and b.table_name = 'ORDER_VAR';

SYS_LOB0000092761C00003$$

SET SERVEROUTPUT on
BEGIN show_space(
p_segname => 'SYS_LOB0000092761C00003$$',
p_owner => 'TEST',
p_type => 'LOB' );
END;
Unformatted Blocks ..................... 889
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 50,000
Total Blocks............................ 51,200
Total Bytes............................. 419,430,400
Total MBytes............................ 400
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 5
Last Used Ext BlockId................... 706,176
Last Used Block......................... 1,024


So the total space usage for ORDER_VAR
Total MBytes: table+index+lob = 6+5+400 = 411
Total Blocks: table+index+lob = 768+640+51,200 = 52,608


The table order_var is using less than half of space used by order_IOT

--
-- SECOND: COMPARE THE RUN_STATS
--

create or replace procedure run_iot
as
l_data item_id_var;
begin
for i in 1 .. &nusers
loop
for j in 1 .. &ncards
loop
select item_id bulk collect into l_data from order_iot
where user_id = LPAD(i,20,'0') and card_id = LPAD(j,20,'0');
for k in 1 .. l_data.count
loop
if ( l_data(k) is null ) then raise program_error; end if;
end loop;
end loop;
end loop;
end;
/
old 5: for i in 1 .. &nusers
new 5: for i in 1 .. 5000
old 7: for j in 1 .. &ncards
new 7: for j in 1 .. 10

Procedure created.

Elapsed: 00:00:00.14

create or replace procedure run_var
as
l_data item_id_var;
begin
for i in 1 .. &nusers
loop
for j in 1 .. &ncards
loop
select item_id_list into l_data from order_var
where user_id = LPAD(i,20,'0') and card_id = LPAD(j,20,'0');
for k in 1 .. l_data.count
loop
if ( l_data(k) is null ) then raise program_error; end if;
end loop;
end loop;
end loop;
end;
/
old 5: for i in 1 .. &nusers
new 5: for i in 1 .. 5000
old 7: for j in 1 .. &ncards
new 7: for j in 1 .. 10

Procedure created.

Elapsed: 00:00:00.18

-- get the stats:

EXEC runStats_pkg.rs_start;
Elapsed: 00:00:00.32
EXEC run_iot;
Elapsed: 00:13:06.53
EXEC runStats_pkg.rs_middle;
Elapsed: 00:00:00.09
EXEC run_var;
Elapsed: 00:05:12.57
EXEC runStats_pkg.rs_stop;



Run1 ran in 122542 hsecs
Run2 ran in 35509 hsecs
run 1 ran in 345.1% of the time

Name Run1 Run2 Diff
STAT...db block gets from cach 0 1 1
LATCH.intra txn parallel recov 2 1 -1
LATCH.test excl. parent l0 2 1 -1
LATCH.test excl. parent2 l0 2 1 -1
LATCH.longop free list parent 2 1 -1
LATCH.ksim group membership ca 2 1 -1
LATCH.X$KSFQP 1 0 -1
LATCH.ksfv messages 2 1 -1
LATCH.msg queue latch 2 1 -1
LATCH.done queue latch 2 1 -1
LATCH.session queue latch 2 1 -1
LATCH.message pool operations 2 1 -1
LATCH.ksv msg queue latch 2 1 -1
LATCH.second spare latch 2 1 -1
LATCH.third spare latch 2 1 -1
LATCH.fifth spare latch 2 1 -1
LATCH.IPC stats buffer allocat 2 1 -1
LATCH.granule operation 2 1 -1
LATCH.ges process parent latch 2 1 -1
LATCH.ges process hash list 2 1 -1
LATCH.ges resource table freel 2 1 -1
LATCH.ges resource hash list 2 1 -1
LATCH.ges resource scan list 2 1 -1
LATCH.ges enqueue table freeli 2 1 -1
LATCH.KJC message pool free li 2 1 -1
STAT...cursor authentications 1 2 1
STAT...parse time cpu 3 4 1
STAT...cluster key scans 0 1 1
STAT...cluster key scan block 0 1 1
STAT...HSC Heap Segment Block 15 16 1
STAT...Heap Segment Array Inse 15 16 1
STAT...active txn count during 8 9 1
STAT...cleanout - number of kt 8 9 1
STAT...calls to kcmgas 0 1 1
STAT...redo entries 15 16 1
STAT...redo ordering marks 0 1 1
LATCH.KGNFS-NFS:SHM structure 2 1 -1
LATCH.kgnfs mount latch 2 1 -1
LATCH.KGNFS-NFS:SVR LIST 2 1 -1
LATCH.SGA heap locks 2 1 -1
LATCH.SGA pool locks 2 1 -1
LATCH.SGA bucket locks 2 1 -1
LATCH.SGA blob parent 2 1 -1
LATCH.kgb parent 2 1 -1
LATCH.virtual circuit buffers 2 1 -1
LATCH.virtual circuit queues 2 1 -1
LATCH.virtual circuit holder 2 1 -1
LATCH.cp server hash latch 2 1 -1
LATCH.cp pool latch 2 1 -1
LATCH.cp cmon/server latch 2 1 -1
LATCH.query server freelists 2 1 -1
LATCH.process queue 2 1 -1
LATCH.process queue reference 2 1 -1
LATCH.parallel query stats 2 1 -1
LATCH.business card 2 1 -1
LATCH.queuing load statistics 2 1 -1
LATCH.PX hash array latch 2 1 -1
LATCH.KJCT flow control latch 2 1 -1
LATCH.ges domain table 2 1 -1
LATCH.ges group table 2 1 -1
LATCH.gcs resource hash 2 1 -1
LATCH.gcs resource freelist 2 1 -1
LATCH.gcs resource scan list 2 1 -1
LATCH.gcs shadows freelist 2 1 -1
LATCH.gcs commit scn state 2 1 -1
LATCH.name-service namespace b 2 1 -1
LATCH.gcs partitioned table ha 2 1 -1
LATCH.gcs pcm hashed value buc 2 1 -1
LATCH.recovery domain hash lis 2 1 -1
LATCH.ges value block free lis 2 1 -1
LATCH.Testing 2 1 -1
LATCH.heartbeat check 2 1 -1
LATCH.tablespace key chain 2 1 -1
LATCH.sim partition latch 2 1 -1
LATCH.redo copy 2 1 -1
LATCH.gc element 2 1 -1
LATCH.logminer work area 2 1 -1
LATCH.logminer context allocat 2 1 -1
LATCH.logical standby cache 2 1 -1
LATCH.mapped buffers lru chain 2 1 -1
LATCH.lock DBA buffer during m 2 1 -1
LATCH.flashback copy 2 1 -1
LATCH.cvmap freelist lock 2 1 -1
LATCH.io pool granule metadata 2 1 -1
LATCH.SQL memory manager latch 1 0 -1
LATCH.AQ deq hash table latch 2 1 -1
LATCH.XDB unused session pool 2 1 -1
LATCH.XDB used session pool 2 1 -1
LATCH.XDB NFS Security Latch 2 1 -1
LATCH.KFC Hash Latch 2 1 -1
LATCH.KFC FX Hash Latch 2 1 -1
LATCH.ASM map operation hash t 2 1 -1
LATCH.Lsod array latch 2 1 -1
LATCH.I/O Staticstics latch 2 1 -1
LATCH.KFCL LE Freelist 2 1 -1
LATCH.WCR: sync 2 1 -1
LATCH.change notification clie 2 1 -1
LATCH.lob segment query latch 2 1 -1
LATCH.lob segment dispenser la 2 1 -1
LATCH.File State Object Pool P 2 1 -1
LATCH.Write State Object Pool 2 1 -1
LATCH.kdlx hb parent latch 2 1 -1
LATCH.Locator state objects po 2 1 -1
LATCH.second Audit Vault latch 2 1 -1
LATCH.fourth Audit Vault latch 2 1 -1
LATCH.global tx hash mapping 2 1 -1
LATCH.k2q lock allocation 2 1 -1
LATCH.Token Manager 2 1 -1
LATCH.cas latch 2 1 -1
LATCH.rm cas latch 2 1 -1
LATCH.resmgr:actses change sta 2 1 -1
LATCH.resmgr:session queuing 2 1 -1
LATCH.resmgr:plan CPU method 2 1 -1
LATCH.resmgr:resource group CP 2 1 -1
LATCH.QMT 2 1 -1
LATCH.shared pool sim alloc 2 1 -1
LATCH.Streams Generic 2 1 -1
LATCH.Memory Queue 2 1 -1
LATCH.Memory Queue Subscriber 2 1 -1
LATCH.Memory Queue Message Sub 2 1 -1
LATCH.Memory Queue Message Sub 2 1 -1
LATCH.Memory Queue Message Sub 2 1 -1
LATCH.Memory Queue Message Sub 2 1 -1
LATCH.pesom_hash_node 2 1 -1
LATCH.pesom_free_list 2 1 -1
LATCH.peshm 2 1 -1
LATCH.Mutex 2 1 -1
LATCH.Mutex Stats 2 1 -1
LATCH.queued dump request 4 2 -2
STAT...redo subscn max counts 0 2 2
LATCH.temp lob duration state 2 0 -2
LATCH.state object free list 2 0 -2
LATCH.KMG resize request state 2 0 -2
LATCH.object stats modificatio 3 0 -3
STAT...workarea executions - o 2 5 3
STAT...parse time elapsed 4 7 3
LATCH.JS Sh mem access 4 1 -3
STAT...sorts (rows) 0 3 3
STAT...consistent changes 31 34 3
STAT...calls to kcmgcs 25 28 3
STAT...SQL*Net roundtrips to/f 2 5 3
LATCH.FIB s.o chain latch 4 0 -4
LATCH.JS mem alloc latch 4 0 -4
LATCH.buffer pool 5 1 -4
STAT...user calls 4 8 4
STAT...db block changes 46 51 5
STAT...parse count (hard) 2 7 5
LATCH.KTF sga latch 7 2 -5
LATCH.JS queue access latch 6 1 -5
LATCH.job workq parent latch 7 2 -5
STAT...enqueue releases 2 8 6
STAT...enqueue requests 2 8 6
STAT...sorts (memory) 2 8 6
LATCH.lob segment hash table l 8 1 -7
STAT...db block gets 31 41 10
LATCH.MinActiveScn Latch 14 4 -10
STAT...db block gets from cach 31 41 10
STAT...session cursor cache hi -15,531 -15,520 11
LATCH.internal temp table obje 12 0 -12
LATCH.job_queue_processes free 14 2 -12
STAT...opened cursors cumulati 50,010 50,024 14
LATCH.cp sga latch 20 5 -15
LATCH.dml lock allocation 20 5 -15
LATCH.deferred cleanup latch 20 5 -15
LATCH.ASM network state latch 20 5 -15
LATCH.ncodef allocation latch 20 5 -15
LATCH.JS slv state obj latch 22 5 -17
STAT...parse count (total) 11 28 17
STAT...workarea memory allocat 20 1 -19
LATCH.kss move lock 24 3 -21
STAT...execute count 50,010 50,032 22
LATCH.Memory Management Latch 25 1 -24
STAT...calls to get snapshot s 50,008 50,035 27
LATCH.ksv class latch 40 11 -29
LATCH.ktm global data 34 4 -30
STAT...pinned buffers inspecte 31 0 -31
LATCH.kwqbsn:qsga 44 12 -32
LATCH.Shared B-Tree 46 13 -33
STAT...session cursor cache co 0 -34 -34
LATCH.sort extent pool 41 6 -35
LATCH.threshold alerts latch 49 7 -42
LATCH.parameter list 62 14 -48
LATCH.ksv allocation latch 61 12 -49
LATCH.cache buffer handles 130 78 -52
LATCH.cache table scan latch 55 0 -55
LATCH.begin backup scn array 56 0 -56
LATCH.loader state object free 58 0 -58
LATCH.ksuosstats global area 84 24 -60
LATCH.file cache latch 62 0 -62
STAT...undo change vector size 3,440 3,508 68
LATCH.global KZLD latch for me 103 32 -71
LATCH.resmgr group change latc 113 34 -79
LATCH.hash table modification 80 0 -80
LATCH.resmgr:schema config 81 0 -81
LATCH.resmgr:actses change gro 121 35 -86
LATCH.Event Group Locks 126 36 -90
LATCH.compile environment latc 129 36 -93
LATCH.hash table column usage 76 191 115
LATCH.PL/SQL warning settings 193 72 -121
LATCH.qmn task queue latch 179 51 -128
LATCH.kokc descriptor allocati 158 0 -158
STAT...recursive calls 50,046 50,213 167
LATCH.process group creation 246 73 -173
LATCH.OS process: request allo 246 73 -173
LATCH.ksz_so allocation latch 246 73 -173
LATCH.channel handle pool latc 248 74 -174
LATCH.resmgr:free threads list 256 75 -181
LATCH.transaction allocation 253 71 -182
LATCH.dummy allocation 258 76 -182
LATCH.resmgr:active threads 257 75 -182
LATCH.multiblock read objects 188 2 -186
LATCH.list of block allocation 258 70 -188
LATCH.process allocation 267 76 -191
LATCH.job_queue_processes para 270 78 -192
LATCH.session state list latch 311 87 -224
LATCH.Consistent RBA 425 199 -226
LATCH.post/wait queue 323 94 -229
LATCH.parallel query alloc buf 330 97 -233
LATCH.FOB s.o list latch 310 74 -236
STAT...redo size 4,520 4,764 244
LATCH.transaction branch alloc 408 118 -290
LATCH.session timer 408 118 -290
LATCH.KMG MMAN ready and start 409 118 -291
LATCH.Change Notification Hash 409 118 -291
LATCH.mostly latch-free SCN 617 259 -358
LATCH.lgwr LWN SCN 617 259 -358
LATCH.OS process 494 133 -361
LATCH.parameter table manageme 522 147 -375
STAT...shared hash latch upgra 780 1,189 409
LATCH.session switching 632 196 -436
LATCH.sequence cache 569 99 -470
LATCH.DML lock allocation 1,147 611 -536
LATCH.client/application info 771 231 -540
LATCH.In memory undo latch 705 160 -545
STAT...dirty buffers inspected 579 33 -546
LATCH.session allocation 742 189 -553
LATCH.statistics aggregation 560 0 -560
LATCH.ASM db client latch 814 236 -578
STAT...bytes received via SQL* 599 1,199 600
LATCH.call allocation 783 157 -626
LATCH.undo global data 1,955 965 -990
LATCH.simulator lru latch 1,045 34 -1,011
STAT...bytes sent via SQL*Net 342 1,457 1,115
LATCH.space background task la 1,642 332 -1,310
LATCH.session idle bit 1,947 483 -1,464
LATCH.OS process allocation 2,686 767 -1,919
LATCH.redo allocation 3,462 1,384 -2,078
LATCH.shared pool simulator 2,376 68 -2,308
LATCH.active service list 3,299 930 -2,369
LATCH.active checkpoint queue 4,062 993 -3,069
LATCH.cache buffers lru chain 4,097 382 -3,715
LATCH.redo writing 6,195 1,838 -4,357
LATCH.channel operations paren 6,961 2,013 -4,948
LATCH.JS queue state obj latch 8,926 2,556 -6,370
STAT...session uga memory 10,696 0 -10,696
STAT...recursive cpu usage 19,428 7,469 -11,959
STAT...CPU used by this sessio 22,351 10,121 -12,230
STAT...CPU used when call star 22,376 10,121 -12,255
STAT...hot buffers moved to he 13,979 1,215 -12,764
LATCH.messages 20,271 5,682 -14,589
LATCH.simulator hash latch 37,501 22,276 -15,225
LATCH.enqueues 21,201 5,854 -15,347
LATCH.Real-time plan statistic 22,786 6,511 -16,275
LATCH.enqueue hash chains 23,806 7,228 -16,578
LATCH.SQL memory manager worka 27,586 7,917 -19,669
STAT...consistent gets from ca 73,687 50,067 -23,620
LATCH.AWR Alerted Metric Eleme 25,693 0 -25,693
LATCH.object queue header heap 34,209 8,203 -26,006
LATCH.shared pool 25,103 53,294 28,191
STAT...user I/O wait time 57,312 21,554 -35,758
STAT...non-idle wait time 57,312 21,554 -35,758
STAT...DB time 78,682 31,281 -47,401
STAT...index scans kdiixs1 50,004 22 -49,982
STAT...rows fetched via callba 0 50,008 50,008
STAT...index fetch by key 0 50,009 50,009
STAT...table fetch by rowid 10 50,048 50,038
STAT...consistent gets - exami 150,014 200,061 50,047
STAT...session logical reads 352,657 300,187 -52,470
STAT...consistent gets 352,626 300,146 -52,480
STAT...consistent gets from ca 352,626 300,146 -52,480
STAT...session pga memory -65,536 0 65,536
STAT...free buffer inspected 90,202 18,952 -71,250
STAT...non-idle wait count 129,707 51,214 -78,493
STAT...free buffer requested 129,705 51,210 -78,495
STAT...physical reads cache 129,705 51,209 -78,496
STAT...physical read IO reques 129,705 51,209 -78,496
STAT...physical reads 129,705 51,209 -78,496
STAT...physical read total IO 129,705 51,209 -78,496
LATCH.row cache objects 86,072 4,703 -81,369
STAT...Elapsed Time 122,551 35,515 -87,036
STAT...buffer is not pinned co 52,903 150,132 97,229
STAT...lob reads 0 100,000 100,000
LATCH.object queue header oper 299,654 110,980 -188,674
STAT...no work - consistent re 202,603 75 -202,528
LATCH.checkpoint queue latch 543,841 152,240 -391,601
LATCH.cache buffers chains 869,295 462,783 -406,512
STAT...file io wait time 573,125,727 215,538,567-357,587,160
STAT...cell physical IO interc############ 419,504,128-643,039,232
STAT...physical read bytes ############ 419,504,128-643,039,232
STAT...physical read total byt############ 419,504,128-643,039,232

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
2,103,653 865,767 -1,237,886 242.98%

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.27

Thanks !

Thanks for the info about the hint.

"in real life do you load ALL of the information for ALL USERS for a given single card number? Is that how the data will truly arrive?"

Kind of. Basically the upstream system (have many clients) send out the full set of info about given user and card_id once there is any change to the user_id and card_id. we will pick up those data (in xml events) from AQ and process them in our system. the process result will be used for our high frequency query only applications, and the query is asking full list of items and other informations.

Thanks again !

Sir,
from your book 'Expert one-to-one'

"
tkyte@TKYTE816> select name, home_address.state, work_address.state
2 from people
3 /
select name, home_address.state, work_address.state
*
ERROR at line 1:
ORA-00904: invalid column name
tkyte@TKYTE816> select name, P.home_address.state, P.work_address.state
2 from people P
3 /
NAME HOME_ADDRESS.STATE WORK_ADDRESS.STATE
---------- -------------------- --------------------
Tom Kyte VA CA
I’ve shown both the incorrect and correct method to do this. The first example is probably what most
people would naturally try. It obviously does not work. To access the components of an object type, we
must use a correlation name, as I did in the second query. Here I alias the table PEOPLE with P (any
valid identifier could have been used, including the word PEOPLE itself). Then, when I want to
reference the individual components of the addresses, I use the alias."



but why it is must to use alias ? is it becuase of the way the TYPE was defined at schema level ?

I like using O-R views better than object tables in most circumstances (especially if you have some vendor packages that don't let you write your own SQL and don't handle OR). Like most things there are a few trade-offs you have to be aware of (assuming you're not going to materialize the views). If Tom has any ways around these issues I'd love to hear them. :)

1. The optimizer may not be able to optimize queries against views as well as it can with queries against physical tables in some circumstances.

2. You can't use locators. You must always bring back the child objects which will cause Oracle to retrieve objects you may never need. At best you can make a table of REFs but Oracle will still need to scan an index to build the REFs you'll never use.

3. You can't index the functions on the objects.

4. If you need a domain index on an attribute of the type ( such as a spatial index ) you're out of luck. Even if you have the domain index on the underlying table the data cartridge functions want the column that's indexed - not the view column.

Usually if you run into serious problems you still have the option of swapping out the view for a table with the same name.

Hi Tom,

What is the difference between overriding and hiding? The below definitions are from oracle documentation
"Redefining an inherited method to customize its behavior in a subtype is called overriding, in the case of member methods, or hiding, in the case of static methods."

Ref: http://docs.oracle.com/cd/B28359_01/appdev.111/b28371/adobjbas.htm

It would be great help if you can provide me a simple example.
When to use overriding and when to use hiding methods? Thanks a lot for your help.

Thanks for your explanations. Could you please give a simple example for overriding vs hiding?