Thread: varray overhead in storage
08-11-09, 11:50 #1Registered User
- Join Date
- Jul 2009
Unanswered: varray overhead in storage
I observe significant overhead in space usage by a table containing a varray where element of varray is a UDT.
Oracle documentation says that the varray storage takes "slightly" more than the number of bytes per element x number of elements (and not the maxsize of varray).
Design Considerations for Oracle Objects
But I found the overhead to be much more.
Attaching a sample case below:
drop table test_tab;
drop type test_array;
drop type test_type;
create type test_type as object (
b varchar (2000)
create type test_array as varray(100) of test_type;
create table test_tab(x number, y test_array);
i number := 0;
insert into test_tab values (i,test_array(test_type(1,'abc'),test_type(2,'def' ),test_type(3,'ghi')));
i := i+1;
exit when i = 4352;
select bytes from user_segments where lower(segment_name) like 'test_tab'
--This returns 589824 bytes in my db. (The table adds 64K per extension)
select sum(vsize(x)) from test_tab;
--This returns 12912 bytes
select sum(vsize(x.a)), sum(vsize(x.b)), sum(vsize(x.a)) + sum(vsize(x.b)) from test_tab t, table(t.y) x
--This returns a total of 65280 bytes
So I assume that the actual space consumed by data - (overhead of any kind for table, varray, udt) is
12912 + 65280 = 78252 bytes.
My table consumes 589824 bytes. I'm wondering what is occupying 589824 - 78252 = 511572 bytes!
Even if I ignore the final 64k added to the table, still a lot!
I've also observed that the size varies with change in maxsize or size of varchar inside the udt. But still an unexplainable lot remains as the difference.
Appreciate any thoughts/suggestions.
08-11-09, 12:51 #2:-)
Provided Answers: 1Originally Posted by Tom1234---
- Join Date
- Jun 2003
- Toronto, Canada
"It does not work" is not a valid problem statement.
08-11-09, 13:18 #3Registered User
Provided Answers: 1
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
>Appreciate any thoughts/suggestions.
please explain how having VARRAY inside a table conforms to Third Normal Form.
Just because something can be done, does not necessarily mean it is a good idea to do soYou can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.