sql optimization
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: sql optimization

  1. #1
    Join Date
    Jan 2004
    Posts
    2

    sql 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_EURO),
    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

  2. #2
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317

    Re: sql optimization

    For starters, I guess, you can remove this:
    ( INVOICE_MASTER.ITEM_ID = INVOICE_MASTER.ITEM_ID )
    My questions to you:
    -What Oracle version are we talking about?
    -Do you have the proper, analyzed, indexes on the WHERE-columns?
    -Are all tables analyzed with DBMS_STATS()?
    -Do you use histograms?
    -If the first hard parse takes a long time, you might have to look at OPTIMIZER_MAX_PERMUTATIONS and OPTIMIZER_SEARCH_LIMIT parameters.

    I'm not a SQL-wizzard, and am looking for a good book for SQL-optimizing. Suggestions anyone?

    HTH
    Last edited by efrijters; 01-29-2004 at 03:04 AM.
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Code:
     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'))
    The CBO often does a poor job on an OR involving columns from different tables. My first thought would be to see if removing the "OR CUSTOMER. . . . . " clause makes it run faster. If so, then you could split the full query into two selects with a UNION.

  4. #4
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    And also make sure you have placed the DRIVING table (which returns less no of recrods)first in the FROM clause.
    anandkl

Posting Permissions

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



Click Here to Expand Forum to Full Width