The Oracle Weblog
Tag Archives: global optimizer hints
April 28, 2010Posted by on
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.
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.