simple tuning using decode

Currently I have to assist in tuning of a really time critic application (in comparison to other applications/projects I have to support). There I found 2 statements called within an PL/SQL procedure:

1. select count(id)
   from berx1
   where val=1 and type is null

2. select count(id)
   from berx1
   where val=1 and type is null and dim= 2

(The statements are reduced to test-cases, my test-table is
create table berx1 (id number, val number, type number, dim number);

insert into berx1 values (1, 1, NULL, 1);
insert into berx1 values (2, 1, 1, 2);
insert into berx1 values (3, 1, NULL, 3);
insert into berx1 values (4, 1, 1, 1);
insert into berx1 values (5, 2, NULL, 2);
insert into berx1 values (6, 2, 1, 3);
insert into berx1 values (7, 2, NULL, 1);
insert into berx1 values (8, 2, 1, 2);
insert into berx1 values (9, 3, NULL, 3);
insert into berx1 values (10, 3, 1, 1);
insert into berx1 values (11, 3, NULL, 2);
insert into berx1 values (12, 3, 1, 3);
insert into berx1 values (13, 4, NULL, 1);
insert into berx1 values (14, 4, 1, 2);
insert into berx1 values (15, 4, NULL, 3);
insert into berx1 values (16, 4, 1, 1);
insert into berx1 values (17, 5, NULL, 2);
insert into berx1 values (18, 5, 1, 3);)
Even both of them are very fast (with an index on val), As we have to save every slice of time we can, I merged the statements into one:

SELECT count(id) count
, NVL( SUM( DECODE(dim, 2   -- if 'dim' is 2 
                      , 1   -- add 1 to sum
                      , 0)  -- otherwise add nothing
          ), 0
     ) count_dim
FROM berx1 where val=1 and type is null;

What did I expect so far? (not tested into detail!)

• less context-switches between PL/SQL and SQL
• less soft parses / library cache lookups
• less activity in the buffer cache

In fact, at the end of the optimization I hope I can modify the program logic, maybe we can save a lot more time on other places and also get rid of this statement. But this is still matter of investigation with the application-designers.