OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » query problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

query problem [message #482145]

Wed, 10 November 2010 01:07

narang79
Messages: 131
Registered: June 2010

Senior Member

sir when we run this following query it take 3 mins

if i remove account table which is joined with party master
then it take 2 sec only

if we run single query with joined account & party then it take 2 sec

how i can fast this query sir

SELECT DISTINCT PM.PARTY_CODE,PM.PARTY_NAME,PM.PARTY_CODE STATUS_CUST,PM.DEFAULTPURCHASER
FROM PR_INDENT_DET ID_DET,PR_INDENT_MAST ID_MAST,ITEM ITM,
EMPLOYEE EMP,(
SELECT POI.INDENT_NO,POI.INDENT_DATE,
POI.ITEM_CODE,POI.QTY_ORD - NVL(T.EXTENDED_QTY,0) QTY_ORD
FROM PURCHASE_ORDER PO,PURCHASE_ORDER_ITEM POI,
(SELECT PO_NO,PO_DATE,ITEM_CODE,SUM(NVL(EXTENDED_QTY,0)) EXTENDED_QTY,INDENT_NO,INDENT_DATE FROM TBL_PO_ADJUSTMENT
GROUP BY PO_NO,PO_DATE,ITEM_CODE,INDENT_NO,INDENT_DATE)T
WHERE PO.PO_NO = POI.PO_NO
AND PO.PO_DATE = POI.PO_DATE
AND POI.PO_NO = T.PO_NO(+)
AND POI.PO_DATE = T.PO_DATE(+)
AND POI.INDENT_NO = T.INDENT_NO(+)
AND POI.INDENT_DATE = T.INDENT_DATE(+)
AND POI.ITEM_CODE = T.ITEM_CODE(+)
AND PO.CANC_STATUS = 'N'
AND PO.CCODE ='AL'--:GLOBAL.MYCODE
AND NVL(POI.APPROVAL_STATUS,'A')='A'
ORDER BY 2 DESC
) POI,PARTY_MASTER PM, ACCOUNT A,
(
SELECT Q.QUOT_COMP_NO,Q.QUOT_COMP_DATE,
Q.SUPP_CODE,ITEM.PART_NO,ITEM.CCODE
FROM QUOT_COMPARISON_DTL Q,QUOT_COMPARISON_MST QM,(
SELECT MAX(Q.QUOT_COMP_NO) QUOT_COMP_NO,
Q.QUOT_COMP_DATE,Q.ITEM_CODE,M.CCODE
FROM QUOT_COMPARISON_DTL Q,QUOT_COMPARISON_MST M,(
SELECT MAX(QM.QUOT_COMP_DATE) QUOT_COMP_DATE,ITEM_CODE,CCODE
FROM QUOT_COMPARISON_DTL QD,QUOT_COMPARISON_MST QM
WHERE QM.QUOT_COMP_NO = QD.QUOT_COMP_NO
AND QM.QUOT_COMP_DATE = QD.QUOT_COMP_DATE
AND QM.CCODE = 'AL'--:GLOBAL.MYCODE
GROUP BY QD.ITEM_CODE,QM.CCODE
) MAX_DATE
WHERE M.QUOT_COMP_NO = Q.QUOT_COMP_NO
AND M.QUOT_COMP_DATE = Q.QUOT_COMP_DATE
AND Q.QUOT_COMP_DATE = MAX_DATE.QUOT_COMP_DATE
AND Q.ITEM_CODE = MAX_DATE.ITEM_CODE
AND M.CCODE = 'AL'--:GLOBAL.MYCODE
GROUP BY Q.QUOT_COMP_DATE,Q.ITEM_CODE,M.CCODE
) MAX_NO,ITEM
WHERE QM.QUOT_COMP_NO = Q.QUOT_COMP_NO
AND QM.QUOT_COMP_DATE = Q.QUOT_COMP_DATE
AND QM.CCODE ='AL'--:GLOBAL.MYCODE
AND Q.QUOT_COMP_NO = MAX_NO.QUOT_COMP_NO
AND Q.QUOT_COMP_DATE = MAX_NO.QUOT_COMP_DATE
AND Q.ITEM_CODE = MAX_NO.ITEM_CODE
AND SUPP_TYPE = 'V1'
AND Q.ITEM_CODE = ITEM.ITEM_CODE
AND ITEM.CCODE = 'AL'--:GLOBAL.MYCODE
) IVD
WHERE ID_MAST.INDENT_NO = ID_DET.INDENT_NO
AND A.SUPP_CODE = PM.PARTY_CODE
AND A.CCODE=PM.CCODE
AND A.FREEZE='N'
AND ID_MAST.INDENT_DATE = ID_DET.INDENT_DATE
AND ID_DET.ITEM_CODE = ITM.ITEM_CODE
AND ID_MAST.CCODE ='AL'--:GLOBAL.MYCODE
AND ITM.CCODE = 'AL'--:GLOBAL.MYCODE
AND ID_MAST.APPROVAL_STATUS = 'A'
AND ID_MAST.FYCODE ='10-11'--:GLOBAL.MYFINY
AND ID_MAST.EMPLOYEE_CODE = EMP.EMPLOYEE_CODE
AND ITM.PART_NO = IVD.PART_NO
AND IVD.SUPP_CODE = PM.PARTY_CODE
AND ID_DET.INDENT_NO = POI.INDENT_NO(+)
AND ID_DET.INDENT_DATE = POI.INDENT_DATE(+)
AND ID_DET.ITEM_CODE = POI.ITEM_CODE(+)
AND ITM.PART_NO IN (SELECT PART_NO FROM ITEM WHERE STORE_CODE ='0001'--:PURCHASE_ORDER.STORE_CODE
)
AND ITM.STORE_CODE ='0001'--:PURCHASE_ORDER.STORE_CODE
GROUP BY ID_DET.IND_SEQ_NO,ID_DET.INDENT_NO,ID_DET.INDENT_DATE,ID_DET.ITEM_CODE,ID_DET.ITEM_RATE,
ID_DET.IND_QTY,ID_MAST.EMPLOYEE_CODE,ITM.ITEM_NAME,EMP.EMPLOYEE_NAME,ID_DET.IND_PURPOSE,
IVD.SUPP_CODE,ITM.STORE_CODE,PM.PARTY_CODE,PM.PARTY_NAME,PM.DEFAULTPURCHASER
HAVING (ID_DET.IND_QTY - NVL(SUM(POI.QTY_ORD),0))>0
ORDER BY 2

Report message to a moderator

Re: query problem [message #482146 is a reply to message #482145]

Wed, 10 November 2010 01:10

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

Report message to a moderator

Re: query problem [message #482150 is a reply to message #482146]

Wed, 10 November 2010 01:26

narang79
Messages: 131
Registered: June 2010

Senior Member

ok sir

next time i will take care guide lines

my oracle version is 10.2.0.1.0

Report message to a moderator

Re: query problem [message #482152 is a reply to message #482150]

Wed, 10 November 2010 01:27

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

ok next time I will help you.

Regards
Michel

Report message to a moderator

Re: query problem [message #482154 is a reply to message #482152]

Wed, 10 November 2010 01:47

narang79
Messages: 131
Registered: June 2010

Senior Member

select distinct pm.party_code,
pm.party_name,
pm.party_code status_cust,
pm.defaultpurchaser

from pr_indent_det id_det,
pr_indent_mast id_mast,
item itm,
employee emp,
(
select poi.indent_no,poi.indent_date,
poi.item_code,
poi.qty_ord - nvl(t.extended_qty,0) qty_ord
from purchase_order po,
purchase_order_item poi,
(select po_no,po_date,item_code,
sum(nvl(extended_qty,0)) extended_qty,indent_no,
indent_date from tbl_po_adjustment
group by po_no,po_date,item_code,
indent_no,indent_date)t
where po.po_no = poi.po_no
and po.po_date = poi.po_date
and poi.po_no = t.po_no(+)
and poi.po_date = t.po_date(+)
and poi.indent_no = t.indent_no(+)
and poi.indent_date = t.indent_date(+)
and poi.item_code = t.item_code(+)
and po.canc_status = 'n'
and po.ccode ='al'--:global.mycode
and nvl(poi.approval_status,'a')='a'
order by 2 desc
) poi,party_master pm, account a,--problem table-------
(
select q.quot_comp_no,q.quot_comp_date,
q.supp_code,item.part_no,item.ccode
from quot_comparison_dtl q,
quot_comparison_mst qm,
(
select max(q.quot_comp_no) quot_comp_no,
q.quot_comp_date,q.item_code,m.ccode
from quot_comparison_dtl q,
quot_comparison_mst m,
(
select max(qm.quot_comp_date) quot_comp_date,
item_code,ccode
from quot_comparison_dtl qd,
quot_comparison_mst qm
where qm.quot_comp_no = qd.quot_comp_no
and qm.quot_comp_date = qd.quot_comp_date
and qm.ccode = 'al'--:global.mycode
group by qd.item_code,qm.ccode
)
max_date
where m.quot_comp_no = q.quot_comp_no
and m.quot_comp_date = q.quot_comp_date
and q.quot_comp_date = max_date.quot_comp_date
and q.item_code = max_date.item_code
and m.ccode = 'al'--:global.mycode
group by q.quot_comp_date,
q.item_code,m.ccode
) max_no,
item
where qm.quot_comp_no = q.quot_comp_no
and qm.quot_comp_date = q.quot_comp_date
and qm.ccode ='al'--:global.mycode
and q.quot_comp_no = max_no.quot_comp_no
and q.quot_comp_date = max_no.quot_comp_date
and q.item_code = max_no.item_code
and supp_type = 'v1'
and q.item_code = item.item_code
and item.ccode = 'al'--:global.mycode
) ivd
where id_mast.indent_no = id_det.indent_no
and a.supp_code = pm.party_code
and a.ccode=pm.ccode
and a.freeze='n'
and id_mast.indent_date = id_det.indent_date
and id_det.item_code = itm.item_code
and id_mast.ccode ='al'--:global.mycode
and itm.ccode = 'al'--:global.mycode
and id_mast.approval_status = 'a'
and id_mast.fycode ='10-11'--:global.myfiny
and id_mast.employee_code = emp.employee_code
and itm.part_no = ivd.part_no
and ivd.supp_code = pm.party_code
and id_det.indent_no = poi.indent_no(+)
and id_det.indent_date = poi.indent_date(+)
and id_det.item_code = poi.item_code(+)
and itm.part_no in (select part_no from item where store_code ='0001'--:purchase_order.store_code
)
and itm.store_code ='0001'--:purchase_order.store_code
group by id_det.ind_seq_no,id_det.indent_no,
id_det.indent_date,id_det.item_code,
id_det.item_rate,
id_det.ind_qty,id_mast.employee_code,
itm.item_name,emp.employee_name,id_det.ind_purpose,
ivd.supp_code,itm.store_code,
pm.party_code,pm.party_name,pm.defaultpurchaser
having (id_det.ind_qty - nvl(sum(poi.qty_ord),0))>0
order by 2

Report message to a moderator

Re: query problem [message #482164 is a reply to message #482154]

Wed, 10 November 2010 03:43

ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

This is your 94 post and don`t you know what are required for tuning?
Read the posting guide lines as well as performance tuning guide requirements in this forum and come back here

sriram

Report message to a moderator

Previous Topic:	MV when detailed table consists of Millions of records
Next Topic:	Order by Clause is too slow

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Apr 28 00:08:59 CDT 2024