`

Oracle start with connect by ...

 
阅读更多

oracle 提供了start with connect by 语法结构可以实现递归查询。

1. 一个简单举例:
SQL> select *  from test;

BILL_MONTH           DAY_NUMBER MSISDN
-------------------- ---------- --------------------
200803                        1 13800
200803                        3 13800
200803                        2 13800
200803                        2 13801
200803                        4 13804
200803                        5 13804
200803                        7 13804
200803                        8 13804
200803                        6 13802
200803                        6 13801
200803                        7 13801
200803                        8 13801

12 rows selected

SQL>
SQL> select * from test
  2       start with day_number=1
  3       connect by  prior day_number=day_number-1 and prior msisdn= msisdn
  4      ;

BILL_MONTH           DAY_NUMBER MSISDN
-------------------- ---------- --------------------
200803                        1 13800
200803                        2 13800
200803                        3 13800

SQL>


上面的语句查找出了从1开始，并且day_number 逐渐+1 递增的，并且 msisdn 相同的哪些个数据.


2. start with  connect by 语法结构
 如上面说看到的 例子， 其语法结构为  start with condition  connect by  condition （含 prior 关键字)
start with conditon 给出的seed 数据的范围, connect by  后面给出了递归查询的条件,prior 关键字表示父数据，prior 条件表示子数据需要满足父数据的什么条件。

在下面的这个start with connect by 结构中，就表示 查找出了从1开始，并且day_number 逐渐+1 递增的，并且 msisdn 相同的那些个数据.

start with day_number=1
     connect by  prior day_number=day_number-1 and prior msisdn= msisdn

3.  执行计划
对于这个特殊的语法结构，我们来看看它的执行计划。
通过下面的执行计划，我们可以看出，对于简单的访问一个对象的递归查询，实际上oracle 要三次访问要查询的对象。因此，这一个告诉我们，在使用递归查询时，一定要谨慎，因为即使原表数据不多，但是三倍的访问喜爱来，代价也会很大。

SQL> explain plan for
  2
  2   select * from  test
  3    --where  bill_month='200803'
  4    start with day_number=1
  5    connect by  prior day_number=day_number-1 and prior msisdn= msisdn
  6  ;

Explained

SQL> select *  from  table( dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------------------
| Id  | Operation                 |  Name       | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |             |       |       |       |
|*  1 |  CONNECT BY WITH FILTERING|             |       |       |       |
|*  2 |   FILTER                  |             |       |       |       |
|   3 |    TABLE ACCESS FULL      | TEST        |       |       |       |
|   4 |   NESTED LOOPS            |             |       |       |       |
|   5 |    BUFFER SORT            |             |       |       |       |
|   6 |     CONNECT BY PUMP       |             |       |       |       |
|*  7 |    TABLE ACCESS FULL      | TEST        |       |       |       |
|   8 |   TABLE ACCESS FULL       | TEST        |       |       |       |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("TEST"."DAY_NUMBER"=1)
   2 - filter("TEST"."DAY_NUMBER"=1)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   7 - filter("TEST"."MSISDN"=NULL AND "TEST"."DAY_NUMBER"-1=NULL)
Note: rule based optimization

23 rows selected

SQL>


另外，发现了在含有其他条件的递归中，是先处理所有的递归查询，最后才用加入的条件过滤.
请看下面的例子。
和上面的执行计划对比下我们可以知道，加入条件   where  bill_month='200803' 后，实际上却是在递归完成后，最后才执行的    1 - filter("TEST"."BILL_MONTH"='200803') 。

所以，为了确保语句的性能，不要直接加入条件在start with connect by 结构中，而是要想办法将原表的数据控制住。这个可以采用子查询的办法，或者使用临时表等（最好采用临时表，将数据量从本源上控制住；因为从子查询的执行计划我们可以看到，它每次也都是访问全表，再用条件过滤，要重复三次，不是一次过滤就够了).

--直接加入条件后的执行计划
SQL> explain plan for
  2
  2   select * from  test
  3    where  bill_month='200803'
  4    start with day_number=1
  5    connect by  prior day_number=day_number-1 and prior msisdn= msisdn
  6  ;

Explained

SQL> select *  from  table( dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
| Id  | Operation                  |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |       |       |       |
|*  1 |  FILTER                    |             |       |       |       |
|*  2 |   CONNECT BY WITH FILTERING|             |       |       |       |
|*  3 |    FILTER                  |             |       |       |       |
|   4 |     TABLE ACCESS FULL      | TEST        |       |       |       |
|   5 |    NESTED LOOPS            |             |       |       |       |
|   6 |     BUFFER SORT            |             |       |       |       |
|   7 |      CONNECT BY PUMP       |             |       |       |       |
|*  8 |     TABLE ACCESS FULL      | TEST        |       |       |       |
|   9 |    TABLE ACCESS FULL       | TEST        |       |       |       |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("TEST"."BILL_MONTH"='200803')

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   2 - filter("TEST"."DAY_NUMBER"=1)
   3 - filter("TEST"."DAY_NUMBER"=1)
   8 - filter("TEST"."MSISDN"=NULL AND "TEST"."DAY_NUMBER"-1=NULL)
Note: rule based optimization

25 rows selected

SQL>


--使用子查询，将过滤条件嵌在子查询中
SQL> explain plan for
  2
  2  select * from (select * from test
  3        where  bill_month='200803')
  4       start with day_number=1
  5       connect by  prior day_number=day_number-1 and prior msisdn= msisdn
  6      ;

Explained

SQL> select *  from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------------------
| Id  | Operation                 |  Name       | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |             |       |       |       |
|*  1 |  CONNECT BY WITH FILTERING|             |       |       |       |
|*  2 |   FILTER                  |             |       |       |       |
|*  3 |    TABLE ACCESS FULL      | TEST        |       |       |       |
|   4 |   NESTED LOOPS            |             |       |       |       |
|   5 |    BUFFER SORT            |             |       |       |       |
|   6 |     CONNECT BY PUMP       |             |       |       |       |
|*  7 |    TABLE ACCESS FULL      | TEST        |       |       |       |
|*  8 |   TABLE ACCESS FULL       | TEST        |       |       |       |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("TEST"."DAY_NUMBER"=1)
   2 - filter("TEST"."DAY_NUMBER"=1)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   3 - filter("TEST"."BILL_MONTH"='200803')
   7 - filter("TEST"."BILL_MONTH"='200803' AND "TEST"."MSISDN"=NULL AND
              "TEST"."DAY_NUMBER"-1=NULL)
   8 - filter("TEST"."BILL_MONTH"='200803')
Note: rule based optimization

26 rows selected

SQL>



4. 实际中 递归查询的使用。

问题：
数据库里有字段day_number，msisdn。如何写月度连续3天有记录的手机号？表结构如下：

id   bill_month   day_number     msisdn
1      200803      1           13800000000
2      200803      1           130137.....
3      200803      2           13800000000
4      200803      3           13800000000
..............................

表中3月份连续3天有记录的纪录就是1380000000。请问如何写这样的sql？


解决方案：
SQL> create  table   test ( bill_month varchar2(20),day_number number ,msisdn varchar2(20));

Table created

SQL> insert into  test values ( '200803',1,'13800');

1 row inserted

SQL> insert into  test values ( '200803',3,'13800');

1 row inserted

SQL> insert into  test values ( '200803',2,'13800');

1 row inserted

SQL> insert into  test values ( '200803',2,'13801');

1 row inserted

SQL> insert into  test values ( '200803',4,'13804');

1 row inserted

SQL> insert into  test values ( '200803',5,'13804');

1 row inserted

SQL> commit;

Commit complete

SQL> select *  from test;

BILL_MONTH           DAY_NUMBER MSISDN
-------------------- ---------- --------------------
200803                        1 13800
200803                        3 13800
200803                        2 13800
200803                        2 13801
200803                        4 13804
200803                        5 13804

6 rows selected

SQL>
SQL> select distinct  msisdn  from test  a
  2  where  bill_month='200803'
  3  and exists
  4  ( select msisdn from  test
  5    where  bill_month='200803' and msisdn=a.msisdn
  6    start with day_number=a.day_number
  7    connect by  prior day_number=day_number-1 and prior msisdn= msisdn
  8    group by msisdn
  9    having count(*)>=3
 10    );

MSISDN
--------------------
13800


SQL> select *  from test;

BILL_MONTH           DAY_NUMBER MSISDN
-------------------- ---------- --------------------
200803                        1 13800
200803                        3 13800
200803                        2 13800
200803                        2 13801
200803                        4 13804
200803                        5 13804

6 rows selected

SQL> insert into  test values ( '200803',7,'13804');

1 row inserted

SQL> insert into  test values ( '200803',8,'13804');

1 row inserted

SQL> insert into  test values ( '200803',6,'13802');

1 row inserted

SQL> insert into  test values ( '200803',6,'13801');

1 row inserted

SQL> insert into  test values ( '200803',7,'13801');

1 row inserted

SQL> insert into  test values ( '200803',8,'13801');

1 row inserted

SQL> select *  from test;

BILL_MONTH           DAY_NUMBER MSISDN
-------------------- ---------- --------------------
200803                        1 13800
200803                        3 13800
200803                        2 13800
200803                        2 13801
200803                        4 13804
200803                        5 13804
200803                        7 13804
200803                        8 13804
200803                        6 13802
200803                        6 13801
200803                        7 13801
200803                        8 13801

12 rows selected

SQL> commit;

Commit complete

SQL>
SQL> select distinct  msisdn  from test  a
  2  where  bill_month='200803'
  3  and exists
  4  ( select msisdn from  test
  5    where  bill_month='200803' and msisdn=a.msisdn
  6    start with day_number=a.day_number
  7    connect by  prior day_number=day_number-1 and prior msisdn= msisdn
  8    group by msisdn
  9    having count(*)>=3
 10    );

MSISDN
--------------------
13800
13801

SQL>

分享到：
| memCache
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    Oracle中connect by...start with...的使用

    本文章详细介绍了Oracle中connect by...start with...的用法。

    树状数据库表：Oracle中start with...connect by prior子句用法

    `START WITH...CONNECT BY PRIOR`是Oracle SQL中的一个特性，用于处理具有层级关系的数据。这个子句允许我们遍历和查询具有父子关系的数据，例如部门和其下属子部门，或者员工和他们的上级经理。 1. **START WITH...

    ORACLE查询树型关系(connect_by_prior_start_with)

    ORACLE 查询树型关系(connect_by_prior_start_with) Oracle 查询树型关系是指使用 START WITH 和 CONNECT BY 子句来实现 SQL 的层次查询。从 Oracle 9i 开始，可以通过 SYS_CONNECT_BY_PATH 函数实现将父节点到...

    Oracle start with.connect by prior子句实现递归查询

    ### Oracle Start With.Connect By Prior 子句实现递归查询 #### 概述 在Oracle数据库中，`Start With.Connect By Prior`子句是执行递归查询的一种强大工具，主要用于处理层次结构数据。这类数据通常存在于组织...

    oracle数据库startwith用法

    ### Oracle数据库中的START WITH 和 CONNECT BY 用法详解 在Oracle数据库中，处理层次结构数据时，`START WITH` 和 `CONNECT BY` 是非常有用的两个关键字。这些关键字可以帮助我们在查询时构建出树形或者层级结构的...

    Oracle递归查询start with connect by prior的用法

    connect by：connect by是必须的，start with有些情况是可以省略的，或者直接start with 1=1不加限制 prior：prior关键字可以放在等号的前面，也可以放在等号的后面，表示的意义是不一样的，比如 prior id = pid，...

    浅谈Oracle下connect by原理.pdf

    文章中给出了一个名为`t_test_connectby`的数据表例子，其中包含父子关系的节点数据。通过该表，我们可以更直观地了解Connect By的执行过程。 - 不指定起始条件时，使用`CONNECT BY`： ```sql SELECT * FROM t_test...

    MySQL多种递归查询方法.docx

    在Oracle数据库中，递归查询可以通过`START WITH CONNECT BY PRIOR`语句实现。此语句允许用户按照树状结构来检索数据。 ##### 1. `START WITH CONNECT BY PRIOR`用法详解 **基本语法**: ```sql SELECT * FROM ...

    Oracle的Connect By使用示例

    Oracle数据库通过提供`START WITH...CONNECT BY`语句来简化这类查询操作。此功能最早出现在Oracle 8.1.6版本，并一直沿用至今，成为处理递归查询的强大工具之一。 #### 二、Connect By与Start With详解 1. **...

    Oracle_start_with_connect_by_prior_用法[文].pdf

    Oracle 连接查询是指使用 START WITH 和 CONNECT BY 语句来实现递归查询的方法，这种方法可以生成树形结构的数据。在 Oracle 中，START WITH 语句用于指定递归查询的开始记录，而 CONNECT BY 语句用于指定递归查询的...

    Oracle_start_with_connect_by_prior_用法

    ### Oracle中的START WITH CONNECT BY PRIOR 用法详解 #### 一、概念介绍 在Oracle数据库中，`START WITH` 和 `CONNECT BY PRIOR` 是两个非常强大的特性，主要用于处理具有层级结构的数据。这两个特性可以帮助我们...

    connect by的使用探索

    [ WHERE condition ][ [ START WITH condition ] CONNECT BY condition [ ORDER SIBLINGS BY expression ] ] ``` 其中，`START WITH`子句用于指定查询的起始节点，`CONNECT BY`子句用于指定查询的递归条件。 ...

    oracle connect by level 应用

    通常，`CONNECT BY`与`START WITH`一起使用，`START WITH`指定了层级遍历的起始节点。 例如，假设我们有一个员工表（EMPLOYEE），其中包含上级员工ID（MANAGER_ID）字段，我们可以使用以下查询来展示员工的管理层次...

    oracle-tree-sql.rar_oracle

    其中，`START WITH`用于指定从哪个节点开始，`CONNECT BY`定义了子节点与父节点之间的关系。 1.2 Prior关键字 在`CONNECT BY`语句中，`PRIOR`关键字用于引用当前行的父行。例如，如果`parent_id`是子节点指向父...

    Oracle 数据库树形结构用法总结.mht

    Oracle 数据库树形结构用法总结，例如SYS_CONNECT_BY_PATH 、START WITH . . . CONNECT BY . . .等具体语法介绍

    oracle树结构查询.DOC

    Oracle数据库系统支持对树状结构数据的查询，这种查询方式主要通过`START WITH`和`CONNECT BY`子句实现。这两个子句是Oracle特有的，用于处理层级关系的数据，例如组织结构、产品分类或者树形菜单等。理解并掌握这两...

    Oracle迁移到PG建议.docx

    * 递归查询：在Oracle中，使用START WITH...CONNECT BY来实现递归查询，而在Postgresql中，使用WITH RECURSIVE来实现递归查询。 * UPDATE语句别名：在Postgresql中，UPDATE语句的SET字段不能有别名，而在Oracle中，...

    oracle从入门到精通.pdf

    2. **层次查询**：使用CONNECT BY和START WITH进行层次结构查询。 #### 二、数据库管理 **1. Oracle的构件和组件** - **Oracle架构概述**：包括实例和数据库。 **2. 数据库的物理结构** 1. **控制文件**：存储...

    TN-HDB-0003-HANA SQL参考及Oracle对照-v0.6.docx

    - 在Oracle和HANA中，都可以使用`START WITH...CONNECT BY PRIOR`来执行递归查询，以构建树形结构的数据集。例如，查询具有父子关系的部门结构。两者在语法上基本一致，但HANA不支持Oracle中的`level`和`siblings`...

Magicbox
Global site tag (gtag.js) - Google Analytics