Home » SQL & PL/SQL » SQL & PL/SQL » multiple optimizer hints
icon5.gif  multiple optimizer hints [message #256769] Mon, 06 August 2007 10:08 Go to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
Does Oracle allow multiple optimizer hints for one SQL statement?

When I specify multiple optimizer hints like:

select      /*+ ALL_ROWS + ORDERED */
...


the query plan does not show that the second hint was used.

When I make the second hint to be the first, as in:

select      /*+ ORDERED + ALL_ROWS */
...


then I see in the query plan that it was used.

I have also tried separate comment lines, like:

select      /*+ ALL_ROWS */
            /*+ ORDERED  */
...


and

select      /*+ ORDERED  */
            /*+ ALL_ROWS */
...


and get the same results.

Thanks for your help.
Re: multiple optimizer hints [message #256770 is a reply to message #256769] Mon, 06 August 2007 10:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL Reference
Chapter 2 Basic Elements of Oracle SQL
Section Comments
Subsection Using Hints

Regards
Michel
icon3.gif  Re: multiple optimizer hints [message #256771 is a reply to message #256769] Mon, 06 August 2007 10:23 Go to previous messageGo to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
Thank you!
I took out the second plus sign (+) and the second hint was used.

Evidently, the second plus sign (+) before the second hint is interpreted as something besides a string ... ?

Re: multiple optimizer hints [message #256772 is a reply to message #256771] Mon, 06 August 2007 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is ignored as it is not a hint.
You can put what you want, if it is not a hint, it is ignored.

Regards
Michel
Re: multiple optimizer hints [message #256773 is a reply to message #256772] Mon, 06 August 2007 10:35 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Just to finish a sentence:
Michel
You can put what you want, if it is not a hint, it is ignored ...
... as it is considered to be a comment.
Re: multiple optimizer hints [message #256776 is a reply to message #256769] Mon, 06 August 2007 10:51 Go to previous messageGo to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
The "Using Hints" section that you refered me to specifies that you can intersperse strings in between hints:

Quote:
"string" is other commenting text that can be interspersed with the hints.



The following does work:

select      /*+ ALL_ROWS blablabla ORDERED  */


The following does not work:

select      /*+ ALL_ROWS + ORDERED  */


So, I think I am misunderstanding what you are saying.

I have my problem resolved, so only respond if you wish.

Thanks for your time.


Re: multiple optimizer hints [message #256778 is a reply to message #256776] Mon, 06 August 2007 10:55 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh! I misunderstood you.
So with '+' it does not work.
Maybe a bug or a hidden tag.

Regards
Michel

Previous Topic: inconsistent datatypes: expected - got CLOB
Next Topic: UTL package
Goto Forum:
  


Current Time: Tue Dec 06 08:26:02 CST 2016

Total time taken to generate the page: 0.20065 seconds