Re: using hints on views

From: Paul Baumgartel <treegarden_at_yahoo.com>
Date: Fri, 04 Jan 2002 07:44:15 -0800
Message-ID: <F001.003E6EC1.20020104071023@fatcity.com>

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 <wiwaniec_at_novalistech.com> 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: treegarden_at_yahoo.com

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: ListGuru_at_fatcity.com (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).

Received on Fri Jan 04 2002 - 09:44:15 CST