Re: Performance doubt on my explicit cursor with a subquery

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Fri, 14 Feb 2003 15:57:20 -0800
Message-ID: <3E4D8260.FD20DB14@exesolutions.com>

C Chang wrote:

> DA Morgan wrote:
> >
> > C Chang wrote:
> >
> > > I figured out the following way to my IN list in the explicit
> > > cursor:
> > >
> > > Cursor c_qty ( p_siteID IN varchar2, p_vendorID IN varchar2) is
> > > SELECT SUM(r.qty), r.qty_unit
> > > FROM receipts r, lines p, heads h
> > > WHERE r.site_id= p_siteID
> > > AND h.vendor_id IN (select vendor_id
> > > from vendor v,
> > > (select db_num
> > > from vendor
> > > where vendor_id = p_vendorID ) d
> > > where v.db_num = d.db_num)
> > > AND 'other conditions'
> > > GROUP BY r.qty_unit, r.site_id;
> > >
> > > But the problem is will it be too resource extensive if this cursor been
> > > called in another dynamic cursor loop for 8K times?
> > >
> > > C Chang
> >
> > I don't get the point of your nested subselects in the WHERE clause. You are
> > selecting from vendor and the joining it to another select from the same
> > table. Not knowing your schema I am wondering why you aren't doing it in one
> > step.
> >
> > Daniel Morgan
> Thanks Dan. My Vendor table has records like:

>

> id name dbNo
> ---- ------- ------
> a abba 123
> b abbb 124
> c abbc 125
> d abbd 123
> ..
> The ID column is unique and the other two are not. I want to use the
> dbNo to select out what ID can be with the same dbNo and use the result
> as the condition in main select query. Thanks for your suggest from
> other post. We tried to upgrade to 9i ( at least 8.1.7) for almost a
> year. Becasue I am not oracle "Expert" (not DBA, not oracle programmer,
> just a "casual" developer), not enough to persude our Manager to make
> the decision.

>
> C Chang

Still confused about what you are trying to do ... can you (without snipping any of this) post a sample result set from your demo data.

Daniel Morgan Received on Fri Feb 14 2003 - 17:57:20 CST