CODEWITHSUNDEEP
sqloracle-databaseoptimizationoracle11g
Michael Kali
Michael Kali

Reputation: 36

Query does not use index when used as a view

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.

Upvotes: 0

Views: 1192

Answers (2)

APC
APC

Reputation: 146239

Your standalone query includes this WHERE clause:

Where department in (1,4,7,8)

So it would be efficient to use an index to retrieve records for a few departments.

Your view does not filter on by department so it's going to execute a Full Table Scan. What you're hoping is that the query using the view is going to push the department predicate into the view's query ...

From Employees​ e Left join
     Departments_Avg_Salary d
     On d.department = e.Employees_Department

... but alas the optimizer doesn't work like that

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You are probably best off writing the query like this:

Select e.Employee_Name, e.Employee_Salary,
       (select avg(s.salary)
        from salaries s
        where s.department = e.Employees_Department
       ) avg_salary
From Employees​ e 
Where e.Employee_Name in ('Michael', 'Nittai', 'Jack');

And then having indexes on employees(Employee_name) and salaries(department, salary).

The index in your original query is probably being used for the WHERE clause, not the GROUP BY. As for hints in views, I would start with the warning in the documentation:

Oracle does not encourage the use of hints inside or on views (or subqueries). This is because you can define views in one context and use them in another. Also, such hints can result in unexpected execution plans. In particular, hints inside views or on views are handled differently, depending on whether the view is mergeable into the top-level query.

Upvotes: 1

Related Questions