| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: using hints on views
I would say it's a different way ;-)
It depends on the data, tables joined, and other things. It may look and work great in typical emp-dept example: few departments, few dozens employees. When you use GLOBAL hints you can overwrite hints used inside the view but I don't think you can change order of tables. Maybe you can? I don't know.
If you had tens of thousands departments and millions employees, it would make a big difference when you use ORDERED hint.
Also, if I use the view in different applications, I have to write hints in each applictaion. If the hints are inside a view, there is only one place.
Witold
On 4 Jan 2002 at 7:10, Paul Baumgartel wrote:
> 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).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec INET: wiwaniec_at_novalistech.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 - 11:30:45 CST
 |
 |