Hi
I am analyzing a query performance, and have a problem when trying to use optimizer hint.
I have the same db on both, production server and test server.
When I analyze the same query on both servers, I have different explain plans.
On test server it runs fast and on production server it has a poor performance. The problem is that on test server it uses an index on CONTRATO table, while on production server, it makes a table scan on CONTRATO table.
I had already posted a thread today about different explain plans on different servers :
469124
This table is a big one( 3034774 rows and 1,93 gb) and scans on that is a problem for any query.
Here is explain plans for both:
Test server
SELECT STATEMENT, GOAL = CHOOSE 9 1 80
_FILTER
__NESTED LOOPS 7 1 80
___NESTED LOOPS 6 1 52
____TABLE ACCESS BY INDEX ROWID BMCTOOLS PARCELA 5 1 27
_____INDEX RANGE SCAN BMCTOOLS DT_MOV 4 1
____TABLE ACCESS BY INDEX ROWID BMCTOOLS CONTRATO 2 1 25
_____INDEX UNIQUE SCAN BMCTOOLS CONTRATO_PK01 65
___TABLE ACCESS BY INDEX ROWID BMCTOOLS PRODUTO 2 1 28
____INDEX UNIQUE SCAN BMCTOOLS SYS_C0022492 1
__INDEX UNIQUE SCAN BMCTOOLS CESSAO_PK 1 1 9
Production server.
SELECT STATEMENT, GOAL = CHOOSE 4646 1408 123904
_FILTER
__TABLE ACCESS BY INDEX ROWID BMCTOOLS PARCELA 2 1 30
___NESTED LOOPS 1830 1408 123904
____MERGE JOIN 1773 1402 81316
_____TABLE ACCESS BY INDEX ROWID BMCTOOLS PRODUTO 3 77 2310
______INDEX FULL SCAN BMCTOOLS SYS_C0022492 1 77
_____SORT JOIN 1771 1491 41748
______TABLE ACCESS FULL BMCTOOLS CONTRATO 1767 1491 41748
____INDEX RANGE SCAN BMCTOOLS PARCELA_I5 3 10
__INDEX UNIQUE SCAN BMCTOOLS CESSAO_PK 1 1 10
To solve the problem, I tried to use the following index hint on select, but it did not work:
/*+ index_desc (contrato contrato_pk01)*/
Why Oracle is not using the index I "forced"?
Thanks in advance
Alex