层次查询SQL性能故障不断？给你份可靠的避坑指南！

但其实这两种写法在语义上差别很大，结果集也可能不相同，如下：

  SQL> select id from test2 where id3 = 10 start with id = 3 connect by nocycle prior id2 = id; 
 
    ID 
---------- 
     8 
 
Elapsed: 00:00:00.13 
 
SQL> select id from test2  start with id = 3 connect by nocycle prior id2 = id and id3=10; 
 
    ID 
---------- 
     3 
 
Elapsed: 00:00:00.00 

二、CBO估算不准确

层次查询的SQL语句频繁出现的问题，就是CBO估算返回结果集偏差，引起执行计划不准确。虽然表上收集过统计信息，但是CBO对于结果集的估算跟实际值偏差非常大(几百上千的倍的差距)，但是这个也不能全怪CBO，毕竟递归查询有多少层、有多少数据要裁剪，结合起来考虑，结果确实难以估量。

对于CBO估算不准的问题，我们考虑了对结果集相对特殊的参数，在SQL文本上做区分，应用识别特殊参数运行带hint地改造SQL，通过hint来指定返回结果集。这种情况不同于普通的数据倾斜，无法通过baseline给出一个不涉及应用改造的方案。

三、并行处理

层次查询的SQL直接使用parallel的hint，会遭遇并行串行化的问题，也就是不能真正并行。对于一些重要且耗时长的层次查询，可以考虑PIPELINED TABLE FUNCTION改写SQL的方式来实现。

以下脚本测试参考了陈焕生童鞋的blog以及oracle相关文档(Doc ID 2168864.1)：

drop table t1; 
-- t1 with 100,000 rows 
create table t1 
as 
select 
    rownum                      id, 
    lpad(rownum, 10, '0')       v1, 
    trunc((rownum - 1)/100)     n1, 
    rpad(rownum, 100)           padding 
from 
    dual 
connect by level <= 100000 
; 
 
begin 
    dbms_stats.gather_table_stats(user,'T1'); 
end; 
/ 
 
select /*+ monitor */ 
    count(*) 
from 
( 
    select 
        CONNECT_BY_ROOT ltrim(id) root_id, 
        CONNECT_BY_ISLEAF is_leaf, 
        level as t1_level, 
        a.v1 
    from t1 a 
    start with a.id <=1000 
    connect by NOCYCLE id = prior id + 1000 
); 
 
create or replace package refcur_pkg 
AS 
    TYPE R_REC IS RECORD (row_id ROWID); 
    TYPE refcur_t IS REF CURSOR RETURN R_REC; 
END; 
/ 
 
create or replace package connect_by_parallel 
as 
   /*  Naviagates a shallow hiearchy in parallel, where we do a tree walk for each root */ 
 
    CURSOR C1 (p_rowid ROWID) IS     -- Cursor done for each subtree. This select is provided by the customer 
    select  CONNECT_BY_ROOT ltrim(id) root_id, CONNECT_BY_ISLEAF is_leaf, level as t1_level, a.v1 
          from t1 a 
          start with rowid = p_rowid 
          connect by NOCYCLE id = prior id + 1000; 
 
    TYPE T1_TAB is TABLE OF C1%ROWTYPE; 
 
    FUNCTION treeWalk (p_ref refcur_pkg.refcur_t) RETURN T1_TAB 
             PIPELINED 
    PARALLEL_ENABLE(PARTITION p_ref BY ANY); 
 
END connect_by_parallel; 
/ 
 
create or replace package body connect_by_parallel 
as  
FUNCTION treeWalk (p_ref refcur_pkg.refcur_t) RETURN T1_TAB 
          PIPELINED PARALLEL_ENABLE(PARTITION p_ref BY ANY) 
IS 
  in_rec p_ref%ROWTYPE; 
BEGIN 
   execute immediate 'alter session set "_old_connect_by_enabled"=true'; 
   LOOP -- for each root 
    FETCH p_ref INTO in_rec; 
    EXIT WHEN p_ref%NOTFOUND; 
    FOR c1rec IN c1(in_rec.row_id)  LOOP -- retrieve rows of subtree 
        PIPE ROW(c1rec); 
    END LOOP; 
  END LOOP; 
  execute immediate 'alter session set "_old_connect_by_enabled"=false';  
  RETURN; 
END  treeWalk; 
 
END connect_by_parallel; 
/ 
 
SELECT 
  /*+ monitor */ 
  COUNT(*) 
FROM TABLE(connect_by_parallel.treeWalk (CURSOR 
  (SELECT /*+ parallel (a 100) */ 
    rowid FROM t1 a WHERE id <= 100))) b; 

（编辑：滁州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!