I have a merge statement which is used to INSERT/UPDATE table A from another table B, table A and table B has exactly same structure, I expect Oracle to use the primary key of table A, but it doesn't, instead, Oracle choose to use a foreign key on table A - FK_A_TO_C, the table this foreign key pointing to is an empty table - table C, I checked the 10046 trace of the statement, it shows:
0 TABLE ACCESS BY INDEX ROWID A(cr=29511977 pr=0 pw=0 time=1184298365 us)
1041617280 INDEX RANGE SCAN FK_A_TO_C (cr=2859856 pr=0 pw=0 time=142514 us)(object id 101659)
Although table C is empty, table A contains around 100000 rows, table B contains 10000 rows, but Oracle use FK_A_TO_C index to gather 1041617280 rows, and it takes 1184298365/1000000=1184 seconds, why Oracle is doing this? Should I add /*+ INDEX(A PK_A) */ hint to MERGE statement to force it to use PK_A or use a stored outline?