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>
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>
分享到：
发表评论
-
Oracle关于时间/日期的操作
2012-04-09 23:12 8241.日期时间间隔操作 当前时间减去7分钟的时间 s ... -
oracle 数据库增量备份
2012-03-16 20:03 1109EXP和IMP是Oracle提供的一种逻辑备份工具。逻辑备份创 ... -
Oracle数据导入导出imp/exp
2012-02-09 17:38 583Oracle数据导入导出imp/exp 功能：Oracle数 ... -
性能监控SQL语句
2011-12-23 13:51 793分析表 analyze table tablename co ... -
常用SQL语句
2011-12-23 13:50 942查找数据库中所有字段 以对应的表 select C.colu ... -
安装到最后的两个脚本作用
2011-12-06 18:32 1006在linux下安装Oralce的时候，最后会让你以root的身 ... -
ORACLE中一个字符占多少字节
2011-12-01 22:07 3817在oracle中一个字符特别是中文占几个字节是不同的。 比如 ... -
PL/SQL流程控制
2011-12-01 14:55 767------------------------------- ... -
提高SQL效率
2011-12-01 00:13 990下面就某些SQL 语句的 where 子句编写中需要注意 ... -
“exists”和“in”的效率问题
2011-11-30 23:49 978有两个简单例子，以说明 “exists”和“in”的效率问题 ... -
Oracle建立全文索引详解
2011-11-30 20:42 14441.全文检索和普通检索的区别 不使用Oracle text功 ... -
全文检索CLOB
2011-11-30 12:00 956建议使用全文检索(FULL TEXT SEARCH) ... -
“Oracle”数据库的“周数计算”
2011-11-16 13:25 943——日期计算，算第n周的第一天及最后一天是几号。 by key ... -
Oracle中TO_DATE格式
2011-11-16 13:17 797TO_DATE格式(以时间:2007-11-02 13:4 ... -
Oracle 索引 详解
2011-11-15 13:00 25992Oracle 索引 详解 ... -
Oracle SQL中的IN 和 EXSITS区别总结
2011-11-15 11:06 972IN 确定给定的值是否与子查询或列表中的值相匹配。 EX ... -
Oracle 字符集的查看和修改
2011-11-14 23:09 899一、什么是Oracle字符集 ... -
Oracle Flashback技术总结
2011-11-14 01:06 931Flashback 技术是以Undo segment中的内容为 ... -
oracle日志归档模式改变
2011-11-14 00:15 1061在Oracle数据库中，主要 ... -
如何最大程度的 把表空间里面的数据给抢救出来
2011-11-03 16:18 1221今天一朋友问到一个比较有意识的问题： 如果一个表空间，其中一个 ...
相关推荐
oracle中的数查询，介绍的详细，有例子。
主要给大家介绍了关于Oracle递归查询start with connect by prior、的相关资料，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面来一起学习学习吧
本文章详细介绍了Oracle中connect by...start with...的用法。
NULL 博文链接：https://yunqiang-zhang-hotmail-com.iteye.com/blog/1312354
Oracle 递归查询， start with connect by prior 用法 find_in_set 函数 concat，concat_ws，group_concat 函数 MySQL 自定义函数 手动实现 MySQL 递归查询 Oracle 递归查询 在 Oracle 中是通过 start ...
Oracle_start_with_connect_by_prior_用法[文].pdf
oracle中 connect by prior 递归算法 Oracle中start with...connect by prior子句用法 connect by 是结构化查询中用到的
Oracle 数据库树形结构用法总结，例如SYS_CONNECT_BY_PATH 、START WITH . . . CONNECT BY . . .等具体语法介绍
下面按类别列出一些ORACLE用户常用数据字典的查询使用方法。 1、用户 查看当前用户的缺省表空间 SQL>select username,default_tablespace from user_users; 查看当前用户的角色 SQL>select * from user_...
如下所示 忽略以#开头的注释部分 : cams:/u01/app/oracle/product/8.1.7:Y 其中 cams 为实例 ID /u01/app/oracle/product/8.1.7为 ORACLE_HOME目 录 Y表示允许使用 dbstart和 dbshut 启动和关闭该实例...
2.最好学习过一门别的数据库(sql server，mysql ， access) 教程推荐:oracle使用教程， 深入浅出oracle 记住:欲速则不达，做任何事情要遵循他的规律，循序渐进，信心很重要 成为一个oracle高手过程:理解小知识点->...
前言 对于数据库中的树形结构数据，如...oracle实现递归查询的话，就可以使用start with … connect by connect by递归查询基本语法是： select 1 from 表格 start with … connect by prior id = pId start with
CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。 注意： 对于普通用户：授予connect, resource权限。 对于DBA管理用户：授予connect，resource, dba权限。 授予系统...
本文介绍Oracle中使用START WITH...CONNECT BY PRIOR子句实现递归查询树形结构的方法，小伙伴们可以参考一下。
[start with START_DATE next NEXT_DATE] as QUERY; create snapshot snapshot_to_study as select * from TABLE_NAME@to_study; 创建角色 create role aa identified by aaa; 授权 grant create snapshot,...
下面按类别列出一些ORACLE用户常用数据字典的查询使用方法。 1、用户 查看当前用户的缺省表空间 SQL>select username,default_tablespace from user_users; 查看当前用户的角色 SQL>select * from user_...
connect by t.parentcode = prior t.organcode; //递归查询子节点 1.8.7 union 和 union all 1.8.7.1 语法 select * from dual union all select * from dual 1.8.7.2 说明 union 会对查询数据进行去重并排序，union...
1 性能优化 1.1 避免频繁 commit，尤其...说明：除非是单据的单号，要求必须是唯一，并且依据流水号不可以跳号，不然在大量交易的表格中，不在乎跳耗时，要取得唯一的Primary Key 建议使用Oracle Sequence这样速度会较...
TNS:listener Could Not Resolve SID Given in Connect Descriptor 484 Insufficient Privileges During Grants484 Escape Character in Your Statement--Invalid Character 485 Cannot Create Operating System ...