oracle的层次查询

 

Q7 ，oracle 层次查询：在业务执行过程中，如果涉及到的目标表具有层次结构数据，在传统的查询执行过程中，我们需要使用自连接来完成查询的执行，这样不仅代码书写比较麻烦，而且执行时的资源花销也比较大。Oracle提供了层次查询函数来优化这类查询要求。在层次查询中，伪列level可以用于返回层次，根层次为1，第二级层次为2，以此类推。层次查询子句的语法为

Start with condition

Connect by condition

其中start with用于指定层次查询的根行。Connect by用于指定父行（上级行）和子行之间的关系。在condition表达式中，必须使用prior引用父行。

-- 创建测试表单

create table temployee(

employeeid varchar2 ( 10 ),

employeename varchar2 ( 20 ),

mgrid varchar2 ( 10 )

);

-- 插入测试数据

insert into temployee values ( 10000 , 'zhangsna' , null );

begin

for i in 1 .. 9999 loop

  if mod (i, 1000 ) = 0 then

    insert into temployee values (i+ 10000 , 'zhangsna' ,trunc(i+ 10000 ,- 4 ));

  elsif mod (i, 100 ) = 0 then

    insert into temployee values (i+ 10000 , 'zhangsna' ,trunc(i+ 10000 ,- 3 ));

  elsif mod (i, 10 ) = 0 then

    insert into temployee values (i+ 10000 , 'zhangsna' ,trunc(i+ 10000 ,- 2 ));

  else

    insert into temployee values (i+ 10000 , 'zhangsna' ,trunc(i+ 10000 ,- 1 ));

  end if ;

end loop ;  --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

where not mgrid is null

start with mgrid is null

connect by prior 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扫描，一次索引范围扫描。