Hierarchical Data

Order of Operations

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

  1. join conditions (either in the FROM clause or the WHERE clause)
  2. START WITH clause
  3. CONNECT BY clause
  4. 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