when you have
and COL in
( select X
from table(plsql_table_type)
)
you might want to consider the cardinality hint in the sub query so that Oracle knows how rows in
the PL/SQL table are expected and can (hopefully) optimize accordingly. Alternatively, you could
try the WITH clause to lean Oracle toward evaluating that stuff first into temp tables.
hth
connor
- "Stephens, Chris" <ChrisStephens_at_pqa.com> wrote:
> Yeah, I was thinking about this last night. We actually did try using pl/sql tables to prevent
> all the parses earlier but were getting horrible execution plans. I seem to remember an 'inlist
> pickler fetcher' operation or something to that affect. ...basically it executes the entire
> query as if the in list did not exist and then applies the in list filter in the final step of
> the plan regardless of how selective it is.
>
> So there are hints to possible get the optimize to use the in list filters earlier?
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Connor
> McDonald
> Sent: Sunday, November 21, 2004 7:33 PM
> To: oracle-l_at_freelists.org
> Subject: Re: Ref cursor length limit...
>
> You're caught between the proverbial rock and hard place...
>
> a) use massive SQL stmts
> - you'll hit some sort of limit (perhaps IN-list items, total statement length, big parse times
> etc)
>
> b) use temp tables
> - got some redo issues there in most releases
>
> c) use PL/SQL tables
> - possible dubious explain plans
>
> If you opt for (c), you might want to architect in some CARDINALITY hints et al to give the
> optimizer some better clues
>
> hth
> connor
>
> --- "Stephens, Chris" <ChrisStephens_at_pqa.com> wrote:
> >
> >
> > We have a situation where we are generating a ref cursor based on a
> > set of strings passed into a package. The problem is that one of
> > those strings is a list of id's that could potentially be larger than
> > the length limit of varchar2 so someone here decided to change the
> > datatypes to clobs. =20
> >
> > Once I get the clob, I am supposed to break it down into a set of
> > varchar2 strings and then open the ref cursor based on the
> > concatenation of those strings.
> >
> > ...except I believe there is a limit (32k) on the length of a sql
> > statement that a ref cursor can open up correct?
> >
> > So I want to:
> >
> > Open rc for varcharString1 || varcharString2 || ....;
> >
> > ...and all the varchar strings will potentially be =3D 32k (or just
> > under).
> >
> > I've been searching the documention for this limit but I cannot find it.
> > ....anyone know where I can get this so I can show my manager?
> >
> >
> > ...i wrote what I believe to be a test but I don't think the error
> > message is going to be convincing enough:
> >
> >
> > CREATE OR REPLACE package body tst_pkg is
> >
> > procedure tst(v_record_set out rc)
> > is
> > =09
> > part1 varchar2(30000):=3D '1';
> > part2 varchar2(30000):=3D '1';
> > part3 varchar2(30000):=3D '1';
> > =09
> > counter number :=3D 0;
> > rc1 rc;
> > =09
> > begin
> > =20
> > while counter < 1000 loop
> > =09
> > part1 :=3D part1 ||' or ppl_id =3D '||counter;
> > part2 :=3D part2 ||' or ppl_id =3D '||counter;
> > part3 :=3D part3 ||' or ppl_id =3D '||counter;
> > =20
> > counter :=3D counter + 1;
> > =20
> > end loop;
> > =09
> > dbms_output.put_line(length(part1));
> > =09
> > open rc1 for 'select count(*) from rep_year_dim where ppl_id =3D
> > '||part1||part2||part3||part1||part2||part3||part1||part2||part3;
> > =09
> > end;
> >
> >
> > end tst_pkg;
> > /
> >
> >
> > Numeric or value error on the 'open...' Line.
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
>
> =====
> Connor McDonald
> Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
> ISBN: 1590592174
>
> web: http://www.oracledba.co.uk
> web: http://www.oaktable.net
> email: connor_mcdonald_at_yahoo.com
>
> Coming Soon! "Oracle Insight - Tales of the OakTable"
>
> "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a
> boat and drink beer all day"
>
> ------------------------------------------------------------
>
>
>
> ___________________________________________________________
> Moving house? Beach bar in Thailand? New Wardrobe? Win £10k with Yahoo! Mail to make your dream
> a reality.
> Get Yahoo! Mail www.yahoo.co.uk/10k
> --
> http://www.freelists.org/webpage/oracle-l
>
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174
web:
http://www.oracledba.co.uk
web:
http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com
Coming Soon! "Oracle Insight - Tales of the OakTable"
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"
ALL-NEW Yahoo! Messenger - all new features - even more fun!
http://uk.messenger.yahoo.com
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Nov 22 2004 - 08:41:25 CST
