Re: Performance doubt on my explicit cursor with a subquery

From: C Chang <cschang_at_maxinter.net>
Date: Fri, 14 Feb 2003 18:29:13 -0500
Message-ID: <3E4D7BC9.3EC9@maxinter.net>

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 Received on Fri Feb 14 2003 - 17:29:13 CST