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.
Any ideas?
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