Q7,oracle层次查询:在业务执行过程中,如果涉及到的目标表具有层次结构数据,在传统的查询执行过程中,我们需要使用自连接来完成查询的执行,这样不仅代码书写比较麻烦,而且执行时的资源花销也比较大。Oracle提供了层次查询函数来优化这类查询要求。在层次查询中,伪列level可以用于返回层次,根层次为1,第二级层次为2,以此类推。层次查询子句的语法为
Start with condition
Connect by condition
其中start with用于指定层次查询的根行。Connect by用于指定父行(上级行)和子行之间的关系。在condition表达式中,必须使用prior引用父行。
-- 创建测试表单
createtable temployee(
employeeid varchar2(10),
employeename varchar2(20),
mgrid varchar2(10)
);
--插入测试数据
insertinto temployee values(10000,'zhangsna',null);
begin
for i in1..9999loop
ifmod(i,1000) = 0then
insertinto temployee values(i+10000,'zhangsna',trunc(i+10000,-4));
elsifmod(i,100) = 0then
insertinto temployee values(i+10000,'zhangsna',trunc(i+10000,-3));
elsifmod(i,10) = 0then
insertinto temployee values(i+10000,'zhangsna',trunc(i+10000,-2));
else
insertinto temployee values(i+10000,'zhangsna',trunc(i+10000,-1));
endif;
endloop; --select * from temployee
end;
使用传统的自连接查询时语句及其执行计划如下所示:
select a.employeeid, a.mgrid,a.employeename
from temployee a, temployee b
where a.mgrid = b.employeeid(+)
步骤描述
|
Owner
|
对象名
|
耗费
|
基数
|
字节
|
Select statement,goal=all_rows
|
|
|
10
|
1
|
33
|
Hash join outer
|
|
|
10
|
1
|
33
|
Table access full
|
Sys
|
Temployee
|
10
|
1
|
26
|
Table access full
|
Sys
|
Temployee
|
10
|
1
|
7
|
使用层次函数执行查询的语句及执行计划如下所示:
select employeeid,mgrid,employeename
from temployee
wherenot mgrid isnull
startwith mgrid isnull
connectbyprior employeeid = mgrid;
步骤描述
|
Owner
|
对象名
|
耗费
|
基数
|
字节
|
Select statement,goal=all_rows
|
|
|
10
|
1
|
26
|
Filter
|
|
|
|
|
|
Connect by with filtering
|
|
|
|
|
|
filter
|
|
|
|
|
|
Table access full
|
Sys
|
Temployee
|
10
|
1
|
26
|
Hash join
|
|
|
|
|
|
Connect by pump
|
|
|
|
|
|
Table access full
|
Sys
|
Temployee
|
10
|
1
|
26
|
Table access full
|
Sys
|
Temployee
|
10
|
1
|
26
|
另外,作者先后执行了如下步骤1,在employeeid上建立主键;2,在employeeid上建立主键的同时为mgrid列建立索引。对数据的测试结果表明,适当的建立索引后oracle层次函数可以有效提高数据查询的速度。
Alter table temployee add constraints temployee_employeeid_pk primary key(employeeid);
Create index temployee_mgrid_ind on temployee(mgrid);
步骤描述
|
耗费
|
基数
|
字节
|
备注
|
不建立索引(传统查询)
|
21,21,10,10
|
1,1,1,1
|
153
|
执行两次全表扫描
|
不建立索引(层次函数)
|
10,10,10,10
|
1,1,1,1
|
152
|
执行了三次全表扫描
|
主键索引
|
14,14,10,0
|
10000,10000,
10000,1
|
|
一次全表扫描
一次唯一索引扫描
|
主键索引(层次函数)
|
10,10,10,10
|
10000,10000,
10000,10000
|
|
执行了三次全表扫描
|
主键索引的基础上为mgrid建立普通索引
|
14,14,10,0
|
10000,10000,
10000,1
|
|
一次全表扫描
一次唯一索引扫描
|
主键索引的基础上为mgrid建立普通索引(层次函数)
|
2,2,2,1,2
|
100,100,100,40,100
|
144000
|
一次全表扫描,一次rowid扫描,一次索引范围扫描。
|