Oracle分层“先连接”或递归CTE查询的性能

我需要追踪文件相互卷进的路径。为了做到这一点，我使用一个由先前的查询连接构建了一个层次结构树。我还将查询重写为递归CTE ，试图查看是否可以获得更好的性能。我的表上有大约1.9亿条记录，其中大约1.1亿条记录需要我跟踪。

TEST TABLE AND DATA:

CREATE TABLE TEST_TABLE
(
    TABLE_KEY INTEGER
  , DOC1 VARCHAR2(39)
  , DOC2 VARCHAR2(39)
  , DOC1_KEY VARCHAR2(50)
  , DOC2_KEY VARCHAR2(50)
  , LOC INTEGER
  , TXN_DATE DATE
);
commit; 

INSERT INTO TEST_TABLE VALUES (1, 'DOC123', 'DOC123', 'DOC1230', 'DOC1230', 111, '18-JAN-2008 13:37:00');
INSERT INTO TEST_TABLE VALUES (2, 'TM123', 'DOC123', 'TM1230111', 'DOC1230', 111, '26-AUG-2008 20:41:29');
INSERT INTO TEST_TABLE VALUES (3, 'TM123', 'TM123', 'TM1230222', 'TM1230111', 222, '29-AUG-2008 09:57:22');
INSERT INTO TEST_TABLE VALUES (4, 'TM123', 'TM123', 'TM1230333', 'TM1230111', 333, '31-MAY-2011 08:46:06');
INSERT INTO TEST_TABLE VALUES (5, 'TM123', 'TM123', 'TM1230444', 'TM1230111', 444, '15-NOV-2011 10:43:44');
INSERT INTO TEST_TABLE VALUES (6, 'FIN123', 'TM123', 'FIN1230', 'TM1230222', 555, '09-APR-2015 07:49:21');
INSERT INTO TEST_TABLE VALUES (7, 'FIN456', 'TM123', 'FIN4560', 'TM1230111', 111, '20-MAY-2015 15:12:59');
INSERT INTO TEST_TABLE VALUES (8, 'FIN222', 'TM123', 'FIN2220', 'TM1230222', 222, '25-MAY-2015 15:12:59');

INSERT INTO TEST_TABLE VALUES (9, 'FIN222', 'TM499', 'FIN2220', 'TM4991111', 222, '27-FEB-2009 16:42:39');
INSERT INTO TEST_TABLE VALUES (10, 'DOC456', 'DOC456', 'DOC4561', 'DOC4561', 111, '20-APR-2012 09:21:59');
INSERT INTO TEST_TABLE VALUES (11, 'TM499', 'DOC456', 'TM4991111', 'DOC4561', 111, '20-APR-2012 09:22:10');
INSERT INTO TEST_TABLE VALUES (12, 'FIN456', 'TM499', 'FIN4560', 'TM4991111', 111, '04-JUN-2012 23:44:44');
commit;

QUERY #1 (connect by prior):

select /*+ parallel 8 */ 
  DOC,
  max(FIN_DOC) keep (dense_rank first order by rownum ) FIN_DOC,
  max(LOC) keep (dense_rank first order by rownum )LOC,
  max(TXN_DATE) keep (dense_rank first order by rownum ) TXN_DATE,
  max(PATH) keep (dense_rank first order by rownum ) PATH,
  max(PATHLEN) keep (dense_rank first order by rownum ) PATHLEN
from 
(
  SELECT 
    CONNECT_BY_ROOT DOC2 as DOC,
    DOC1 as FIN_DOC,
    LOC,
    TXN_DATE,
    trim(SYS_CONNECT_BY_PATH(DOC1, ' ->')) as PATH,
    LEVEL-1 as PATHLEN
  FROM TEST_TABLE 
  WHERE CONNECT_BY_ISLEAF = 1
  START WITH DOC2 in 
  (
    select DOC2
    from TEST_TABLE 
    WHERE DOC2 LIKE 'DOC%'
  )
  CONNECT BY NOCYCLE
    PRIOR DOC1_key = DOC2_KEY 
    AND PRIOR TXN_DATE < TXN_DATE 
  ORDER SIBLINGS BY TXN_DATE
)
GROUP BY DOC;

QUERY #2 (recursive CTE):

WITH RECURSIVE_CTE_QUERY(DOC2_KEY, DOC1_KEY, ROOT_DOC2, DOC1, LOC, TXN_DATE, PATH, PATHLEN) 
AS
(
  SELECT sh.DOC2_KEY,  sh.DOC1_KEY, sh.DOC2 as ROOT_DOC2, sh.DOC1, sh.LOC, sh.TXN_DATE,  
         ('->' || sh.DOC1) as PATH, 
         0 as PATHLEN
  FROM TEST_TABLE sh
  WHERE DOC2 LIKE 'DOC%'

  UNION ALL
  
  SELECT sh2.DOC2_KEY, sh2.DOC1_KEY, rq.ROOT_DOC2, sh2.DOC1, sh2.LOC, sh2.TXN_DATE, 
         (rq.PATH || ' ->' || sh2.DOC1) as PATH,  
         (rq.PATHLEN + 1) as PATHLEN
  FROM TEST_TABLE sh2 
  JOIN RECURSIVE_CTE_QUERY rq
    ON rq.DOC1_KEY = sh2.DOC2_KEY
   AND rq.TXN_DATE < sh2.TXN_DATE
)  SEARCH DEPTH FIRST BY TXN_DATE DESC SET SEQ
    
select /*+ parallel 8 */ 
  ROOT_DOC2 as DOC,
  max(DOC1) keep (dense_rank last order by SEQ) FIN_DOC,
  max(LOC) keep (dense_rank last order by SEQ) LOC,
  max(TXN_DATE) keep (dense_rank last order by SEQ) TXN_DATE,
  max(PATH) keep (dense_rank last order by SEQ) PATH,
  max(PATHLEN) keep (dense_rank last order by SEQ) PATHLEN
from RECURSIVE_CTE_QUERY
GROUP BY ROOT_DOC2;

我需要做的是从某些类型的文档开始，然后跟踪它们的进展，然后给出最终的文档，原始文档最终被卷进去。相同的文档可以多次卷入到其他文档中，因此只跟踪每个文档的第一个路径非常重要。因此，从上面的测试数据来看，下面是我应该得到的结果（上面的两个查询都给出了正确的结果） ：

DOC     FIN_DOC   LOC  TXN_DATE          PATH                               PATHLEN
------- -------- ----- ----------------- ---------------------------------- --------
DOC123  FIN123    555  4/9/15 07:49:21   ->DOC123 ->TM123 ->TM123 ->FIN123       3 
DOC456  FIN456    111  6/4/12 23:44:44   ->DOC456 ->TM499 ->FIN456               2 

所以我基本上是在追踪每个文档到它的第一页的路径。我有DOC1、DOC2、DOC1_KEY、DOC2_KEY、TXN_DLTD索引和LOC上的位图索引。但是有了这么多数据，我看到唯一使用的索引就是DOC2 （在尝试查找开始使用哪些文档的子查询中）.

我还尝试了在构建树后使用第一叶的方法，但是我认为使用（Dense_RANK...）会占用更少的内存，并且希望速度更快。

是否有更有效/更简单的方法来实现同样的结果？或者对使用的查询或表进行任何其他性能调整？

提前感谢您的帮助！

如果您以110M行开始，则无论您执行什么操作，此查询都会花费一些时间！

上的索引：

( doc2_key,txn_date )

可能有助于递归查询中的连接。

另一种方法是预先计算路径、长度和根文档。然后将这些作为额外列存储在表中。这意味着您只需扫描一次表。如果叶行相对较少，则可以添加一个“is_叶”列并为其建立索引，这可能会进一步帮助您。

缺点是每次改变表时都需要重新计算这些计算。如果您的数据很少更改，而且您经常查询，这可能是一个更好的方法。

但是，如果您经常在层次结构底部以外的任何位置添加/删除行，则此开销可能无法证明查询的好处是合理的。

如果您需要有关查询的进一步帮助，请根据实际表发布查询的执行计划。