Index or Hint, which one is efficient? [message #23511] |
Fri, 13 December 2002 15:22 |
Reddy Peram
Messages: 52 Registered: December 2002
|
Member |
|
|
I have an index on 5 columns for a tableA. I am using the first 4 columns in the Where clause in an UPDATE statement to update tableA.
Creating a new index on 4 columns OR forcing an optimizer to use the existing index, which one is more efficient?
|
|
|
Re: Index or Hint, which one is efficient? [message #23512 is a reply to message #23511] |
Fri, 13 December 2002 15:37 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
I'm assuming your stats are current and the optimizer is still not using the index in question. You could add a hint - certainly don't add another index - but I would recommend to first adjust the optimizer_index_cost_adj setting (the default of 100 is way too high) and checking the plan.
You can adjust this at the session level (alter session set optimizer_index_cost_adj = 5) and then set it in init.ora once you have found a suitable value. Don't be concerned with a low value - my systems run with a value of 1.
|
|
|
|
|