sql optimization

[paginit]

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

[efrijters]

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

[DaPi]

 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.

[anandkl]

And also make sure you have placed the DRIVING table (which returns less no of recrods)first in the FROM clause.