层次查询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; 

（编辑：湖南网）

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