oracle 求BOM树型展开的总用量

oracle 求BOM树型展开的总用量 (2012-08-26 18:27:31)转载▼
标签： oracle bom 树型 展开 it 分类： 数据库
一个BOM展开求总使用量问题求助:
当然也请各路高手拔刀相助

1.建表:

表1 结构表
create table MANUF_STRUCTURE_TAB
(PART_NO VARCHAR2(25) not null, –父
COMPONENT_PART VARCHAR2(25) not null, –子
QTY_PER_ASSEMBLY NUMBER not null) –子对父的构成数(即1个父由多少个子构成)

表2 分类表

create table INVENTORY_PART_TAB
(PART_NO VARCHAR2(25) not null, –编码
SK_PART_CATEGORY_DB VARCHAR2(15) not null) –类别

2.说明:
表2中SK_PART_CATEGORY_DB分3类:
Product:成品
SemiProdudct:半成品
Component:原材料

3.想要结果:
1个成品所需要原材料的总使用量

PART_NO COMPONENT_PART T_QTY
……

测试数据及想要结果(想要结果未能列完)
想用sys_connect_by_path 把QTY_PER_ASSEMBLY用乘號串起來, 然後調用一個能執行動態SQL的function以得到結果.

简化一下你的数据：

create table MANUF_STRUCTURE_TAB
(PART_NO VARCHAR2(25) not null,
COMPONENT_PART VARCHAR2(25) not null,
QTY_PER_ASSEMBLY NUMBER not null
);

create table INVENTORY_PART_TAB
(PART_NO VARCHAR2(25) not null,
SK_PART_CATEGORY_DB VARCHAR2(15) not null
);

INSERT INTO MANUF_STRUCTURE_TAB VALUES (‘A1’,’B’,2);
INSERT INTO MANUF_STRUCTURE_TAB VALUES (‘A2’,’B’,3);
INSERT INTO MANUF_STRUCTURE_TAB VALUES (‘B’,’C’,4);
INSERT INTO MANUF_STRUCTURE_TAB VALUES (‘C’,’D’,7);
INSERT INTO MANUF_STRUCTURE_TAB VALUES (‘A1’,’C’,6);

INSERT INTO INVENTORY_PART_TAB VALUES (‘A1’,’Product’);
INSERT INTO INVENTORY_PART_TAB VALUES (‘A2’,’Product’);
INSERT INTO INVENTORY_PART_TAB VALUES (‘B’,’SemiProdudct’);
INSERT INTO INVENTORY_PART_TAB VALUES (‘C’,’Component’);
INSERT INTO INVENTORY_PART_TAB VALUES (‘D’,’Component’);

三种方法（既然你不喜欢自定义函数就没写了）：

WITH t AS (
SELECT SYS_CONNECT_BY_PATH(a.part_no,’\’)||’\’||a.component_part||’\’ as path
,qty_per_assembly
FROM manuf_structure_tab a JOIN inventory_part_tab b
ON a.part_no = b.part_no
START WITH b.sk_part_category_db = ‘Product’
CONNECT BY a.part_no = PRIOR a.component_part
)
SELECT root_part,part,SUM(qty) AS qty
FROM (
SELECT SUBSTR(t1.path,2,INSTR(t1.path,’\’,1,2)-INSTR(t1.path,’\’,1,1)-1) as ROOT_PART
,SUBSTR(t1.path,INSTR(t1.path,’\’,-1,2)+1,INSTR(t1.path,’\’,-1,1)-INSTR(t1.path,’\’,-1,2)-1) as PART
,EXP(SUM(LN(t2.qty_per_assembly))) AS qty
FROM t t1,t t2
WHERE t1.path LIKE t2.path||’%’
GROUP BY t1.path
)
GROUP BY root_part,part;

WITH t AS (
SELECT SYS_CONNECT_BY_PATH(a.part_no,’\’)||’\’||a.component_part||’\’ as path
,qty_per_assembly
FROM manuf_structure_tab a JOIN inventory_part_tab b
ON a.part_no = b.part_no
START WITH b.sk_part_category_db = ‘Product’
CONNECT BY a.part_no = PRIOR a.component_part
)
SELECT root_part,part,SUM(qty) AS qty
FROM (
SELECT SUBSTR(t1.path,2,INSTR(t1.path,’\’,1,2)-INSTR(t1.path,’\’,1,1)-1) as ROOT_PART
,SUBSTR(t1.path,INSTR(t1.path,’\’,-1,2)+1,INSTR(t1.path,’\’,-1,1)-INSTR(t1.path,’\’,-1,2)-1) as PART
,(SELECT EXP(SUM(LN(t2.qty_per_assembly))) FROM t t2 WHERE t1.path LIKE t2.path||’%’) AS qty
FROM t t1
GROUP BY t1.path
)
GROUP BY root_part,part;

WITH t AS (
SELECT SYS_CONNECT_BY_PATH(a.part_no,’\’)||’\’||a.component_part||’\’ as path
,SYS_CONNECT_BY_PATH(a.qty_per_assembly,’\’)||’\’ as qty_path
,qty_per_assembly
FROM manuf_structure_tab a JOIN inventory_part_tab b
ON a.part_no = b.part_no
START WITH b.sk_part_category_db = ‘Product’
CONNECT BY a.part_no = PRIOR a.component_part
)
SELECT root_part,part,SUM(qty) AS qty
FROM (
SELECT SUBSTR(path,2,INSTR(path,’\’,1,2)-INSTR(path,’\’,1,1)-1) as ROOT_PART
,SUBSTR(path,INSTR(path,’\’,-1,2)+1,INSTR(path,’\’,-1,1)-INSTR(path,’\’,-1,2)-1) as PART
,(SELECT EXP(SUM(LN(SUBSTR(qty_path,INSTR(qty_path,’\’,1,rn)+1,INSTR(qty_path,’\’,1,rn+1)-INSTR(qty_path,’\’,1,rn)-1))))
FROM (SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM<=100)
WHERE SUBSTR(qty_path,INSTR(qty_path,’\’,1,rn)+1,INSTR(qty_path,’\’,1,rn+1)-INSTR(qty_path,’\’,1,rn)-1) IS NOT NULL
) AS qty
FROM t
)
GROUP BY root_part,part;

如果最后只需要原材料，你自己过滤一下。

等会有空我用10G的MODEL来写一个。

用11GR2的递归WITH子查询应该是易如反掌，但我现在没环境测试。

---

发现有大量重复的，这是怎么回事？父、子应该构成唯一键。如果不唯一要先DISTINCT.

经测试第三种效果最好，和10G的MODEL差不多。

上面的方法都不新鲜了，这个10G的MODEL方法好像没见过：

WITH t AS (
SELECT SYS_CONNECT_BY_PATH(a.part_no,’\’)||’\’||a.component_part as path
,qty_per_assembly
,LEVEL lvl
FROM manuf_structure_tab a JOIN inventory_part_tab b
ON a.part_no = b.part_no
START WITH b.sk_part_category_db = ‘Product’
CONNECT BY a.part_no = PRIOR a.component_part
)
SELECT root_part,part,SUM(qty) AS qty
FROM (
SELECT SUBSTR(path,2,INSTR(path,’\’,1,2)-INSTR(path,’\’,1,1)-1) as ROOT_PART
,SUBSTR(path,INSTR(path,’\’,-1,1)+1) as PART
,qty
FROM t
MODEL IGNORE NAV RETURN UPDATED ROWS
DIMENSION BY (path)
MEASURES (qty_per_assembly, 1 qty,lvl)
RULES AUTOMATIC ORDER (
qty[any] order by path=(CASE WHEN lvl[cv()]=1 THEN qty_per_assembly[cv()]
ELSE qty_per_assembly[cv()] *
qty[SUBSTR(cv(),1,INSTR(cv(),’\’,-1,1)-1)]
END)
)
)
GROUP BY root_part,part;

我的manuf_structure_tab有15万记录,INVENORY_PART_TAB有8W.
目前执行了5MIN还没反应.
WITH t AS (
SELECT SYS_CONNECT_BY_PATH(a.part_no,’\’)||’\’||a.component_part||’\’ as path
,qty_per_assembly
FROM manuf_structure_tab a,inventory_part_tab b
where a.part_no = b.part_no
and a.contract=b.contract
and b.contract=’SKY’
START WITH b.sk_part_category_db = ‘Product’
CONNECT BY a.part_no = PRIOR a.component_part
)
SELECT root_part,part,SUM(qty) AS qty
FROM (
SELECT SUBSTR(t1.path,2,INSTR(t1.path,’\’,1,2)-INSTR(t1.path,’\’,1,1)-1) as ROOT_PART
,SUBSTR(t1.path,INSTR(t1.path,’\’,-1,2)+1,INSTR(t1.path,’\’,-1,1)-INSTR(t1.path,’\’,-1,2)-1) as PART
,(SELECT EXP(SUM(LN(t2.qty_per_assembly))) FROM t t2 WHERE t1.path LIKE t2.path||’%’) AS qty
FROM t t1
GROUP BY t1.path
)
GROUP BY root_part,part

这样呢？
WITH t AS (
SELECT SYS_CONNECT_BY_PATH(a.part_no,’\’)||’\’ as path
,component_part as part
,LEVEL lvl
,SYS_CONNECT_BY_PATH(a.qty_per_assembly,’\’)||’\’ as qty_path
FROM manuf_structure_tab a JOIN inventory_part_tab b
ON a.part_no = b.part_no
START WITH b.sk_part_category_db = ‘Product’
CONNECT BY a.part_no = PRIOR a.component_part
)
SELECT root_part,part,SUM(qty) AS qty
FROM (
SELECT SUBSTR(path,2,INSTR(path,’\’,1,2)-2) as ROOT_PART
,PART
,(SELECT EXP(SUM(LN(SUBSTR(qty_path,INSTR(qty_path,’\’,1,rn)+1,INSTR(qty_path,’\’,1,rn+1)-INSTR(qty_path,’\’,1,rn)-1))))
FROM (SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM<=100)
WHERE rn<=lvl
) AS qty
FROM t
)
GROUP BY root_part,part;

---

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 804 | 3163K| | 1216 (1)| 00:00:15 |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
|* 2 | VIEW | | 1 | 13 | | 2 (0)| 00:00:01 |
| 3 | COUNT | | | | | | |
| 4 | CONNECT BY WITHOUT FILTERING| | | | | | |
| 5 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 6 | HASH GROUP BY | | 804 | 3163K| 13M| 1216 (1)| 00:00:15 |
| 7 | VIEW | | 2587 | 9M| | 9 (12)| 00:00:01 |
|* 8 | CONNECT BY WITH FILTERING | | | | | | |
|* 9 | FILTER | | | | | | |
| 10 | COUNT | | | | | | |
|* 11 | HASH JOIN | | 2587 | 126K| | 9 (12)| 00:00:01 |
| 12 | TABLE ACCESS FULL | INVENTORY_PART_TAB | 887 | 20401 | | 3 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | MANUF_STRUCTURE_TAB | 2587 | 69849 | | 5 (0)| 00:00:01 |
|* 14 | HASH JOIN | | | | | | |
| 15 | CONNECT BY PUMP | | | | | | |
| 16 | COUNT | | | | | | |
|* 17 | HASH JOIN | | 2587 | 126K| | 9 (12)| 00:00:01 |
| 18 | TABLE ACCESS FULL | INVENTORY_PART_TAB | 887 | 20401 | | 3 (0)| 00:00:01 |
| 19 | TABLE ACCESS FULL | MANUF_STRUCTURE_TAB | 2587 | 69849 | | 5 (0)| 00:00:01 |
| 20 | COUNT | | | | | | |
|* 21 | HASH JOIN | | 2587 | 126K| | 9 (12)| 00:00:01 |
| 22 | TABLE ACCESS FULL | INVENTORY_PART_TAB | 887 | 20401 | | 3 (0)| 00:00:01 |

| 23 | TABLE ACCESS FULL | MANUF_STRUCTURE_TAB | 2587 | 69849 | | 5 (0)| 00:00:01 |

从你的数据量来看，遍历深度应该很浅啊？如果单独挑几种产品(在START WITH中指定)，速度怎么样？结果对吗？
单独把WITH里面的查询CREATE TABLE AS…要花多少时间？生成的结果多大？这一步完成后尽量过滤（比如只保留你要的原料），让后面的乘法计算更少。
SELECT xmlquery(‘1*2*3*4’ returning content).getnumberval() FROM DUAL;

但是10G不支持变量，只能常量：
SELECT xmlquery(str returning content).getnumberval()
FROM (SELECT ‘1*2*3*4’ as str FROM DUAL);

ERROR at line 1:
ORA-19102: XQuery string literal expected
—- 11GR2:
WITH t(root_part,part,qty) AS (
SELECT a.part_no as root_part, component_part as part,qty_per_assembly as qty
FROM manuf_structure_tab a JOIN inventory_part_tab b
ON a.part_no = b.part_no
WHERE b.sk_part_category_db = ‘Product’
UNION ALL
SELECT a.root_part,b.component_part,a.qty*b.qty_per_assembly
FROM t a, manuf_structure_tab b
WHERE a.part = b.part_no
)
SELECT root_part,part,SUM(qty) AS qty
FROM t
GROUP BY root_part,part;

ROOT_PART PART QTY

---

A2 C 12
A1 D 98
A1 B 2
A2 B 3
A1 C 14
A2 D 84