Re: Questioning Oracle Documentation

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> a écrit dans le message de news:ccqs08$eft$1_at_hercules.btinternet.com...
>
> Daniel,
>
> You have got into the habit of not reading
> carefully before replying. It doesn't do any
> good to you, or others.
>
> Notes in line:
>
>
> --
> Regards
>
> Jonathan Lewis
>
> http://www.jlcomp.demon.co.uk
>
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ
>
> http://www.jlcomp.demon.co.uk/seminar.html
> Optimising Oracle Seminar - schedule updated May 1st
>
>
> "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1089503541.986917_at_yasure...
> > Jonathan Lewis wrote:
> >
> > > Is the example the same in the 9i manual ?
> > >
> > > I believe 10g has mechanisms for promoting constant
> > > assignments to a position outside the loop - but possibly
> > > this only works in a restricted number of cases.
> > >
> > > You might try:
> > > n := 15
> > > n := 15.0
> > >
> > > c as a varchar2()
> > > c as char(2) -- to match the size of '15'
> >
> > The example is from the 10g Tuning Guide to which I
> > provided the link.
> >
>
> Yes, I was aware of that. You would have been aware
> that I knew that if you had read the response:
>
> -- Is the example the same in the 9i manual ? --
>
> Do you see the expression "the same" - did you consider
> that I was suggesting a comparison of two manuals, rather
> than asking which manual the link came from ?
>
> I was assuming that you would spot the suggestion that
> the 10g manual could be wrong because it was just a
> copy from the 9i manual. (It was a sort of compliment
> really - I didn't think I would have to spell it out for you).
>
> >
> > I can understand an implicit conversion being
> > optimized. But faster than no conversion? I am
> > at a loss to explain this one.
> >
>
> Read these two fragments carefully:
>
> -- mechanisms for promoting constant assignments --
> -- to a position outside the loop --
>
> -- only works in a restricted number of cases --
>
> Something outside a loop happens only once, rather than
> the 10,000,000 times that is indicated in your loop counter.
>
> Try putting a NULL line into the loop, and you will find that
> the timing for the implicit conversion matches the null operation.
> The implicit conversion does not happen in the loop.
>
> I do not know how Oracle decides whether or not to move
> some code outside the loop -- but I would not be surprised
> if there were some apparently arbitrary split which allowed
> the line with the implicit conversion to be moved, whilst the
> line which needed the explicit conversion and the line which
> needed no conversion had to stay put. (In fact n := '15', with
> its implicit conversion, gets the same optimisation).
>
> Perhaps you ought to be more surprised that the EXPLICIT
> conversion at 304 cs is quicker than the direct assignment at
> 307 cs.

And double explicit conversions are 3.5 times faster than direct assignement in my tests in 9.2 with an average of 2940 for direct assignement vs 824 with double explicit conversion.

--
Regards
Michel Cadot



>

>

> > Daniel Morgan

> >

> >

>

>