我们先举个例子。
SQL> create table TEST01 (C1 varchar2(20),C2 varchar2(20), C3 number(5));
Table created.
SQL> create index INX_PRODUCT on TEST01 (C1);
Index created.
SQL> insert into TEST01 values ('LIAONING','DALIAN',1);
insert into TEST01 values ('LIAONING','SHENYANG',1);
insert into TEST01 values ('DALIAN','ZHONGSHANQU',1);
insert into TEST01 values ('DALIAN','XIGANGQU',1);
insert into TEST01 values ('SHENYANG','HEPINGQU',1);
insert into TEST01 values ('SHENYANG','SHENHEQU',1);
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
SQL> commit;
Commit complete.
表中的数据,其中第一列中的值包含下一列的值,以此类推。 这类表就是有层次结构的表。
LIAONING
/ \
DALIAN SHENYANG
/ \ / \
ZHONGSHANQU XIGANGQU HEPINGQU SHENHEQU
那么什么是层次查询呢?
通过如下查询,可以表示出各个列的层次结构。这类查询就是层次查询。
其中用"start with"引导,用"connect by prior"来关联列。
SQL> select C1,C2,C3,level from TEST01 start with c1 = 'LIAONING' connect by prior c2 = c1;
C1 C2 C3 LEVEL
-------------------- -------------------- ---------- ----------
LIAONING DALIAN 1 1
DALIAN XIGANGQU 1 2
DALIAN ZHONGSHANQU 1 2
LIAONING SHENYANG 1 1
SHENYANG HEPINGQU 1 2
SHENYANG SHENHEQU 1 2
阶层查询有2种实现方式。 "CONNECT BY WITH FILTERING" 以及 "CONNECT BY NO FILTERING WITH START-WITH"。
可以分别用 CONNECT_BY_FILTERING 以及 NO_CONNECT_BY_FILTERING hint 来指定。
select /*+ CONNECT_BY_FILTERING */ C1,C2,C3,level from TEST01 start with c1 = 'LIAONING' connect by prior c2 = c1;
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 222 | 4 (50)| 00:00:01 |
|* 1 | CONNECT BY WITH FILTERING | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | TEST01 | 2 | 74 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | INX_PRODUCT | 2 | | 1 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 4 | 196 | 1 (0)| 00:00:01 |
| 5 | CONNECT BY PUMP | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| TEST01 | 2 | 74 | 0 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | INX_PRODUCT | 2 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1"=PRIOR "C2")
3 - access("C1"='LIAONING')
7 - access("connect$_by$_pump$_002"."prior c2 "="C1")
select /*+ NO_CONNECT_BY_FILTERING */ C1,C2,C3,level from TEST01 start with c1 = 'LIAONING' connect by prior c2 = c1;
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 222 | 4 (25)| 00:00:01 |
|* 1 | CONNECT BY NO FILTERING WITH START-WITH| | | | | |
| 2 | TABLE ACCESS FULL | TEST01 | 6 | 222 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1"=PRIOR "C2")
filter("C1"='LIAONING')
通过2种方式的执行计划,能看到"CONNECT BY WITH FILTERING"将会使用索引扫描,
并按照 start with 和 connect by prior 将执行计划分成了2部分。
而"CONNECT BY NO FILTERING WITH START-WITH"则使用了全表扫描。
通常默认的情况下会oracle优化器会选择"CONNECT BY WITH FILTERING"。
------------------
接下来说说什么是递归查询。
递归查询一般是指对包含层次结构数据的表进行的递归查询。以下的例子中,临时表TEMP和TEST01形成了一个结合的循环。
SQL> with temp (C1,C2,C3,lvl) as (
select C1,C2,C3,1 from TEST01 where C1 = 'LIAONING'
union all
select B.C1,B.C2,B.C3,T.lvl+1 from TEST01 B, temp T where B.C1 = T.C2
)
select * from temp;
C1 C2 C3 LVL
-------------------- -------------------- ---------- ----------
LIAONING DALIAN 1 1
LIAONING SHENYANG 1 1
DALIAN ZHONGSHANQU 1 2
DALIAN XIGANGQU 1 2
SHENYANG HEPINGQU 1 2
SHENYANG SHENHEQU 1 2
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 300 | 2 (0)| 00:00:01 |
| 1 | VIEW | | 6 | 300 | 2 (0)| 00:00:01 |
| 2 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | TEST01 | 2 | 74 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | INX_PRODUCT | 2 | | 1 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 4 | 248 | 1 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 4 | 248 | 1 (0)| 00:00:01 |
| 7 | RECURSIVE WITH PUMP | | | | | |
|* 8 | INDEX RANGE SCAN | INX_PRODUCT | 2 | | 0 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | TEST01 | 2 | 74 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("C1"='LIAONING')
8 - access("B"."C1"="T"."C2")
我们可以通过参数 DEPTH FIRST(深度优先)和 BREADTH FIRST(宽度优先)来定制排序规则。
SQL> with temp (C1,C2,C3,lvl) as (
select C1,C2,C3,1 from TEST01 where C1 = 'LIAONING'
union all
select B.C1,B.C2,B.C3,T.lvl+1 from TEST01 B, temp T where B.C1 = T.C2
) SEARCH BREADTH FIRST BY C1 SET order01
select * from temp;
C1 C2 C3 LVL ORDER01
-------------------- -------------------- ---------- ---------- ----------
LIAONING DALIAN 1 1 1
LIAONING SHENYANG 1 1 2
DALIAN ZHONGSHANQU 1 2 3
DALIAN XIGANGQU 1 2 4
SHENYANG HEPINGQU 1 2 5
SHENYANG SHENHEQU 1 2 6
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 378 | 3 (34)| 00:00:01 |
| 1 | VIEW | | 6 | 378 | 3 (34)| 00:00:01 |
| 2 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | TEST01 | 2 | 74 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | INX_PRODUCT | 2 | | 1 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 4 | 248 | 1 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 4 | 248 | 1 (0)| 00:00:01 |
| 7 | RECURSIVE WITH PUMP | | | | | |
|* 8 | INDEX RANGE SCAN | INX_PRODUCT | 2 | | 0 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | TEST01 | 2 | 74 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("C1"='LIAONING')
8 - access("B"."C1"="T"."C2")
SQL> with temp (C1,C2,C3,lvl) as (
select C1,C2,C3,1 from TEST01 where C1 = 'LIAONING'
union all
select B.C1,B.C2,B.C3,T.lvl+1 from TEST01 B, temp T where B.C1 = T.C2
) SEARCH DEPTH FIRST BY C1 set order01
select * from temp;
C1 C2 C3 LVL ORDER01
-------------------- -------------------- ---------- ---------- ----------
LIAONING DALIAN 1 1 1
DALIAN XIGANGQU 1 2 2
DALIAN ZHONGSHANQU 1 2 3
LIAONING SHENYANG 1 1 4
SHENYANG HEPINGQU 1 2 5
SHENYANG SHENHEQU 1 2 6
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 378 | 3 (34)| 00:00:01 |
| 1 | VIEW | | 6 | 378 | 3 (34)| 00:00:01 |
| 2 | UNION ALL (RECURSIVE WITH) DEPTH FIRST| | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | TEST01 | 2 | 74 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | INX_PRODUCT | 2 | | 1 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 4 | 248 | 1 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 4 | 248 | 1 (0)| 00:00:01 |
| 7 | RECURSIVE WITH PUMP | | | | | |
|* 8 | INDEX RANGE SCAN | INX_PRODUCT | 2 | | 0 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | TEST01 | 2 | 74 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("C1"='LIAONING')
8 - access("B"."C1"="T"."C2")
看到区别了吗。
---------------------
递归查询有一个功能限制, 就是无法完全的并行化。因为在循环结果的
过程无法使用并行。导致的结果就是对大型表进行查询的时候会很慢。这是一个功能上的限制。
为了解决这个问题,Oracle 12.2之后增加的递归查询的强化功能解决了该问题。
如下例子,通过创建一个临时表的方式,将循环过程中的结果存放在
位于sort buffer上来回避该问题。但是注意一点的是使用 CONNECT BY 的层次查询仍无法回避。
with temp (C1,C2,C3,lvl) as (
select C1,C2,C3,1 from TEST01 where C1 = 'LIAONING'
union all
select B.C1,B.C2,B.C3,T.lvl+1 from TEST01 B, temp T where B.C1 = T.C2
) select * from temp;
Execution Plan
----------------------------------------------------------
Plan hash value: 4887393
--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 300 | 7 (0)| 00:00:01 | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D68C2_3C468A8 | | | | | | | |
| 3 | UNION ALL (RECURSIVE WITH) BREADTH FIRST | | | | | | | | |
| 4 | PX COORDINATOR | | | | | | | | |
| 5 | PX SEND QC (RANDOM) | :TQ20000 | 2 | 40 | 2 (0)| 00:00:01 | Q2,00 | P->S | QC (RAND) |
| 6 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D68C2_3C468A8 | | | | | Q2,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 2 | 40 | 2 (0)| 00:00:01 | Q2,00 | PCWC | |
|* 8 | TABLE ACCESS FULL | TEST01 | 2 | 40 | 2 (0)| 00:00:01 | Q2,00 | PCWP | |
| 9 | PX COORDINATOR | | | | | | | | |
| 10 | PX SEND QC (RANDOM) | :TQ10000 | 4 | 65780 | 3 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 11 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D68C2_3C468A8 | | | | | Q1,00 | PCWP | |
| 12 | NESTED LOOPS | | 4 | 65780 | 3 (0)| 00:00:01 | Q1,00 | PCWP | |
| 13 | PX BLOCK ITERATOR | | | | | | Q1,00 | PCWC | |
|* 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D68C2_3C468A8 | | | | | Q1,00 | PCWP | |
| 15 | TABLE ACCESS BY INDEX ROWID | TEST01 | 2 | 40 | 1 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 16 | INDEX RANGE SCAN | INX_PRODUCT | 2 | | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 17 | PX COORDINATOR | | | | | | | | |
| 18 | PX SEND QC (RANDOM) | :TQ30000 | 6 | 300 | 2 (0)| 00:00:01 | Q3,00 | P->S | QC (RAND) |
| 19 | VIEW | | 6 | 300 | 2 (0)| 00:00:01 | Q3,00 | PCWP | |
| 20 | PX BLOCK ITERATOR | | 6 | 65820 | 2 (0)| 00:00:01 | Q3,00 | PCWC | |
| 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9D68C2_3C468A8 | 6 | 65820 | 2 (0)| 00:00:01 | Q3,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------------------
希望对大家有所帮助。