CODEWITHSUNDEEP
sqloracle-databaseperformanceindexingquery-optimization
Mtho
Mtho

Reputation: 21

Oracle use a hint index to a view and force it to base table

How to use a hint on a query against a view to force Oracle to use an index on the base table?

E.g

View

select emp_id 
from v_employees

base table 

select emp_id /*+ INDEX(employees IDX_emp_id) */ 
from employees

I want to read from the view but use the index from the base table, without changing the view query. Something like this:

select emp_id /*+ INDEX(v_employees employees IDX_emp_id) */
from v_employees

Upvotes: 2

Views: 34166

Answers (2)

APC
APC

Reputation: 146239

We can use global hints to reference the tables inside views. The view name is nested with the table name(s) or alias(es) (if we want to access indexes on tables in subqueries) using ..

So in your example it would be:

select /*+ INDEX(v_employees.employees IDX_emp_id) */ emp_id 
from v_employees

Note that the hint comes before the projection.

Upvotes: 3

Popeye
Popeye

Reputation: 35910

According to Oracle documentation

You can send hints for a SQL statement to the optimizer by enclosing them in a comment within the statement.

A block in a statement can have only one comment containing hints following the SELECT, UPDATE, MERGE, or DELETE keyword.

SELECT /*+ hint [text] [hint[text]]... */ COLUMNS... FROM TABLE....

So in you case, Use the Hint following the SELECT keyword.

select /*+ INDEX(employees IDX_emp_id) */ emp_id  from employees

Cheers!!

Upvotes: 0

Related Questions