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

Hi:

Typical returned string is less than 128 bytes in length.

Table XYZ has about 600000 rows.

Column METHOD has type VARCHAR2(128).

DISTINCT is needed here, there is no unique constraint on that column.

Guang

"Pablo Sanchez" <pablo_at_dev.null> wrote in message news:<Vgnv8.427$Dz3.108180_at_news.uswest.net>...
> 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;
> > > > -------------
> >
Received on Thu Apr 18 2002 - 10:07:54 CDT