How to (cheap) reference an object

Hi everybody,

I try to implement a fast two dimensional lookup table in pl/sql by storing pl/sql tables in another pl/sql table. This works fine but when I try to retrieve stored tables I get a problem: Oracle seems to create a copy of the table every time I try to get the reference to it.
I tried to pass the table to a subroutine (nocopy is implicit for in-only parameters) but this did not work as supposed.
I must be missing something.
Can anybody help?

create or replace procedure p1(p_cnt int, p_pass boolean) as

• types type c is table of number(5) index by binary_integer; type cc is table of c index by binary_integer;
• variables g_c c; g_cc cc; i binary_integer;
• procedures procedure p_int(p_tab in c) as begin null; end; procedure p_int(p_val in int) as begin null; end; begin
• init g_c for i in 1 .. 1000 loop g_c(i) := i; end loop;
• install g_c g_cc(1) := g_c;
• call procedure if p_pass then for i in 1 .. p_cnt loop
  • assignment and copy cost about the same --g_c := g_cc(1); p_int(g_cc(1)); end loop; else for i in 1 .. p_cnt loop p_int(1); end loop; end if; end; / show errors

SQL> exec p1(10000, false);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
SQL> exec p1(10000, true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.29

I test with 9i under Linux.

Richard Received on Tue Jan 01 2002 - 14:47:45 CST