本文共 4140 字，大约阅读时间需要 13 分钟。

Oracle start with connect by 语句详解

1. 简介

在 Oracle 中，start with connect by (prior) 语句主要用于对具有树形结构的数据进行高效查询。该语句通过定义数据的搜索范围（start with）和递归查询条件（connect by），帮助用户快速定位所需数据。prior 关键字用于指定递归查询的方向，例如，当 prior 后面跟字段名时，它将用于查找子节点；当 prior 后面跟表达式时，则用于查找父节点。

例如，以下 SQL 语句可以查询 ID 为 '10001' 的记录，并递归查找其父节点和子节点：

start with id = '10001' connect by prior parent_id = id and prior num = 5

2. 实例

2.1 数据构造

为了更好地理解 start with connect by 的应用，我们首先需要创建一个具有树形结构的菜单数据表。以下是创建表格和初始化数据的 SQL 代码：

create table menu (
    id varchar2(64) not null,
    parent_id varchar2(64) not null,
    name varchar2(100) not null,
    depth number(2) not null,
    primary key (id)
);
insert into menu values ('100000', '0', '顶级菜单1', 1);
insert into menu values ('200000', '0', '顶级菜单2', 1);
insert into menu values ('300000', '0', '顶级菜单3', 1);
insert into menu values ('110000', '100000', '菜单11', 2);
insert into menu values ('120000', '100000', '菜单12', 2);
insert into menu values ('130000', '100000', '菜单13', 2);
insert into menu values ('140000', '100000', '菜单14', 2);
insert into menu values ('210000', '200000', '菜单21', 2);
insert into menu values ('220000', '200000', '菜单22', 2);
insert into menu values ('230000', '200000', '菜单23', 2);
insert into menu values ('310000', '300000', '菜单31', 2);
insert into menu values ('131000', '130000', '菜单131', 3);
insert into menu values ('132000', '130000', '菜单132', 3);
insert into menu values ('133000', '130000', '菜单133', 3);
insert into menu values ('132100', '132000', '菜单1321', 4);
insert into menu values ('132200', '132000', '菜单1332', 4);

2.2 SQL 查询

2.2.1 查找父节点

以下 SQL 语句可以查找指定 ID 的父节点：

select * from menu start with id='130000' connect by id = prior.parent_id

2.2.2 查找子节点

以下 SQL 语句可以查找指定 ID 的所有子节点：

select * from menu start with id='130000' connect by prior.id = parent_id

2.2.3 查找叶子节点

以下 SQL 语句可以查询所有叶子节点（即没有子节点的节点）：

select t2.* from menu t2 where t2.id not in (
    select t.parent_id from menu t
) order by t2.id

2.2.4 统计层级关系

以下 SQL 语句可以统计每个菜单包含的子菜单数量：

select id, max(name) name, count(1) from menu group by id connect by prior.parent_id = id order by id

3. 性能优化

3.1 生成执行计划

为了优化 start with connect by 语句的性能，可以通过生成执行计划来分析查询的执行步骤。以下是生成执行计划的 SQL 代码：

explain plan for select * from menu start with id='132100' connect by prior.parent_id = id;

执行计划示例如下：

Plan hash value: 3563250490
----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
|----|------------------------------|-------------|------|------|----------|---------|
| 0  | SELECT STATEMENT             |             | 1    | 133  |          | 00:00:01 |
|    | *  1                          | CONNECT BY  |      |      |          |         |
|    | 2  | TABLE ACCESS BY INDEX ROWID  | MENU       | 1    | 133   |          | 00:00:01 |
|    | 3  | INDEX UNIQUE SCAN          | SYS_C0018586 | 1    |       |          | 00:00:01 |
|    | 4  | CONNECT BY PUMP           |            |      |      |          |         |
|    | 5  | TABLE ACCESS BY INDEX ROWID | MENU       | 1    | 133   |          | 00:00:01 |
|    | 6  | INDEX UNIQUE SCAN         | SYS_C0018586 | 1    |       |          | 00:00:01 |
|    | 7  | CONNECT BY PUMP           |            |      |      |          |         |
|    | 8  | TABLE ACCESS BY INDEX ROWID | MENU       | 1    | 133   |          | 00:00:01 |
|    | 9  | INDEX UNIQUE SCAN         | SYS_C0018586 | 1    |       |          | 00:00:01 |
|    | ...                            |             |      |      |          |         |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=PRIOR "PARENT_ID")
3 - access("ID"='132100')
7 - access("ID"=PRIOR "PARENT_ID")

3.2 优化建议

通过分析执行计划，可以看出 start with connect by 语句在数据量较大时可能导致性能问题。为了优化，可以考虑以下方法：

通过以上优化，可以显著提升 start with connect by 语句的执行效率，确保在大数据量下依然能够快速响应。

转载地址：http://lepfk.baihongyu.com/