Hi All,
I have oracle 11gR2 database. I some history tables which has 15+ millions of data. I'm executing the select query with where clause of having two columns, on index defined on it and remaining 3 columns which does not have index defined. If I ran the query first time it return the rows in 10 secs but If I reran the query it taking 35 to 40 mins.
So I went through the discussion which have similar problem. They have used /*+ opt_param('_optimizer_use_feedback','false') */ hint in the select query and performance improved. For the same scenario, What I did is used index hint in the select query it return the rows very fast, executed several times I got same time interval like in milli secs.
query example:
select * from emp_history where empno='1234' and hire_dt='12 February 2014' and region='USA' and reference_no like '%789456%' and salary='2300.50'.
Here my question: 1) which one is preferable to use in the select query like optimizer_user_feedback or index hint in the production environment.
Please check and help me to resolve the issue. Many thanks.
Regards,
Raj