當對象存在父節點、子節點時，通過特定的方式獲取父節點、子節點數據構建樹狀結構或其它形式結構時，通常都會使用遞歸，如:一個公司有多個部門、每個部門下可能有多個小部門，小部門下面又有組….為了數據容易管理和維護,通過構建合適的表結構存儲這些數據,以下示例以省市縣為例學習了解遞歸:
1.創建存儲省市縣數據表:
1: create table tb_distree
2: (
3: id number,
4: name varchar2(300),
5: pid number
6: )
7: /
8: remark 添加主外鍵
9: alter table tb_distree add (
10: constraints pk_id primary key(id),
11: constraints fk_pid foreign key(pid) references tb_distree(id)
12: )
13: /
2.初始化數據:
1: insert into tb_distree(id,name) values(1,'雲南省');
2: insert into tb_distree(id,name,pid) values(2,'昆明市',1);
3: insert into tb_distree(id,name,pid) values(3,'臨滄市',1);
4: insert into tb_distree(id,name,pid) values(4,'麗江市',1);
5: insert into tb_distree(id,name,pid) values(5,'雲縣',3);
6: insert into tb_distree(id,name,pid) values(6,'鳳慶',3);
7: insert into tb_distree(id,name,pid) values(7,'幸福',3);
8: insert into tb_distree(id,name,pid) values(8,'盤龍區',2);
9: insert into tb_distree(id,name,pid) values(9,'五華區',2);
10: insert into tb_distree(id,name,pid) values(10,'西山區',2);
3.遞歸查詢語法:
1: select column... from table_name
2: where .... 過濾條件
3: start with ... 遞歸開始點
4: connect by prior .... 優先級
4.遞歸查詢數據:
4.1 從父節點開始查詢出所有父節點和子節點:
SQL> select id,name,pid from tb_distree start with pid is null connect by prior id=pid;
ID NAME PID
---------- ------------ ----------
1 雲南省
2 昆明市 1
8 盤龍區 2
9 五華區 2
10 西山區 2
3 臨滄市 1
5 雲縣 3
6 鳳慶 3
7 幸福 3
4 麗江市 1
10 rows selected.
4.2 查詢某個節點的父節點:
SQL> select id,name,pid from tb_distree start with name='雲縣' connect by prior pid=id;
ID NAME PID
---------- ------------ ----------
5 雲縣 3
3 臨滄市 1
1 雲南省
在上例中"雲縣"屬於"臨滄市","臨滄市"屬於"雲南省";對於從父節點遞歸到子節點,優先級條件為子節點id等於父節點id;對於從子節點到父節點遞歸,方向剛好相反;
4.3 通過層次查詢出父節點和某個子節點:
SQL> select id,name,pid,level from tb_distree where level in(1,2) start with pid is null connect by prior id=pid;
ID NAME PID LEVEL
---------- ------------ ---------- ----------
1 雲南省 1
2 昆明市 1 2
3 臨滄市 1 2
4 麗江市 1 2
層次也很重要,某些時候要修改某個節點父節點或子節點時會很有用;
5. 遞歸查詢效率:
SQL>select/*+ selectDG1 */ id,name,pid from tb_distree start with pid is null connect by prior id=pid;
SQL> select sql_id,sql_text from v$sql where sql_text like '%selectDG1%' ;
SQL_ID SQL_TEXT
--------------- --------------------------------------------------
2wnu324ga4n0y select sql_id,sql_text from v$sql where sql_text l
ike '%selectDG1%'
d4g89bucsbvzd select/*+ selectDG1 */ id,name,pid from tb_distree
start with pid is null connect by prior id=pid
SQL>select * from table(dbms_xplan.display_cursor('d4g89bucsbvzd',null,'advanced allstats last peeked_binds'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID d4g89bucsbvzd, child number 0
-------------------------------------
select/*+ selectDG1 */ id,name,pid from tb_distree start with pid is
null connect by prior id=pid
Plan hash value: 1466399788
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 18 (100)| | 10 |00:00:00.01 | 22 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
|* 1 | CONNECT BY NO FILTERING WITH START-WITH| | 1 | | | | | 10 |00:00:00.01 | 22 |
| 2 | TABLE ACCESS FULL | TB_DISTREE | 1 | 10 | 1780 | 17 (0)| 00:00:01 | 10 |00:00:00.01 | 22 |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2 / TB_DISTREE@SEL$2
Outline Data
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$4")
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$1")
NO_ACCESS(@"SEL$1" "connect$_by$_work$_set$_006"@"SEL$1")
NO_CONNECT_BY_FILTERING(@"SEL$1")
CONNECT_BY_COMBINE_SW(@"SEL$1")
FULL(@"SEL$4" "TB_DISTREE"@"SEL$4")
FULL(@"SEL$3" "connect$_by$_pump$_002"@"SEL$3")
FULL(@"SEL$3" "TB_DISTREE"@"SEL$3")
LEADING(@"SEL$3" "connect$_by$_pump$_002"@"SEL$3" "TB_DISTREE"@"SEL$3")
USE_HASH(@"SEL$3" "TB_DISTREE"@"SEL$3")
FULL(@"SEL$2" "TB_DISTREE"@"SEL$2")
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("PID"=PRIOR NULL)
filter("PID" IS NULL)
10046 trace:
Rows (1st) Rows (avg) Rows (max) Row Source Operation
675 ---------- ---------- ---------- ---------------------------------------------------
676 10 10 10 CONNECT BY NO FILTERING WITH START-WITH (cr=22 pr=0 pw=0 time=269 us)
677 10 10 10 TABLE ACCESS FULL TB_DISTREE (cr=22 pr=0 pw=0 time=118 us cost=17 size=1780 card=10)
678
679
680 Elapsed times include waiting on following events:
681 Event waited on Times Max. Wait Total Waited
682 ---------------------------------------- Waited ---------- ------------
683 row cache lock 3 0.00 0.00
684 Disk file operations I/O 1 0.00 0.00
685 db file sequential read 3 0.03 0.04
686 SQL*Net message to client 2 0.00 0.00
687 SQL*Net message from client 2 0.00 0.00
688 ********************************************************************************
在遞歸表上創建索引是沒有用的,都必須進行全表掃描,當然通常這樣的表也不會很大,如果數據量的確很大,建議將表中的節點拆分成多個表提高效率;
