very nice, but if i use empno index then there is no empno index on bonus table so it does a full table scan on bonus table.
I'm usnig something like this:
select /*+ index (emp EMPNO)*/e.ename, d.dname, b.comm from
emp e, dept d, bonus b
It does a index scan on emp but full scan on bonus.
I guess what Slimdave is trying to say is, that hints are only valid for a specific moment. If the load of the table changes, the hint must be altered. This makes your life as a dba harder (in other words: you must work harder )
An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.
I would do more investigation into why performance is not-so-good without the index hints before resorting to them -- I'm assuming there that you have verfified that the hinted query really is better than the non-hinted one, and you have gathered statistics on the tables, indexed columns, and indexes.