Shankar,

I'm not familiar with PL/SQL, so I can't help with your cursor code, but I 
think you might be able to avoid using a cursor altogether if you re-write 
your query without using subqueries. Try something like this (for the 2 
table case) and see if it's fast enough:

SELECT	prd1.Companycode,
	mfr.Company,
	prd1.ProductID,
	fd.Item_code,
	fd.Price
FROM FoodItems fd
INNER JOIN OtherItems othr ON 	fd.Price = othr.Price
INNER JOIN Product_tab prd1 ON 	prd1.Item_code = fd.Item_code 
INNER JOIN Product_tab prd2 ON	prd2.Item_code = othr.Item_code
INNER JOIN Manufacturer mfr ON 	mfr.Companycode = prd1.Companycode
WHERE prd1.ProductID = prd2.ProductID AND prd1.CompanyCode = 
prd2.CompanyCode

UNION ALL

SELECT	prd1.Companycode,
	mfr.Company,
	prd1.ProductID,
	othr.Item_code,
	othr.Price
FROM OtherItems othr
INNER JOIN FoodItems fd ON 	fd.Price = othr.Price
INNER JOIN Product_tab prd1 ON 	prd1.Item_code = othr.Item_code 
INNER JOIN Product_tab prd2 ON	prd2.Item_code = fd.Item_code
INNER JOIN Manufacturer mfr ON 	mfr.Companycode = prd1.Companycode
WHERE prd1.ProductID = prd2.ProductID AND prd1.CompanyCode = 
prd2.CompanyCode

-Lam


> Hi,

I'm using an Oracle Database. This is a sample query written to select
values from two tables. But there are actually 4 tables. So the query
runs to 3 pages and also the number of data in the tables is somewhere
around 50,000. I need some way to optimize the query.

Table: Manufacturer
Companycode	Company
2		P&G
3		Hindustan Lever
4		Nestle
5		Cadbury

Table: Product_tab
Companycode	ProductID	Item_code
2		1234		123456
2		1234		123457
4		1234		123458
4		1234		123459
5		1234		123460
5		2345		123461
5		2345		123462
5		2345		123463

Table: FoodItems			Table: OtherItems
Item_code	Price			Item_code	Price
123456		100			123459		500
123457		100			123461		100
123458		500			123463		200
123460		200
123462		200

Output:-
Companycode	Company	ProductID	ItemCode	Price
4		Nestle	1234		123458		500
4		Nestle	1234		123459		500
5		Cadbury	2345		123462		200
5		Cadbury	2345		123463		200

Requirement:- If the price of the items in table-food items and 
table-otheritems are equal and if they belong to the same productid and

companycode, then we have to list them in the output.
Item code is unique.
Note:- we can also have the items in the same table with same price,
productid, companycode listed in the output. But this query will not
give that output.

SQL: 
select mfr.company,mfr.companycode,prd.productid, prd.itemcode, 
otr.price as price 
from manufacturer mfr, product_tab prd, otheritems otr 
where otr.itemcode 
in (select prd2.itemcode 
from otheritems otr, Product_tab prd2 
where prd2.itemcode=otr.itemcode 
and otr.price 
in (select fd.price 
from Product_tab prd1, fooditems fd 
where prd1.itemcode = fd.itemcode 
and prd1.productid = prd2.productid 
and prd2.companycode = prd1.companycode)) 
and prd.itemcode=otr.itemcode 
and prd.companycode=mfr.companycode 
union all 
select mfr.company,mfr.companycode,prd.productid, prd.itemcode, 
fd.price as price 
from manufacturer mfr, product_tab prd, fooditems fd 
where fd.itemcode 
in (select prd2.itemcode 
from fooditems fd, Product_tab prd2 
where prd2.itemcode=fd.itemcode 
and fd.price 
in (select otr.price 
from Product_tab prd1, otheritems otr 
where prd1.itemcode = otr.itemcode 
and prd1.productid = prd2.productid 
and prd2.companycode = prd1.companycode)) 
and prd.itemcode=fd.itemcode 
and prd.companycode=mfr.companycode

I thought of going for PL/SQL. But I'm struckup half way in that. Can
someone help me to solve this.

DECLARE 
precomp NUMBER;
preprodid NUMBER:=0;
preitem_code NUMBER;
preprice NUMBER:=0;

CURSOR test 
is Select p.companycode, p.productid, p.item_code, f.price 
From Product_tab P, FoodItems F 
where P.item_code = f.item_code 
union 
Select p.companycode, p.productid, p.item_code, f.price 
From Product_tab P, OtherItems F 
where P.item_code = f.item_code 
order by 2, 4;

companycode NUMBER;
productid NUMBER;
item_code NUMBER;
price NUMBER;

BEGIN
	OPEN test;
	LOOP
	FETCH test INTO companycode, productid, item_code, price;
		EXIT WHEN test%NOTFOUND;
		IF(preprodid=productid and preprice=price) THEN
			DBMS_OUTPUT.PUT_LINE
(companycode,productid,item_code,price);
		ELSE
			precomp:=companycode;
			preprodid:=productid;
			preitem_code:=item_code;
			preprice:=price;
		FETCH test INTO companycode, productid, item_code, price;
		END IF;
	END LOOP;
	CLOSE test;
END;



The following program will work fine in SQL SERVER

DECLARE test INSENSITIVE CURSOR 
FOR Select p.companycode, p.productid, p.item_code, f.price
  From Product_tab P, FoodItems F
 where P.item_code = f.item_code
union
Select p.companycode, p.productid, p.item_code, f.price
  From Product_tab P, OtherItems F
  where P.item_code = f.item_code
order by 2, 4
 FOR READ ONLY

open test

declare @companycode int, @productid int, @item_code int, @price money
declare @precomp int, @preprodid int, @preitem_code int, @preprice 
money
declare @iCount int

Set @preprodid = 0
Set @preprice = 0

   FETCH NEXT FROM test INTO @companycode, @productid, @item_code, 
@price
   IF @@FETCH_STATUS <> 0 
      PRINT ' No Records '

   WHILE @@FETCH_STATUS = 0
   BEGIN

      if (@preprodid = @productId and @preprice = @price)
      begin
            print convert(char(8), @precomp) + ' ' + convert(char(8), 
@preprodid) + ' ' + convert(char(8), @preitem_code) + ' ' + 
convert(char(8), @preprice)

         print convert(char(8), @companycode) + ' ' + convert(char(8), 
@productid) + ' ' + convert(char(8), @item_code) + ' ' + 
convert(char(8), @price)
      end
      else
      Set @precomp = @companycode
      Set @preprodid = @productid
      Set @preitem_code = @item_code
      Set @preprice = @price
      
      FETCH NEXT FROM test INTO @companycode, @productid, @item_code, 
@price
   END

close test
deallocate test

Thanx in advance.

Regards,
Shankar

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com