Results 1 to 3 of 3
  1. #1
    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 (
    a number,
    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;
    end loop;

    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.

  2. #2
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    Quote Originally Posted by Tom1234

    Appreciate any thoughts/suggestions.
    May be you should try specifying the precision of your numbers...
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    >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 so
    You 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts