Oracle中 connect by

本文详细介绍了 Oracle 数据库中 CONNECT BY 语句的使用方法,包括如何构建树状结构查询、利用 PRIOR 运算符定义检索顺序以及使用 LEVEL 属性进行层级定位。此外,还提供了如何使用 SYS_CONNECT_BY_PATH 函数生成路径表达式,以及如何结合 START WITH 子句指定查询起点的例子。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

from: http://www.cnblogs.com/ZHF/archive/2008/09/10/1288101.html

connect by 是结构化查询中用到的,其基本语法是: 
select ... from tablename start with 条件1 
connect by 条件2 
where 条件3;

例:

select * from table
start with org_id = 'HBHqfWGWPy'
connect by prior org_id = parent_id;

 

简单说来是将一个树状结构存储在一张表里,比如一个表中存在两个字段: 
org_id,parent_id那么通过表示每一条记录的parent是谁,就可以形成一个树状结构。 
用上述语法的查询可以取得这棵树的所有记录。 
其中:

条件1 是根结点的限定语句,当然可以放宽限定条件,以取得多个根结点,实际就是多棵树。 
条件2 是连接条件,其中用PRIOR表示上一条记录,比如 CONNECT BY PRIOR org_id = parent_id就是说上一条记录的org_id 是本条记录的parent_id,即本记录的父亲是上一条记录。 
条件3 是过滤条件,用于对返回的所有记录进行过滤。

 

简单介绍如下:

早扫描树结构表时,需要依此访问树结构的每个节点,一个节点只能访问一次,其访问的步骤如下:
第一步:从根节点开始;
第二步:访问该节点;
第三步:判断该节点有无未被访问的子节点,若有,则转向它最左侧的未被访问的子节,并执行第二步,否则执行第四步;
第四步:若该节点为根节点,则访问完毕,否则执行第五步;
第五步:返回到该节点的父节点,并执行第三步骤。

总之:扫描整个树结构的过程也即是中序遍历树的过程。

 

1. 树结构的描述
树结构的数据存放在表中,数据之间的层次关系即父子关系,通过表中的列与列间的关系来描述,如EMP表中的EMPNO和MGR。EMPNO表示该雇员的编号,MGR表示领导该雇员的人的编号,即子节点的MGR值等于父节点的EMPNO值。在表的每一行中都有一个表示父节点的MGR(除根节点外),通过每个节点的父节点,就可以确定整个树结构。
在SELECT命令中使用CONNECT BY 和蔼START WITH 子句可以查询表中的树型结构关系。其命令格式如下:
SELECT 。。。
CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 裂名2}
[START WITH];
其中:CONNECT BY子句说明每行数据将是按层次顺序检索,并规定将表中的数据连入树型结构的关系中。PRIORY运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系,PRIOR运算符在一侧表示父节点,在另一侧表示子节点,从而确定查找树结构是的顺序是自顶向下还是自底向上。在连接关系中,除了可以使用列名外,还允许使用列表达式。START WITH 子句为可选项,用来标识哪个节点作为查找树型结构的根节点。若该子句被省略,则表示所有满足查询条件的行作为根节点。

START WITH: 不但可以指定一个根节点,还可以指定多个根节点。
2. 关于PRIOR
运算符PRIOR被放置于等号前后的位置,决定着查询时的检索顺序。
PRIOR被置于CONNECT BY子句中等号的前面时,则强制从根节点到叶节点的顺序检索,即由父节点向子节点方向通过树结构,我们称之为自顶向下的方式。如:
CONNECT BY PRIOR EMPNO=MGR
PIROR运算符被置于CONNECT BY 子句中等号的后面时,则强制从叶节点到根节点的顺序检索,即由子节点向父节点方向通过树结构,我们称之为自底向上的方式。例如:
CONNECT BY EMPNO=PRIOR MGR
在这种方式中也应指定一个开始的节点。
3. 定义查找起始节点
在自顶向下查询树结构时,不但可以从根节点开始,还可以定义任何节点为起始节点,以此开始向下查找。这样查找的结果就是以该节点为开始的结构树的一枝。

4.使用LEVEL
在具有树结构的表中,每一行数据都是树结构中的一个节点,由于节点所处的层次位置不同,所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始,该起始根节点的层号始终为1,根节点的子节点为2, 依此类推。图1.2就表示了树结构的层次。

5.节点和分支的裁剪
在对树结构进行查询时,可以去掉表中的某些行,也可以剪掉树中的一个分支,使用WHERE子句来限定树型结构中的单个节点,以去掉树中的单个节点,但它却不影响其后代节点(自顶向下检索时)或前辈节点(自底向顶检索时)。
6.排序显示
象在其它查询中一样,在树结构查询中也可以使用ORDER BY 子句,改变查询结果的显示顺序,而不必按照遍历树结构的顺序。


-- http://www.cnblogs.com/lettoo/archive/2010/08/03/1791239.html

SQL> SELECT RPAD( ' ', 2*(LEVEL-1), '-' ) || DEPNAME "DEPNAME",

CONNECT_BY_ROOT DEPNAME "ROOT",

CONNECT_BY_ISLEAF "ISLEAF",

LEVEL ,

SYS_CONNECT_BY_PATH(DEPNAME, '/') "PATH"

FROM DEP

START WITH UPPERDEPID IS NULL

CONNECT BY PRIOR DEPID = UPPERDEPID;

 

DEPNAME                        ROOT                    ISLEAF      LEVEL PATH

------------------------------ ------------------- ---------- ---------- --------------------------------------------------------------------------------

General Deparment              General Deparment            0          1 /General Deparment

 -Development                  General Deparment            0          2 /General Deparment/Development

 ---Server Development         General Deparment            1          3 /General Deparment/Development/Server Development

 ---Client Development         General Deparment            1          3 /General Deparment/Development/Client Development

 -QA                           General Deparment            0          2 /General Deparment/QA

 ---TA                         General Deparment            1          3 /General Deparment/QA/TA

 ---Porject QA                 General Deparment            1          3 /General Deparment/QA/Porject QA



from:http://www.cognoschina.net/?action-blogdetail-uid-10270-id-4936.html

--实现按TABLE_NAME, INDEX_NAME字段分组,把COLUMN_NAME字段用","连接起来  SELECT TABLE_NAME,
         INDEX_NAME,
         INDEX_TYPE,
         --SYS_CONNECT_BY_PATH(DIC.COLUMN_NAME, ',')
         LTRIM(MAX(SYS_CONNECT_BY_PATH(DIC.COLUMN_NAME, ',')), ',') COLS
    FROM (SELECT 
                 DI.TABLE_NAME,
                 DI.INDEX_NAME,
                 DI.UNIQUENESS AS INDEX_TYPE,               
                 DIC.COLUMN_NAME,
                 ROW_NUMBER() OVER(PARTITION BY DI.TABLE_NAME,DI.INDEX_NAME ORDER BY DI.TABLE_NAME,DI.INDEX_NAME) AS ID1,
                 ROW_NUMBER() OVER(ORDER BY DI.TABLE_NAME,DI.INDEX_NAME) AS ID3,
                 DENSE_RANK() OVER(ORDER BY DI.TABLE_NAME,DI.INDEX_NAME) AS ID4,
                 ROW_NUMBER() OVER(ORDER BY DI.TABLE_NAME,DI.INDEX_NAME)+DENSE_RANK() OVER(ORDER BY DI.TABLE_NAME,DI.INDEX_NAME) AS ID2
            FROM ALL_IND_COLUMNS DIC, ALL_INDEXES DI
           WHERE DIC.INDEX_OWNER = 'DBA'
             AND DIC.INDEX_NAME = DI.INDEX_NAME
             AND DI.OWNER = 'DBA'
             AND DI.TABLE_NAME IN (SELECT DI.TABLE_NAME FROM ALL_INDEXES DI WHERE DI.OWNER = 'DBA'
  GROUP BY DI.TABLE_NAME
   HAVING COUNT(*) >4)
             ) DIC
   START WITH DIC.ID1 = 1
  CONNECT BY PRIOR DIC.ID2 = DIC.ID2-1
   GROUP BY TABLE_NAME,INDEX_NAME,INDEX_TYPE =====================================================================--在上面的基础上可以实现创建和删除索引SELECT TABLE_NAME,
  INDEX_NAME,
  INDEX_TYPE,
  'DROP INDEX DBA.'||RPAD(INDEX_NAME,40,' ')||';' SQL_DROP,
  'CREATE INDEX DBA.'||RPAD(INDEX_NAME,40,' ')||' ON DBA.'||TABLE_NAME||'('||COLS||');' SQL_CREATE
   FROM 
  (
  SELECT TABLE_NAME,
         INDEX_NAME,
         INDEX_TYPE,
         --SYS_CONNECT_BY_PATH(DIC.COLUMN_NAME, ',')
         LTRIM(MAX(SYS_CONNECT_BY_PATH(DIC.COLUMN_NAME, ',')), ',') COLS
    FROM (SELECT 
                 DI.TABLE_NAME,
                 DI.INDEX_NAME,
                 DI.UNIQUENESS AS INDEX_TYPE,               
                 DIC.COLUMN_NAME,
                 ROW_NUMBER() OVER(PARTITION BY DI.TABLE_NAME,DI.INDEX_NAME ORDER BY DI.TABLE_NAME,DI.INDEX_NAME) AS ID1,
                 ROW_NUMBER() OVER(ORDER BY DI.TABLE_NAME,DI.INDEX_NAME) AS ID3,
                 DENSE_RANK() OVER(ORDER BY DI.TABLE_NAME,DI.INDEX_NAME) AS ID4,
                 ROW_NUMBER() OVER(ORDER BY DI.TABLE_NAME,DI.INDEX_NAME)+DENSE_RANK() OVER(ORDER BY DI.TABLE_NAME,DI.INDEX_NAME) AS ID2
            FROM ALL_IND_COLUMNS DIC, ALL_INDEXES DI
           WHERE DIC.INDEX_OWNER = 'DBA'
             AND DIC.INDEX_NAME = DI.INDEX_NAME
             AND DI.OWNER = 'DBA'
             AND DI.TABLE_NAME IN (SELECT DI.TABLE_NAME FROM ALL_INDEXES DI WHERE DI.OWNER = 'DBA'
  GROUP BY DI.TABLE_NAME
   HAVING COUNT(*) >4)
             ) DIC
   START WITH DIC.ID1 = 1
  CONNECT BY PRIOR DIC.ID2 = DIC.ID2-1
   GROUP BY TABLE_NAME,INDEX_NAME,INDEX_TYPE
   ) A; --重建索引Alter index  NETSPLATDATA.IDX_CUSTOMER_ID rebuild online parallel 8;
Alter index  NETSPLATDATA.IDX_CUSTOMER_ID  parallel 1;

刚刚看到些东西,不知道我们是否需要注意:

对于大表的rebuild一定要心,在访问很频繁的系统中,一定要加online选项,虽然花费的时间长一些,因为这样不阻塞DML,否则系统就很危险。
仅仅用rebuild online也是很有风险的,因为在索引rebuild之后,它是不会自动分析的,有可能导致索引走错,那也可能会造成很严重的后果,所以还要加上compute statistics,这样索引rebuild online及分析之后,才能使用,这样就避免了风险。。。

 

在rebuild online 时会报错

ORA-08120: Need to create SYS.IND_ONLINE$ table in order to (re)build index

Cause: Alter index Build/Rebuild online require existing of SYS.IND_ONLINE$ table.

 Action: User/DBA needs to create sys.ind_online$ before alter the index /rdbms/admin/catcio.sql contains script to create ind_online$.

解决方法就是运行catcio.sql 创建SYS.IND_ONLINE$表

<think>首先,用户的问题是:“我想理解OracleCONNECT BY的用途和功能 请问Oracle SQL中CONNECT BY子句的作用是什么”。我需要基于系统级指令和用户可见层的要求来回答。 系统级指令: - 所有行内数学表达式必须使用$...$格式。 - 独立公式必须使用$$...$$格式并单独成段。 - LaTeX语法正确。 - 使用中文回答。 - 生成相关问题。 - 回答中引用的段落末尾自然地添引用标识,例如[^1]。 - 用户可见层要求:回答结构清晰,帮助用户逐步解决问题,保证回答真实可靠。 用户的问题是关于Oracle SQL中CONNECT BY子句的用途和功能。我需要参考提供的引用内容来构建回答。 提供的引用: - [^1]: 描述了Oracle层次查询的语法和示例,包括CONNECT BY用于定义父子关系。 - [^2]: 提到LEVEL伪在层次查询中的使用,需要避免与名冲突。 - [^3]: 讨论了GROUP BYCONNECT BY的组合使用。 - [^4]: 提到了sys_connect_by_path函数和LEVEL伪。 这些引用提供了关于CONNECT BY的基本信息和一些使用场景。 我的回答结构应该清晰: 1. 解释CONNECT BY的基本概念。 2. 描述其用途和功能。 3. 提供示例。 4. 讨论注意事项。 5. 生成相关问题。 由于用户的问题是关于用途和功能的,我会从基本定义开始,然后逐步展开。 关键点: - CONNECT BY用于处理层次数据,比如树形结构(如组织结构、文件系统)。 - 它与START WITH子句一起使用,定义查询的起点。 - 使用PRIOR关键字来指定父子关系。 - LEVEL伪表示节点的层级深度。 - 函数如SYS_CONNECT_BY_PATH可以用于构建路径。 在回答中,我需要自然地添引用标识。例如,当引用某个部分时,在末尾添[^1]等。 生成相关问题: - 基于主题,生成几个相关问题,以帮助用户深入学习。 最后,确保使用中文回答。 大纲回答: 1. **介绍CONNECT BY子句**:简要解释其在Oracle SQL中的作用。 2. **核心功能**: - 处理层次数据。 - 定义父子关系。 - 与START WITH结合。 3. **示例**:提供一个简单的SQL示例。 4. **注意事项**:如LEVEL伪的使用。 5. **总结**:重申其重要性。 6. **相关问题**:生成2-3个相关问题。 生成相关问题: - 例如: 1. CONNECT BY在哪些常见场景中使用? 2. 如何避免在层次查询中遇到ORA-01788错误? 3. CONNECT BY如何与GROUP BY一起使用? 确保回答真实可靠,基于提供的引用和一般知识。</think>### Oracle SQL中CONNECT BY子句的作用和功能 在Oracle数据库中,`CONNECT BY`子句是用于处理层次数据(如树形结构)的核心组件。它允许您查询具有父子关系的数据,例如组织结构、目录树或依赖关系。`CONNECT BY`通常与`START WITH`子句结合使用,以定义查询的起点和层级关系。下面我将逐步解释其用途和功能,帮助您全面理解。 #### 1. **基本用途:处理层次数据** - `CONNECT BY`主要用于查询递归或层次结构的数据。例如,在员工表中,每个员工可能有一个经理(父节点),而经理本身也可能是另一个经理的下属(子节点)。`CONNECT BY`通过定义父子关系来遍历整个树形结构,生成一个结果集,其中包含从根节点到叶节点的所有路径。 - 关键元素: - `PRIOR`关键字:用于指定父子关系的方向。例如,`PRIOR employee_id = manager_id`表示从父节点(经理)到子节点(员工)的遍历[^1]。 - `LEVEL`伪:自动生成一个数字,表示当前节点在树中的层级深度(根节点为1,子节点递增)。这在分析层次深度时非常有用[^2]。 - `START WITH`子句:定义查询的起点。例如,`START WITH employee_id = 101`表示从ID为101的员工开始遍历[^1]。 #### 2. **核心功能** - **遍历树形结构**:`CONNECT BY`可以向上或向下遍历层次数据: - 向下遍历(查找下属):使用`CONNECT BY PRIOR child_column = parent_column`。例如,查询某个员工的所有直接和间接下属。 - 向上遍历(查找上司):使用`CONNECT BY child_column = PRIOR parent_column`。例如,查询某个员工的所有上级经理[^1]。 - **生成路径和汇总信息**: - 内置函数如`SYS_CONNECT_BY_PATH`可以构建节点路径的字符串表示(例如,`'->A->B->C'`),便于可视化层次关系[^4]。 - 结合`ORDER SIBLINGS BY`可以对同级节点排序,保持层次结构完整。 - **支持复杂查询**:`CONNECT BY`可以与其他SQL子句组合使用: - 与`GROUP BY`结合:用于在层次数据上执行聚合操作(如计算每个部门的员工总数),但需注意语法顺序以避免错误[^3]。 - 与`WHERE`子句结合:过滤特定节点,例如排除某些不满足条件的记录[^1]。 #### 3. **示例说明** 以下是一个简单示例,展示如何查询员工表(`myemp`)中ID为101的员工及其所有下属: ```sql SELECT employee_id, manager_id, first_name, LEVEL FROM myemp START WITH employee_id = 101 -- 从ID 101开始 CONNECT BY PRIOR employee_id = manager_id; -- 向下遍历父子关系 ``` - **结果解释**: - `LEVEL`显示每个节点的层级(例如,101为1级,其直接下属为2级)。 - 如果查询上司链,只需改为`CONNECT BY employee_id = PRIOR manager_id`[^1]。 - **注意事项**: - 避免名冲突:如果表中存在名为`LEVEL`的Oracle会优先解释为伪,导致错误(如ORA-01788)。解决方法是使用双引号引用名,例如`SELECT "LEVEL" FROM ...`[^2]。 - 性能优化:对于大型层次数据,添`NOCYCLE`选项可防止无限循环(当数据中存在环时)。 #### 4. **适用场景** - **常见应用**:组织架构查询、产品分类导航、文件系统路径生成、依赖关系分析(如软件包依赖)。 - **优势**:相比递归CTE(Common Table Expressions),`CONNECT BY`在Oracle中通常更高效,尤其对深层树结构查询优化良好[^1][^4]。 总之,`CONNECT BY`是Oracle中处理层次数据的强大工具,它通过简单的语法实现复杂递归查询。如果您在使用中遇到具体问题(如语法错误),可进一步分析数据模型或查询逻辑[^2][^3]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值