Best way
March 1, 2007 - 5pm Central time zone
Reviewer: sandro from Italy
-- 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?
Followup March 4, 2007 - 12pm Central time zone:
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.
March 14, 2007 - 9am Central time zone
Reviewer: sandro from Italy
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?
March 21, 2007 - 1pm Central time zone
Reviewer: A reader
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
Followup March 21, 2007 - 7pm Central time zone:
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
April 20, 2007 - 6am Central time zone
Reviewer: sandro from Italy
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;
/
Followup April 20, 2007 - 7am Central time zone:
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
|