| Business intelligence systems need
to perform complex queries efficiently, and some of these queries
must access hierarchical data. For example, an employee table
may hold an organization's hierarchy information by listing
each employee's name and manager. How can we generate an organization
chart based on the hierarchical data? Oracle9i can readily
perform the necessary hierarchical query with the CONNECT BY
clause. The CONNECT BY clause specifies the relationship between
parent rows and child rows in the hierarchy and the starting
point of the hierarchy.
Here is a simple example of a hierarchical query:
SELECT employee_name, manager_name,
LEVEL
FROM employees
START WITH employee_name = 'King'
CONNECT BY PRIOR employee_id = manager_id;
This query returns rows from the table employees
in a hierarchical order starting with the employee whose name
is 'King.' The LEVEL keyword is used to show the level of
the hierarchy. A partial set of results is shown below:
| EMPLOYEE_NAME |
MANAGER_NAME |
LEVEL |
| King |
|
1 |
| Greenberg |
King |
2 |
| Faviet |
Greenberg |
3 |
| Chen |
Greenberg |
3 |
| Sciarra |
Greenberg |
3 |
| Urman |
Greenberg |
3 |
| Popp |
Greenberg |
3 |
In Oracle9i,
significant improvements have been made to the CONNECT BY
feature.
- In Oracle8i,
the CONNECT BY clause could not be used in a query that
contained a join. In Oracle9i, this restriction has
been removed. The following is an example of a query that
can be used in Oracle9i.
SELECT employee_name, manager_name, dept_name
FROM employee, dept
WHERE employee.deptno = dept.deptno
START WITH employee_name = 'KING'
CONNECT BY PRIOR employee_id = manager_id;
This query returns all employees, their
manager name and their department name starting with the
employee 'KING.'
- The CONNECT BY clause can be used with
all objects in Oracle9i.
That is, it can be used with views, external tables, etc.
In Oracle8i,
CONNECT BY could be used only with tables.
- A new keyword, 'SIBLINGS,' can be used
to order all child rows of a given parent by some criteria.
The query below gives an example of SIBLINGS:
SELECT employee_name, manager_name,
dept_name
FROM employee, dept
WHERE employee.deptno = dept.deptno
START WITH employee_name = 'KING'
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY hire_date;
This query orders all employees reporting
to a common manager by their hire date.
- The new function SYS_CONNECT_BY_PATH can
be used to describe the path to any row in the hierarchy.
This function takes 2 parameters. The first is the column
for which we need the path. The second is the list separator
to use when generating the path. Here is a brief example:
SELECT employee_name,
SYS_CONNECT_BY_PATH(employee_name, '/') "PATH"
FROM employee
START WITH employee_name = 'KING'
CONNECT BY PRIOR employee_id = manager_id;
This query returns the employee name and
the management chain for each employee starting with the
employee 'KING.' The results, using the same values shown
in the first example, would look like:
| EMPLOYEE_NAME |
PATH |
| King |
/King |
| Greenberg |
/King/Greenberg |
| Faviet |
/King/Greenberg/Faviet |
| Chen |
/King/Greenberg/Chen |
| Sciarra |
/King/Greenberg/Sciarra |
| Urman |
/King/Greenberg/Urman |
| Popp |
/King/Greenberg/Popp |
The CONNECT BY enhancements in Oracle9i
enable quicker performance, greater flexibility and more productive
development when querying hierarchical data. These enhancements
are just one aspect of the many Oracle9i
features which support complex business intelligence queries.
More
Info
 |
Oracle9i
SQL Reference Release 1 (9.0.1) Chapter 7 - SQL Queries and Other
SQL Statements - Hierarchical Queries |
Oracle9i
Daily Features
|
 |
Bookmark
