There's a better way: use global hints in the queries that select from the views. From Designing and Tuning for Performance:
Global Hints Table hints (i.e., hints that specify a table) normally refer to tables in the DELETE, SELECT, or UPDATE statement in which the hint occurs, not to tables inside any views or subqueries referenced by the statement. When you want to specify hints for tables that appear inside views or subqueries, you should use global hints instead of embedding the hint in the view or subquery. You can transform any table hint in this chapter into a global hint by using an extended syntax for the table name, as described below. Consider the following view definitions and SELECT statement: CREATE VIEW v1 AS SELECT * FROM emp WHERE empno < 100; CREATE VIEW v2 AS SELECT v1.empno empno, dept.deptno deptno FROM v1, dept WHERE v1.deptno = dept.deptno; SELECT /*+ INDEX(v2.v1.emp emp_empno) FULL(v2.dept) */ * FROM v2 WHERE deptno = 20; The view V1 retrieves all employees whose employee number is less than 100. The view V2 performs a join between the view V1 and the department table. The SELECT statement retrieves rows from the view V2 restricting it to the department whose number is 20. There are two global hints in the SELECT statement. The first hint specifies an index scan for the employee table referenced in the view V1, which is referenced in the view V2. The second hint specifies a full table scan for the department table referenced in the view V2. Note the dotted syntax for the view tables. --- Witold Iwaniec <[EMAIL PROTECTED]> wrote: > I had to use hints inside views number of times and it worked well > but you have to be careful. Hinting just the SQL statement that > builds the view may be worse than no hinting at all. > When you add hints you have to keep in mind how you will use the > view. In result I ended up with few views, selecting the same > columns from the same tables but hinted and ordered differently. It > may look messy if you just look at the data dictionary but within > application you know which button is clicked, what you query on > etc... so you know which view you should use. > > HTH > > Witold > __________________________________________________ Do You Yahoo!? Send your FREE holiday greetings online! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
