Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: June 29, 2001 - 4:36 pm UTC

Last updated: April 20, 2007 - 7:16 am UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Tom:

what is the most efficiency way to know if some kind of row exist or not?

emp(empno,sal,loc);
we want to know if there are somebody have sal>4000.

1.
select count(0) into a where sal>4000
if a>0 the return true;
else return false;

2.
select empno from emp where sal>4000 and rownum<2
return true;
exception
when no_data_found then
return false;

is there any other solution?

and Tom said...

I myself like:

select count(*) into l_cnt
from dual
where exists ( select NULL
from emp
where sal > 4000 );


that'll return 0 or 1. The where exists short circuts after finding the first row.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Best way

sandro, March 01, 2007 - 5:43 pm UTC

-- testcase
drop table t;

create table t
as
select trunc(dbms_random.value(1,100) )
id,
rpad('*', trunc( dbms_random.value(1,10)) ,'*' ) data
from dual
connect by level <= 1000000;

begin
dbms_stats.gather_table_stats( user, 'T');
end;
/

var a varchar2(100);
exec :a := '******';
set autotrace traceonly

select count(*) trovato
from dual
where exists (select null
from t
where data = :a);

select count(*)
from t
where data = :a
and rownum < 2;

FIST Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'DUAL' (Cost=2 Card=1)
4 2 TABLE ACCESS (FULL) OF 'T' (Cost=755 Card=111111 Bytes=777777)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
378 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SECOND Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=755 Card=1 Bytes=7)
1 0 SORT (AGGREGATE)
2 1 COUNT (STOPKEY)
3 2 TABLE ACCESS (FULL) OF 'T' (Cost=755 Card=111111 Bytes=777777)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
379 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SECOND WAY ARE THE BEST?

Tom Kyte
March 04, 2007 - 12:38 pm UTC

This is my favorite way to see if a row exists:

[THIS SPACE LEFT BLANK INTENTIONALLY]


I hate code that looks like:

count to see if a row is there
if count > 0
then
read that row and process it
end if


That code should just be:

read that row and process it

if there isn't any row, deal with it - don't process it.


that is the best way.


but do not count from dual, just count from the source table itself.

sandro, March 14, 2007 - 9:59 am UTC

But you have proposed solution is "FIRST execution plan" and this query dual!
<<...I myself like:
select count(*) into l_cnt
from dual
where exists ( select NULL
from ...);
...>>


I have proposed another solution that generate "SECOND execution plan"
select count(*)
from t
where data = :a
and rownum < 2;
where I query only my table.

What do you think about this?
I can add HINT first_rows(n) to optimize statement?
select /* first_rows(1) */ count(*)
from t
where data = :a
and rownum < 2;
THIS IS THE BEST WAY?

Definitely I want code a SP were I test if almost 1 row exist....
IF ifExistRow() then ecc...

FIRST SOLUTION (based on your)
------------------------------
function ifExistRowreturn(p_cond in varchar2) boolean is
l_found pls_integer default 0;
begin
select count(*)
into l_found
from dual
where exists (select null
from t
where c = p_cond);

return l_found > 0;

end ifExistRow;

SECOND SOLUTION
---------------
function ifExistRowreturn(p_cond in varchar2) boolean is
l_found pls_integer default 0;
begin
select /*+ first_rows(1)*/ count(*)
into l_found
from t
where c = p_cond
and rownum < 2;

return l_found > 0;

end ifExistRow;

WHAT DO YOU THINK?

A reader, March 21, 2007 - 1:17 pm UTC

I'm calculating total memory in oracle RAC environment.

Why I have very different value in this 2 statements on pga values?

first stat
----------
with vs as
(
select 'PGA: ' pid
,iid
,session_pga_memory + session_uga_memory bytes
from (select inst_id iid
,(select ss.value
from gv$sesstat ss
where ss.sid = s.sid
and ss.inst_id = s.inst_id
and ss.statistic# = 20) session_pga_memory
,(select ss.value
from gv$sesstat ss
where ss.sid = s.sid
and ss.inst_id = s.inst_id
and ss.statistic# = 15) session_uga_memory
from gv$session s)
union all
select 'SGA: ' || name pid
,s.inst_id iid
,value bytes
from gv$sga s
)
select distinct iid, pid, sum(bytes) over (partition by iid, pid) bytes from vs
;


IID PID BYTES
---------- ------------------------- ----------
1 PGA: 196764792 <=====
1 SGA: Database Buffers 318767104
1 SGA: Fixed Size 733688
1 SGA: Redo Buffers 811008
1 SGA: Variable Size 335544320
2 PGA: 77159560 <=====
2 SGA: Database Buffers 318767104
2 SGA: Fixed Size 733688
2 SGA: Redo Buffers 811008
2 SGA: Variable Size 335544320



second stat
----------
with vs as
(
select 'PGA: ' pid
,p.inst_id iid
,p.pga_alloc_mem bytes
from gv$session s
,gv$sesstat pcur
,gv$process p
where pcur.statistic# in ( 20 -- = session pga memory
,15 -- = session uga memory
)
and s.paddr = p.addr
and pcur.sid = s.sid
and pcur.INST_ID = s.INST_ID
and pcur.INST_ID = p.INST_ID
union all
select 'SGA: ' || name pid
,s.inst_id iid
,value bytes
from gv$sga s
)
select distinct iid, pid, sum(bytes) over (partition by iid, pid) bytes from vs
;


IID PID BYTES
---------- ------------------------- ----------
1 PGA: 342558636 <=====
1 SGA: Database Buffers 318767104
1 SGA: Fixed Size 733688
1 SGA: Redo Buffers 811008
1 SGA: Variable Size 335544320
2 PGA: 186091416 <=====
2 SGA: Database Buffers 318767104
2 SGA: Fixed Size 733688
2 SGA: Redo Buffers 811008
2 SGA: Variable Size 335544320

Tom Kyte
March 21, 2007 - 7:23 pm UTC

well first - unless you run these at the same split instant - they will differ (eg: copy your v$ views, run your queries against that so you can compare the same query against the same data)....

and second - please don't use statistic#, they change by version and port - you'll have to rewrite this every time.

third - well, do the above first. I'd rather not reverse engineer your logic to see if it is correct or not if I don't have to.

and why not use pga_stat views instead?

general porpose function to test if if some kind of row exist

sandro, April 20, 2007 - 6:26 am UTC

This is my ipotesi for general porpose function to test if if some kind of row exist.
What do ypu think?

-- function
create or replace function rowexist
(
p_tabname varchar2
,p_whereclause varchar2 default null
) return boolean as
l_cur integer;
l_sql_text varchar2(200);
l_rowcount number;
l_row_proc integer;
begin
l_cur := dbms_sql.open_cursor;
l_sql_text := 'select count(*) cnt from ' || p_tabname || ' where ';
if p_whereclause is null then
l_sql_text := l_sql_text || 'rownum < 2';
else
l_sql_text := l_sql_text || p_whereclause || ' and rownum < 2';
end if;
--dbms_output.put_line(l_sql_text);
dbms_sql.parse(l_cur
,l_sql_text
,dbms_sql.native);
dbms_sql.define_column(l_cur
,1
,l_rowcount);
l_row_proc := dbms_sql.execute(l_cur);
loop
if dbms_sql.fetch_rows(l_cur) > 0 then
dbms_sql.column_value(l_cur
,1
,l_rowcount);
--dbms_output.put_line('ROW_COUNT:' || to_char(l_rowcount));
else
exit;
end if;
end loop;
dbms_sql.close_cursor(l_cur);
return l_rowcount > 0;
exception
when others then
if dbms_sql.is_open(l_cur) then
dbms_sql.close_cursor(l_cur);
end if;
raise;
end rowexist;

-- TEST
begin
if rowexist('TAB'
,'tname=''T''') then
dbms_output.put_line('exist');
else
dbms_output.put_line('NOT exist');
end if;
end;
/
Tom Kyte
April 20, 2007 - 7:16 am UTC

why the heck would you do this, it is beyond comprehension why you would want to do this in real life.


no binds
sql injectable
parses like mad
inefficient