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
- WAS CANCELLED BY =======:
Path: news.uni-stuttgart.de!dns.phoenix-ag.de!newsfeed01.sul.t-online.de!t-online.de!newsfeed.easynews.net!easynews.net!newsfeed.freenet.de!news2.euro.net!uunet!sac.uu.net!lax.uu.net!news.navix.net!u.n.a.c.4.n.c.3.l.l.e.r
From: richard.limanowski_at_t-online.de (Richard Limanowski)
Newsgroups: news.admin.censorship,alt.test,comp.databases.oracle.misc
Subject: cmsg cancel <a77e1f06.0201011247.7f1e8dc3_at_posting.google.com>
Control: cancel <a77e1f06.0201011247.7f1e8dc3_at_posting.google.com>
Date: Wed, 2 Jan 2002 00:43:28 GMT
Organization: Navix Internet Subscribers
Lines: 2
Message-ID: <cancel.a77e1f06.0201011247.7f1e8dc3_at_posting.google.com>
NNTP-Posting-Host: 166.102.15.34
X-Trace: iac5.navix.net 1009943276 19888 166.102.15.34 (2 Jan 2002 03:47:56 GMT)
X-Complaints-To: abuse_at_navix.net
NNTP-Posting-Date: 2 Jan 2002 03:47:56 GMT
X-No-Archive: yes
Comment: Dude, where's my NewsAgent?
Xref: news.uni-stuttgart.de control:40276110
autocancel
Received on Tue Jan 01 2002 - 14:47:45 CST
