RE: Simple Select Return Record

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Thu, 9 Jun 2005 16:04:08 +0200
Message-ID: <7F0C000A3ABA6241A10C9ABF37EEB46D0F016F@MSXVS01.trivadis.com>

Hi Vlad  

>I think it'll fetch at least batch_size(arraysize) rows.
 

It depends on your environment, e.g. with JDBC, per default, yes.  

>Could you please kindly provide your form of the query needed. Please,

>don't say it's bad without an alternative.
 

I didn't say it's bad. In fact it's a nice trick. But fetching single rows instead of joining dual is still better (even in 10g with fast dual...).

An example:  

SQL> DECLARE
  2 l_found PLS_INTEGER;
  3 BEGIN
  4 FOR i IN 1..100000 LOOP

  5      SELECT count(*)
  6      INTO l_found
  7      FROM dual
  8      WHERE EXISTS (SELECT null FROM employees WHERE employee_id = 100);

  9 END LOOP;
 10 END;
 11 /
Elapsed: 00:00:05.01

SQL> DECLARE
  2 l_found PLS_INTEGER;
  3 BEGIN
  4 FOR i IN 1..100000 LOOP

  5      l_found := 0;
  6      FOR i IN (SELECT null FROM employees WHERE employee_id = 10) LOOP
  7        l_found := 1;
  8        EXIT;
  9      END LOOP;

 10 END LOOP;
 11 END;
 12 /
Elapsed: 00:00:03.00    

HTH Chris

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jun 09 2005 - 10:12:13 CDT