Skip to Main Content
  • Questions

  • Pl/sql table type declaration approaches, which is better.

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Arvind.

Asked: August 14, 2002 - 2:43 pm UTC

Last updated: September 29, 2009 - 11:57 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,
If we have some plsql table type o/p parameters in some oracle stored procedure like as follows:

create table emp( empname varchar2(80));

create or replace package pck1 is
type tp_varchar2 is table of varchar2(100) index by binary_integer;
procedure proc( var1 out tp_varchar2);
end;

create or replace package pck2 is
type tp_varchar2 is table of emp.empname%type index by binary_integer;
procedure proc( var1 out tp_varchar2);
end;

The only change is this, that in first package I have fixed length of 100 for plsql table type while in other I have table field length which is dynamic.

Lets assume while returning data o/p variable var1 is returning max data length 50 in all records in plsql table. So will it use the same amount of memory while returning data in both approaches or it will be different.
which one is better in your opinion, since by using first approach I can use only one declared type in other o/p parameters also where returning length is <= 100, but by using second approach I need define as many plsql table types as many number of plsql table o/p parameters.

Thanks for any help.
Arvind Kapil Sharma


and Tom said...

Me, I would just use varchar2(some_big_number) once. The data in the table is allocated using varying length strings. varchar2(100), (20), (10000) -- they will consume the same space if you put the same strings in.


ops$tkyte@ORA9I.WORLD> !cat test.sql
declare
type array is table of varchar2(&1) index by binary_integer;

l_data array;
begin
for i in 1 .. 1000
loop
l_data(i) := rpad( '*', 20, '*' );
end loop;
end;
/
@mystat pga



ops$tkyte@ORA9I.WORLD> @test 100
old 2: type array is table of varchar2(&1) index by binary_integer;
new 2: type array is table of varchar2(100) index by binary_integer;

PL/SQL procedure successfully completed.

old 4: and lower(a.name) like '%' || lower('&1')||'%'
new 4: and lower(a.name) like '%' || lower('pga')||'%'

NAME VALUE
------------------------------ ----------
session pga memory 237344
session pga memory max 237344

ops$tkyte@ORA9I.WORLD> Disconnected from Oracle9i Enterprise Edition Release 9.0.1.2.0 - Production
With the Partitioning option
JServer Release 9.0.1.2.0 - Production

Wed Aug 14 13:43:28 EDT 2002
(tkyte@aria-dev) /export/home/tkyte
> plus

SQL*Plus: Release 9.0.1.0.0 - Production on Wed Aug 14 14:57:00 2002

(c) Copyright 2001 Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.0.1.2.0 - Production
With the Partitioning option
JServer Release 9.0.1.2.0 - Production

ops$tkyte@ORA9I.WORLD> @test 30
old 2: type array is table of varchar2(&1) index by binary_integer;
new 2: type array is table of varchar2(30) index by binary_integer;

PL/SQL procedure successfully completed.

old 4: and lower(a.name) like '%' || lower('&1')||'%'
new 4: and lower(a.name) like '%' || lower('pga')||'%'

NAME VALUE
------------------------------ ----------
session pga memory 237344
session pga memory max 237344

ops$tkyte@ORA9I.WORLD> Disconnected from Oracle9i Enterprise Edition Release 9.0.1.2.0 - Production
With the Partitioning option
JServer Release 9.0.1.2.0 - Production

Wed Aug 14 13:43:28 EDT 2002
(tkyte@aria-dev) /export/home/tkyte
> plus

SQL*Plus: Release 9.0.1.0.0 - Production on Wed Aug 14 14:57:06 2002

(c) Copyright 2001 Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.0.1.2.0 - Production
With the Partitioning option
JServer Release 9.0.1.2.0 - Production

ops$tkyte@ORA9I.WORLD> @test 10000
old 2: type array is table of varchar2(&1) index by binary_integer;
new 2: type array is table of varchar2(10000) index by binary_integer;

PL/SQL procedure successfully completed.

old 4: and lower(a.name) like '%' || lower('&1')||'%'
new 4: and lower(a.name) like '%' || lower('pga')||'%'

NAME VALUE
------------------------------ ----------
session pga memory 237344
session pga memory max 237344


Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thats what I was expecting

Arvind K Sharma, August 14, 2002 - 3:42 pm UTC

Thanks tom.
Really it solved some of the doubts in my mind.

Take care
Arvind K Sharma

Does 'session pga memory' shrink ?

Adrian Billington, November 01, 2002 - 5:44 am UTC

Tom

I was always under the assumption that using the DELETE method on a collection did not release the memory, whereas assigning an empty collection to a currently populated one released the memory. However, Steven Fueurstein's latest OPP3 says that DELETE of all elements of a collection returns all the memory back.

How do you measure this? I had a go as follows, but it doesn't seem to do what I was expecting it to do:-

920> declare
2
3 g_mem pls_integer;
4 g_mem_max pls_integer;
5
6 type typ_ibt_bin is table of varchar2(30)
7 index by binary_integer;
8
9 ibt_bin typ_ibt_bin;
10 ibt_bin_empty typ_ibt_bin;
11
12 procedure snap is
13 begin
14 select (select value from v$mystat
15 where statistic# = (select statistic# from v$statname where name = 'session pga memory'))
16 , (select value from v$mystat
17 where statistic# = (select statistic# from v$statname where name = 'session pga memory max'))
18 into g_mem, g_mem_max
19 from dual;
20 --dbms_output.put_line('Memory is '||to_char(g_mem));
21 --dbms_output.put_line('Memory max is '||to_char(g_mem_max));
22 end snap;
23
24 procedure show_mem is
25 v_mem pls_integer := g_mem;
26 v_mem_max pls_integer := g_mem_max;
27 begin
28 snap();
29 dbms_output.put_line('Memory has increased by '||to_char(g_mem - v_mem)||' bytes.');
30 dbms_output.put_line('Memory max has increased by '||to_char(g_mem_max - v_mem_max)||' bytes.');
31 end show_mem;
32
33 begin
34
35 /* Now let's start... */
36 snap();
37
38 /* Get memory usage above default session allocation... */
39 select name bulk collect into ibt_bin from dba_source;
40 show_mem();
41
42 /* Any released ? (should be no)... */
43 ibt_bin.delete(150);
44 show_mem();
45
46 /* Any released ? (I thought it should be no, OPP3 says it should)... */
47 ibt_bin.delete;
48 show_mem();
49
50 /* Any released ? (I thought it should be)... */
51 ibt_bin := ibt_bin_empty;
52 show_mem();
53
54 end;
55 /
Memory has increased by 5864796 bytes.
Memory max has increased by 6585692 bytes.
Memory has increased by 0 bytes.
Memory max has increased by 0 bytes.
Memory has increased by 0 bytes.
Memory max has increased by 0 bytes.
Memory has increased by 0 bytes.
Memory max has increased by 0 bytes.

PL/SQL procedure successfully completed.

Can v$mystat provide the answer as I was expecting?

Regards

Adrian

Tom Kyte
November 01, 2002 - 6:49 am UTC 

If you have my book "expert one on one", I have a discussion on this and why pga memory in general won't go down (it would not be worth the effort -- it won't really "shrink" at the OS level regardless).  We can "force it" but in general, you are only making yourself feel better but the system is running slower for it.  

Here is an example, if you want the details, my book goes into why shrinking PGA memory in dedicated server isn't worth the effort:

ops$tkyte@ORA920.US.ORACLE.COM> declare
  2          type array is table of varchar2(4000) index by binary_integer;
  3
  4          l_data array;
  5
  6          procedure show_pga_usage( p_msg in varchar2 )
  7          is
  8          begin
  9          dbms_output.put_line( p_msg );
 10          for x in (      select a.name, b.value
 11                    from v$statname a, v$mystat b
 12                   where a.statistic# = b.statistic#
 13                     and a.name like '%pga%' )
 14          loop
 15                  dbms_output.put_line( to_char( x.value, '999,999,999' ) || ' ' || x.name );
 16          end loop;
 17          end;
 18  begin
 19          show_pga_usage( 'starting' );
 20
 21          for i in 1 .. 1000
 22          loop
 23                  l_data(i) := rpad( '*', 4000, '*' );
 24          end loop;
 25          show_pga_usage( 'after filling array: ' );
 26
 27          for i in 1 .. l_data.count
 28          loop
 29                  if ( mod( i, 500 ) = 0 )
 30                  then
 31                          show_pga_usage( 'after deleting ' || i || ' entries' );
 32                  end if;
 33                  l_data.delete(i);
 34          end loop;
 35          show_pga_usage( 'after deleting ALL entries' );
 36  end;
 37  /
starting
401,444 session pga memory
401,444 session pga memory max
after filling array:
5,888,396 session pga memory
5,888,396 session pga memory max
after deleting 500 entries
5,888,396 session pga memory
5,888,396 session pga memory max
after deleting 1000 entries
5,888,396 session pga memory
5,888,396 session pga memory max
after deleting ALL entries
5,888,396 session pga memory
5,888,396 session pga memory max

PL/SQL procedure successfully completed.

ops$tkyte@ORA920.US.ORACLE.COM> @mystat pga
ops$tkyte@ORA920.US.ORACLE.COM> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&1')||'%'
  5  --and b.value > 0
  6  /
old   4: and lower(a.name) like '%' || lower('&1')||'%'
new   4: and lower(a.name) like '%' || lower('pga')||'%'

NAME                                VALUE
------------------------------ ----------
session pga memory                5888396
session pga memory max            5888396

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> declare
  2          type array is table of varchar2(4000) index by binary_integer;
  3
  4          l_data array;
  5
  6          procedure show_pga_usage( p_msg in varchar2 )
  7          is
  8          begin
  9          dbms_output.put_line( p_msg );
 10          for x in (      select a.name, b.value
 11                    from v$statname a, v$mystat b
 12                   where a.statistic# = b.statistic#
 13                     and a.name like '%pga%' )
 14          loop
 15                  dbms_output.put_line( to_char( x.value, '999,999,999' ) || ' ' || x.name );
 16          end loop;
 17          end;
 18  begin
 19          show_pga_usage( 'starting' );
 20
 21          for i in 1 .. 1000
 22          loop
 23                  l_data(i) := rpad( '*', 4000, '*' );
 24          end loop;
 25          show_pga_usage( 'after filling array: ' );
 26
 27          for i in 1 .. l_data.count
 28          loop
 29                  if ( mod( i, 500 ) = 0 )
 30                  then
 31                          show_pga_usage( 'after deleting ' || i || ' entries' );
 32                          dbms_session.FREE_UNUSED_USER_MEMORY;
 33                          show_pga_usage( 'after deleting ' || i || ' entries and calling "free memory"' );
 34                  end if;
 35                  l_data.delete(i);
 36          end loop;
 37          show_pga_usage( 'after deleting ALL entries' );
 38  end;
 39  /
starting
5,888,396 session pga memory
5,888,396 session pga memory max
after filling array:
5,888,396 session pga memory
5,888,396 session pga memory max
after deleting 500 entries
5,888,396 session pga memory
5,888,396 session pga memory max
after deleting 500 entries and calling "free memory"
3,194,204 session pga memory
5,888,396 session pga memory max
after deleting 1000 entries
3,194,204 session pga memory
5,888,396 session pga memory max
after deleting 1000 entries and calling "free memory"
467,156 session pga memory
5,888,396 session pga memory max
after deleting ALL entries
467,156 session pga memory
5,888,396 session pga memory max

PL/SQL procedure successfully completed.

That's got it...

Adrian Billington, November 01, 2002 - 9:35 am UTC

Tom

Of course! By freeing the memory with DBMS_SESSION (OK I would never use this in "real" code), it at least enabled me to verify that the DELETE method (all elements only) and empty collection assignment both work in the same way.

Thanks.

Adrian

OK

Catherine, March 11, 2004 - 1:06 am UTC

Dear Tom,
I would like to split an array into parts.Is it possible in
Oracle?For example
sql>declare
type t is table of number;
array t := t(1,2,3,4,5,6);
1)How to split the array into 2 or more parts?
2)How to obtain the MEDIAN of the array ROUDED OFF to nearest integer?
Could you please help?
Bye!

Tom Kyte
March 11, 2004 - 9:34 am UTC 

1) i would do procedurally for sure.  something like

  for i in 1 .. t.count
  loop
     if ( mod(i,2) = 0 ) then 
        t2.extend;
        t2(t2.count) := t(i);
     else
        t1.extend .....


2) you would either do procedurally, or if you created the type at the SQL level, you can use SQL:


ops$tkyte@ORA9IR2> create or replace type array as table of number
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2      t array := array(1,2,3,4,5,6);
  3      l_med  number;
  4  begin
  5
  6      SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY column_value DESC)
  7        into l_med
  8        from TABLE( cast( t as array ) );
  9
 10      dbms_output.put_line( round(l_med) );
 11  end;
 12  /
4
 
PL/SQL procedure successfully completed.

Max value of type

Mise, May 22, 2004 - 10:29 am UTC

Tom,

Thanks a lot for all your comments/clarifications.

In oracle 9i, what is the max length of an array (table) can be extended.

like if i declare type for emp.ename which is of size varchar2(50)

create type emp1 is table of emp.ename%type.

And assuming that i have one million employees in my table, can i still populate the type emp1 with all those emp names. What is the max size, i can store? is it limited by pga size? if yes, then procedurally how i overcome this if i have to use type in my pl/sql for 1 million records with less pga itself?

Thanks for your reply

Tom Kyte
May 22, 2004 - 5:22 pm UTC

you will not want to use collections (in memory) for millions of records, you'll kill you system. they are stored in RAM when fetched.

You can bulk fetch LIMIT <some reasonable number, say 100> instead into that collection.

deleting elements and freeing memory

anon, September 25, 2009 - 4:08 pm UTC

Hi Tom,

Simple question for you hopefully. If you create an index by array, add 500 elements (and put data in), delete 500 elements and then re-populate starting from index value 1 to 500 - would the pga memory increase or stay the same?
Tom Kyte
September 29, 2009 - 11:57 am UTC

the pga is managed as a heap, the delete would free space, the subsequent re-populate would reuse it as best it could - it might increase or decrease the pga memory.

If the stuff you put in was exactly the same, you would expect the memory used to be about the same.

if the stuff you put in was smaller - the pga MIGHT decrease - depends on what bytes are allocated and where in the heap. Interior holes of free memory in the pga would not be releasable

if the stuff you put in was larger - it would increase of course.

ops$tkyte%ORA10GR2> declare
  2      type array is table of long index by binary_integer;
  3      l_data array;
  4
  5  function pga return number
  6  is
  7      l_pga number;
  8  begin
  9      select b.value into l_pga
 10        from v$statname a, v$mystat b
 11       where a.statistic# = b.statistic#
 12         and a.name = 'session pga memory';
 13
 14      return l_pga;
 15  end;
 16
 17  procedure msg( p_str in varchar2 )
 18  is
 19  begin
 20      dbms_output.put_line( rpad(p_str,30,'.') || to_char(pga(),'999,999,999') );
 21  end;
 22
 23  procedure fill_data
 24  is
 25  begin
 26      for i in 1 .. 500
 27      loop
 28          l_data(i) := rpad('*',32760,'*');
 29      end loop;
 30  end;
 31
 32
 33  begin
 34      msg( 'start pga ' );
 35      msg( 'start pga ' );
 36
 37      fill_data();
 38      msg( 'first allocation pga ' );
 39
 40      l_data.delete;
 41      msg( 'first deallocation pga ' );
 42
 43      fill_data();
 44      msg( 'second allocation pga ' );
 45
 46      l_data.delete;
 47      msg( 'second deallocation pga ' );
 48
 49      fill_data();
 50      msg( 'third allocation pga ' );
 51
 52      l_data.delete;
 53      msg( 'third deallocation pga ' );
 54
 55  end;
 56  /
start pga ....................   1,166,404
start pga ....................   1,166,404
first allocation pga .........  33,999,940
first deallocation pga .......  33,999,940
second allocation pga ........  34,065,476
second deallocation pga ......  34,065,476
third allocation pga .........  34,065,476
third deallocation pga .......  34,065,476

PL/SQL procedure successfully completed.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.