如何在基于父链接的Oracle分层表上执行计数 [英] How to Perform Counts on a Oracle Hierarchical Table based on Parent Link
问题描述
我有以下Oracle 11g R2分级表定义：
表名称：TECH_VALUES：
ID，
GROUP_ID，
LINK_ID
PARENT_GROUP_ID，
TECH_TYPE
基于上述分层表定义，一些示例数据可能如下所示：
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
LINK_IDsTECH_TYPEs执行计数，只基于父链接ID，父组ID为0， 还可能有重复的父链接ID，例如LETTER_B和所有相同的子值，但是不同的GROUP ID。
我基本上是在一个表/报表查询后，看起来像这样：
ID链接ID技术类型A技术类型B技术类型C组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
分层，我的表可能包含超过30,000条记录，我还需要确保性能生成上面显示的报告查询速度很快。
显然，为了生成上述报告，我需要收集所有必要的计数细目，基于
TECH_TYPE所有父链接ID，其中PARENT_GROUP_ID = 0，并按照此报告布局将它们存储在表中。
希望有人可以帮助执行计数以及将信息存储到名为LINK_COUNTS的新表中的组合查询，此报表将基于此。此表格的列为：
ID，
LINK_ID，
TECH_TYPE_A，
TECH_TYPE_B ，
TECH_TYPE_C，
GROUP_ID
希望能够基于从上面的示例数据返回的结果更新LINK_COUNTS表作为仅一个SQL UPDATE事务的一部分，因为顶层父链接ids将在我的LINK_COUNTS表中已经存在，只需要提供计数明细值每个父节点链接，即
LETTER_A
LETTER_B
LETTER_C
LETTER_B
使用类似方式：
UPDATE link_counts
SET（TECH_TYPE_A，TECH_TYPE_B，TECH_TYPE_C）=
（与xyz
其中link_id = LINK_COUNTS.link_id
....
etc
谢谢。
方案要选择数据，请尝试这样：
使用xyz as（
select tech_type，
connect_by_root（link_id）as root_link_id，
connect_by_root（group_id）as root_group_id
从tech_values
开始with parent_group_id = 0
按先前的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
））
;
在39000条记录的测试集上，采用带有Oracle 11.2的英特尔酷睿i5（以及闪存缓冲区缓存）约0.15秒来计算。
要用这些数据更新目标表，eithr循环遍历上述
SELECT并更新您的link_counts表，或简单地使用MERGE语句：
使用（
with xyz as（
select tech_type，
connect_by_root（link_id））来合并到link_counts T
）as root_link_id，
connect_by_root（group_id）as root_group_id
来自tech_values X
从parent_group_id = 0开始
按先前的group_id = parent_group_id连接
）
选择*
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）
当匹配时
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
如果不匹配则
插入（link_id，group_id，tech_type_a， tech_type_b，tech_type_c）
值（S.root_link_id，S.root_group_id，S.a_tech_type，S.b_tech_type，S.c_tech_type）
;
MERGE $ c> GROUP_ID 是 link_counts 表中的主/唯一键。原始问题中 tech_values 表的实际定义支持此假设。
SQL * Plus如下：
SQL>
SQL> select count（1）
2 from user_indexes
3其中table_name ='TECH_VALUES'
4;
COUNT（1）
----------
0
SQL>
SQL>选择count（1）
2来自user_constraints
3其中table_name ='TECH_VALUES'
4和constraint_type！='C'
5;
COUNT（1）
----------
0
SQL>
SQL> alter system flush buffer_cache;
系统更改。
SQL> alter system flush shared_pool;
系统更改。
SQL>
SQL>从双选择systimestamp;
SYSTIMESTAMP
------------------------------------- --------------------------------------
08.06.14 23:18:40 ，053000 +02：00
SQL>
SQL> select tech_type，count（1）
2从tech_values
3其中parent_group_id！= 0
4按分组汇总（tech_type）;
T COUNT（1）
- ----------
A 20048
B 39984
C 19984
80016
SQL>
SQL>从双选择systimestamp;
SYSTIMESTAMP
------------------------------------- --------------------------------------
08.06.14 23:18:40 ，144000 +02：00
SQL>
SQL> alter system flush buffer_cache;
系统更改。
SQL> alter system flush shared_pool;
系统更改。
SQL>
SQL>从双选择systimestamp;
SYSTIMESTAMP
------------------------------------- --------------------------------------
08.06.14 23:18:40 ，246000 +02：00
SQL>
SQL>与xyz as（
2选择connect_by_root（link_id）作为root_link_id，connect_by_root（group_id）作为root_group_id，tech_type
3从tech_values X
4从parent_group_id = 0开始
5连接previous group_id = parent_group_id
6）
7选择*
8从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从表（dbms_xplan.display_cursor（））
3;
PLAN_TABLE_OUTPUT
------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------
SQL_ID 59hmrtw6q3f4u，子编号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 （'A'为A，
'B'为B）的选择（计数（1）
作为tech_type的技术类型，'C'as C））
计划散列值：3833790953
--------------------- -------------------------------------------------- --------------------
| Id |操作|名称|行| Bytes |成本（％CPU）|时间|
---------------------------------------------- ---------------------------------------------
| 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 |连接与过滤| | | | | |
| * 4 | TABLE ACCESS FULL | TECH_VALUES | 4 | 84 | 187（0）| 00:27:46 |
| * 5 | HASH JOIN | | 29098 | 966K | 375（1）| 00:55:41 |
| 6 |连接泵| | | | | |
| 7 | TABLE ACCESS FULL | TECH_VALUES | 80020 | 1641K 187（0）| 00:27:46 |
---------------------------------------------- ---------------------------------------------
谓词信息（由操作id标识）：
---------------------------------- -----------------
3 - 访问（PARENT_GROUP_ID= PRIOR NULL）
4 - 过滤器（PARENT_GROUP_ID= 0）
5 - access（connect $ _by $ _pump $ _002。previous group_id=PARENT_GROUP_ID）
选择31行。
SQL>
SQL>从双选择systimestamp;
SYSTIMESTAMP
------------------------------------- --------------------------------------
08.06.14 23:18:40 ，805000 +02：00
SQL>
SQL> spool off
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 .
Hope someone can assist with perhaps 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
Thanks.
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
这篇关于如何在基于父链接的Oracle分层表上执行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
