Home » RDBMS Server » Performance Tuning » Using Index hint (merged)
Using Index hint (merged) [message #385620] Tue, 10 February 2009 05:11 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member

There should be no schema names in hints. Hints must use aliases if alias names are used for table names. So the following is wrong:
select /*+ index(scott.emp ix_emp) */ from scott.emp emp_alias

better:
select /*+ index(emp_alias ix_emp) */ ... from scott.emp emp_alias



In the link below its written that we should not use schema name while using INDEX hint but when I tried using schema it doesnt show any error.

[Source: http://www.adp-gmbh.ch/ora/sql/hints/index.html]



SELECT /*+ INDEX (employees emp_department_ix)*/ 
       employee_id, department_id 
  FROM employees 
  WHERE department_id > 50;


source: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements006.htm#SQLRF50405

Is it wrong if we use schema name?



SELECT /*+ INDEX (scott.employees scott.emp_department_ix)*/ 
       employee_id, department_id 
  FROM employees 
  WHERE department_id > 50;
Re: Using Index hint: Correct approach [message #385630 is a reply to message #385620] Tue, 10 February 2009 05:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You will never get an error from using a hint.
Invalid hints are quietly ignored.

Re: Using Index hint: Correct approach [message #385645 is a reply to message #385630] Tue, 10 February 2009 06:24 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
First of all, sorry for being muliposted. There was network error while posting the topic. My apologies.

Quote:

You will never get an error from using a hint.
Invalid hints are quietly ignored.



True, that means we cant use schema name?
Regards,
Oli


Re: Using Index hint: Correct approach [message #385664 is a reply to message #385645] Tue, 10 February 2009 07:54 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You use aliases.

Regards
Michel
Previous Topic: increase Cache size
Next Topic: procedure tunning (merged)
Goto Forum:
  


Current Time: Fri Dec 09 19:28:10 CST 2016

Total time taken to generate the page: 0.23786 seconds