SQL & PL/SQL

Hi there,

I'll try to explain the case with an example, everybody can execute:

create table t_agg (id1 number(5), id2 number(5), text varchar2(20));

Insert into t_agg (ID1,ID2,TEXT) values (1,1,'a');
Insert into t_agg (ID1,ID2,TEXT) values (1,1,'b');
Insert into t_agg (ID1,ID2,TEXT) values (1,2,'c');
Insert into t_agg (ID1,ID2,TEXT) values (1,2,'d');
Insert into t_agg (ID1,ID2,TEXT) values (2,1,'aa');
Insert into t_agg (ID1,ID2,TEXT) values (2,1,'bb');
Insert into t_agg (ID1,ID2,TEXT) values (2,2,'cc');
Insert into t_agg (ID1,ID2,TEXT) values (2,2,'dd');
commit;

create or replace view v_agg_roll as select id1, id2, count(*) counter from t_agg group by rollup (id1, id2);

-- (1)
select * from v_agg_roll;
ID1 ID2 COUNTER
1 1 2
1 2 2
1 null 4
2 1 2
2 2 2
2 null 4
null null 8

-- (2)
select * from v_agg_roll where id2=2 or id2 is null;
ID1 ID2 COUNTER
1 2 2
1 null 4
2 2 2
2 null 4
null null 8

Statement (2) returns wrong counts, doesn't it? The obvious reason is, that it calculates the sub-totals before executing the filtering conditions. The explain plan shows the following:

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 3198 | 3 (34)| 00:00:01 |
| 1 | VIEW | V_AGG_ROLL | 82 | 3198 | 3 (34)| 00:00:01 |
|* 2 | FILTER | | | | | |
| 3 | SORT GROUP BY ROLLUP| | 82 | 2132 | 3 (34)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T_AGG | 82 | 2132 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("ID2"=2 OR "ID2" IS NULL)

From reading the "Data Warehousing Guide", chapter 20 for Oracle 10gR2, I thought, that Oracle could cope with that kind of SQL. It is an understandable requirement, to supply a view with all sub-totals and no filter conditions and leave it up to the frontend application to query that view, while adding filter conditions to it.

In this case the sub-totals should of course be correct...

Many thanks in advance,

Xenofon