This quiz was focused on the use of the EXISTS method to avoid a NO_DATA_FOUND exception when trying to "read" an element at a given index value.
But all the choices showed ways of looping through the contents of a collection. Several players wrote to remind me that if you want to loop through a collection that is sparse, the best way to do so is to use the FIRST, LAST, PRIOR, NEXT methods.
In other words, instead of this:
DECLARE
l_sons ages_pkg.sons_t;
BEGIN
l_sons (19860923).name_of_son := 'Eli';
l_sons (19860923).age_of_son := 23;
l_sons (19720425).name_of_son := 'Chris';
l_sons (19720425).age_of_son := 38;
FOR indx IN l_sons.FIRST .. l_sons.LAST
LOOP
show_age (l_sons, indx);
END LOOP;
END;You would write:
DECLARE
l_sons ages_pkg.sons_t;
l_index PLS_INTEGER;
BEGIN
l_sons (19860923).name_of_son := 'Eli';
l_sons (19860923).age_of_son := 23;
l_sons (19720425).name_of_son := 'Chris';
l_sons (19720425).age_of_son := 38;
l_index := l_sons.FIRST;
WHILE (l_index IS NOT NULL)
LOOP
LOOP
show_age (l_sons, l_index);l_index := l_sons.NEXT (l_index);
END LOOP;
END;
Here are the remarks submitted by Jeff Kemp:
Steven, you wrote "Generally, the best way to handle such situations is to call the EXISTS method to verify that am element exists at the specified index value, thereby avoiding the raising of a NO_DATA_FOUND exception" Certainly, this is one way to solve the problem, but when I am dealing with a sparsely-populated array I prefer to forego the FOR LOOP and use the .NEXT property of the array to jump from each index directly to its successor. (Alternatively, there is also the INDICES OF syntax for the FOR LOOP that also avoids all the extraneous EXISTS checks) If there are hundreds of millions indexes between two entries in the array, your loop will test the EXISTS method for each and every one of them; sure, on modern processors this will not take much time, but I'm willing to bet (and I think I will test this when I get a chance) that a measurable performance improvement could be had by avoiding all the unnecessary EXISTS checks. Just my $0.02 :)
I agree completely. I should have pointed that out, and will do so in the answer text.
Anyone else care to comment?
SF
I should correct myself, the INDICES OF syntax, of course, only applies when using a FORALL :)
ReplyDeleteQuite a big difference in performance (tested on 11gR1 running on my laptop):
ReplyDeleteset timing on
prompt FOR LOOP with no_data_found handler
declare
type t is table of number index by binary_integer;
r t;
begin
r(1000000) := 1;
r(2000000) := 2;
for i in r.first..r.last loop
begin
r(i) := r(i) + 1;
exception
when no_data_found then
null;
end;
end loop;
end;
/
prompt LOOP with FIRST/NEXT
declare
type t is table of number index by binary_integer;
r t;
i binary_integer;
begin
r(1000000) := 1;
r(2000000) := 2;
i := r.first;
loop
exit when i is null;
r(i) := r(i) + 1;
i := r.next(i);
end loop;
end;
/
FOR LOOP with no_data_found handler
anonymous block completed
3,625ms elapsed
LOOP with FIRST/NEXT
anonymous block completed
0ms elapsed
The difference might be less if this code were to be natively compiled.
In my opinion, we cannot generalize. It would depend upon how dense the array is.
ReplyDeleteI find using "exception when_no_data_found" to be advantageous in most of my situations.
My customer table is sparsely-populated, but more or less dense. (The table gets inserted based on a sequence, but if customer closes the record the row is deleted. So for E.g. if my table contains customer_no from 0 to 10000, normally there would be 9000+ records.) In this scenario, I prefer to handle no_data_found, than adding a bit more load on the processor for array lookup using .NEXT or .EXISTS methods.
prompt sparse first/next
declare
t dbms_sql.varchar2_table;
n number;
m number :=0;
begin
for i in 1..1000000
loop
if mod(i,100) != 0
then
t(i) := null;
end if;
end loop;
dbms_output.put_line('count = '||t.count);
n := t.first;
loop
m := m+1;
if n = t.last
then
exit;
end if;
n := t.next(n);
end loop;
dbms_output.put_line('m = '||m);
end;
/
prompt sparse handling no_data_found
declare
t dbms_sql.varchar2_table;
n number;
m number := 0;
begin
for i in 1..1000000
loop
if mod(i,100) != 0
then
t(i) := null;
end if;
end loop;
dbms_output.put_line('count = '||t.count);
for i in t.first .. t.last
loop
begin
n := t(i);
m := m + 1;
exception when no_data_found
then
null;
end;
end loop;
dbms_output.put_line('m = '||m);
end;
/
prompt truly dense first/next
declare
t dbms_sql.varchar2_table;
n number;
m number :=0;
begin
for i in 1..1000000
loop
--if mod(i,100) != 0
--then
t(i) := null;
--end if;
end loop;
dbms_output.put_line('count = '||t.count);
n := t.first;
loop
m := m+1;
if n = t.last
then
exit;
end if;
n := t.next(n);
end loop;
dbms_output.put_line('m = '||m);
end;
/
prompt truly dense handling no_data_found
declare
t dbms_sql.varchar2_table;
n number;
m number := 0;
begin
for i in 1..1000000
loop
--if mod(i,100) != 0
--then
t(i) := null;
--end if;
end loop;
dbms_output.put_line('count = '||t.count);
for i in t.first .. t.last
loop
begin
n := t(i);
m := m + 1;
exception when no_data_found
then
null;
end;
end loop;
dbms_output.put_line('m = '||m);
end;
/
sparse first/next
count = 990000
m = 990000
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.42
sparse handling no_data_found
count = 990000
m = 990000
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.11
truly dense first/next
count = 1000000
m = 1000000
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.01
truly dense handling no_data_found
count = 1000000
m = 1000000
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.62
To me "exception no_data_found" does not mean "I dont care!", but it says "Its OK!"
My above comment was for comparing ".next" approach to "exception when no_data_found"
ReplyDeleteBut when I compare ".exists" to "exception when no_data_found", I would always prefer to use the latter.
For the .exists approach, we are looking up the array 2 times.
if tb.exists(i) --first
then
x := tb(i); --second
as against once in the when_no_data_found approach:
begin
x := tb(i); --once
exception when no_data_found
then
null;
end;
Of these two, won't the EWNDF one be always better, from a performance stand point?
Excellent points, Spoon. You definitely need to look at the data that you are manipulating. Different patterns can easily lead to different implementations.
ReplyDeleteI would only add that when you do this, be sure to encapsulate the implementation so that you can change it as easily as possible as the underlying data changes.
And, yes, I would expect that an occasional NDF trapping would be more efficient than calling exists for every element.
SF