20.层次检索

hierarchical Retrieval 层次检索
--创建雇员表
CREATE TABLE EMPLOYEE_CHART
( EMPLOYEE_ID NUMBER(7) PRIMARY KEY,  --主键
TITLE VARCHAR2(20),
REPORTS_TO NUMBER(7));
--增加外键
ALTER TABLE EMPLOYEE_CHART
ADD CONSTRAINT FK_EM_EM
FOREIGN KEY (REPORTS_TO)
REFERENCES EMPLOYEE_CHART (EMPLOYEE_ID);
--数据
1 CEO
2 VP 1
3 SVP 1
4 CFO 1
5 Director1 2
6 Director2 2
7 Director3 3
8 Director4 3
9 Manager1 6
--进行层次检索查询
SELECT LEVEL, EMPLOYEE_ID, TITLE
 FROM EMPLOYEE_CHART
 START WITH EMPLOYEE_ID = 1                     --根节点
 CONNECT BY REPORTS_TO = PRIOR EMPLOYEE_ID;     --检索条件，PRIOR树的上级
--
/*
SQL> set autotrace traceo
SQL> set linesize 200
SQL> select * from dba_objects;


已选择9860行。




执行计划
----------------------------------------------------------
Plan hash value: 2127761497                               


----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |  8902 |  1538K|    32   (4)| 00:00:01 |
|   1 |  VIEW                          | DBA_OBJECTS |  8902 |  1538K|    32   (4)| 00:00:01 |
|   2 |   UNION-ALL                    |             |       |       |            |          |
|*  3 |    FILTER                      |             |       |       |            |          |
|*  4 |     HASH JOIN                  |             | 10045 |   882K|    30   (4)| 00:00:01 |
|   5 |      TABLE ACCESS FULL         | USER$       |    28 |   476 |     2   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS FULL         | OBJ$        | 10045 |   716K|    27   (0)| 00:00:01 |
|*  7 |     TABLE ACCESS BY INDEX ROWID| IND$        |     1 |     7 |     2   (0)| 00:00:01 |
|*  8 |      INDEX UNIQUE SCAN         | I_IND1      |     1 |       |     1   (0)| 00:00:01 |
|   9 |    TABLE ACCESS BY INDEX ROWID | LINK$       |     1 |    88 |     0   (0)| 00:00:01 |
|  10 |     NESTED LOOPS               |             |     1 |   105 |     2   (0)| 00:00:01 |
|  11 |      TABLE ACCESS FULL         | USER$       |    28 |   476 |     2   (0)| 00:00:01 |
|* 12 |      INDEX RANGE SCAN          | I_LINK1     |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   3 - filter("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND  (SELECT 1 FROM
              "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR
              "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1)
   4 - access("O"."OWNER#"="U"."USER#")
   6 - filter("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_'
              AND "O"."LINKNAME" IS NULL)
   7 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
              "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
   8 - access("I"."OBJ#"=:B1)
  12 - access("L"."OWNER#"="U"."USER#")




统计信息
----------------------------------------------------------
        464  recursive calls
          0  db block gets
       3238  consistent gets
          0  physical reads
          0  redo size
     434480  bytes sent via SQL*Net to client
       7612  bytes received via SQL*Net from client
        659  SQL*Net roundtrips to/from client
         11  sorts (memory)
          0  sorts (disk)
       9860  rows processed
*/
Select * From v$sql_plan Where plan_hash_value ='2127761497'  -- 执行结果在内存中，很快会刷新掉，id,parent_id,是形成树的条件
Select Level,OPERATION,lpad(' ',Level*2)||OPERATION OPERATION_level,OPTIONS,Id,PARENT_ID,lpad(id,Level*2,' ') id_level,                 --OPERATION_level,id_level 增加层次感，用的方法不同，注意LEVEL=1时，lpad(OPERATION,Level*2,' ')不能显示全，所以使用||的方式比较好
SYS_CONNECT_BY_PATH(Id,'/'),                                                                                                            --SYS_CONNECT_BY_PATH函数，自动分层次
From (Select OPERATION,OPTIONS,Id,PARENT_ID From v$sql_plan Where plan_hash_value ='2127761497')         --创建子查询优化执行速度
Start With Id = 0
Connect By Prior Id=parent_id
--connect_by_root  求树的父亲节点id

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15810196/viewspace-1079772/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15810196/viewspace-1079772/