... Are my findings / assumptions right, would this technique work: would always only the matching parts of the union all actually get executed? ...
never say never, never say always, I always say
"would ALWAYS only..."
No, I cannot say that, I can say - the plan would show you if it did or not.
ops$tkyte%ORA10GR2> create or replace view v
2 as
3 select *
4 from
5 ( select * from (select 'Hello' as greeting, to_number('a') as dummy from dual) where greeting = 'Hello'
6 union all
7 select * from (select 'Hi' as greeting, to_number('a') as dummy from dual) where greeting = 'Hi'
8 union all
9 select * from (select 'Allo' as greeting, to_number(null) as dummy from dual) where greeting = 'Allo'
10 )
11 /
View created.
ops$tkyte%ORA10GR2> delete from plan_table;
0 rows deleted.
ops$tkyte%ORA10GR2> explain plan for select * from v where greeting = 'Hello';
Explained.
ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 1780069912
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 51 | 2 (0)| 00:00:01 |
| 1 | VIEW | | 3 | 51 | 2 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 6 | FILTER | | | | | |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(NULL IS NOT NULL)
6 - filter(NULL IS NOT NULL)
20 rows selected.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> delete from plan_table;
8 rows deleted.
ops$tkyte%ORA10GR2> explain plan for select * from v where greeting = 'Allo';
Explained.
ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 47571485
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 51 | 2 (0)| 00:00:01 |
| 1 | VIEW | | 3 | 51 | 2 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | FILTER | | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(NULL IS NOT NULL)
5 - filter(NULL IS NOT NULL)
20 rows selected.
see the filters in there - they show the subtrees that are pruned.
However, I would not RELY on this always being true (eg: with your to_number conversions). The optimizer is free to change the plan and that could make your query unsafe.