Hierachical Query

名称

层次查询算子

介绍

层次查询算子用于处理窗口函数，窗口函数是在满足某种条件的一系列数据(元组)上执行的特殊函数。

GROUP BY子句可对数据进行分组，但无法投影非分组属性而在实际的使用中会需要投影非分组属性，可使用WindowAgg算子

举例

--清理环境
DROP TABLE EMPLOYEES;

--创建环境
CREATE TABLE EMPLOYEES (employee_id int,last_name char(10), manager_id int);
insert into EMPLOYEES values(101,'Kochhar',100);
insert into EMPLOYEES values(108,'Greenberg',101);
insert into EMPLOYEES values(113,'Popp',108);
insert into EMPLOYEES values(200,'Whalen',101);
insert into EMPLOYEES values(109,'Faviet',108);
insert into EMPLOYEES values(110,'Chen',108);
ANALYZE EMPLOYEES;

EXPLAIN ANALYZE SELECT employee_id, last_name, manager_id,level,
    sys_connect_by_path (last_name,'/')
from EMPLOYEES
    WHERE employee_id>100
    START WITH last_name = 'Kochhar'
    CONNECT BY PRIOR employee_id = manager_id;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Hierachical Query  (cost=0.00..89.92 rows=8192 width=0) (actual time=0.19..0.20 rows=6 loops=1)
   UseHash
   Start With Filter: ("inner".LAST_NAME = 'Kochhar')
   Child Clause: ("outer".EMPLOYEE_ID = "inner".MANAGER_ID)
   Filter: ("subplan".EMPLOYEE_ID > 100)
   ->  Seq Scan on EMPLOYEES  (cost=0.00..89.92 rows=8192 width=22) (actual time=0.02..0.02 rows=6 loops=1)
 Planning Time: 0.15 msec
 Execution Time: 0.28 msec
(8 rows)

• UseHash 限定条件,使用hash表对节点进行查找
• Start With Filter 限定条件，开始进行查找的节点
• Child Clause 限定条件，确定层次关系的条件
• Filter 限定条件，对结果集进行限定
• Connect By Filter 限定条件，当语句存在CONNECT BY且不使用Hash表时会使用此限定条件