Re: benchmarking, which statement is faster

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 26 Jul 2001 12:22:28 +0100
Message-ID: <996146373.6141.0.nnrp-14.9e984b29@news.demon.co.uk>

Very good point, and worth raising.
However, there is an argument that says:

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.

>

>

>

Received on Thu Jul 26 2001 - 06:22:28 CDT