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

Hi Guang,

A quick question on your problem before I recommend anything ... how much data are you planning typically returning? Also, any reason why we have a 'DISTINCT'? Do we really need that? If not, no biggie but let me know on that too.

Thx!

--
Pablo Sanchez, High-Performance Database Engineering
mailto:pablo_at_hpdbe.com
Available for short-term and long-term contracts

"G M" <zlmei_at_hotmail.com> wrote in message
news:50a5e6b6.0204170951.6dc87909_at_posting.google.com...


> 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;

> > > -------------

>