Re: Performance doubt on my explicit cursor with a subquery

DA Morgan wrote:
>
> 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

Thanks again for your kindness help. The result of my subquery will be like
ID

--
a
d

if the the p_vendorID of the subquery is a or b which both has the same
dbNo. 
    The bigger picture is that there are a receipt table, a purchase
order table and a purchase heading table ( in the main select section
).  PO heading is composed with vendor_ID, and PO header .., and
Purchase Order contains the detail of PO, such as different line item
(line number), name of item (a unique ID), qty, unit... And the receipt
consists name of the item, qty, unit, vendor ID, po header  and line
number.  What the cursor try to do is to find out the total qty of the
same item from many differnt orders but the same COMPANY ( supposed to
be the same vendor ID). But somehow, the same COMPANY changed their
vendor ID from shipment to shipment for the same item, which has a
unique number. That's why the cursor can not use the h.vendor_id =
p_vendorID .   I hope this will clear out a little.  I will print out
the real query of the subset and the main cursor.  If you need more, I
also can print the whole procedure of the package.  Sincerely appreciate
for a learning experience.

C chang