Family Tree and Connect by

Family Tree and
Connect By
Hierarchical Queries
If a table contains hierarchical data, then you can select rows in a
hierarchical order using the hierarchical query clause:
hierarchical_query_clause::=
Text Description of Hierarchal Query :
1. START WITH specifies the root row(s) of the hierarchy.

2. CONNECT BY specifies the relationship between parent
rows and child rows of the hierarchy. In a hierarchical query,
one expression in condition must be qualified with the
PRIOR operator to refer to the parent row. For example,
3. ... PRIOR expr = expror... expr = PRIOR expr If the
CONNECT BY condition is compound, then only one
condition requires the PRIOR operator. For example:
4. CONNECT BY last_name != 'King' AND PRIOR
employee_id = manager_id In addition, the CONNECT BY
condition cannot contain a subquery.
If the CONNECT BY condition is compound, then only one
condition requires the PRIOR operator. For example:
CONNECT BY last_name != 'King' AND PRIOR employee_id =
manager_id In addition, the CONNECT BY condition cannot
contain a subquery.
PRIOR is a unary operator and has the same precedence as the
unary + and - arithmetic operators. It evaluates the immediately
following expression for the parent row of the current row in a
hierarchical query.
PRIOR is most commonly used when comparing column values
with the equality operator. (The PRIOR keyword can be on either
side of the operator.) PRIOR causes Oracle to use the value of the
parent row in the column. Operators other than the equal sign (=)
are theoretically possible in CONNECT BY clauses. However,
the conditions created by these other operators can result in an
infinite loop through the possible combinations. In this case
Oracle detects the loop at run time and returns an error.
The following hierarchical query uses the CONNECT BY clause to
define the relationship between employees and managers:
SELECT empno,ename, mgr FROM emp CONNECT BY

PRIOR empno = mgr;
The next example is similar to the preceding example, but uses
the LEVEL pseudocolumn to show parent and child rows:
SELECT empno, ename, mg, LEVEL FROM employees

CONNECT BY PRIOR empno = mgr;
SQL> SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH
(ename, '/') "Path" FROM emp START WITH
ename = 'KING' CONNECT BY PRIOR empno = mgr;
LEVEL
For each row returned by a hierarchical query, the LEVEL pseudo

column returns 1 for a root row, 2 for a child of a root, and so on.
A root row is the highest row within an inverted tree. A child
row is any non root row. A parent row is any row that has
children. A leaf row is any row without children.
To define a hierarchical relationship in a query, you must use the
START WITH and CONNECT BY clauses.
Restriction on LEVEL in WHERE clauses: In a [NOT] IN
condition in a WHERE clause, if the right-hand side of the
condition is a sub query, you cannot use LEVEL on the left-hand
side of the condition. However, you can specify LEVEL in a sub
query of the FROM clause to achieve the same result. For
example, the following statement is not valid:
SELECT empno, ename FROM employees

WHERE (empno)
IN (SELECT empno FROM employees)
START WITH employee_id = 7766
CONNECT BY PRIOR empno = mgr;
SYS_CONNECT_BY_PATH
Syntax
sys_connect_by_path::=
Purpose
SYS_CONNECT_BY_PATH is valid only in hierarchical queries.
It returns the path of a column value from root to node, with
column values separated by char for each row returned by
CONNECT BY condition.
Both column and char can be any of the datatypes CHAR,

VARCHAR2, NCHAR, or NVARCHAR2. The string returned is
of VARCHAR2 datatype and is in the same character set as
column.
Examples
The following example returns the path of employee names from
employee Kochhar to all employees of Kochhar (and their
employees):
SELECT LPAD(' ', 2*level-1)||
SYS_CONNECT_BY_PATH(ename, '/') "Path" FROM emp
START WITH ename = 'KING' CONNECT BY PRIOR empno
= mgr;
Some More Examples
create table corporate_slaves ( slave_id integer
primary key, supervisor_id references
corporate_slaves, name varchar2(100));
insert into corporate_slaves values (1, NULL, 'Big Boss Man');
insert into corporate_slaves values (2, 1, 'VP Marketing');
insert into corporate_slaves values (3, 1, 'VP Sales');
insert into corporate_slaves values (4, 3, 'Joe Sales Guy');
insert into corporate_slaves values (5, 4, 'Bill Sales Assistant');
insert into corporate_slaves values (6, 1, 'VP Engineering');
insert into corporate_slaves values (7, 6, 'Jane Nerd');
insert into corporate_slaves values (8, 6, 'Bob Nerd');
SQL> column name format a20
SQL> select * from corporate_slaves;
select name, slave_id, supervisor_idfrom corporate_slavesconnect

by prior slave_id = supervisor_id;
select name, slave_id, supervisor_idfrom corporate_slavesconnect

by prior slave_id = supervisor_idstart with slave_id in (select
slave_id from corporate_slaves where supervisor_id is null);
select name, slave_id, supervisor_id, level from corporate_slaves
connect by prior slave_id = supervisor_id start with slave_id = 1;
column padded_name format a30

select lpad(' ', (level - 1) * 2) || name as padded_name,
slave_id, supervisor_id, level from corporate_slaves connect by
prior slave_id = supervisor_id start with slave_id = 1;
select lpad(' ', (level - 1) * 2) || name as padded_name, slave_id,
supervisor_id, level from corporate_slaves where level <= 3
connect by prior slave_id = supervisor_id start with slave_id = 1;
supervisor_id, level from corporate_slaves connect by prior
slave_id = supervisor_id start with slave_id = 1 order by level,
name;
supervisor_id, level from corporate_slaves connect by prior
slave_id = supervisor_id start with slave_id = 1 order by name;
Performance and Tuning
Oracle is not getting any help from the Tree Fairy in producing
results from a CONNECT BY. If you don't want tree queries to take
O(N^2) time, you need to build indices that let Oracle very quickly
answer questions of the form "What are all the children of Parent
X?"
For the corporate slaves table, you'd want two concatenated indices:
create index corporate_slaves_idx1 on corporate_slaves (slave_id,
supervisor_id);create index corporate_slaves_idx2 on
corporate_slaves (supervisor_id, slave_id);

Family Tree and Connect by

Uploaded by

Copyright:

Available Formats

Family Tree and Connect by

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Family Tree and Connect by

Uploaded by

Copyright:

Available Formats

Family Tree and

1. START WITH specifies the root row(s) of the hierarchy.

SELECT empno,ename, mgr FROM emp CONNECT BY

SELECT empno, ename, mg, LEVEL FROM employees

For each row returned by a hierarchical query, the LEVEL pseudo

SELECT empno, ename FROM employees

Both column and char can be any of the datatypes CHAR,

select name, slave_id, supervisor_idfrom corporate_slavesconnect

select name, slave_id, supervisor_idfrom corporate_slavesconnect

column padded_name format a30

You might also like