ANY translated to filter(("col1"= OR "col2=))

I had to tweak a sequence of statements, in pseudo-code it was something like this:

select col1 into v1
from tab1
where col2=<condition1>;
delete from tab2
where col3 = v1;
delete from tab2
where col4 = v1;

My first step was to avoid the variable v1 and put the select into the deletes as a subselect.
My 2nd step was to merge the 2 deletes into one, combining the 2 selerated conditions using OR.
this gave an execution plan similar to this (I use the test-table from my previous post and a really simple statement):
delete
from berx1
where type=(select 1 from dual)
or dim=(select 1 from dual);


-------------------------------------
| Id  | Operation           | Name  |
-------------------------------------
|   1 |  DELETE             | BERX1 |
|*  2 |   TABLE ACCESS FULL | BERX1 |
|   3 |    FAST DUAL        |       |
|   4 |     FAST DUAL       |       |
-------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("TYPE"= OR "DIM"=))

The next idea was to avoid running the same subselect twice:
My first thought was to use the subquery_factoring_clause (WITH), but it's only useable in SELECT statements. So I loitered through the documentation and stumbled across the ANY keyword. After some ests I came to this statement:
delete
from berx1
where (select 1 from dual) = any (type, dim);

------------------------------------
| Id | Operation | Name |
------------------------------------
| 1 | DELETE | BERX1 |
|* 2 | TABLE ACCESS FULL| BERX1 |
| 3 | FAST DUAL | |
------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("TYPE"= OR "DIM"=))

Anything more to say? Only one subselect, even the filter is the same.
I tested this behaviour with 11.1.0.6 and 10.2.0.4 - both are the same.

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.