Home » RDBMS Server » Performance Tuning » optimatize query??
optimatize query?? [message #250537] |
Tue, 10 July 2007 06:52 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
steve_kc
Messages: 5 Registered: July 2007 Location: Malaysia
|
Junior Member |
|
|
Hi All,
My query takes about 1 hour to excute, is there anyway to reduce the excution time??
SELECT
CIQ.CIQ_STOCK_CODE,
QT.QT_CS_CODE AS Country,
QT.QT_REFERENCE_NUMBER AS "Order#",
CBD.CBD_NAME AS "Cust Name",
SP.SP_NAME AS "Sales Rep Name",
CC.CC_CHANNEL,
PIQ.PIQ_QUANTITY AS Qty,
QT.QT_CONFIRM_DATE,
QT.QT_REQUESTED_DELIVERY_DATE,
PIQ.PIQ_DESCRIPTION,
PRD.PRD_DESCRIPTION,
QT.QT_CUSTOMER_CODE,
QT.QT_STATUS,
QA.QA_S_WDP_1 AS "Work/Daytime Phone",
QA.QA_S_MBL_1 AS "Mobile Phone",
QA.QA_S_HP_1 AS "Home Phone",
QA.QA_PHONE_1 AS "Cust Phone 1",
QA.QA_PHONE_2 AS "Cust Phone 2",
QT.QT_DELIVERY_CHANGE AS FDD,
PIQ.PIQ_IS_CUSTOMER_KIT,
FROM
DM_QUOTATIONS@ISMANZ QT,
DM_PRODUCTS_IN_QUOTATION@ISMANZ PIQ,
DM_COMPONENTS_IN_QUOTATION@ISMANZ CIQ,
DM_SALES_PEOPLE@ISMANZ SP,
DM_QUOTATION_OTHER_DETAILS@ISMANZ QOD,
DM_CUSTOMER_BASIC_DETAILS@ISMANZ CBD,
SO_COST_CENTRES@ISMANZ CC,
DM_QUOTATION_ADDRESSES@ISMANZ QA,
DM_PRODUCT_SUB_GROUPS@ISMANZ PSG,
DM_PRODUCTS@ISMANZ PRD
WHERE
QT.QT_NUMBER =PIQ.PIQ_QT_NUMBER AND
PIQ.PIQ_SERIAL_NO = CIQ.CIQ_PIQ_SERIAL_NO AND
PIQ.PIQ_QT_NUMBER = CIQ.CIQ_PIQ_QT_NUMBER AND
CIQ.CIQ_PRD_CODE = PRD.PRD_CODE AND
CIQ.CIQ_PSG_CODE = PSG.PSG_CODE AND
QT.QT_NUMBER = QOD.QOD_QT_NUMBER AND
QOD.QOD_CS_CODE = CC.CC_CS_CODE AND
QOD.QOD_CC_CODE = CC.CC_CODE AND
QOD.QOD_CS_CODE = SP.SP_CS_CODE AND
QOD.QOD_SP_CODE_INTERNAL = SP.SP_CODE AND
QT.QT_CBD_NUMBER = CBD.CBD_NUMBER AND
QT.QT_NUMBER = QA.QA_QT_NUMBER AND
CIQ.CIQ_STOCK_CODE IN (Select CIQ_STOCK_CODE FROM VMIBackOrder ) AND
(QT.QT_CS_CODE='361'OR QT.QT_CS_CODE='391')AND
QT.QT_STATUS<=500 AND
QT.QT_TYPE='3' AND
QT.QT_OUT_OF_WAREHOUSE_DATE Is Null;
Thanks in advance.
|
|
|
Re: optimatize query?? [message #250542 is a reply to message #250537] |
Tue, 10 July 2007 06:56 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
MarcL
Messages: 455 Registered: November 2006 Location: Connecticut, USA
|
Senior Member |
|
|
Can you format your code according to the sticky in the forum.
Also to even attempt at trying to tune a query please post the results from explain plan and tkprof.
|
|
|
Re: optimatize query?? [message #251332 is a reply to message #250537] |
Fri, 13 July 2007 00:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
orafan23
Messages: 13 Registered: December 2005
|
Junior Member |
|
|
Hi,It`s really tough to tune a query without execution plan or information on the objects being used.However,by a glance on the query I see the following joins
(QT.QT_CS_CODE='361'OR QT.QT_CS_CODE='391')AND
QT.QT_STATUS<=500 AND
QT.QT_TYPE='3' AND
QT.QT_OUT_OF_WAREHOUSE_DATE Is Null;
Try adding this as the from clause and make it an inline query,
say in your from clause something like this
************************************
From
(select QT.QT_CS_CODE Country,
QT.QT_REFERENCE_NUMBER "Order#",QT.QT_CONFIRM_DATE,
QT.QT_REQUESTED_DELIVERY_DATE , QT.QT_CUSTOMER_CODE,
QT.QT_STATUS,QT.QT_DELIVERY_CHANGE FDD,QT.QT_NUMBER,QT.QT_CBD_NUMBER,QT.QT_NUMBER
from DM_QUOTATIONS@ISMANZ A where
A.QT_STATUS<=500 AND
A.QT_TYPE='3' AND
A.QT_OUT_OF_WAREHOUSE_DATE Is Null) QT,
DM_PRODUCTS_IN_QUOTATION@ISMANZ PIQ,
DM_COMPONENTS_IN_QUOTATION@ISMANZ CIQ,
DM_SALES_PEOPLE@ISMANZ SP,
DM_QUOTATION_OTHER_DETAILS@ISMANZ QOD,
DM_CUSTOMER_BASIC_DETAILS@ISMANZ CBD,
SO_COST_CENTRES@ISMANZ CC,
DM_QUOTATION_ADDRESSES@ISMANZ QA,
DM_PRODUCT_SUB_GROUPS@ISMANZ PSG,
DM_PRODUCTS@ISMANZ PRD
WHERE
-------------------------------
Let know the execution plan of this query.
Thanks,
Raghava
|
|
|
Goto Forum:
Current Time: Fri Sep 06 15:49:47 CDT 2024
|