slow PL/SQL function

Hi,

   Perhaps there is a better way than doing this - but this is what I have - a function that looks up descriptions:

1)
create or replace
 function get_description( param_code in number) return varchar2 is cursor cur is
select

   c.description
from codes c
where c.code = param_code;
 description varchar2(40);
 begin
   for rec in cur loop

      return rec.display;
   end loop;
   return description;
 end;
/

2) create or replace view my_slow_view as

   select get_description(m.field_code) as field_description    from my_table m

3) select * from my_slow_view

The lookup is incredibly slow and while a join to the codes table would be optimal for one join - I want up to 50 code descriptions in the my_table and other tables - so I really want that function to be lightning fast...

any ideas?

Tim Received on Thu Jan 30 2003 - 08:21:48 CET