Boost Oracle Hierarchical Query Performance: Filtering, CBO, and Parallel Strategies

1. Filtering After Tree Generation vs. During Traversal

Placing filter conditions in the WHERE clause trims leaf nodes after the result tree is fully built, while putting them in the CONNECT BY clause prunes sub‑trees during generation. Developers often mistakenly filter after the tree is built, causing massive intermediate result sets and poor performance.

A typical problematic query on the zzzz.SYS_RC_ROUTE_DETAIL table (over 3,000 rows) ran for more than a minute without returning results because many rows satisfied nextnodeid = node_id, producing tens of millions of intermediate rows.

Moving the filter into the CONNECT BY clause reduced execution time from 9 minutes to 0.3 seconds.

2. CBO Estimation Inaccuracy

Oracle's Cost‑Based Optimizer (CBO) often severely misestimates row counts for hierarchical queries, sometimes off by hundreds or thousands of times, even when statistics are gathered. The recursive nature of CONNECT BY makes accurate cardinality prediction difficult.

To mitigate this, the author suggests adding special hint parameters that force the optimizer to use a more realistic row‑count estimate, rather than relying on baseline statistics.

3. Parallel Processing with Pipelined Table Functions

Applying the PARALLEL hint directly to a hierarchical query often leads to “parallel‑serialization” where true parallelism is not achieved. Instead, rewriting the query using a pipelined table function enables genuine parallel execution.

Below is a complete example that creates a test table, gathers statistics, defines a REF CURSOR package, and implements a pipelined function treeWalk that walks each subtree in parallel.

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;
/

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 body refcur_pkg is end;
/

create or replace package connect_by_parallel as
  cursor c1(p_rowid rowid) is
    select CONNECT_BY_ROOT ltrim(id) root_id,
           CONNECT_BY_ISLEAF is_leaf,
           level as t1_level,
           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
      fetch p_ref into in_rec;
      exit when p_ref%notfound;
      for c1rec in c1(in_rec.row_id) loop
        pipe row(c1rec);
      end loop;
    end loop;
    execute immediate 'alter session set "_old_connect_by_enabled"=false';
    return;
  end;
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)));

This approach reduces execution time dramatically compared with the original hierarchical query.

Conclusion

Although hierarchical queries represent a small fraction of overall SQL workloads, they are often the hardest to tune. By moving filters into the CONNECT BY clause, addressing CBO misestimates with targeted hints, and leveraging pipelined table functions for parallel execution, developers can achieve substantial performance improvements in Oracle databases.