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 <[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). ================================== Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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).
