Use of /*All*/ or /*One*/ at the end of a Query [message #622365] |
Mon, 25 August 2014 13:19 |
|
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 #622374 is a reply to message #622373] |
Mon, 25 August 2014 14:48 |
|
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.
|
|
|