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扫描,一次索引范围扫描。 |