层次查询

层次查询（Hierarchical Query）是一种具有特殊功能的查询语句，通过它能够将分层数据按照层次关系展示出来。

分层数据是指关系表中的数据之间具有层次关系。这种关系在现实生活中十分常见，示例如下：

• 组织架构中组长和组员之间的关系

• 企业中上下级部门之间的关系

• Web 网页中页面跳转的关系

使用限制及注意事项

如果层次查询中包含了 FOR UPDATE 子句，那么不支持以下的使用场景：

• 如果子查询使用了 DISTINCT 关键字或者聚合，这种情况下将不能和 FOR UPDATE 一起使用。
• 任何包含公共表达式（Common Table Expressions，CTE）的情景都不支持，即带有 WITH ... AS ... 子句的 SELECT 查询不能与 FOR UPDATE 同时使用。更多 WITH ... AS ... 子句的信息，请参见 WITH CLAUSE。
• 如果层次查询中包含 CONNECT BY，数据量大的场景下，可以通过 USE_HASH Hint 或 USE_NL Hint 调优选择最佳执行计划。更多关于 USE_HASH 和 USE_NL 的信息，请参见 与联接操作相关的 Hint。

为了保证数据库的正常操作及性能优化，请避免在上述提及的前两种场景中使用 FOR UPDATE 子句。

语法

简单层次查询的 SELECT 语法如下：

SELECT [LEVEL], column, expr... FROM table [WHERE condition] 
[ START WITH start_expression ] CONNECT BY [NOCYCLE] { PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr } [ ORDER SIBLINGS BY ...]
[ GROUP BY ... ] [ HAVING ... ] [ ORDER BY ... ]
[FOR UPDATE [OF column] [ {NOWAIT | WAIT integer | SKIP LOCKED } ] ]

有关 SELECT 语法的详细信息，请参见 SIMPLE SELECT。

参数解释

执行流程

使用和实现层次查询最关键是要理解其执行流程，一般的层次查询执行流程如下：

1. 执行 FROM 后面的 SCAN 或 JOIN 操作。

2. 根据 START WITH 和 CONNECT BY 的内容生成层次关系结果。

   对于步骤 2 中生成层次关系的流程可以理解为：

   1. 根据 START WITH 中的表达式得到根行（Root Rows）。

   2. 根据 CONNECT BY 中的表达式选择每个根行（Root Rows）的子行（Child Rows）。

3. 将步骤 2 中生成的子行（Child Rows）作为新的根行（Root Rows）进一步生成子行（Child Rows），循环执行直到没有新行生成。

4. 按照常规查询执行流程执行剩下的子句（例如 WHERE、GROUP、ORDER BY......）。

示例

1. 创建表 emp，并向表中的 emp_id、position 和 mgr_id 列插入数据。

   CREATE TABLE emp(emp_id INT,position VARCHAR(50),mgr_id INT);
   INSERT INTO emp VALUES (1,'全球经理',NULL);
   INSERT INTO emp VALUES (2,'欧洲区经理',1);
   INSERT INTO emp VALUES (3,'亚太区经理',1);
   INSERT INTO emp VALUES (4,'美洲区经理',1);
   INSERT INTO emp VALUES (5,'意大利区经理',2);
   INSERT INTO emp VALUES (6,'法国区经理',2);
   INSERT INTO emp VALUES (7,'中国区经理',3);
   INSERT INTO emp VALUES (8,'韩国区经理',3);
   INSERT INTO emp VALUES (9,'日本区经理',3);
   INSERT INTO emp VALUES (10,'美国区经理',4);
   INSERT INTO emp VALUES (11,'加拿大区经理',4);
   INSERT INTO emp VALUES (12,'北京区经理',7);
   

   以上示例表中的列 position 具有清晰的层次关系。树状结构如下：

   

2. 执行以下语句将结果按照层次结构展示出来。

   obclient [SYS]> SELECT emp_id, mgr_id, position, LEVEL 
   FROM emp
   START WITH mgr_id IS NULL
       CONNECT BY PRIOR emp_id = mgr_id;
   

   返回结果如下：

   +--------+--------+--------------------+-------+
   | EMP_ID | MGR_ID | POSITION           | LEVEL |
   +--------+--------+--------------------+-------+
   |      1 |   NULL | 全球经理            |     1 |
   |      2 |      1 | 欧洲区经理          |     2 |
   |      5 |      2 | 意大利区经理        |     3 |
   |      6 |      2 | 法国区经理          |     3 |
   |      3 |      1 | 亚太区经理          |     2 |
   |      7 |      3 | 中国区经理          |     3 |
   |     12 |      7 | 北京区经理          |     4 |
   |      8 |      3 | 韩国区经理          |     3 |
   |      9 |      3 | 日本区经理          |     3 |
   |      4 |      1 | 美洲区经理          |     2 |
   |     10 |      4 | 美国区经理          |     3 |
   |     11 |      4 | 加拿大区经理        |     3 |
   +--------+--------+--------------------+-------+
   12 rows in set
   

3. 仅查询 "亚太区" 的层次结构，并且使用 FOR UPDATE 子句锁定查询结果。

   SELECT emp_id, mgr_id, position, LEVEL 
   FROM emp
   START WITH position = '亚太区经理'
       CONNECT BY PRIOR emp_id = mgr_id
   FOR UPDATE;
   

   返回结果如下：

   +--------+--------+-----------------+-------+
   | EMP_ID | MGR_ID | POSITION        | LEVEL |
   +--------+--------+-----------------+-------+
   |      3 |      1 | 亚太区经理       |     1 |
   |      7 |      3 | 中国区经理       |     2 |
   |     12 |      7 | 北京区经理       |     3 |
   |      8 |      3 | 韩国区经理       |     2 |
   |      9 |      3 | 日本区经理       |     2 |
   +--------+--------+-----------------+-------+
   5 rows in set
   

4. 使用 CONNECT BY 子句进行递归查询，并使用 USE_HASH Hint 指示优化器使用 HASH-JOIN 算法。

   obclient> SELECT /*+ USE_HASH(emp) */ EMP_ID, POSITION, MGR_ID  FROM emp  START WITH EMP_ID = 1  CONNECT BY PRIOR EMP_ID = MGR_ID;
   

   返回结果如下：

   +--------+--------------------+--------+
   | EMP_ID | POSITION           | MGR_ID |
   +--------+--------------------+--------+
   |      1 | 全球经理           |   NULL |
   |      2 | 欧洲区经理         |      1 |
   |      5 | 意大利区经理       |      2 |
   |      6 | 法国区经理         |      2 |
   |      3 | 亚太区经理         |      1 |
   |      7 | 中国区经理         |      3 |
   |     12 | 北京区经理         |      7 |
   |      8 | 韩国区经理         |      3 |
   |      9 | 日本区经理         |      3 |
   |      4 | 美洲区经理         |      1 |
   |     10 | 美国区经理         |      4 |
   |     11 | 加拿大区经理       |      4 |
   +--------+--------------------+--------+
   12 rows in set (0.004 sec)
   

   执行计划如下：

   obclient> EXPLAIN SELECT /*+ USE_HASH(emp) */ EMP_ID, POSITION, MGR_ID  FROM emp  START WITH EMP_ID = 1  CONNECT BY PRIOR EMP_ID = MGR_ID;
   +--------------------------------------------------------------------------------------------------------------------------------+
   | Query Plan                                                                                                                     |
   +--------------------------------------------------------------------------------------------------------------------------------+
   | ====================================================                                                                           |
   | |ID|OPERATOR           |NAME |EST.ROWS|EST.TIME(us)|                                                                           |
   | ----------------------------------------------------                                                                           |
   | |0 |HASH CONNECT BY    |     |3       |11          |                                                                           |
   | |1 |├─SUBPLAN SCAN     |VIEW1|1       |5           |                                                                           |
   | |2 |│ └─TABLE FULL SCAN|EMP  |1       |5           |                                                                           |
   | |3 |└─SUBPLAN SCAN     |VIEW2|12      |5           |                                                                           |
   | |4 |  └─TABLE FULL SCAN|EMP  |12      |5           |                                                                           |
   | ====================================================                                                                           |
   | Outputs & filters:                                                                                                             |
   | -------------------------------------                                                                                          |
   |   0 - output([VIEW2.EMP.EMP_ID], [VIEW2.EMP.POSITION], [VIEW2.EMP.MGR_ID]), filter(nil)                                        |
   |       equal_conds([VIEW1.EMP.EMP_ID = VIEW2.EMP.MGR_ID]), other_conds(nil)                                                     |
   |   1 - output([VIEW1.EMP.__pk_increment], [VIEW1.EMP.EMP_ID], [VIEW1.EMP.MGR_ID], [VIEW1.EMP.POSITION]), filter(nil), rowset=16 |
   |       access([VIEW1.EMP.__pk_increment], [VIEW1.EMP.EMP_ID], [VIEW1.EMP.MGR_ID], [VIEW1.EMP.POSITION])                         |
   |   2 - output([EMP.__pk_increment], [EMP.EMP_ID], [EMP.MGR_ID], [EMP.POSITION]), filter([EMP.EMP_ID = 1]), rowset=16            |
   |       access([EMP.__pk_increment], [EMP.EMP_ID], [EMP.MGR_ID], [EMP.POSITION]), partitions(p0)                                 |
   |       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                              |
   |       range_key([EMP.__pk_increment]), range(MIN ; MAX)always true                                                             |
   |   3 - output([VIEW2.EMP.__pk_increment], [VIEW2.EMP.EMP_ID], [VIEW2.EMP.MGR_ID], [VIEW2.EMP.POSITION]), filter(nil), rowset=16 |
   |       access([VIEW2.EMP.__pk_increment], [VIEW2.EMP.EMP_ID], [VIEW2.EMP.MGR_ID], [VIEW2.EMP.POSITION])                         |
   |   4 - output([EMP.__pk_increment], [EMP.EMP_ID], [EMP.MGR_ID], [EMP.POSITION]), filter(nil), rowset=16                         |
   |       access([EMP.__pk_increment], [EMP.EMP_ID], [EMP.MGR_ID], [EMP.POSITION]), partitions(p0)                                 |
   |       is_index_back=false, is_global_index=false,                                                                              |
   |       range_key([EMP.__pk_increment]), range(MIN ; MAX)always true                                                             |
   +--------------------------------------------------------------------------------------------------------------------------------+
   25 rows in set (0.004 sec)
   

相关文档

• SIMPLE SELECT
• WITH CLAUSE
• 锁定查询结果 SELECT FOR UPDATE