Home » SQL & PL/SQL » SQL & PL/SQL » Use of /*All*/ or /*One*/ at the end of a Query
Use of /*All*/ or /*One*/ at the end of a Query [message #622365] Mon, 25 August 2014 13:19 Go to next message
sread11
Messages: 2
Registered: August 2014
Junior Member
In seeing what should be the same SQL running on different 11GR2 databases with very similar settings, I see different trailing hints (I guess) of /*All*/ and /*One*/ .
I can't find any documentation on these and they are not standard hints that I would expect. They also execute with different execution plans with the /*All*/ being much more efficient with a Merge Join vs Nested Loops.

Does anyone have any info on this, and how they could be different when executing the same code base against different databases?



DATABASE 1
SELECT D.COL1, D.COL2, D.COL3, SUM(D.COL4)
FROM
TABLE1 P, TABLE2 D WHERE ( 'Y' = 'Y' OR EXISTS ( SELECT
Q.COL3 FROM TABLE3 Q WHERE P.COL3= Q.COL3 AND Q.COL8 = :1
) ) AND D.COL5 = :2 AND D.COL6 = :3 AND
D.COL1 = P.COL7 AND D.COL2 = P.COL2 AND D.COL3 =
P.COL3 GROUP BY D.COL1, D.COL2, D.COL3/*All*/


DATABASE 2
SELECT D.COL1, D.COL2, D.COL3, SUM(D.COL4)
FROM
TABLE1 P, TABLE2 D WHERE ( 'Y' = 'Y' OR EXISTS ( SELECT
Q.COL3 FROM TABLE3 Q WHERE P.COL3= Q.COL3 AND Q.COL8 = :1
) ) AND D.COL5 = :2 AND D.COL6 = :3 AND
D.COL1 = P.COL7 AND D.COL2 = P.COL2 AND D.COL3 =
P.COL3 GROUP BY D.COL1, D.COL2, D.COL3/*One*/
Re: Use of /*All*/ or /*One*/ at the end of a Query [message #622368 is a reply to message #622365] Mon, 25 August 2014 13:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/


please post formatted EXPLAIN PLAN for both statements
Re: Use of /*All*/ or /*One*/ at the end of a Query [message #622372 is a reply to message #622365] Mon, 25 August 2014 14:02 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
>(I guess) of /*All*/ and /*One*/

Hints would never work with that incorrect syntax. Also, those two are not hints at all.

By the way, I might be wrong, but I find it too funny to tell that those trailing words, /*All*/ and /*One*/ are just comments Laughing
Re: Use of /*All*/ or /*One*/ at the end of a Query [message #622373 is a reply to message #622372] Mon, 25 August 2014 14:21 Go to previous messageGo to next message
sread11
Messages: 2
Registered: August 2014
Junior Member
Smile Well thank you, that clears some of the confusion/ignorance. Still confused on how the same source code would comment queries differently then, but at least I'm clear that the explain plans are merely different because of the environment and can address that.

Hopefully you were reciprocated with a good laugh.
Re: Use of /*All*/ or /*One*/ at the end of a Query [message #622374 is a reply to message #622373] Mon, 25 August 2014 14:48 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Same query as often (well not so often it does) flagged with comments like this to "force" the optimizer to use the correct execution plan among different one with the same query text (without the comment) but different values for bind variables.
Search for "bind variable peeking" here, in Oracle documentation and on the web to get details.
Previous Topic: datatype of Null
Next Topic: Sql Satement with is null
Goto Forum:
  


Current Time: Thu Mar 28 16:04:00 CDT 2024