:meta-keywords: start with clause, connect by clause, hierarchical pseudo column, hierarchical query example, hierarchical query function
:meta-description: Hierarchical Query is used to obtain a set of data organized in a hierarchy. The START WITH ... CONNECT BY clause is used in combination with the SELECT clause in the following form.
******************
Hierarchical Query
******************
Hierarchical Query is used to obtain a set of data organized in a hierarchy. The **START WITH ... CONNECT BY** clause is used in combination with the **SELECT** clause in the following form.
You can execute the queries by changing the order of two clauses like **CONNECT BY ... START WITH**. ::
SELECT column_list
FROM table_joins | tables
[WHERE join_conditions and/or filtering_conditions]
[hierarchical_clause]
hierarchical_clause :
[START WITH condition] CONNECT BY [NOCYCLE] condition
| CONNECT BY [NOCYCLE] condition [START WITH condition]
START WITH Clause
=================
The **START WITH** clause will filter the rows from which the hierarchy will start. The rows that satisfy the **START WITH** condition will be the root nodes of the hierarchy. If **START WITH** is omitted, then all the rows will be considered as root nodes.
.. note::
If **START WITH** clause is omitted or the rows that satisfy the **START WITH** condition does not exist, all of rows in the table are considered as root nodes; which means that hierarchy relationship of sub rows which belong each root is searched. Therefore, some of results can be duplicate.
CONNECT BY Clause
=================
* **PRIOR** : The **CONNECT BY** condition is tested for a pair of rows. If it evaluates to true, the two rows satisfy the parent-child relationship of the hierarchy. We need to specify the columns that are used from the parent row and the columns that are used from the child row. We can use the **PRIOR** operator when applied to a column, which will refer to the value of the parent row for that column. If **PRIOR** is not used for a column, the value in the child row is used.
* **NOCYCLE** : In some cases, the resulting rows of the table joins may contain cycles, depending on the **CONNECT BY** condition. Because cycles cause an infinite loop in the result tree construction, CUBRID detects them and either returns an error doesn't expand the branches beyond the point where a cycle is found (if the **NOCYCLE** keyword is specified). This keyword may be specified after the **CONNECT BY** keywords. It makes CUBRID run a statement even if the processed data contains cycles.
If a **CONNECT BY** statement causes a cycle at runtime and the **NOCYCLE** keyword is not specified, CUBRID will return an error and the statement will be canceled. When specifying the **NOCYCLE** keyword, if CUBRID detects a cycle while processing a hierarchy node, it will set the **CONNECT_BY_ISCYCLE** attribute for that node to the value of 1 and it will stop further expansion of that branch.
The following example shows how to execute hierarchical query.
.. code-block:: sql
-- Creating tree table and then inserting data
CREATE TABLE tree(ID INT, MgrID INT, Name VARCHAR(32), BirthYear INT);
INSERT INTO tree VALUES (1,NULL,'Kim', 1963);
INSERT INTO tree VALUES (2,NULL,'Moy', 1958);
INSERT INTO tree VALUES (3,1,'Jonas', 1976);
INSERT INTO tree VALUES (4,1,'Smith', 1974);
INSERT INTO tree VALUES (5,2,'Verma', 1973);
INSERT INTO tree VALUES (6,2,'Foster', 1972);
INSERT INTO tree VALUES (7,6,'Brown', 1981);
-- Executing a hierarchical query with CONNECT BY clause
SELECT id, mgrid, name
FROM tree
CONNECT BY PRIOR id=mgrid
ORDER BY id;
::
id mgrid name
========================================
1 NULL 'Kim'
2 NULL 'Moy'
3 1 'Jonas'
3 1 'Jonas'
4 1 'Smith'
4 1 'Smith'
5 2 'Verma'
5 2 'Verma'
6 2 'Foster'
6 2 'Foster'
7 6 'Brown'
7 6 'Brown'
7 6 'Brown'
.. code-block:: sql
-- Executing a hierarchical query with START WITH clause
SELECT id, mgrid, name
FROM tree
START WITH mgrid IS NULL
CONNECT BY prior id=mgrid
ORDER BY id;
::
id mgrid name
========================================
1 NULL 'Kim'
2 NULL 'Moy'
3 1 'Jonas'
4 1 'Smith'
5 2 'Verma'
6 2 'Foster'
7 6 'Brown'
Hierarchical Query Execution
============================
Hierarchical Query for Table Join
---------------------------------
When target table is joined in **SELECT** statement, **WHERE** clause can include not only searching conditions but also joining conditions. At this time, CUBRID applies the joining conditions in **WHERE** clause at first, then conditions in **CONNECT BY** clause; at last, the left searching conditions.
When specifying joining conditions and searching conditions together in **WHERE** clause, joining conditions can be applied as searching conditions even if there was no intention; so the operating order can be different; therefore, we recommend that you specify the table joining conditions in **FROM** clause, not in **WHERE** conditions.
Query Results
-------------
The resulting rows of the table joins are filtered according to the **START WITH** condition to obtain the root nodes for the hierarchy. If no **START WITH** condition is specified, then all the rows resulting from the table joins will be considered as root nodes. After the root nodes are obtained, CUBRID will select the child rows for the root nodes. These are all nodes from the table joins that respect the **CONNECT BY** condition. This step will be repeated for the child nodes to determine their child nodes and so on until no more child nodes can be added.
In addition, CUBRID evaluates the **CONNECT BY** clause first and all the rows of the resulting hierarchy tress by using the filtering condition in the **WHERE** clause.
The example illustrates how joins can be used in **CONNECT BY** queries.
.. code-block:: sql
-- Creating tree2 table and then inserting data
CREATE TABLE tree2(id int, treeid int, job varchar(32));
INSERT INTO tree2 VALUES(1,1,'Partner');
INSERT INTO tree2 VALUES(2,2,'Partner');
INSERT INTO tree2 VALUES(3,3,'Developer');
INSERT INTO tree2 VALUES(4,4,'Developer');
INSERT INTO tree2 VALUES(5,5,'Sales Exec.');
INSERT INTO tree2 VALUES(6,6,'Sales Exec.');
INSERT INTO tree2 VALUES(7,7,'Assistant');
INSERT INTO tree2 VALUES(8,null,'Secretary');
-- Executing a hierarchical query onto table joins
SELECT t.id,t.name,t2.job,level
FROM tree t INNER JOIN tree2 t2 ON t.id=t2.treeid
START WITH t.mgrid is null
CONNECT BY prior t.id=t.mgrid
ORDER BY t.id;
::
id name job level
==============================================================
1 'Kim' 'Partner' 1
2 'Moy' 'Partner' 1
3 'Jonas' 'Developer' 2
4 'Smith' 'Developer' 2
5 'Verma' 'Sales Exec.' 2
6 'Foster' 'Sales Exec.' 2
7 'Brown' 'Assistant' 3
Ordering Data with the Hierarchical Query
-----------------------------------------
The **ORDER SIBLINGS BY** clause will cause the ordering of the rows while preserving the hierarchy ordering so that the child nodes with the same parent will be stored according to the column list. ::
ORDER SIBLINGS BY col_1 [ASC|DESC] [, col_2 [ASC|DESC] [...[, col_n [ASC|DESC]]...]]
The following example shows how to display information about seniors and subordinates in a company in the order of birth year.
The result with hierarchical query shows parent and child nodes in a row according to the column list specified in **ORDER SIBLINGS BY** statement by default. Sibling nodes that share the same parent node have outputted in a specified order.
.. code-block:: sql
-- Outputting a parent node and its child nodes, which sibling nodes that share the same parent are sorted in the order of birthyear.
SELECT id, mgrid, name, birthyear, level
FROM tree
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER SIBLINGS BY birthyear;
::
id mgrid name birthyear level
==================================================================
2 NULL 'Moy' 1958 1
6 2 'Foster' 1972 2
7 6 'Brown' 1981 3
5 2 'Verma' 1973 2
1 NULL 'Kim' 1963 1
4 1 'Smith' 1974 2
3 1 'Jonas' 1976 2
The following example shows how to display information about seniors and subordinates in a company in the order of joining. For the same level, the employee ID numbers are assigned in the order of joining. *id* indicates employee ID numbers (parent and child nodes) and *mgrid* indicates the employee ID numbers of their seniors.
.. code-block:: sql
-- Outputting a parent node and its child nodes, which sibling nodes that share the same parent are sorted in the order of id.
SELECT id, mgrid, name, LEVEL
FROM tree
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER SIBLINGS BY id;
::
id mgrid name level
=====================================================
1 NULL 'Kim' 1
3 1 'Jonas' 2
4 1 'Smith' 2
2 NULL 'Moy' 1
5 2 'Verma' 2
6 2 'Foster' 2
7 6 'Brown' 3
Pseudo Columns for Hierarchical Query
=====================================
LEVEL
-----
**LEVEL** is a pseudocolumn representing depth of hierarchical queries. The **LEVEL** of root node is 1 and the LEVEL of its child node is 2.
The **LEVEL** (pseudocolumn) can be used in the **WHERE** clause, **ORDER BY** clause, and **GROUP BY ... HAVING** clause of the **SELECT** statement. And it can also be used in the statement using aggregate functions.
The following example shows how to retrieve the **LEVEL** value to check level of node.
.. code-block:: sql
-- Checking the LEVEL value
SELECT id, mgrid, name, LEVEL
FROM tree
WHERE LEVEL=2
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER BY id;
::
id mgrid name level
=====================================================
3 1 'Jonas' 2
4 1 'Smith' 2
5 2 'Verma' 2
6 2 'Foster' 2
The following example shows how to add **LEVEL** conditions after the **CONNECT BY** statement.
.. code-block:: sql
SELECT LEVEL FROM db_root CONNECT BY LEVEL <= 10;
::
level
=============
1
2
3
4
5
6
7
8
9
10
Note that the format of "CONNECT BY expr(LEVEL) < expr", for example "CONNECT BY LEVEL +1 < 5") is not supported.
CONNECT_BY_ISLEAF
-----------------
**CONNECT_BY_ISLEAF** is a pseudocolumn representing that the result of hierarchical query is leaf node. If the current row is a leaf node, it returns 1; otherwise, it returns 0.
The following example shows how to retrieve the **CONNECT_BY_ISLEAF** value to check whether it is a leaf node or not.
.. code-block:: sql
-- Checking a CONNECT_BY_ISLEAF value
SELECT id, mgrid, name, CONNECT_BY_ISLEAF
FROM tree
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER BY id;
::
id mgrid name connect_by_isleaf
=============================================================
1 NULL 'Kim' 0
2 NULL 'Moy' 0
3 1 'Jonas' 1
4 1 'Smith' 1
5 2 'Verma' 1
6 2 'Foster' 0
7 6 'Brown' 1
CONNECT_BY_ISCYCLE
------------------
**CONNECT_BY_ISCYCLE** is a pseudocolumn representing that a cycle was detected while processing the node, meaning that a child was also found to be an ancestor. A value of 1 for a row means a cycle was detected; the pseudo-column's value is 0, otherwise.
The **CONNECT_BY_ISCYCLE** pseudo-column can be used in the **WHERE**, **ORDER BY** and **GROUP BY** ... **HAVING** clauses of the **SELECT** statement. It can also be used in aggregate functions.
.. note:: This pseudocolumn is available only when the **NOCYCLE** keyword is used in the statement.
The following example shows how to retrieve the **CONNECT_BY_ISCYCE** value to check a row that occurs loop.
.. code-block:: sql
-- Creating a tree_cycle table and inserting data
CREATE TABLE tree_cycle(ID INT, MgrID INT, Name VARCHAR(32));
INSERT INTO tree_cycle VALUES (1,NULL,'Kim');
INSERT INTO tree_cycle VALUES (2,11,'Moy');
INSERT INTO tree_cycle VALUES (3,1,'Jonas');
INSERT INTO tree_cycle VALUES (4,1,'Smith');
INSERT INTO tree_cycle VALUES (5,3,'Verma');
INSERT INTO tree_cycle VALUES (6,3,'Foster');
INSERT INTO tree_cycle VALUES (7,4,'Brown');
INSERT INTO tree_cycle VALUES (8,4,'Lin');
INSERT INTO tree_cycle VALUES (9,2,'Edwin');
INSERT INTO tree_cycle VALUES (10,9,'Audrey');
INSERT INTO tree_cycle VALUES (11,10,'Stone');
-- Checking a CONNECT_BY_ISCYCLE value
SELECT id, mgrid, name, CONNECT_BY_ISCYCLE
FROM tree_cycle
START WITH name in ('Kim', 'Moy')
CONNECT BY NOCYCLE PRIOR id=mgrid
ORDER BY id;
::
id mgrid name connect_by_iscycle
==================================================
1 NULL 'Kim' 0
2 11 'Moy' 0
3 1 'Jonas' 0
4 1 'Smith' 0
5 3 'Verma' 0
6 3 'Foster' 0
7 4 'Brown' 0
8 4 'Lin' 0
9 2 'Edwin' 0
10 9 'Audrey' 0
11 10 'Stone' 1
Operators for Hierarchical Query
================================
CONNECT_BY_ROOT
---------------
The **CONNECTION_BY_ROOT** operator returns the value of a root row as a column value. This operator can be used in the **WHERE** and **ORDER BY** clauses of the **SELECT** statement.
The following example shows how to retrieve the root row's *id* value.
.. code-block:: sql
-- Checking the id value of a root row for each row
SELECT id, mgrid, name, CONNECT_BY_ROOT id
FROM tree
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER BY id;
::
id mgrid name connect_by_root id
=============================================================
1 NULL 'Kim' 1
2 NULL 'Moy' 2
3 1 'Jonas' 1
4 1 'Smith' 1
5 2 'Verma' 2
6 2 'Foster' 2
7 6 'Brown' 2
.. _prior-operator:
PRIOR
-----
The PRIOR operator returns the value of a parent row as a column value and returns NULL for the root row. This operator can be used in the **WHERE**, **ORDER BY** and **CONNECT BY** clauses of the **SELECT** statement.
The following example shows how to retrieve the parent row's *id* value.
.. code-block:: sql
-- Checking the id value of a parent row for each row
SELECT id, mgrid, name, PRIOR id as "prior_id"
FROM tree
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER BY id;
::
id mgrid name prior_id
=====================================================
1 NULL 'Kim' NULL
2 NULL 'Moy' NULL
3 1 'Jonas' 1
4 1 'Smith' 1
5 2 'Verma' 2
6 2 'Foster' 2
7 6 'Brown' 6
Functions for Hierarchical Query
================================
SYS_CONNECT_BY_PATH
-------------------
The **SYS_CONNECT_BY_PATH** function returns the hierarchical path from a root to the specified row in string. The column and separator specified as an argument must be a character type. Each path separated by specified separator will be displayed consecutively. This function can be used in the **WHERE** and **ORDER BY** clauses of the **SELECT** statement. ::
SYS_CONNECT_BY_PATH (column_name, separator_char)
The following example shows how to retrieve path from a root to the specified row.
.. code-block:: sql
-- Executing a hierarchical query with SYS_CONNECT_BY_PATH function
SELECT id, mgrid, name, SYS_CONNECT_BY_PATH(name,'/') as [hierarchy]
FROM tree
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER BY id;
::
id mgrid name hierarchy
==============================================================
1 NULL 'Kim' '/Kim'
2 NULL 'Moy' '/Moy'
3 1 'Jonas' '/Kim/Jonas'
4 1 'Smith' '/Kim/Smith'
5 2 'Verma' '/Moy/Verma'
6 2 'Foster' '/Moy/Foster'
7 6 'Brown' '/Moy/Foster/Brown'
Examples of Hierarchical Query
==============================
The examples in this page shows how to write hierarchical queries by specifying the **CONNECT BY** clause within the **SELECT** statement.
A table that have relationship with recursive reference is create and the table consists of two columns named *ID* and *ParentID*; assume that *ID* is a primary key for the table and *ParentID* is a foreign key for the same table. In this context, the root node will have a *ParentID* value of **NULL**.
Once a table is create, you can get the entire data with hierarchical structure and a value of **LEVEL** by using the **UNION ALL** as shown below.
.. code-block:: sql
CREATE TABLE tree_table (ID int PRIMARY KEY, ParentID int, name VARCHAR(128));
INSERT INTO tree_table VALUES (1,NULL,'Kim');
INSERT INTO tree_table VALUES (2,1,'Moy');
INSERT INTO tree_table VALUES (3,1,'Jonas');
INSERT INTO tree_table VALUES (4,1,'Smith');
INSERT INTO tree_table VALUES (5,3,'Verma');
INSERT INTO tree_table VALUES (6,3,'Foster');
INSERT INTO tree_table VALUES (7,4,'Brown');
INSERT INTO tree_table VALUES (8,4,'Lin');
INSERT INTO tree_table VALUES (9,2,'Edwin');
INSERT INTO tree_table VALUES (10,9,'Audrey');
INSERT INTO tree_table VALUES (11,10,'Stone');
SELECT L1.ID, L1.ParentID, L1.name, 1 AS [Level]
FROM tree_table AS L1
WHERE L1.ParentID IS NULL
UNION ALL
SELECT L2.ID, L2.ParentID, L2.name, 2 AS [Level]
FROM tree_table AS L1
INNER JOIN tree_table AS L2 ON L1.ID=L2.ParentID
WHERE L1.ParentID IS NULL
UNION ALL
SELECT L3.ID, L3.ParentID, L3.name, 3 AS [Level]
FROM tree_table AS L1
INNER JOIN tree_table AS L2 ON L1.ID=L2.ParentID
INNER JOIN tree_table AS L3 ON L2.ID=L3.ParentID
WHERE L1.ParentID IS NULL
UNION ALL
SELECT L4.ID, L4.ParentID, L4.name, 4 AS [Level]
FROM tree_table AS L1
INNER JOIN tree_table AS L2 ON L1.ID=L2.ParentID
INNER JOIN tree_table AS L3 ON L2.ID=L3.ParentID
INNER JOIN tree_table AS L4 ON L3.ID=L4.ParentID
WHERE L1.ParentID IS NULL;
::
ID ParentID name Level
=====================================================
1 NULL 'Kim' 1
2 1 'Moy' 2
3 1 'Jonas' 2
4 1 'Smith' 2
9 2 'Edwin' 3
5 3 'Verma' 3
6 3 'Foster' 3
7 4 'Brown' 3
8 4 'Lin' 3
10 9 'Audrey' 4
Because you do not know how many levels exist in the data, you can rewrite the query above as a stored procedure that loops until no new row is retrieved.
However, the hierarchical structure should be checked every step while looping, specify the **CONNECT BY** clause within the **SELECT** statement as follows; the example below shows how to get the entire data with hierarchical structure and the level of each row in the hierarchy.
.. code-block:: sql
SELECT ID, ParentID, name, Level
FROM tree_table
START WITH ParentID IS NULL
CONNECT BY ParentID=PRIOR ID;
::
ID ParentID name level
=====================================================
1 NULL 'Kim' 1
2 1 'Moy' 2
9 2 'Edwin' 3
10 9 'Audrey' 4
11 10 'Stone' 5
3 1 'Jonas' 2
5 3 'Verma' 3
6 3 'Foster' 3
4 1 'Smith' 2
7 4 'Brown' 3
8 4 'Lin' 3
You can specify **NOCYCLE** to prevent an error from occurring as follows. When you run the following query, a loop does not appear; therefore, the result is the same as the above.
.. code-block:: sql
SELECT ID, ParentID, name, Level
FROM tree_table
START WITH ParentID IS NULL
CONNECT BY NOCYCLE ParentID=PRIOR ID;
CUBRID judges that this hierarchical query has a loop if the same row is found during searching process on the query. The below is an example that the loop exists; we define **NOCYCLE** to end the additional searching process if a loop exists.
.. code-block:: sql
CREATE TABLE tbl(seq INT, id VARCHAR(10), parent VARCHAR(10));
INSERT INTO tbl VALUES (1, 'a', null);
INSERT INTO tbl VALUES (2, 'b', 'a');
INSERT INTO tbl VALUES (3, 'b', 'c');
INSERT INTO tbl VALUES (4, 'c', 'b');
INSERT INTO tbl VALUES (5, 'c', 'b');
SELECT seq, id, parent, LEVEL,
CONNECT_BY_ISCYCLE AS iscycle,
CAST(SYS_CONNECT_BY_PATH(id,'/') AS VARCHAR(10)) AS idpath
FROM tbl
START WITH PARENT is NULL
CONNECT BY NOCYCLE PARENT = PRIOR id;
::
seq id parent level iscycle idpath
=============================================================================
1 'a' NULL 1 0 '/a'
2 'b' 'a' 2 0 '/a/b'
4 'c' 'b' 3 0 '/a/b/c'
3 'b' 'c' 4 1 '/a/b/c/b'
5 'c' 'b' 5 1 '/a/b/c/b/c'
5 'c' 'b' 3 0 '/a/b/c'
3 'b' 'c' 4 1 '/a/b/c/b'
4 'c' 'b' 5 1 '/a/b/c/b/c'
The below shows to output dates of March, 2013(201303) with a hierarchical query.
.. code-block:: sql
SELECT TO_CHAR(base_month + lvl -1, 'YYYYMMDD') h_date
FROM (
SELECT LEVEL lvl, base_month
FROM (
SELECT TO_DATE('201303', 'YYYYMM') base_month FROM db_root
)
CONNECT BY LEVEL <= LAST_DAY(base_month) - base_month + 1
);
::
h_date
======================
'20130301'
'20130302'
'20130303'
'20130304'
'20130305'
'20130306'
'20130307'
'20130308'
'20130309'
'20130310'
'20130311'
'20130312'
'20130313'
'20130314'
'20130315'
'20130316'
'20130317'
'20130318'
'20130319'
'20130320'
'20130321'
'20130322'
'20130323'
'20130324'
'20130325'
'20130326'
'20130327'
'20130328'
'20130329'
'20130330'
'20130331'
31 rows selected. (0.066175 sec) Committed.
Performance of Hierarchical Query
=================================
Hierarchical query with single-table
------------------------------------
A single-table hierarchical query can improve performance by adding an index to the columns corresponding to the child in the **CONNECT BY** clause. The following example shows how to improve performance by adding an index to the *mgrid* column.
.. code-block:: sql
-- Creating tree table and then inserting data
CREATE TABLE tree(ID INT, MgrID INT, Name VARCHAR(32), BirthYear INT);
INSERT INTO tree VALUES (1,NULL,'Kim', 1963);
INSERT INTO tree VALUES (2,NULL,'Moy', 1958);
INSERT INTO tree VALUES (3,1,'Jonas', 1976);
INSERT INTO tree VALUES (4,1,'Smith', 1974);
INSERT INTO tree VALUES (5,2,'Verma', 1973);
INSERT INTO tree VALUES (6,2,'Foster', 1972);
INSERT INTO tree VALUES (7,6,'Brown', 1981);
-- Copy data
INSERT INTO tree select * from tree;
INSERT INTO tree select * from tree;
INSERT INTO tree select * from tree;
INSERT INTO tree select * from tree;
INSERT INTO tree select * from tree;
-- Executing a single table hierarchical query
SELECT count(*)
FROM tree
CONNECT BY PRIOR id=mgrid
ORDER BY id;
::
Trace Statistics:
SELECT (time: 1686, fetch: 155971, fetch_time: 11, ioread: 0)
SCAN (table: dba.tree), (heap time: 1, fetch: 1, ioread: 0, readrows: 224, rows: 224)
CONNECTBY (time: 1685, fetch: 155970, fetch_time: 0, ioread: 0)
SCAN (table: dba.tree), (heap time: 1607, fetch: 38112, ioread: 0, readrows: 8537088, rows: 37888)
ORDERBY (time: 11, sort: true, page: 105, ioread: 0)
.. code-block:: sql
-- Create index
CREATE INDEX idx ON tree(mgrid);
-- Executing a single table hierarchical query with index
SELECT count(*)
FROM tree
CONNECT BY PRIOR id=mgrid
ORDER BY id;
::
Trace Statistics:
SELECT (time: 128, fetch: 195206, fetch_time: 9, ioread: 0)
SCAN (table: dba.tree), (heap time: 0, fetch: 1, ioread: 0, readrows: 224, rows: 224)
CONNECTBY (time: 128, fetch: 195203, fetch_time: 0, ioread: 0)
SCAN (index: dba.tree.idx), (btree time: 35, fetch: 77344, ioread: 0, readkeys: 1120, filteredkeys: 0, rows: 37888) (lookup time: 10, rows: 37888)
ORDERBY (time: 11, sort: true, page: 105, ioread: 0)
Hierarchical query with join-table
----------------------------------
A hierarchical query for table join is optimized using a hash list scan when applying the condition in the **CONNECT BY** clause after performing the table join operation. The following example shows the difference in performance between a hash list scan and a list scan.
.. code-block:: sql
-- Creating tree2 table and then inserting data
CREATE TABLE tree2(id int, treeid int, job varchar(32));
INSERT INTO tree2 VALUES(1,1,'Partner');
INSERT INTO tree2 VALUES(2,2,'Partner');
INSERT INTO tree2 VALUES(3,3,'Developer');
INSERT INTO tree2 VALUES(4,4,'Developer');
INSERT INTO tree2 VALUES(5,5,'Sales Exec.');
INSERT INTO tree2 VALUES(6,6,'Sales Exec.');
INSERT INTO tree2 VALUES(7,7,'Assistant');
INSERT INTO tree2 VALUES(8,null,'Secretary');
-- Copy data
INSERT INTO tree2 select * from tree2;
-- Executing a hierarchical query onto table joins
SELECT count(*)
FROM tree t INNER JOIN tree2 t2 ON t.id=t2.treeid
START WITH t.mgrid is null
CONNECT BY prior t.id=t.mgrid
ORDER BY t.id;
::
Trace Statistics:
SELECT (time: 663, fetch: 877290, fetch_time: 54, ioread: 0)
SCAN (table: dba.tree), (heap time: 0, fetch: 225, ioread: 0, readrows: 224, rows: 224)
SCAN (table: dba.tree2), (heap time: 0, fetch: 224, ioread: 0, readrows: 3584, rows: 448)
CONNECTBY (time: 662, fetch: 876841, fetch_time: 0, ioread: 0)
SCAN (hash temp(m), build time: 0, time: 53, fetch: 0, ioread: 0, readrows: 278976, rows: 278528)
ORDERBY (time: 92, sort: true, page: 1560, ioread: 0)
.. code-block:: sql
-- Executing a hierarchical query onto table joins without hash list scan
SELECT /*+ NO_HASH_LIST_SCAN */ count(*)
FROM tree t INNER JOIN tree2 t2 ON t.id=t2.treeid
START WITH t.mgrid is null
CONNECT BY prior t.id=t.mgrid
ORDER BY t.id;
::
Trace Statistics:
SELECT (time: 8977, fetch: 877335, fetch_time: 57, ioread: 0)
SCAN (table: dba.tree), (heap time: 0, fetch: 225, ioread: 0, readrows: 224, rows: 224)
SCAN (table: dba.tree2), (heap time: 0, fetch: 224, ioread: 0, readrows: 3584, rows: 448)
CONNECTBY (time: 8977, fetch: 876886, fetch_time: 0, ioread: 0)
SCAN (temp time: 8353, fetch: 0, ioread: 0, readrows: 124837888, rows: 278528)
ORDERBY (time: 91, sort: true, page: 1560, ioread: 0)
.. note::
**HASH LIST SCAN** optimization for a hierarchical query with join table is supported starting from CUBRID 11.0."I can't think of it," replied Frank; "what is it?" Judy Abbott Still the tonga; uphill and down, over the hilly country, with a horizon of dull, low mountains, and the horses worse and worse, impossible to start but by a storm of blows. Towards evening a particularly vicious pair ended by overturning us into a ditch full of liquid mud. The sais alone was completely immersed, and appealed loudly to Rama with shrieks of terror. Abibulla on his part, after making sure that the sahibs and baggage were all safe and sound, took off his shoes, spread his dhoti on the ground, and made the introductory salaams of thanksgiving to the Prophet, while the coolie driver returned thanks to Rama. "Did the girl know her own story?" she asked. "Nonsense—you're coming wud me." "I'm afraid father forgets things. But come in, he's bound to be home to his dinner soon." HoME香港一级高清仑片
ENTER NUMBET 0018go-deer.net.cn
www.hongxingsumo.com.cn
szgdst.com.cn
online1718.com.cn
cctg.org.cn
www.bfnl931.com.cn
www.suiyiba.com.cn
www.bmpp373.com.cn
www.ddyhq.com.cn
www.hbcme.net.cn