PL/SQL Tables - Memory Allocation Puzzle

From: <jobrien99_at_my-deja.com>
Date: Thu, 02 Dec 1999 00:13:32 GMT
Message-ID: <824dj8$77m$1@nnrp1.deja.com>

Can anyone explain this:

Run:
 declare

     type t_tab is table of varchar2(30) index by binary_integer;
     l_tab  t_tab;
  begin
     for i in 1 .. 1000 loop
        l_tab(i*10) := 'AAA';
     end loop;

  end;

Check memory ( ps v on aix )
then run

 declare

     type t_tab is table of varchar2(30) index by binary_integer;
     l_tab  t_tab;
  begin
     for i in 1 .. 1000 loop
        l_tab(i*1000) := 'AAA'; -- KEY CHANGE
     end loop;

  end;

Check memory ( ps v on aix )

and the memory used for each process varies greatly even though the number of elements is the same , just the number of empty elements betweenn them varies. PL/SQL tables are sparse structures ?

Some output ( Platform AIX - ps v | grep pid , Oracle 8.0 ):

   PID TTY STAT TIME PGIN SIZE RSS LIM TSIZ TRS %CPU %MEM COMMAND
 28048 - A 0:00 1 936 9224 xx 15969 8220 0.0 0.0 oracleRDP

Above the data is 936 K used for a process that has 1000 elements qith a gap of 10.

   PID TTY STAT TIME PGIN SIZE RSS LIM TSIZ TRS %CPU %MEM COMMAND
 28048 - A 0:00 1 5000 13288 xx 15969 8220 0.0 1.0 oracleRDP

Above here is 1000 elements with 1000 gaps and the memory is 5000K

Am I losing about 4M for nothing ?

Anybody got any ideas ? or can anyone expand on the problem ?

When programming with PL/SQL tables I will be taking this into account especially when using hashing and similar schemes.

Regards,
John

P.S
If I was to guess, I would say this has to do with ranges of numbers being held in different buckets. The more I spread the data the more buckets there are. So its not really the gap but the ranges that causes the memory allocation. If I had raesonable confidence in that, I'd state it but its just a guess, something to get the ball rolling.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Dec 01 1999 - 18:13:32 CST