Re: Checking if more than 0 rows exist.

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Wed, 11 May 2005 14:33:44 +0200
Message-ID: <d5str7$ase$1@news1.zwoll1.ov.home.nl>

DA Morgan wrote:

>> Where's the cursor/open/fetch?

>
>
> Ask Oracle. ;-)
>
> What you see is the result of AUTOTRACE TRACEONLY in 10.1.0.4.
> I performed no editing.
>

No, Daniel, I'm asking you, as you dismiss a cursor solution as fit for Oracle Version over 5 year old, and useless in any other version.

You compare rownum=1, combined with an index to an exists. Reality check:

SQL> select 1 from dual where exists (select empno from scott.emp);

         1

---

         1

Elapsed: 00:00:00.29

Execution Plan

---

   0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1)    1 0 FILTER

   2    1     FAST DUAL (Cost=2 Card=1)
   3    1     INDEX (FULL SCAN) OF 'PK_EMP' (INDEX (UNIQUE)) (Cost=1 C
          ard=14)

Statistics

---

        122  recursive calls
          0  db block gets
         27  consistent gets
          1  physical reads
          0  redo size
        386  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

declare
cursor c_emp is select empno from scott.emp; dummy c_emp%rowtype;
begin
  open c_emp;
  fetch c_emp into dummy;
if c_emp%found then
null;
end if;
close c_emp;
end;
/
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

-- 
Regards,
Frank van Bortel

Received on Wed May 11 2005 - 07:33:44 CDT