Hi Tom, thanks for taking a look at my question. I have a table with several million rows, and I need some help thinking about the best way to aggregate multiple levels of the table in a single query (or if not perhaps using a global temporary table). The end result in my application is a tree that has counts at various levels of the tree for search results.
Some quick test data:
create table t1 as
select 'A' as owner1, x.*
from all_objects x;
insert into t1
select 'B' as owner1, x.*
from all_objects x;
insert into t1
select 'C' as owner1, x.*
from all_objects x;
insert into t1
select 'D' as owner1, x.*
from all_objects x;
commit;
The query below does the job and provides me with the data in a format that works. BUT querying the table 3 times to get aggregations at the three different levels (owner1, owner, object_type) can't be right:
select owner1, '-' owner, '-' object_type, count(*) m
from t1
group by owner1
union all
select owner1, owner, '-' object_type, count(*) m
from t1
group by owner1, owner
union all
select owner1, owner, object_type, count(*) m
from t1
group by owner1, owner, object_type
order by owner1, owner, object_type;
results from sqldeveloper:
A - - 74625
A AERS - 44
A AERS INDEX 25
A AERS TABLE 19
A APEX_030200 - 2251
A APEX_030200 FUNCTION 12
A APEX_030200 INDEX 946
A APEX_030200 PACKAGE 189
A APEX_030200 PACKAGE BODY 181
A APEX_030200 PROCEDURE 19
A APEX_030200 SEQUENCE 4
A APEX_030200 SYNONYM 45
A APEX_030200 TABLE 360
A APEX_030200 TRIGGER 366
A APEX_030200 TYPE 4
A APEX_030200 VIEW 125
A APEX_040000 - 2774
A APEX_040000 FUNCTION 12
A APEX_040000 INDEX 1177
A APEX_040000 JOB 4
I would prefer to run the bottom level query once:
select owner1, owner, object_type, count(*) m
from t1
group by owner1, owner, object_type
but I could not figure out an Analytics, or SQL Model, or really any efficient multi-level aggregation process. I am leaning towards using a global temporary table to store the results of the bottom level query and then running the level 1 and level 2 aggregations off of that.
But that seems less than ideal. Any quick thoughts would be much appreciated, am I missing something obvious?
you want to read up on group by rollup, group by cube and group by grouping sets:
http://docs.oracle.com/cd/E11882_01/server.112/e25554/aggreg.htm#i1007462 ops$tkyte%ORA11GR2> select owner1, owner, object_type,
2 grouping(owner1) g1,
3 grouping(owner) g2,
4 grouping(object_type) g3,
5 count(*)
6 from t1
7 group by rollup( owner1, owner, object_type )
8 /
O OWNER OBJECT_TYPE G1 G2 G3 COUNT(*)
- ------------------ ------------------ ---------- ---------- ---------- ----------
A BI SYNONYM 0 0 0 8
A BI 0 0 1 8
A HR VIEW 0 0 0 1
A HR INDEX 0 0 0 19
A HR TABLE 0 0 0 7
A HR TRIGGER 0 0 0 2
A HR SEQUENCE 0 0 0 3
A HR PROCEDURE 0 0 0 2
A HR 0 0 1 34
A IX TYPE 0 0 0 1
...
A OWBSYS_AUDIT 0 0 1 12
A SI_INFORMTN_SCHEMA SYNONYM 0 0 0 8
A SI_INFORMTN_SCHEMA 0 0 1 8
A 0 1 1 72235
B BI SYNONYM 0 0 0 8
...