There's more than one way to skin a cat

We're off on an adventure trying to figure out why one of our 9.2.0.5 databases has poor response time during a certain period. During the investigation, I came across a process that was executing a query that had a function call in it. It wasn't our problem, but was interesting none the less.

I started tracing the user's session and found that for every row in table T the query was executing function getCount (the names have been changed and the function simplified to clarify the example).

SELECT x, getcount()
FROM t

OK, seams reasonable enough, right. But inside getCount() was a query:

create or replace function getcount return integer
as
   c integer;
begin
   select count(*) into c from xyz where (code1='7' or code2='3');
   return c;
end;

Ah, here's the query that showed up in the trace as being the top dog.

SELECT COUNT(*)
FROM
XYZ WHERE (CODE1='7' OR CODE2='3')


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      2.47       8.55      57428      63059          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      2.47       8.55      57428      63059          0           1

"Hmm, let me just fix this and let the developer know how to fix his problem" I thought to myself, "he'll appreciate it."

So I suggested the following query for the fix:

select count(*) from (
select id from xyz where code1='7'
union
select id from xyz where code2='3'
)

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        2      0.05       0.05          0        666          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.05       0.05          0        666          0           1

The number of logical reads was much better and the wall clock response time was much better as well. Perfectly suitable solution.

Well, proving that DBAs don't know anything about how to write code, the fix ended up being:

create or replace function getcount return integer
as
   cursor c1 is select count(*) cnt from xyz where code1='7';
   cursor c2 is select count(*) cnt from xyz where code2='3';
   t integer := 0;
begin
   for i in c1 loop
      t := t+i.cnt;
   end loop;
   for i in c2 loop
      t := t+i.cnt;
   end loop;
   return t;
end;
/

SELECT COUNT(*) CNT
FROM
XYZ WHERE CODE2='3'


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        2      0.03       0.03          0        387          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.03          0        387          0           1

SELECT COUNT(*) CNT
FROM
XYZ WHERE CODE1='7'

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        2      0.03       0.02          0        279          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.02          0        279          0           1

OK, that works. No reason to do something the easy way when you can write code and do it the cool way.

Addition: As Gary points out in the coments, there is a potential in the new function for records with both a '3' AND a '7' to be picked up twice. In my actual data, there is a business rule that makes sure that doesn't happen, but in general it is an issue.