oracle中的树状查询
工作中经常会遇到将数据库中的数据以树的形式展现的需求。以下我们来看一下该需求在Oracle中如何实现。
首先我们需要有一个树形的表结构（当然有时候会出现表结构不是典型的树形结构，而是多表存储，需要根据多表连接查询生成树）
一、树型表结构：
节点ID 上级ID 节点名称
二、用法：
select 节点ID,节点名称,level
from 表名
connect by prior 节点ID=上级节点ID
start with 上级节点ID=节点值
说明：
1、常见的树形结构为公司组织机构、地区……
2、求节点ID以上的结构，或以下的结构，将“节点ID=上级节点ID”左右顺序换一下即可。
3、Level为Oracle的特殊字段，表示“层”的意思。当前节点ID的下一层节点为“1”。
测试SQL: 1，建立表结构
CREATE TABLE "TEAMB0"."MATBOM"
( "FACT_NO" CHAR(4) NOT NULL ENABLE,
"CARRY_MK" CHAR(1) NOT NULL ENABLE,
"MAIN_MAT_NO" CHAR(20) NOT NULL ENABLE,
"SUB_MAT_NO" CHAR(20) NOT NULL ENABLE,
"PRD_CODE" CHAR(1) NOT NULL ENABLE,
"PRD_RATE" NUMBER(8,5) NOT NULL ENABLE,
"MODIFY_USER" VARCHAR2(60),
"MODIFY_DATE" CHAR(14),
"BLOC_MK" CHAR(1)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USER_DATA"
插入数据：
Insert into MATBOM (FACT_NO,CARRY_MK,MAIN_MAT_NO,SUB_MAT_NO,PRD_CODE,PRD_RATE,MODIFY_USER,MODIFY_DATE,BLOC_MK) values ('216M','N','A020408G02A059 ','A020408G01A059 ','3',1,null,null,null);
Insert into MATBOM (FACT_NO,CARRY_MK,MAIN_MAT_NO,SUB_MAT_NO,PRD_CODE,PRD_RATE,MODIFY_USER,MODIFY_DATE,BLOC_MK) values ('216M','N','A020408G15A059 ','A020408G01A059 ','3',1,null,null,null);
Insert into MATBOM (FACT_NO,CARRY_MK,MAIN_MAT_NO,SUB_MAT_NO,PRD_CODE,PRD_RATE,MODIFY_USER,MODIFY_DATE,BLOC_MK) values ('216M','N','A020408G18A059 ','A020408G01A059 ','5',1,'S6042382','20081020133712',null);
Insert into MATBOM (FACT_NO,CARRY_MK,MAIN_MAT_NO,SUB_MAT_NO,PRD_CODE,PRD_RATE,MODIFY_USER,MODIFY_DATE,BLOC_MK) values ('216M','N','N090823G09A059 ','A020408G01A059 ','1',1,'S6042382','20081208150154',null);
Insert into MATBOM (FACT_NO,CARRY_MK,MAIN_MAT_NO,SUB_MAT_NO,PRD_CODE,PRD_RATE,MODIFY_USER,MODIFY_DATE,BLOC_MK) values ('216M','N','A020408G01A059 ','A020408G01A059-1 ','3',1,null,null,null);
INSERT INTO MATBOM (FACT_NO,CARRY_MK,MAIN_MAT_NO,SUB_MAT_NO,PRD_CODE,PRD_RATE,MODIFY_USER,MODIFY_DATE,BLOC_MK) VALUES ('216M','N','A020408G01A059 ','A020408G01A059-2 ','3',1,NULL,NULL,NULL);
INSERT INTO MATBOM (FACT_NO,CARRY_MK,MAIN_MAT_NO,SUB_MAT_NO,PRD_CODE,PRD_RATE,MODIFY_USER,MODIFY_DATE,BLOC_MK)
VALUES ('216M','N','T1-A020408G02A059 ','A020408G02A059 ','3',1,NULL,NULL,NULL);
SQL查询
--prior放的左右位置决定了检索是自底向上还是自顶向下. 左边是自上而下（找子节点），右边是自下而上（找父节点） --找父节点
SELECT MAIN_MAT_NO,sub_mat_no,level
FROM MATBOM
CONNECT BY SUB_MAT_NO = PRIOR MAIN_MAT_NO
START WITH SUB_MAT_NO = 'A020408G01A059 '；
;or
SELECT MAIN_MAT_NO,sub_mat_no,level
FROM MATBOM
START WITH SUB_MAT_NO = 'A020408G01A059 '
CONNECT BY SUB_MAT_NO = PRIOR MAIN_MAT_NO；
;
--找子节点节点 -- (子节点)SUB_MAT_NO 为A020408G01A059 的子材料，以及A020408G01A059 子材料下的所有直接或间接子材料(prior 在左边， prior、MAIN_MAT_NO(等号右边)在右边）
SELECT MAIN_MAT_NO,sub_mat_no,level
FROM MATBOM
START WITH SUB_MAT_NO = 'A020408G01A059'
CONNECT BY PRIOR SUB_MAT_NO = MAIN_MAT_NO;
三、性能问题
对于 start with connect by语句的执行，oracle会进行递归查询，当数据量大的时候会产生性能相关问题。
--生成执行计划
EXPLAIN PLAN FOR SELECT * FROM MATBOM START WITH SUB_MAT_NO = 'A020408G01A059' CONNECT BY SUB_MAT_NO = PRIOR MAIN_MAT_NO;
-- 查询执行计划
select * from table( dbms_xplan.display);
语句执行计划结果如下：
Plan hash value: 1868394721
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 840 | 3 (0)| 00:00:01 |
|* 1 | CONNECT BY NO FILTERING WITH START-WITH| | | | | |
| 2 | TABLE ACCESS FULL | MATBOM | 7 | 840 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("SUB_MAT_NO"=PRIOR "MAIN_MAT_NO")
filter("SUB_MAT_NO"='A020408G01A059')
Note
-----
- dynamic sampling used for this statement
通过该执行计划得知，改语句执行了7步操作，才将结果集查询并返回。当需要查询条件进行过滤的时候，我们可以通过查看执行计划从而对sql进行优化。