I wrote a query that when executed on several values - uses the table's index and returns results quickly.
When I created a view using the very same syntax, the index sometimes remain unused.
For example: when querying the following query, the index DEP_IDX is used, and the query takes a few seconds to return results:
Select /*+INDEX (s DEP_IDX) */ department, avg(salary) as dep_avg From salaries s Where department in (1,4,7,8) Group by department
When I create a view using the same syntax, like this:
Create or replace view Departments_Avg_Salary as Select /*+INDEX (s DEP_IDX)*/ department, avg(salary) as dep_avg From salaries s Group by department
And then use the view in a query:
Select e.Employee_Name, e.Employee_Salary, d.dep_avg From Employees e Left join Departments_Avg_Salary d On d.department = e.Employees_Department Where e.Employee_Name in ('Michael', 'Nittai', 'Jack')
The Index is not used and the query takes a lifetime to return!
As you can see, using the INDEX hint made no difference...
As it turns out, given the table's huge size, there will be no scenario in which using table access storage full will be the efficient way, so I am really looking for a solution that will force the DB to use the index.
Thanks in advance.
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `salaries` ADD INDEX `salaries_idx_department` (`department`);
SELECT s.department, avg(s.salary) AS dep_avg FROM salaries s WHERE s.department IN ( 1, 4, 7, 8 ) GROUP BY s.department ORDER BY NULL