Hierarchical Data: Order of Operations

The clauses in hierarchical queries are processed in the following order.

join conditions (either in the FROM clause or the WHERE clause)
START WITH clause
CONNECT BY clause
WHERE clause conditions that are not joins.

The following two snippets demonstrate how this order of operations affects query results when filter conditions are in the WHERE clause versus when they are in the CONNECT BY clause.

Filter Condition in WHERE

Filter Condition in CONNECT BY

select
  lpad(' ', level-1 ) ||
    key as key_indented ,
  level
from
  t
WHERE
  LEVEL != 3
start with
  key = 'server'
connect by
  parent_key = prior key
  --
;

KEY_INDENTED     LEVEL
--------------- ------
server               1
 bin                 2
 config              2
 ctx                 2
   delx              4
   enlx              4
   eslx              4

select
  lpad(' ', level-1 ) ||
    key as key_indented ,
  level
from
  t
--
--
start with
  key = 'server'
CONNECT BY
  parent_key = prior key and
  LEVEL != 3
;

KEY_INDENTED     LEVEL
--------------- ------
server               1
 bin                 2
 config              2
 ctx                 2

Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

[url=http://www.sqlsnippets.com/en/topic-11812.html]SQL Snippets: Hierarchical Data - Order of Operations[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

<a href="http://www.sqlsnippets.com/en/topic-11812.html">SQL Snippets: Hierarchical Data - Order of Operations</a>

To link to this page in other web sites use the following values.

Link Text : SQL Snippets: Hierarchical Data - Order of Operations
URL (href): http://www.sqlsnippets.com/en/topic-11812.html

SQL Snippets ™: Tutorials for Oracle Developers

Hierarchical Data

Order of Operations

Linking to SQL Snippets ™