Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to hint hash join order on indexes ?

698658Nov 16 2010 — edited Nov 16 2010
Hi,
in my 9.2.0.8 DB I've got query like this:
SELECT COUNT (agreementno) cnt
FROM followup
WHERE agreementno = :v001 AND actioncode = :v002 AND resultcode = :v003;

Plan
SELECT STATEMENT  CHOOSECost: 11  Bytes: 18  Cardinality: 1  				
	5 SORT AGGREGATE  Bytes: 18  Cardinality: 1  			
		4 VIEW index$_join$_001 Cost: 11  Bytes: 18  Cardinality: 1  		
			3 HASH JOIN  Bytes: 18  Cardinality: 1  	
				1 INDEX RANGE SCAN NON-UNIQUE IDX_FOLLOWUP06 Cost: 13  Bytes: 18  Cardinality: 1  
				2 INDEX RANGE SCAN UNIQUE PK_FOLLOWUP Cost: 13  Bytes: 18  Cardinality: 1  
I need to change join order of indexes, so the proble one would be PK_FOLLOWUP .
Of course the best plan is index range scan on pk but during to hight CF Oracle is combining 2 indexes .
Regards.
Greg
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 14 2010
Added on Nov 16 2010
5 comments
1,260 views