Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Location
    India
    Posts
    191

    Thumbs up Unanswered: Query Optimization

    Hi,
    The below query is taking long time to run. Can anyone help me to tune the below query.


    SELECT DISTINCT
    sum(INVOICE_MASTER.NET_SALES*INVOICE_MASTER.EXRT_E URO),
    CUSTOMER_SOLD.CUST_CODE,
    CUSTOMER_SOLD.CUST_NAME,
    ITEM_MST.HPL_CODE,
    DB_MST.REPORT_REGION_NAME,
    CUSTOMER.CUST_CODE,
    CUSTOMER.CUST_NAME
    FROM
    INVOICE_MASTER,
    CUSTOMER CUSTOMER_SOLD,
    ITEM_MST,
    DB_MST,
    CUSTOMER,
    OPERATION_WEEK
    WHERE
    ( INVOICE_MASTER.ITEM_ID = INVOICE_MASTER.ITEM_ID )
    AND ( INVOICE_MASTER.OPERATION_WEEK = OPERATION_WEEK.OPERATION_WEEK )
    AND ( INVOICE_MASTER.DB_CODE = DB_MST.DB_CODE )
    AND ( CUSTOMER_SOLD.CUST_ID=INVOICE_MASTER.CUST_SOLD_ID )
    AND ( CUSTOMER.CUST_ID = INVOICE_MASTER.CUST_BILL_ID )
    AND (
    decode(ITEM_MST.SBG_CODE,'CO','CDC','SA','CDC','FO ','CDC','IC','MAI','1','EPI','7','AUT','8','MIC',' Unassigned') = 'CDC'
    AND OPERATION_WEEK.FISCAL_MONTH BETWEEN 200301 AND 200304
    AND (CUSTOMER_SOLD.CUST_CODE IN ('415A003', '11645906', '13338901', '13338902', '11645901', '11070908', '14581901')
    OR CUSTOMER.CUST_CODE IN ('415A003', '11645906', '13338901', '1333902', '11645901', '11070908', '14581901'))
    )
    GROUP BY
    CUSTOMER_SOLD.CUST_CODE,
    CUSTOMER_SOLD.CUST_NAME,
    ITEM_MST.HPL_CODE,
    DB_MST.REPORT_REGION_NAME,
    CUSTOMER.CUST_CODE,
    CUSTOMER.CUST_NAME



    EXPLAIN PLAN:

    OPERATION OPTIONS OBJECT_NAME
    ------------------------------ --------------- --------------------
    SELECT STATEMENT
    SORT GROUP BY
    CONCATENATION
    NESTED LOOPS
    NESTED LOOPS
    HASH JOIN
    TABLE ACCESS FULL DB_MST
    HASH JOIN
    TABLE ACCESS BY INDEX ROWID OPERATION_WEEK
    INDEX RANGE SCAN I_FISCAL_MONTH
    TABLE ACCESS BY GLOBAL INDEX INVOICE_MASTER

    OPERATION OPTIONS OBJECT_NAME
    ------------------------------ --------------- --------------------
    ROWID

    NESTED LOOPS
    INLIST ITERATOR
    TABLE ACCESS BY INDEX ROWID CUSTOMER
    INDEX RANGE SCAN IU_CUST_CODE
    INDEX RANGE SCAN I_INVOICE_BILL_TO
    TABLE ACCESS BY INDEX ROWID ITEM_MST
    INDEX UNIQUE SCAN PK_ITEM_MST
    TABLE ACCESS BY INDEX ROWID CUSTOMER
    INDEX UNIQUE SCAN PK_CUSTOMER

    OPERATION OPTIONS OBJECT_NAME
    ------------------------------ --------------- --------------------
    NESTED LOOPS
    NESTED LOOPS
    NESTED LOOPS
    NESTED LOOPS
    NESTED LOOPS
    TABLE ACCESS FULL CUSTOMER
    TABLE ACCESS BY GLOBAL INDEX INVOICE_MASTER
    ROWID

    INDEX RANGE SCAN I_INVOICE_BILL_TO
    TABLE ACCESS BY INDEX ROWID CUSTOMER

    OPERATION OPTIONS OBJECT_NAME
    ------------------------------ --------------- --------------------
    INDEX UNIQUE SCAN PK_CUSTOMER
    TABLE ACCESS BY INDEX ROWID OPERATION_WEEK
    INDEX UNIQUE SCAN PK_OPERATION_WEEK
    TABLE ACCESS BY INDEX ROWID ITEM_MST
    INDEX UNIQUE SCAN PK_ITEM_MST
    TABLE ACCESS BY INDEX ROWID DB_MST
    INDEX UNIQUE SCAN PK_DB_MST
    Pagnint
    (No need to search web before posting new question)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Query Optimization

    Have the tables been analyzed recently or at all? No cost information is showing in your EXPLAIN PLAN output.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •