- 浏览: 200192 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
zoutuo1986:
翻过的帖子,这篇讲配置最细
solr -
18612536750:
Struts2 标签,取得Map的某一个key对应value值 -
zhangzhihua5:
solr -
xiaguangme:
“capacity才是真正的Entry数组的大小,即真实的En ...
源码阅读之Map和Set -
qiyang199132:
我擦民工 都没人来。。 我来捧场了
JSP 防止重复提交 防止重复刷新 防止后退问题以及处理方式
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-11-07 15:13 1517转自(http://www.cnblogs.com ... -
Hibernate操作Oarcle中Clob、Blob字段小结
2011-08-24 11:19 1162最近常碰到有人问如何运用Hibernate操作Oracle中的 ... -
数据库优化基本策略(转载)
2011-07-15 08:58 8751.选择正确类型的sql。 有时候你想要动态生成sql,却发现 ... -
oracle 查询数据null值排序
2011-07-11 18:16 1226sqlserver 认为 null 最小。 升序排列:nul ... -
不错的sql
2011-07-04 09:05 9801,说明:复制表(源表名a,新表名b) 法一:select ... -
oracle CLOB和BLOB
2011-06-25 21:18 1406一、区别和定义 LONG: 可变长的字符串数据, ... -
Oracle中TO_DATE格式
2011-05-19 17:34 852TO_DATE格式(以时间:2007-11-02 13:4 ... -
ORACLE错误一览表,方便大家查询!
2011-05-03 17:50 1503ORACLE错误一览表,方便 ... -
oracle 锁表
2011-04-26 16:37 1141查看锁表进程SQL语句1: select sess.sid, ... -
ORACLE 创建索引
2011-03-18 14:42 1038Oracle的分区技术在某些条件下可以极大的提高查询的性能,所 ... -
PL/SQL Developer 常用技巧
2011-03-18 14:10 8491、PL/SQL Developer记住登 ... -
java调用oracle存储过程
2010-12-17 11:14 1173在大型数据库系统中,有两个很重要作用的功能,那就是存储过程和触 ...
相关推荐
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 ...