Doing it wrong...
I hate queries of the following form:
select count(*) from <anything else here>
The reason? The code typically looks something like this around the count(*):
select count(*) into l_cnt from .....;
if ( l_cnt > 0 )
I've always wondered why that code isn't just:
Why bother counting - and then processing if that count was greater than zero. Why not just process_the_data - that routine already knows how to stop when it runs out of data - just let it run out of data naturally on row zero if there is no data.
Many people don't stop to consider that
- The count can change between the select count(*) and the process_the_data call - there might be nothing by the time you get into the process_some_data
- The count can change while you are running the process_some_data call itself - you cannot use the count as "this is how many times to iterate" (I've seen it done - it fails spectacularly when there are less rows than you counted, it fails silently when there are suddenly more and you never get to them, it also sometimes works by accident).
I've seen code like:
select count(*) into :cnt from t;
allocate array of :cnt elements
open C for select * from t;
for i in 1 .. :cnt
fetch c into array(i);
You can just imagine the damage that could do in a language like C for example - interesting results when there are less than :cnt rows to get, segmentation fault - core dumped (we HOPE - we hope it crashes) if there are more than :cnt rows to get.
Anyway, this isn't a post about "don't count and then process" (well, ok, it is in part) - this is a post about an interesting snippet of code a friend sent me. They are on site doing some "tuning". I've modified the variables and such to disguise it - but the "logic" is intact:
( p_input1 in number,
p_input2 in varchar2
WHERE a_column = p_input1
AND another_column = p_input2;
l_the_cnt number default 0;
FOR rec IN C
l_the_cnt := l_the_cnt+1;
WHEN OTHERS THEN
That hurts me in so many ways.
- The dreaded "when others <no error raised here>"
- A loop to COUNT THE ROWS RETRIEVED BY A QUERY!!!
- Because I did not believe it: A loop to COUNT!!! (had to be said twice)
- A function to count rows - probably used in higher level code like this "if count_em_up(x,y) > 0 then process_some_data; end if;"
Well, at least there is the very real probability of tuning this particular application - there is probably lots and lots of low hanging fruit out there like this!