我正在努力提高性能,以证明我已经写过并且没有运气的概念证明。我认为这种方法可能有缺陷,但是我正在努力寻找另一种解决方案。我介绍了我可以找到的所有Ask Tom文章和论坛帖子。
我们正在运行Oracle 10g R2。
我们将项目按层次结构排列。数量是在关系上定义的。层次结构中有两种类型的对象:逻辑分组的程序集和代表实际项目的项目。因此,如果我们要代表一个完整的工具集,我们将有一个代表整个工具集的根,以及一个代表实际工具的叶子。所以:
工具套件->螺丝刀->平头螺丝刀->小平头螺丝刀
程序集和项目一样可以在层次结构中重用。
我需要展平层次结构,以便项目的每个实例都有一行和数量。任何关系都可以具有> = 1的数量。要获取某项的数量,我们需要从根到叶的所有关系中获取数量的乘积。
我的解决方案有效,但是扩展性不佳。根据实际数据运行,大约需要8分钟才能产生6000+行,而我们的层次结构将产生50k +行。理想情况下,此过程将在10秒或更短的时间内完成,但我知道这很乐观;)
我的解决方案和简化的数据集如下。任何反馈将不胜感激!
CREATE TABLE ITEMHIER ( PARENT VARCHAR2(30 BYTE), CHILD VARCHAR2(30 BYTE), QUANTITY NUMBER(15,2), ISLEAF NUMBER ); INSERT INTO ITEMHIER (PARENT, CHILD, QUANTITY, ISLEAF) VALUES ('ASSY005','ITEM001',2,1); INSERT INTO ITEMHIER (PARENT, CHILD, QUANTITY, ISLEAF) VALUES ('ASSY005','ITEM002',1,1); INSERT INTO ITEMHIER (PARENT, CHILD, QUANTITY, ISLEAF) VALUES ('ASSY005','ITEM003',5,1); INSERT INTO ITEMHIER (PARENT, CHILD, QUANTITY, ISLEAF) VALUES ('ASSY006','ITEM002',10,1); INSERT INTO ITEMHIER (PARENT, CHILD, QUANTITY, ISLEAF) VALUES ('ASSY006','ITEM004',3,1); INSERT INTO ITEMHIER (PARENT, CHILD, QUANTITY, ISLEAF) VALUES ('ASSY007','ITEM005',12,1); INSERT INTO ITEMHIER (PARENT, CHILD, QUANTITY, ISLEAF) VALUES ('ASSY007','ITEM006',1,1); INSERT INTO ITEMHIER (PARENT, CHILD, QUANTITY, ISLEAF) VALUES ('ASSY008','ITEM006',2,1); INSERT INTO ITEMHIER (PARENT, CHILD, QUANTITY, ISLEAF) VALUES ('ASSY008','ITEM005',5,1); INSERT INTO ITEMHIER (PARENT, CHILD, QUANTITY, ISLEAF) VALUES ('ASSY002','ASSY005',2,0); INSERT INTO ITEMHIER (PARENT, CHILD, QUANTITY, ISLEAF) VALUES ('ASSY002','ASSY007',1,0); INSERT INTO ITEMHIER (PARENT, CHILD, QUANTITY, ISLEAF) VALUES ('ASSY003','ASSY006',3,0); INSERT INTO ITEMHIER (PARENT, CHILD, QUANTITY, ISLEAF) VALUES ('ASSY003','ASSY008',2,0); INSERT INTO ITEMHIER (PARENT, CHILD, QUANTITY, ISLEAF) VALUES ('ASSY004','ASSY007',1,0); INSERT INTO ITEMHIER (PARENT, CHILD, QUANTITY, ISLEAF) VALUES ('ASSY004','ASSY005',3,0); INSERT INTO ITEMHIER (PARENT, CHILD, QUANTITY, ISLEAF) VALUES ('ASSY004','ASSY006',2,0); INSERT INTO ITEMHIER (PARENT, CHILD, QUANTITY, ISLEAF) VALUES ('ASSY001','ASSY002',1,0); INSERT INTO ITEMHIER (PARENT, CHILD, QUANTITY, ISLEAF) VALUES ('ASSY001','ASSY003',2,0); INSERT INTO ITEMHIER (PARENT, CHILD, QUANTITY, ISLEAF) VALUES ('ASSY001','ASSY004',1,0); COMMIT; / CREATE OR REPLACE FUNCTION GETQTY(P_NAVPATH IN VARCHAR2, P_STARTWITH IN VARCHAR2) RETURN INTEGER AS R_QTY INTEGER; BEGIN SELECT EXP(SUM(LN(QUANTITY))) INTO R_QTY FROM ( SELECT QUANTITY, SYS_CONNECT_BY_PATH(CHILD,'/') NAV_PATH FROM ITEMHIER START WITH PARENT = P_STARTWITH CONNECT BY PRIOR CHILD = PARENT ) WHERE INSTR(P_NAVPATH, NAV_PATH) = 1; RETURN R_QTY; END; / SELECT 'ASSY001' || SYS_CONNECT_BY_PATH(CHILD,'/') NAV_PATH, GETQTY(SYS_CONNECT_BY_PATH(CHILD,'/'), 'ASSY001') QTY, CHILD FROM ITEMHIER WHERE ISLEAF = 1 START WITH PARENT = 'ASSY001' CONNECT BY PRIOR CHILD = PARENT;
- - 编辑
使用该WITH子句,我可以将处理时间减少约1/2,这是一个很大的收获!还有其他想法吗?
WITH
with h as ( select sys_connect_by_path(child,'/') navpath, child, quantity qty, isleaf from itemhier start with parent = 'ASSY001' connect by prior child = parent ) select h1.navpath, h1.child, (SELECT exp(sum(ln(h2.qty))) FROM h h2 WHERE instr(h1.navpath, h2.navpath) = 1) qty from h h1 where isleaf = 1
编辑2
jonearles建议使用sys_connect_by_path来构建算术表达式,然后使用PL / SQL对其进行评估似乎是可行的方法。在最大的数据集上运行,我能够在55秒内产生77k行的输出。
我也尝试使用并行性,但是正如他指出的那样,几乎没有性能提升。
您可以使用SYS_CONNECT_BY_PATH生成表达式,即分支中所有数量的乘积。然后使用函数动态执行该表达式以获取最终数量。
SYS_CONNECT_BY_PATH
这不是理想的解决方案。SQL和PL / SQL之间的上下文切换将花费一些时间。您将需要担心SQL注入。但是至少您可以避免两次查询同一张表。
(如Dan A.和podiluska所建议的那样,递归CTE很可能是最好的解决方案。以我的经验,即使两种语法在做相同的事情并且使用相似的访问路径,递归CTE也会比快得多connect by。但您需要等到升级到11gR2。)
connect by
CREATE OR REPLACE FUNCTION EVALUATE_EXPRESSION(P_EXPRESSION IN VARCHAR2) RETURN NUMBER AS R_QTY INTEGER; BEGIN EXECUTE IMMEDIATE 'SELECT '||P_EXPRESSION||' FROM DUAL' INTO R_QTY; RETURN R_QTY; END; / SELECT 'ASSY001' || SYS_CONNECT_BY_PATH(CHILD,'/') NAV_PATH, GETQTY(SYS_CONNECT_BY_PATH(CHILD,'/'), 'ASSY001') QTY, SUBSTR(SYS_CONNECT_BY_PATH(QUANTITY,'*'), 2) QTY_EXPRESSION, EVALUATE_EXPRESSION(SUBSTR(SYS_CONNECT_BY_PATH(QUANTITY,'*'), 2)) QTY2, CHILD FROM ITEMHIER WHERE ISLEAF = 1 START WITH PARENT = 'ASSY001' CONNECT BY PRIOR CHILD = PARENT;
另外,您提到该表具有索引。但是查询是否使用索引?你可以发布解释计划吗?
最后,对于这种缓慢的查询,您可能需要研究并行性。不幸的是,我从来没有使用并行和运气connect by。