Oracle Global Optimizer Hints

Table hints refer to the tables in the statement block in which the hint is specified. If you have a view in your statement block and you try to hint the base tables using a table hint, it won’t work.

Here we have two options:

1. Embed the hint in the view definition

2. Use GLOBAL hints

Oracle recommends using the Global Hints as preferred method to hint base tables inside the views. Any oracle specified table hint can be transformed into a global hint by using a bit of extra syntax for table name.

Consider the below example:

CREATE OR REPLACE VIEW V1 AS  SELECT *  FROM students  WHERE student_no < 200;

CREATE OR REPLACE VIEW V2 AS SELECT * v1.student_no ,v1.student_name ,lecturers.lecturer_name ,lecturers.subject ,lecturers.subject_ID  FROM V1 ,lecturers  WHERE v1.subject = lecturers.subject; 

SELECT /*+ INDEX(V2.V1.STUDENTS STUDENTS_N01) */ *
  FROM V2
 WHERE subject_ID = 02;

The above SELECT query has a global hint which specifies an INDEX Scan for table student which is referenced in view V1, which in turn is referenced in view v2. If I had used

/*+ INDEX(students students_n01)*/,

the optimizer would have ignored the hint as the STUDENTS table is not present in the FROM clause of the statement block.

Note:

1. In case of a UNION or UNION ALL in the view referenced by a GLOBAL HINT, the hint is applied to the first statement branch that contains the hinted table.

2. The global hint syntax also applies to un-mergeable views.