Sql Tunning ..

EXPLAIN PLAN FOR SELECT /*+ INDEX(RH XPK_CUSTOMER_HOMES) INDEX(CD IDX_CUSTOMER_DETAILS) */
COUNT(CD.invoice_no) FROM CUSTOMER_DETAILS CD,CUSTOMER_INVOICES FI ,CUSTOMER_HOMES CH
WHERE RH.CUSTOMER_ID = CD.CUSTOMER_ID
AND CD.invoice_no = FI.invoice_no
AND CH.invoice_no = CD.invoice_no
AND FI.invoice_date BETWEEN to_date('01-feb-03') AND to_date('04-feb-03') ;

Production
SELECT STATEMENT Cost = 690412
SORT AGGREGATE
MERGE JOIN
SORT JOIN
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID CUSTOMER_INVOICES 2
INDEX RANGE SCAN IDX_CUSTOMER_INVOICES NON-UNIQUE
TABLE ACCESS BY INDEX ROWID CUSTOMER_DETAILS 1
INDEX RANGE SCAN IDX_CUSTOMER_DETAILS NON-UNIQUE
SORT JOIN
TABLE ACCESS BY INDEX ROWID CUSTOMER_HOMES 3
INDEX FULL SCAN XPK_CUSTOMER_HOMES UNIQUE



Development

SELECT STATEMENT Cost =
SORT AGGREGATE
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID CUSTOMER_INVOICES 2
INDEX RANGE SCAN IDX_CUSTOMER_INVOICES NON-UNIQUE
TABLE ACCESS BY INDEX ROWID CUSTOMER_DETAILS 1
INDEX RANGE SCAN IDX_CUSTOMER_DETAILS NON-UNIQUE
TABLE ACCESS BY INDEX ROWID CUSTOMER_HOMES 3
INDEX UNIQUE SCAN XPK_CUSTOMER_HOMES UNIQUE


Production is very slow .
All tables and Indexes are analyzed.
all parameters are same in dev and production .

is the data the same in production and development? Have you tried it without hints? Is the hardware the same? Run tkprof to see what is really going on.

The data is the same in production and development.
Have you tried it without hints?
Yes .tried also (RULE/ORDERED/USE_NL)

Is the hardware the same?
Yes ...

Run tkprof to see what is really going on.

Hi subha

Is the number of records same in production and devlopment..

when were the tables last analyzed in production and development..
what about the table stats are they same in production and development..

regards
Hrishy

Production database is performing sort operations which is causing this delay.

Please check XPK_CUSTOMER_HOMES index in both the database and reanalyze it, becuase it is doing full scan for this index in development. Make sure this index is identical on both databases.

Why This Merge Join ???
I get Good Performance in Nested Loops .
How to avoid Merge Join ...