| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: count(*) vs. a cursor when determining the existing record on primary key
Originally posted by Sybrand Bakker
> On Fri, 20 Jun 2003 19:18:11 +0000, andrewst > wrote: > > > contrary to > >the myth so widely believed even by Senior DBAs apparently > ;) > > > This has nothing to do with myths, but with reading trace files. > >
declare
v_id number;
begin
for i in 1..1000 loop
select id as using_into
into v_id
from t
where id = i;
end loop;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 0 0 0 Execute 1 0.25 0.26 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.26 0.29 0 0 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (TONY)
select user#
from
sys.user$ where name = 'OUTLN'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID USER$
1 INDEX UNIQUE SCAN I_USER1 (object id 44)
********************************************************************************
SELECT id as using_into
from t
where id = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0 Execute 1000 0.10 0.08 0 0 0 0 Fetch 1000 0.02 0.03 0 2000 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2001 0.13 0.13 0 2000 0 1000
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (TONY) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1000 INDEX UNIQUE SCAN SYS_C003349 (object id 30880)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1000 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'SYS_C003349' (UNIQUE)
declare
v_id number;
cursor c(p_id in number) is
select id as using_cursor
from t
where id = p_id;
begin
for i in 1..1000 loop
open c(i);
fetch c into v_id;
close c;
end loop;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 0 0 0 0 Execute 1 0.28 0.33 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.30 0.35 0 0 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (TONY)
select user#
from
sys.user$ where name = 'OUTLN'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID USER$
1 INDEX UNIQUE SCAN I_USER1 (object id 44)
********************************************************************************
SELECT id as using_cursor
from t
where id = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1000 0.07 0.08 0 0 0 0 Fetch 1000 0.06 0.04 0 2000 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2001 0.13 0.13 0 2000 0 1000
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (TONY) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1000 INDEX UNIQUE SCAN SYS_C003349 (object id 30880)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1000 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'SYS_C003349' (UNIQUE)
Tell me how the cursor is better here.
-- Posted via http://dbforums.comReceived on Sat Jun 21 2003 - 09:15:32 CDT
 |
 |