Re: Performance doubt on my explicit cursor with a subquery

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Sat, 15 Feb 2003 15:23:38 -0800
Message-ID: <3E4ECBFA.A868491F@exesolutions.com>

C Chang wrote:

> 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

The solution is likely simple but your example doesn't correspond with your SQL statement.

If you start with:

> > id name dbNo
> > ---- ------- ------
> > a abba 123
> > b abbb 124
> > c abbc 125
> > d abbd 123

and your desired result set is this:

--
a
d

Then the SQL statement is:

SELECT DISTINCT id
FROm table
WHERE dbNo = '123'

Yet I suspect that isn't what you want. A clear demonstration of table data and result
would help. Strive to elminiate ambiguity.

Daniel Morgan

Received on Sat Feb 15 2003 - 17:23:38 CST