Re: /*+ORDERED */ - Works Outside of CURSOR but not in ?!

A copy of this was sent to "Mark J. Bobak" <mark.NOSPAM_at_bobak.net> (if that email address didn't require changing) On Wed, 23 May 2001 06:58:20 GMT, you wrote:

>Is this true? I've never heard of/run into this before. If you're
>running CBO, I'd suspect that the optimizer_mode is CHOOSE or
>FIRST_ROWS but the code inside the PL/SQL is using ALL_ROWS,
>which makes a certain amount of sense, but could be inadvertently
>doing bad things to your execution plan.
>
>Jonathan Lewis covers this on his webpage better than I can.
>For More Info, See:
>http://www.jlcomp.demon.co.uk/sqlplsql.html
>
>-Mark
>In article <jZDO6.9096$CD5.3562037_at_news2.rdc2.tx.home.com>, "Kevin Brand"
><unixoracle_at_hotmail.com> wrote:
>
>> You need spaces after the comment character(s) like this:
>>
>> /* +ORDERED */
it is actually like this:

  /*+ ORDERED */
     ^

Plsql inadverntantly removes the first character in the comment so a hint like:

  /*+ordered*/

becomes
  /*+rdered*/

which does not work.

>>
>> when using PL/SQL
>>
>> -Kevin
>>
>>
>> "Brian Y." <NOSP_at_M> wrote in message
>> news:vEDO6.1344$Rq4.745879_at_news1.news.adelphia.net...
>>> This is an Oracle 8i database. I have attached the /*+ORDERED */ hint
>>> to several large queries in hopes of having them run faster - which
>>> they did. At least that was the case when I was running the queries in
>>> a SQL window outside of my package.
>>>
>>> Here's the kicker: I put the queries back into their respective cursors
>>> inside my package, and they all hang when I call the program. I haven't
>>> waited more than several minutes to see if they will return; anything
>>> more than a minute is unacceptable to our users.
>>>
>>> Why would the /*+ORDERED */ hint work in a free-standing SQL statement,
 but
>>> not in a cursor? I literally cut and paste the query text back into the
>>> program, so there are no differences between the two.
>>>
>>> Thanks for anything at all...
>>>
>>> B~
>>>
>>>
>>>
>>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp