Home » RDBMS Server » Performance Tuning » Query optimization (Oracle 10G)
icon5.gif  Query optimization [message #359644] Mon, 17 November 2008 16:29 Go to next message
habibsh785
Messages: 2
Registered: May 2008
Location: Dubai
Junior Member

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 151 times)
Re: Query optimization [message #359708 is a reply to message #359644] Tue, 18 November 2008 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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 (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the requested and usual information.

You can also have a look at Database Performance Tuning Guide.

Regards
Michel
Re: Query optimization [message #360418 is a reply to message #359708] Thu, 20 November 2008 22:26 Go to previous message
Kevin Meade
Messages: 2101
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
Previous Topic: Generic data models - academic questions
Next Topic: ORA-12013: updatable materialized views must be simple enough to do fast refresh
Goto Forum:
  


Current Time: Sun Dec 04 23:04:35 CST 2016

Total time taken to generate the page: 0.07609 seconds