Reputation: 35557
How to perform counts on a Oracle Hierarchical Table based on Parent Link?
I have the following Oracle 11g R2 hierarchical table definition:
Table Name: TECH_VALUES:
ID,
GROUP_ID,
LINK_ID
PARENT_GROUP_ID,
TECH_TYPE
Based on the above hierarchical table definition, some sample data might look like this:
ID GROUP_ID LINK_ID PARENT_GROUP_ID TECH_TYPE
------- ------------- ------------ -------------------- --------------
1 100 LETTER_A 0
2 200 LETTER_B 0
3 300 LETTER_C 0
4 400 LETTER_A1 100 A
5 500 LETTER_A2 100 A
6 600 LETTER_A3 100 A
7 700 LETTER_AA1 400 B
8 800 LETTER_AAA1 700 C
9 900 LETTER_B2 200 B
10 1000 LETTER_BB5 900 B
12 1200 LETTER_CC1 300 C
13 1300 LETTER_CC2 300 C
14 1400 LETTER_CC3 300 A
15 1500 LETTER_CCC5 1400 A
16 1600 LETTER_CCC6 1500 C
17 1700 LETTER_BBB8 900 B
18 1800 LETTER_B 0
19 1900 LETTER_B2 1800 B
20 2000 LETTER_BB5 1900 B
21 2100 LETTER_BBB8 1900 B
Keeping in mind that there are only three Tech Types, i.e. A, B and C but can span over over different LINK_IDs, how can I perform a count on these three different TECH_TYPEs based only on the parent link ids where the parent group id is 0 and there children below them?
Updated note
It is also possible to have duplicate parent link ids, such as LETTER_B and all of the same children values but for different GROUP IDs.
I am basically after a table/report query that looks like this:
ID Link ID Tech Type A Tech Type B Tech Type C Group ID
--- -------------- ------------------- -------------------- ------------ --------------
1 LETTER_A 3 1 1 100
2 LETTER_B 0 3 0 200
3 LETTER_C 2 0 3 300
4 LETTER_B 0 3 0 1800
Being hierarchical and that my table may consist of over 30,000 records, I need to also ensure that the performance to produce the above report query shown here is fast.
Obviously in order to produce the above report, I need to gather all necessary count breakdowns based on TECH_TYPE for all parent link id where the PARENT_GROUP_ID = 0 and store these in a table as per this report layout .
How can I write a combined query that performs the counts as well as stores the information into a new table called LINK_COUNTS, which this report will be based off? Columns for this table will be:
ID,
LINK_ID,
TECH_TYPE_A,
TECH_TYPE_B,
TECH_TYPE_C,
GROUP_ID
In the end of this whole requirement, I want to be able to update the LINK_COUNTS table based on the results returned from the sample data above as part of just one SQL UPDATE transaction as the top level parent link ids will already exist within my LINK_COUNTS table, just need to provide count breakdown values for each parent node link, i.e.
LETTER_A
LETTER_B
LETTER_C
LETTER_B
using something like:
UPDATE link_counts
SET (TECH_TYPE_A,TECH_TYPE_B,TECH_TYPE_C) =
(with xyz
where link_id = LINK_COUNTS.link_id
....
etc
Upvotes: 0
Views: 180
Answers (1)
Reputation: 4141
To select the data try this:
with xyz as (
select tech_type,
connect_by_root(link_id) as root_link_id,
connect_by_root(group_id) as root_group_id
from tech_values
start with parent_group_id = 0
connect by prior group_id = parent_group_id
)
select root_link_id, root_group_id,
a_tech_type as tech_type_a, b_tech_type as tech_type_b, c_tech_type as tech_type_c
from xyz pivot (
count(1) as tech_type
for tech_type in (
'A' as A,
'B' as B,
'C' as C
))
;
On a test set of 39000 records it took an Intel Core i5 with Oracle 11.2 (and flushed buffer cache) approx 0.15 sec to compute this. I hope it is quick enough to satisfy your needs.
To update the target table with these data, eithr loop through the above SELECT and update your link_counts table iteratively, or simply use the MERGE statement:
merge into link_counts T
using (
with xyz as (
select tech_type,
connect_by_root(link_id) as root_link_id,
connect_by_root(group_id) as root_group_id
from tech_values X
start with parent_group_id = 0
connect by prior group_id = parent_group_id
)
select *
from xyz pivot (
count(1) as tech_type
for tech_type in (
'A' as A,
'B' as B,
'C' as C
))
) S
on ( T.group_id = S.root_group_id )
when matched then
update
set T.tech_type_a = S.a_tech_type,
T.tech_type_b = S.b_tech_type,
T.tech_type_c = S.c_tech_type
when not matched then
insert (link_id, group_id, tech_type_a, tech_type_b, tech_type_c)
values (S.root_link_id, S.root_group_id, S.a_tech_type, S.b_tech_type, S.c_tech_type)
;
The MERGE assumes that the GROUP_ID is a primary/unique key in the link_counts table. The actual definition of the tech_values table in the original question supports this assumption.
A spool of a test run in SQL*Plus is as follows:
SQL>
SQL> select count(1)
2 from user_indexes
3 where table_name = 'TECH_VALUES'
4 ;
COUNT(1)
----------
0
SQL>
SQL> select count(1)
2 from user_constraints
3 where table_name = 'TECH_VALUES'
4 and constraint_type != 'C'
5 ;
COUNT(1)
----------
0
SQL>
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL>
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
08.06.14 23:18:40,053000 +02:00
SQL>
SQL> select tech_type, count(1)
2 from tech_values
3 where parent_group_id != 0
4 group by rollup(tech_type);
T COUNT(1)
- ----------
A 20048
B 39984
C 19984
80016
SQL>
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
08.06.14 23:18:40,144000 +02:00
SQL>
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL>
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
08.06.14 23:18:40,246000 +02:00
SQL>
SQL> with xyz as (
2 select connect_by_root(link_id) as root_link_id, connect_by_root(group_id) as root_group_id, tech_type
3 from tech_values X
4 start with parent_group_id = 0
5 connect by prior group_id = parent_group_id
6 )
7 select *
8 from xyz pivot (
9 count(1) as tech_type
10 for tech_type in (
11 'A' as A,
12 'B' as B,
13 'C' as C
14 ));
ROOT_LINK_ID ROOT_GROUP_ID A_TECH_TYPE B_TECH_TYPE C_TECH_TYPE
---------------------------------------------------------------- ------------- ----------- ----------- -----------
LETTER_B 1800 3667 7482 3854
LETTER_B 200 3712 7583 3708
LETTER_C 300 6326 12450 6229
LETTER_A 100 6343 12469 6193
SQL>
SQL> select *
2 from table(dbms_xplan.display_cursor())
3 ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 59hmrtw6q3f4u, child number 0
-------------------------------------
with xyz as ( select connect_by_root(link_id) as root_link_id,
connect_by_root(group_id) as root_group_id, tech_type from
tech_values X start with parent_group_id = 0 connect by prior
group_id = parent_group_id ) select * from xyz pivot ( count(1)
as tech_type for tech_type in ( 'A' as A,
'B' as B, 'C' as C ))
Plan hash value: 3833790953
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 563 (100)| |
| 1 | HASH GROUP BY PIVOT | | 29102 | 1420K| 563 (1)| 01:23:35 |
| 2 | VIEW | | 29102 | 1420K| 1044 (47)| 02:35:00 |
|* 3 | CONNECT BY WITH FILTERING| | | | | |
|* 4 | TABLE ACCESS FULL | TECH_VALUES | 4 | 84 | 187 (0)| 00:27:46 |
|* 5 | HASH JOIN | | 29098 | 966K| 375 (1)| 00:55:41 |
| 6 | CONNECT BY PUMP | | | | | |
| 7 | TABLE ACCESS FULL | TECH_VALUES | 80020 | 1641K| 187 (0)| 00:27:46 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PARENT_GROUP_ID"=PRIOR NULL)
4 - filter("PARENT_GROUP_ID"=0)
5 - access("connect$_by$_pump$_002"."prior group_id "="PARENT_GROUP_ID")
31 rows selected.
SQL>
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
08.06.14 23:18:40,805000 +02:00
SQL>
SQL> spool off
Upvotes: 1
Related Questions
- How to obtain total children count for all parents in Oracle tree hierarchy?
- postgresql count the number of children
- retrieve a count of child values under the parent values in PLSQL
- Hierarchical Query - Counting records that belongs to Parent and Child Places
- how to count number of children row by family and put the same in the parent rows in oracle
- PostgreSQL / Oracle => query to group and count hierarchical values
- Recursive SQL - count number of descendants in hierarchical structure
- Getting counts at each level of a hierarchical query
- Oracle SQL Count Children and Parents by Date and ID
- hierarchical query and counting siblings?