sql - Oracle SQL 分层查询 : Flatten Hierarchy and Perform Aggregation

Question

我正在尝试提高我已经编写的概念证明的性能，但没有成功。我认为这种方法可能有缺陷，但我正在努力寻找另一种解决方案。我已经介绍了我能找到的所有 Ask Tom 文章和论坛帖子。

我们运行的是 Oracle 10g R2。

我们的项目按层次结构排列。数量是根据关系定义的。层次结构中有两种类型的对象:作为逻辑分组的程序集和表示实际项的项。因此，如果我们代表一个完整的工具集，我们将有一个代表整个工具集的根和一个代表实际工具的叶子。所以:

工具套装 -> Screwdriver -> 一字 Screwdriver -> 小一字 Screwdriver

程序集可以在层次结构中重复使用，项目也是如此。

我需要展平层次结构，以便项目的每个实例都有一行和数量。任何关系都可以有一个 >= 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
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 行输出。

我也尝试过使用并行性，但正如他所说，几乎没有或没有性能提升。

Answer 1

Podiluska 的建议很好。如果您有 Oracle 11g R2，那么公用表表达式是最佳选择。新语法的递归特性将允许您放弃 sys_connect_by_path 与 instr 的结合，这将严重损害您的性能。

试试这个:

select
  child,
  sum(total_quantity) total_quantity
from (
  with h (parent, child, isleaf, quantity, total_quantity) as (
    select 
      parent,
      child,
      isleaf,
      quantity,
      quantity total_quantity
    from
      itemhier
    where
      parent = 'ASSY001' 
    union all
    select
      ih.parent,
      ih.child,
      ih.isleaf,
      ih.quantity,
      ih.quantity * h.total_quantity total_quantity
    from
      itemhier ih
    join 
      h on h.child = ih.parent
  )
  select * from h
  where isleaf = 1
)
group by child;

这是 sqlfiddle:http://sqlfiddle.com/#!4/9840f/6