slow performance of MERGE statement

Hi

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?

Are you saying you have a merge in between tables A and B and, Oracle is reading from a third table?

If Yes... look at your Synonyms

No, what I am saying is MERGE statement use incorrect index and gather wrong number of rows by using that index

Would you mind in posting actual MERGE statement?
Can you eventually drop the incorrect index and check the execution plan?
Have you checked your execution plan after including the hint?

For security reason, I can't post the statement. I plan to try the hint first and see if it solves the problem.

Yes

HTML Code:

---

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?

---

Yes, it could help you. But need to be tested. Some times oracle picks wrong indexes, reason could be insufficient stats or bind variable...

Malay,

we gather stats on all index and columns with estimated size = 100 every night, what do you mean "bind variable" is a reason?

Is this post a question or just a conclusion? Seems u know everything and know that the index is wrong and that you have done the things fine and CBO is wrong. Then if you hint it it should be better

I just want to konw if anyone else experienced the same issue before and know in and out of it.

Once again...

Can you eventually drop the incorrect index and check the execution plan?
Can you eventually rebuild both the incorrect and the expected index and check the execution plan?
Have you checked your execution plan after including the hint?

Add a dynamic sampling hint (level 4 is a good start) and see if it affects the plan -- increase the dynamic sampling level and see if that changes it also