Home » RDBMS Server » Performance Tuning » Query optimization (Oracle 10G)
Query optimization [message #359644] |
Mon, 17 November 2008 16:29 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
habibsh785
Messages: 2 Registered: May 2008 Location: Dubai
|
Junior Member |
![habibsh785%40yahoo.com](/forum/theme/orafaq/images/yahoo.png)
|
|
Any Suggestion please to optimize this query .............
SELECT SIH.COMPANY_CODE,
SIH.SYSTEM_ID,
SIH.BULK_SUFFIX,
SIH.INV_NUMBER,
SIH.INV_STATUS,
SIH.EXL_LEDGER,
ROW_NUMBER() OVER(PARTITION BY SIH.PARTNER_CODE ORDER BY SIH.PARTNER_CODE) SEQ,
COUNT(* ) OVER(PARTITION BY INSIL.PARTNER_CODE ) TOT_SUB,
SIH.PARTNER_CODE,
SIH.PARTNER_SUB_CODE,
SIH.INV_DATE,
SIH.NUM_LINES,
SIH.SHIPCOMP_CODE,
SIH.DEPARTMENT_CODE,
SIH.COMPANY_CODE,
SIH.SYSTEM_ID,
SIH.INV_NUMBER,
SIH.INV_STATUS,
SIH.INV_NUMBER OUR_REF,
SIH.INV_SOURCE INV_SOURCE,
SIH.EXL_LEDGER,
SIH.CURRENCY T_CURRENCY_CODE,
SIH.INV_AMOUNT SIH_INV_AMOUNT,
SIH.BASE_AMOUNT T_BASE_AMOUNT,
SIH.BASE_VAT_AMT T_BASE_VAT_AMT,
SIH.PARTNER_CODE,
SIH.PARTNER_SUB_CODE,
SIH.INV_TYPE DOC_TYPE,
DECODE(SIH.INV_TYPE,'I','D',
'C','C',
'C') TYPE_DC,
DECODE(SIH.INV_TYPE,'C',SUBSTR(SIH.ORIG_INV_NUMBER,1,12),
'') MATCH_REF,
DECODE(SIH.INV_TYPE,'I',SIH.INV_NUMBER
||'01',
'C',SUBSTR(SIH.ORIG_INV_NUMBER,1,12)
||'02',
SIH.INV_NUMBER
||'01') COMM_INV_NUM,
SIH.DUE_DATE,
SIH.VOYAGE_REFERENCE,
SIH.INV_DATE,
SIH.DEPARTMENT_CODE,
SIH.PAYMENT_OFFICE,
SIH.IMPORT_EXPORT,
SIH.BOL_NUMBER,
SIH.POINT_FROM,
SIH.POINT_LOAD,
SIH.POINT_DISCH,
SIH.VISIT_NO,
SIH.DISCH_VISIT_NO,
SIH.POINT_TO,
SIH.SERVICE_NO,
DECODE(SIH.IMPORT_EXPORT,'I',SIH.ETA_DATE,
SIH.ETD_DATE) VESSEL_DATE,
SIH.APPLICATION_ID T_APPLICATION_ID,
SIH.EXCH_RATE T_EXCH_RATE,
SIH.VAT_AMOUNT_AG_CUR T_VAT_AMOUNT_AG_CUR,
SIH.AGENT_CURRENCY T_AGENT_CURRENCY,
SIH.CARRIER_NUMBER T_CARRIER_NUMBER,
SIH.ADDR_NUMBER T_ADDR_NUMBER,
SIH.ADDRESS_TYPE T_ADDRESS_TYPE,
SIH.INV_SUB_TYPE T_INV_SUB_TYPE,
SIH.INV_NUMBER_GROUP T_INV_NUMBER_GROUP,
SIH.LOCAL_VOYAGE_REFERENCE T_LOCAL_VOYAGE_REFERENCE,
SIH.INV_LOCAL_PAYMENT_REF T_INV_LOCAL_PAYMENT_REF,
SIH.LOGON_ID,
SIH.BOL_TYPE,
SIH.CONSIGNEE_CODE,
SIH.NOTIFY_CODE,
SIH.DECIDING_PARTY_CODE,
SIH.BOOKING_PARTY_CODE,
SIH.INVOICE_MODE_CODE,
SIH.INSIH_CURRENCY_ROE_TYPE INSIH_CCY_ROE_TYPE,
SIH.INSIH_CURRENCY_ROE_DATE INSIH_CCY_ROE_DATE,
COMPANY.VAT_NO,
COMPANY.CURRENCY_CODE BASE_CURRENCY,
INSIL.CURRENCY,
INSIL.VAT_CODE,
INSIL.VAT_AMOUNT,
INSIL.VAT_RATE,
INSIL.VAT_AMOUNT_AG_CUR,
INSIL.INV_AMOUNT,
INSIL.CHARGE_CODE,
INSIL.CARRIER_NUMBER,
INSIL.CURRENCY_AMOUNT,
INSIL.CHG_RATE,
INSIL.CHG_QUANTITY,
INSIL.CHG_LOCATION,
INSIL.BASIC_EXCH_RATE,
INSIL.INVOICE_CHARGE_GROUP,
INSIH.PARTNER_CODE,
INSIH.PARTNER_SUB_CODE,
INSIH.INV_NUMBER,
INSIL.LINE_NUMBER,
INSIH.SHIPCOMP_CODE,
INSIL.CALC_TYPE,
INSIL.PAYMENT_METHOD,
INSIH.CURRENCY,
INSIH.DEPARTMENT_CODE
FROM SALES_INV_HEADERS SIH,
COMPANY,
(SELECT INSIL.CURRENCY,
INSIL.VAT_CODE,
INSIH.PARTNER_CODE INSIL.VAT_AMOUNT,
INSIL.VAT_RATE,
INSIL.VAT_AMOUNT_AG_CUR,
INSIL.INV_AMOUNT,
INSIL.CHARGE_CODE,
INSIL.CARRIER_NUMBER,
INSIL.CURRENCY_AMOUNT,
INSIL.CHG_RATE,
INSIL.CHG_QUANTITY,
INSIL.CHG_LOCATION,
INSIL.BASIC_EXCH_RATE,
INSIL.INVOICE_CHARGE_GROUP,
INSIH.PARTNER_CODE,
INSIH.PARTNER_SUB_CODE,
INSIH.INV_NUMBER,
INSIL.LINE_NUMBER,
INSIH.SHIPCOMP_CODE,
INSIL.CALC_TYPE,
INSIL.PAYMENT_METHOD,
INSIH.CURRENCY,
INSIH.DEPARTMENT_CODE
FROM SALES_INV_LINES INSIL,
SALES_INV_HEADERS INSIH
WHERE INSIL.INV_NUMBER = INSIH.INV_NUMBER
AND INSIL.COMPANY_CODE = INSIH.COMPANY_CODE
AND INSIL.BULK_SUFFIX = INSIH.BULK_SUFFIX
AND INSIL.SYSTEM_ID = INSIH.SYSTEM_ID
AND INSIL.COMPANY_CODE = INSIH.COMPANY_CODE) INSIL
WHERE INSIH.COMPANY_CODE = :p_code
AND INSIH.INV_STATUS = :p_status
--validation of agents and partners
AND ((INSIH.PARTNER_CODE IN (SELECT EDMES.PARTNER_CODE
FROM EDI_MESSAGE EDMES
WHERE EDMES.COMPANY_CODE = :p_code
AND EDMES.APPLICATION = :p_application_1)
OR INSIH.PARTNER_CODE IN (SELECT EDGRP.GROUP_PARTNER_CODE
FROM EDI_PARTNER_GROUP_DETAILS EDGRP
WHERE EDGRP.PARTNER_CODE IN (SELECT PARTNER_CODE
FROM EDI_MESSAGE EDMES
WHERE EDMES.COMPANY_CODE = :p_code
AND EDMES.APPLICATION = :p_application_1)))
OR INSIH.DEPARTMENT_CODE IN (SELECT DEPT.DEPARTMENT_CODE
FROM DEPARTMENTS DEPT
WHERE DEPT.COMPANY_CODE = :p_code
AND DEPT.PARTNER_CODE IN (SELECT PARTNER_CODE
FROM EDI_MESSAGE EDMES
WHERE EDMES.COMPANY_CODE = :p_code
AND EDMES.APPLICATION = :P_application)))
AND INSIH.INV_NUMBER BETWEEN :p_from_number
AND :p_to_number
AND (INSIH.INV_DATE >= :p_to_date
AND INSIH.INV_DATE < :p_from_date);
-
Attachment: query.sql
(Size: 6.35KB, Downloaded 1243 times)
|
|
|
|
Re: Query optimization [message #360418 is a reply to message #359708] |
Thu, 20 November 2008 22:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/56289.jpg) |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Did you mean this query:
SELECT sih.Company_Code,
sih.System_Id,
sih.Bulk_Suffix,
sih.Inv_Number,
sih.Inv_Status,
sih.exl_Ledger,
Row_number() OVER(PARTITION BY sih.Partner_Code ORDER BY sih.Partner_Code) seq,
COUNT(* ) OVER(PARTITION BY Insil.Partner_Code ) Tot_Sub,
sih.Partner_Code,
sih.Partner_Sub_Code,
sih.Inv_Date,
sih.num_Lines,
sih.Shipcomp_Code,
sih.Department_Code,
sih.Company_Code,
sih.System_Id,
sih.Inv_Number,
sih.Inv_Status,
sih.Inv_Number Our_ref,
sih.Inv_Source Inv_Source,
sih.exl_Ledger,
sih.Currency t_Currency_Code,
sih.Inv_Amount sih_Inv_Amount,
sih.Base_Amount t_Base_Amount,
sih.Base_Vat_Amt t_Base_Vat_Amt,
sih.Partner_Code,
sih.Partner_Sub_Code,
sih.Inv_Type Doc_Type,
DECODE(sih.Inv_Type,'I','D',
'C','C',
'C') Type_dc,
DECODE(sih.Inv_Type,'C',Substr(sih.Orig_Inv_Number,1,12),
'') Match_ref,
DECODE(sih.Inv_Type,'I',sih.Inv_Number
||'01',
'C',Substr(sih.Orig_Inv_Number,1,12)
||'02',
sih.Inv_Number
||'01') comm_Inv_num,
sih.Due_Date,
sih.Voyage_Reference,
sih.Inv_Date,
sih.Department_Code,
sih.Payment_Office,
sih.Import_Export,
sih.bol_Number,
sih.Point_From,
sih.Point_Load,
sih.Point_Disch,
sih.Visit_No,
sih.Disch_Visit_No,
sih.Point_To,
sih.Service_No,
DECODE(sih.Import_Export,'I',sih.eta_Date,
sih.etd_Date) Vessel_Date,
sih.Application_Id t_Application_Id,
sih.exch_Rate t_exch_Rate,
sih.Vat_Amount_ag_Cur t_Vat_Amount_ag_Cur,
sih.Agent_Currency t_Agent_Currency,
sih.Carrier_Number t_Carrier_Number,
sih.Addr_Number t_Addr_Number,
sih.Address_Type t_Address_Type,
sih.Inv_Sub_Type t_Inv_Sub_Type,
sih.Inv_Number_Group t_Inv_Number_Group,
sih.Local_Voyage_Reference t_Local_Voyage_Reference,
sih.Inv_Local_Payment_ref t_Inv_Local_Payment_ref,
sih.LogOn_Id,
sih.bol_Type,
sih.Consignee_Code,
sih.Notify_Code,
sih.decIdIng_Party_Code,
sih.BookIng_Party_Code,
sih.Invoice_Mode_Code,
sih.Insih_Currency_Roe_Type Insih_ccy_Roe_Type,
sih.Insih_Currency_Roe_Date Insih_ccy_Roe_Date,
Company.Vat_No,
Company.Currency_Code Base_Currency,
Insil.Currency,
Insil.Vat_Code,
Insil.Vat_Amount,
Insil.Vat_Rate,
Insil.Vat_Amount_ag_Cur,
Insil.Inv_Amount,
Insil.Charge_Code,
Insil.Carrier_Number,
Insil.Currency_Amount,
Insil.chg_Rate,
Insil.chg_Quantity,
Insil.chg_Location,
Insil.Basic_exch_Rate,
Insil.Invoice_Charge_Group,
Insih.Partner_Code,
Insih.Partner_Sub_Code,
Insih.Inv_Number,
Insil.Line_Number,
Insih.Shipcomp_Code,
Insil.calc_Type,
Insil.Payment_Method,
Insih.Currency,
Insih.Department_Code
FROM Sales_Inv_Headers sih,
Company,
(SELECT Insil.Currency,
Insil.Vat_Code,
Insih.Partner_Code Insil.Vat_Amount,
Insil.Vat_Rate,
Insil.Vat_Amount_ag_Cur,
Insil.Inv_Amount,
Insil.Charge_Code,
Insil.Carrier_Number,
Insil.Currency_Amount,
Insil.chg_Rate,
Insil.chg_Quantity,
Insil.chg_Location,
Insil.Basic_exch_Rate,
Insil.Invoice_Charge_Group,
Insih.Partner_Code,
Insih.Partner_Sub_Code,
Insih.Inv_Number,
Insil.Line_Number,
Insih.Shipcomp_Code,
Insil.calc_Type,
Insil.Payment_Method,
Insih.Currency,
Insih.Department_Code
FROM Sales_Inv_Lines Insil,
Sales_Inv_Headers Insih
WHERE Insil.Inv_Number = Insih.Inv_Number
AND Insil.Company_Code = Insih.Company_Code
AND Insil.Bulk_Suffix = Insih.Bulk_Suffix
AND Insil.System_Id = Insih.System_Id
AND Insil.Company_Code = Insih.Company_Code) Insil
WHERE Insih.Company_Code = :p_code
AND Insih.Inv_Status = :p_status
--validation of agents and partners
AND ((Insih.Partner_Code IN (SELECT edMes.Partner_Code
FROM edi_Message edMes
WHERE edMes.Company_Code = :p_code
AND edMes.Application = :p_application_1)
OR Insih.Partner_Code IN (SELECT edgrp.Group_Partner_Code
FROM edi_Partner_Group_Details edgrp
WHERE edgrp.Partner_Code IN (SELECT Partner_Code
FROM edi_Message edMes
WHERE edMes.Company_Code = :p_code
AND edMes.Application = :p_application_1)))
OR Insih.Department_Code IN (SELECT dept.Department_Code
FROM Departments dept
WHERE dept.Company_Code = :p_code
AND dept.Partner_Code IN (SELECT Partner_Code
FROM edi_Message edMes
WHERE edMes.Company_Code = :p_code
AND edMes.Application = :P_application)))
AND Insih.Inv_Number BETWEEN :p_from_number
AND :p_to_number
AND (Insih.Inv_Date >= :p_to_date
AND Insih.Inv_Date < :p_from_date);
With a slightly more readable version for analysis purposes being this:
SELECT *
Row_number() OVER(PARTITION BY sih.Partner_Code ORDER BY sih.Partner_Code) seq,
COUNT(* ) OVER(PARTITION BY Insil.Partner_Code ) Tot_Sub
FROM Sales_Inv_Headers sih,
Company,
(SELECT *
FROM Sales_Inv_Lines Insil,
Sales_Inv_Headers Insih
WHERE Insil.Inv_Number = Insih.Inv_Number
AND Insil.Company_Code = Insih.Company_Code
AND Insil.Bulk_Suffix = Insih.Bulk_Suffix
AND Insil.System_Id = Insih.System_Id
AND Insil.Company_Code = Insih.Company_Code) Insil
WHERE Insih.Company_Code = :p_code
AND Insih.Inv_Status = :p_status
AND Insih.Inv_Number BETWEEN :p_from_number AND :p_to_number
AND Insih.Inv_Date >= :p_to_date
AND Insih.Inv_Date < :p_from_date
AND (
(Insih.Partner_Code IN (SELECT edMes.Partner_Code
FROM edi_Message edMes
WHERE edMes.Company_Code = :p_code
AND edMes.Application = :p_application_1
)
OR Insih.Partner_Code IN (SELECT edgrp.Group_Partner_Code
FROM edi_Partner_Group_Details edgrp
WHERE edgrp.Partner_Code IN (SELECT Partner_Code
FROM edi_Message edMes
WHERE edMes.Company_Code = :p_code
AND edMes.Application = :p_application_1
)
)
)
OR Insih.Department_Code IN (SELECT dept.Department_Code
FROM Departments dept
WHERE dept.Company_Code = :p_code
AND dept.Partner_Code IN (SELECT Partner_Code
FROM edi_Message edMes
WHERE edMes.Company_Code = :p_code
AND edMes.Application = :P_application
)
)
)
Unless my cut/paste hack has dropped some lines, you are maybe missing joins between the tables? Where do you join to COMPANY? Where do you join to Sales_Inv_Headers?
Kevin
|
|
|
Goto Forum:
Current Time: Mon Sep 09 01:23:35 CDT 2024
|