Oracle – Get even numbers

[adsense id=”0514458240″ width=”468″ height=”60″]

Sometimes it is necessary to filter a set of rows based on either even or odd numbers. There are two possible methods in SQL. The first is using the MOD(m,n) function. The MOD function returns the remainder of m divided by n. If the remainder of MOD(m,2) is 0 then we know that it is an even number (assuming 0 is considered even).

An alternative way of doing the same thing is using the BITAND function. This performs a bit AND on two sets of numbers. In binary any value ending in binary 1 is an odd number. For example, 000 – 0, 001 – 1, 010 – 2, 011 – 3, 100 – 4, 101 – 5, 110 – 6, 111 – 7. Anytime the last digit is a 1 the number is odd, conversely anytime the last digit is a 0 it is an even number. So BITAND(m,1) = 0 will display even numbers.

From a performance perspective multiplication and division is more CPU intensive than performing bit operations. We have conducted numerous tests demonstrating the performance difference between the two.

In our tests we will run alternative queries on a table containing 100,000 rows. This is run 10 times per query. We will iterate the process several times in order to make sure non-caching of the contents does not skew results and also as the server is running other processes at the same time by running this iteratively and alternatively we will make sure that CPU load will skew the overall results. In other words if the CPU load increases due to a separate process we should see the same load for the alternative process. This is not the ideal case but it should be sufficient to demonstrate differences.

The results show conclusively that using the BITAND operation was far more efficient than the MOD 2.

Below I have included all the scripts that I used for the tests:

SQL> create table test (id integer, dob date, comments varchar2(255), feedback varchar2(255));

Table created.

  1  declare
  2  count integer;
  3  rows integer := 100000;
  4  i integer := 0;
  5  begin
  6   while i < rows loop
  7    insert into test(id, dob, comments, feedback) values (i, current_date, '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
  8    i := i + 1;
  9   end loop;
 10* end;
SQL> /

PL/SQL procedure successfully completed.

The scripts used to test the BITAND operation is

prompt bitand
set timing on
declare
 lcount integer := 0;
 lmax integer := 10;
 ltmp integer;
begin
 while lcount < lmax loop
	select count(1) into ltmp from test where bitand(id, 1) = 0;
	lcount := lcount + 1;
 end loop;
end;
/

The script to test the mod 2

prompt mod2
set timing on
declare
 lcount integer := 0;
 lmax integer := 10;
 ltmp integer;
begin
 while lcount < lmax loop
        select count(1) into ltmp from test where mod(id, 2) = 0;
	lcount := lcount + 1;
 end loop;
end;
/

And finally the just get a count of the contents of the table:

prompt count
set timing on
declare
 lcount integer := 0;
 lmax integer := 10;
 ltmp integer;
begin
 while lcount < lmax loop
        select count(1) into ltmp from test;
	lcount := lcount + 1;
 end loop;
end;
/