强制 Oracle 在 START WITH 中使用从查询外部的筛选

Question

表定义：

  create table Tree
  (node varchar2(20),
  parentNode varchar2(20),
  val number);
  create index idx_tree_01 on Tree
  (node );
   create index idx_tree_02 on Tree
  (parentnode); 

示例数据：

Insert into TREE (NODE,PARENTNODE,VAL) values ('2','1',2);
Insert into TREE (NODE,PARENTNODE,VAL) values ('3','2',3);
Insert into TREE (NODE,PARENTNODE,VAL) values ('4','2',3);
Insert into TREE (NODE,PARENTNODE,VAL) values ('5','4',1);
Insert into TREE (NODE,PARENTNODE,VAL) values ('6','3',1);

视图定义：

 create view tree_view as 
     select connect_by_root parentnode as firstNode,
      lpad(' ', 2 * level - 2, ' ') || val as MyVal,
      node, parentNode
   from tree 
   start with parentnode in ( select parentnode from tree) 
   connect by parentnode = prior node

现在我想执行查询：

select * from tree_view 
where firstNode = '1'

查询的执行计划为：

-------------------------------------------------------------------------
| Id  | Operation                                | Name        | E-Rows |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |             |        |
|*  1 |  VIEW                                    | TREE_VIEW   |      5 |
|*  2 |   CONNECT BY NO FILTERING WITH START-WITH|             |        |
|   3 |    TABLE ACCESS FULL                     | TREE        |      5 |
|*  4 |    INDEX RANGE SCAN                      | IDX_TREE_02 |      1 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("FIRSTNODE"='1')
   2 - access("PARENTNODE"=PRIOR NULL)
       filter( IS NOT NULL)
   4 - access("PARENTNODE"=:B1)

如您所见，最后使用了filterfirstnode = '1'。我希望它被用作第一件事。

我

无法修改视图（我只能添加一些提示）。

如何提示 Oracle 在 START WITH 中使用 firstNode 值？ 这将带来巨大的性能改进。

我真正的"树"表是几个表，数据量很大。正如我提到的，我无法修改视图。

不要向我提议：

• 具有第一节点参数和流水线结果的函数
• 修改视图以在"开头为"中使用会话上下文
• 临时表酷儿在开始于
• 等。

视图必须相同。仅允许使用提示。

Answer 1

>first_node是视图中由connect_by_root()运算符生成的列。文档说这个运算符：

不能在 START WIT H 条件或CONNECT BY条件中指定此运算符。

所以无论如何都无法实现你想要的。如果您考虑一下，这并非不合理：要派生第一个节点，查询必须遍历整个层次结构以查找每个叶子的根节点。

一种调优方法 - 尽管可能不是对您开放的方法 - 是使用传递闭包实现层次结构。看看这个其他 SO 线程。

Answer 2

您可以尝试创建 PIPELINED 函数以返回视图行

CREATE TYPE t_row AS OBJECT (
  first_name  VARCHAR2(1000),
  my_val      VARCHAR2(1000)
  ... 
  );
CREATE TYPE t_tab IS TABLE OF t_row;
CREATE OR REPLACE FUNCTION get_hierarchy RETURN t_tab PIPELINED
IS
BEGIN
    FOR rec IN (SELECT ...) LOOP
      PIPE ROW (t_row(rec.firstNode, rec.myval, ...));
    END LOOP;
RETURN;
END get_hierarchy;

并重写查询

SELECT *
  FROM TABLE(get_hierarchy)

Answer 3

你会满意吗？

SELECT DISTINCT *
FROM tree_view 
START WITH firstNode = '1'
CONNECT BY parentnode = PRIOR node
 Plan Hash Value  : 2532164387 
-----------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name        | Rows | Bytes | Cost | Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |             |    5 | 10090 |    4 | 00:00:01 |
|   1 |   HASH UNIQUE                                |             |    5 | 10090 |    4 | 00:00:01 |
| * 2 |    CONNECT BY NO FILTERING WITH SW (UNIQUE)  |             |      |       |      |          |
|   3 |     VIEW                                     | TREE_VIEW   |    5 | 10190 |    4 | 00:00:01 |
| * 4 |      CONNECT BY NO FILTERING WITH START-WITH |             |      |       |      |          |
|   5 |       TABLE ACCESS FULL                      | TREE        |    5 |    35 |    3 | 00:00:01 |
| * 6 |       INDEX RANGE SCAN                       | IDX_TREE_02 |    1 |     2 |    1 | 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("T"."PARENTNODE"=PRIOR "T"."NODE")
* 2 - filter("FIRSTNODE"='1')
* 4 - access("PARENTNODE"=PRIOR "NODE")
* 4 - filter( EXISTS (SELECT 0 FROM TESTER."TREE" "TREE" WHERE "PARENTNODE"=:B1))
* 6 - access("PARENTNODE"=:B1)

区别在于第 4 行：filter( EXISTS (SELECT 0 FROM TESTER."TREE" "TREE" WHERE "PARENTNODE"=:B1))此查询返回重复的数据。 DISTINCT 是保持相同结果集所必需的。