Re: Anyway to speed up this pl/sql code?

The idea may work in some situation, but not in mine here. I did a quick test and looked at the explain plan of

select distinct method
from XYZ
where method is not null
and id in ( select * from THE ( select cast( in_list ('1') as

                                    mytableType ) from dual ) a );

The cost is huge (3436410) and it is way too slow in my case.

Anyway, thanks for your input.

Guang

"Sunil" <sunil_franklin_at_hotmail.com> wrote in message news:<8v7v8.29$tV6.295_at_news.oracle.com>...
> I have not tried this but ,
> If you make the "idlist" an object type you can select from it using the
> table cast operator. Then you can rewrite your dynamic sql as a static
> cursor accepting a parameter. ( static sql being faster). Next you can do
> bulk fetch from the opened cursor and operate on it.
>
> Additions/corrections from the experts are welcome.
>
> Sunil.
>
>
>
> "G M" <zlmei_at_hotmail.com> wrote in message
> news:50a5e6b6.0204161239.717ec84c_at_posting.google.com...
> > Hi:
> >
> > Do you think there is a way to optimize the following PL/SQL code (in
> > terms of performance)? An example of the parameter "idlist" could be
> > "1,2,3".
> >
> > TIA.
> >
> > Guang
> >
> > ------------
> > function getMethods(idlist in varchar2) return varchar2 is
> > c integer;
> > dum integer;
> > retstr varchar2(1024) := null;
> > base_sql varchar2(256) := 'select distinct method from XYZ '||
> > 'where method is not null and id in ';
> >
> > begin
> > c := dbms_sql.open_cursor;
> > dbms_sql.parse(c, base_sql||'('||idlist||')', dbms_sql.native);
> > dbms_sql.define_column(c, 1, method, 128);
> > dum := dbms_sql.execute(c);
> >
> > loop
> > if dbms_sql.fetch_rows(c)>0 then
> > dbms_sql.column_value(c, 1, method);
> > -- doing something to set retstr here
> > else
> > dbms_sql.close_cursor(c);
> > return retstr;
> > end if;
> > end loop;
> > end getMethods;
> > -------------
Received on Wed Apr 17 2002 - 12:51:12 CDT