Re: benchmarking, which statement is faster

Jonathan Lewis wrote:

> Very good point, and worth raising.
> However, there is an argument that says:
>
> a) You don't care if there are too many rows,
> so you should include 'and rownum = 1'
> in the implicit version to avoid the problem
> whilst making a clear statement of intent
>
> or conversely
>
> b) You do care if there is more than one row
> in which case your explicit version would
> have to do the explicit second fetch anyway,
> leaving you with the same cost as the automatic
> second fetch on the implicit cursor.
>
> --
> Jonathan Lewis
>
> Host to The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
> See http://www.jlcomp.demon.co.uk/book_rev.html
>
> Seminars on getting the best out of Oracle
> See http://www.jlcomp.demon.co.uk/seminar.html
>
> Screensaver or Lifesaver: http://www.ud.com
> Use spare CPU to assist in cancer research.
>
> Keith Boulton wrote in message ...
> >
> >"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message
> >news:9jnb6c0c3k_at_drn.newsguy.com...
> >
> >>
> >> Until and unless you can post an example of any case where explicit
 cursors beat
> >> an implicit cursor.... I am suspicious of your conclusion.
> >>
> >
> >You can generate an unreasonable example. The implicit cursor performs a
> >second fetch. This means if you look for a single row via a full-table
 scan,
> >the entire table will be scanned every time whereas with an explicit
 cursor,
> >on average, only half the table will be scanned. It is un entirely unreal
> >situation, but one for which you can easily engineer a test case.
> >
> >
> >

Thank you, everyone, for reminding me once again that if you start off stupid enough you can learn something new every day.

Thanks again,

Daniel A. Morgan Received on Thu Jul 26 2001 - 12:12:03 CDT