跟我一起学ORACLE开发之SQL篇

一，基础概述

1，oracle的锁定与事务

仅仅来看下oracle的一些锁和事务的相关视图或参数，基本原理见基础篇的事务原理一节。（本节内容可以参考9i或10g的<reference>）

V$lock:

这个视图列出了所有数据库中的所有锁（locks）和闩（latches）和所有在一个lock或者latch上的请求。简述如下：

Addr

处于lock状态的对象的地址

Kaddr

lock的地址

Sid

session id

Type

用户或者系统锁的类型，用户锁例如：TM-DML入队，

TX-事务入队，UL-用户提供的（User supplied）

系统类型的锁定诸如：SMON指派恢复进程，临时段入队，

分配新的块入队，redo线程全局入队，

写redo log的入队（media recovery）等等

例如：

SQL> select distinct type from v$lock;

TYPE

----

与dba_lock中的lock_type对应，可以得到：

SQL> select a.ADDR, a.KADDR, a.TYPE, b.lock_type, a.ID1, a.LMODE

2 from v$lock a, dba_locks b

3 where a.SID = b.session_id;

ADDR KADDR TYPE LOCK_TYPE ID1 LMODE

-------- -------- ---- -------------------------- ---------- ----------

682BE818 682BE828 MR Media Recovery 201 4

682BE7CC 682BE7DC MR Media Recovery 10 4

682BE780 682BE790 MR Media Recovery 9 4

682BE734 682BE744 MR Media Recovery 8 4

682BE6E8 682BE6F8 MR Media Recovery 7 4

682BE69C 682BE6AC MR Media Recovery 6 4

682BE650 682BE660 MR Media Recovery 5 4

682BE604 682BE614 MR Media Recovery 4 4

682BE5B8 682BE5C8 MR Media Recovery 3 4

682BE56C 682BE57C MR Media Recovery 2 4

682BE520 682BE530 MR Media Recovery 1 4

682BE43C 682BE44C RT Redo Thread 1 6

682BE30C 682BE31C XR XR 4 1

682BE488 682BE498 TS Temp Segment 2 3

14 rows selected

再来看看一般开发中涉及到比较重要的两种锁定类型：DML锁定和DDL锁定

DML锁定

DML锁定简单讲，用于保证一行在一段时间只有一个用户进行修改，并且其他人不能够删除这个表或者修改这个表的结构。Dml锁定指定了数据行的锁定，或者数据表的锁定，也即行锁或者表锁。

TX（事务）锁定

从一个事务开始，一直到该事务commit或者rollback，该事务拥有一个TX锁定，它是一个排队机制，使得其他会话等待这个事务的完成。简单的看一个TX例子：

SQL> update scott.dept a set a.dname = 'dname' where a.deptno = 10;

1 row updated

SQL>

SQL> select /*+ rule*/

2 a.TYPE,

3 c.lock_type lt,

4 a.ID1,

5 c.lock_id1 l_id1,

6 a.ID2,

7 c.lock_id2 l_id2,

8 a.LMODE

9 from v$lock a, (select sid from v$mystat where rownum = 1) b, dba_lock c

10 where a.SID = b.SID

11 and a.SID = c.session_id

12 and a.ID1 = c.lock_id1

13 and a.ID2 = c.lock_id2;

TYPE LT ID1 L_ID1 ID2 L_ID2 LMODE

---- -------------------------- ---------- --------- ---------- ----------

TM DML 30137 30137 0 0 3

TX Transaction 196647 196647 2527 2527 6

注意到上面的v$lock中id1,id2(或者dba_lock中的lock_id1,lock_id2）字段，

对于tm锁，id1就是object_id，id2为0，对于tx锁id1是以十进制数值表示事务占用的回滚段号和事务solt number,具体算法就是把id1除以2的16次方，余数就是solt number，结果的取整数值是回滚段号（参见asktom），那么改进一下上述查询：

SQL> select /*+ rule*/

2 a.TYPE,

3 c.lock_type lt,

4 trunc(a.ID1/power(2,16)) rollback_seq,

5 mod(a.ID1,power(2,16)) slot,

6 c.lock_id1 l_id1,

7 a.ID2 seq,

8 c.lock_id2 l_id2,

9 a.LMODE

10 from v$lock a, (select sid from v$mystat where rownum = 1) b, dba_lock c

11 where a.SID = b.SID

12 and a.SID = c.session_id

13 and a.ID1 = c.lock_id1

14 and a.TYPE = 'TX';

TYPE LT ROLLBACK_SEQ SLOT L_ID1 SEQ L_ID2 LMODE

-----------------------------------------------------------------------

TX Transaction 3 39 196647 2527 2527 6

再看看事务和锁定对象的视图，通过锁定对象的地址和session id进行关联，再改进一下上述查询：

SQL> select /*+ rule*/

2 a.TYPE,

3 trunc(a.ID1/power(2,16)) rollback_seq,

4 mod(a.ID1,power(2,16)) slot,

5 a.ID2 seq,

6 a.LMODE,

7 d.XIDUSN,

8 d.XIDSLOT,

9 d.XIDSQN,

10 e.OBJECT_ID,

11 e.LOCKED_MODE

12 from v$lock a, (select sid from v$mystat where rownum = 1) b, dba_lock c,

13 v$transaction d,v$locked_object e

14 where a.SID = b.SID

15 and a.SID = c.session_id

16 and a.ID1 = c.lock_id1

17 and a.TYPE = 'TX'

18 and a.SID = e.SESSION_ID

19 and a.ADDR = d.ADDR;

TYPE ROLLBACK_SEQ SLOT SEQ LMODE XIDUSN XIDSLOT XIDSQN OBJECT_ID LOCKED_MODE

---- ------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----------

TX 3 39 2527 6 3 39 2527 30137 3

上述（ROLLBACK_SEQ，SLOT，SEQ）分别对应事务的（XIDUSN，XIDSLOT，XIDSQN）这就是事务id，lmode便是锁定的模式，见稍后的详述。

那么如果在一个另外的session中再执行对scott.dept中的相同的行进行修改，这样将会看到tx锁是如何工作的。按照此要求改动后的查询（本例中第一个事务和第二个事务id分别为10，12）：

SQL> select /*+ rule*/

2 a.SID,

3 a.BLOCK,

4 a.REQUEST,

5 a.LMODE,

6 d.XIDUSN,

7 d.XIDSLOT,

8 d.XIDSQN,

9 e.OBJECT_ID,

10 e.LOCKED_MODE

11 from v$lock a,

12 v$transaction d,v$locked_object e

13 where a.SID in (10,12)

14 and a.TYPE = 'TX'

15 and a.SID = e.SESSION_ID

16 and a.ADDR = d.ADDR(+)/*因为session 12的事务被10阻塞了，故现在看到的v$transaction中12的事务并没有开始。 */

17 ;

SID BLOCK REQUEST LMODE XIDUSN XIDSLOT XIDSQN OBJECT_ID LOCKED_MODE

---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----------

10 1 0 6 3 39 2527 30137 3

12 0 6 0 30137 3

对比一下session 10和session 12 的属性值：

10的block=1代表了阻塞了其他，request是请求的锁的类型，其值代表的意义与LMODE一样，为0说明没有请求，Lmode则是拥有6的锁定，事务id为（3 ，39，2527），请求或者锁定的对象id为30137，locked_mode说明事务请求或者锁定已经完成的锁定类型，为3，即是完成了对行的修改。（具体的LOMODE见后续详细说明）。

而12的block=0，说明了当前没有阻塞其他，request=6说明请求了一个6的锁定，当前的lmode为0，还没有获得锁定，事务并没有开始，故而还没有事务id。

接下来让10提交，看看又会发生什么：

SQL> select sid from v$mystat where rownum = 1;

SID

----------

SQL> commit;

Commit complete

查看session 12，发现已经完成了update语句：

SQL> update scott.dept a set a.dname = 'dname' where a.deptno = 10;

1 row updated

SQL>

再看看前面的查询语句：

SQL> select /*+ rule*/

2 a.SID,

3 a.BLOCK,

4 a.REQUEST,

5 a.LMODE,

6 d.XIDUSN,

7 d.XIDSLOT,

8 d.XIDSQN,

9 e.OBJECT_ID,

10 e.LOCKED_MODE

11 from v$lock a,

12 v$transaction d,v$locked_object e

13 where a.SID in (10,12)

14 and a.TYPE = 'TX'

15 and a.SID = e.SESSION_ID

16 and a.ADDR = d.ADDR(+)/*现在session 10已经commit了 */

17 ;

SID BLOCK REQUEST LMODE XIDUSN XIDSLOT XIDSQN OBJECT_ID LOCKED_MODE

---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----------

12 0 0 6 9 42 2544 30137 3

可以很清楚的看到变化，session 12不在被block，请求也完成了，获得了6的锁定，事务id已经生成，而session 10的事务已经完成，已经释放掉了锁定和资源，v$transaction和v$lock中已经没有相应记录。

TM（dml入队）锁定

Tm锁用来保证修改表数据的时候，表结构不被修改。例如：

在一个session中(10)更新表scott.dept，而在另外一个session中（12）中修改表结构，则会出现下面的情况：

SQL> alter table scott.dept add (add_col number);

alter table scott.dept add (add_col number)

ORA-00054: resource busy and acquire with NOWAIT specified.

仍然继续修改在讲述TX锁时使用的查询：

SQL> select /*+ rule*/

2 a.SID,

3 a.ID1,

4 a.TYPE,

5 a.BLOCK,

6 a.REQUEST,

7 a.LMODE,

8 d.XIDUSN,

9 d.XIDSLOT,

10 d.XIDSQN,

11 e.OBJECT_ID,

12 e.LOCKED_MODE

13 from v$lock a,

14 v$transaction d,v$locked_object e

15 where a.SID in (10,12)

16 and a.TYPE in ( 'TM','TX')

17 and a.SID = e.SESSION_ID

18 and a.ADDR = d.ADDR(+)/*现在session 10已经commit了 */

19 ;

SID ID1 TYPE BLOCK REQUEST LMODE XIDUSN XIDSLOT XIDSQN OBJECT_ID LOCKED_MODE

---------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----------

10 30137 TM 0 0 3 30137 3

10 131097 TX 0 0 6 2 25 2545 30137 3

注意到tm锁的id1就是tx锁的对象id。

DDL锁定

主要有三种：

独占的ddl锁定

共享ddl锁定

Breakable parse lock

像前面示例中的：

alter table scott.dept add (add_col number);

就是独占的ddl锁。

DBA_DDL_LOCKS视图

Session_id session id

Owner owner of the lock

Name name of the lock

Type

锁定类型，ddl的锁定类型：

Cursor ，Table/Procedure/Type，Body，Trigger，Index，Cluster，Java Source ，Java Resource，Java Data

Mode_held和Mode_request分别是Lock mode和Lock request type，包括：

None

Null

Exclusive

通常在重新编译包或者过程的时候，实际已经有用户在运行，则编译会被挂起，或者在grant某个正在执行的过程时，也会发生这样的情况，查询DBA_DDL_LOCKS视图可以找到问题的具体所在。

具体将专门在附录中分析锁定相关的问题集。

LMODE（锁定模式）和REQUEST（请求的锁定模式）

会话拥有的锁的模式：

0：none

1：null 空

2：row-s 行共享(RS):共享表锁，用于行的查询

3：row-x 行专用（RX）：用于行的修改，通常表明持有该锁的事务已经完成了对行的修改

4：share 共享锁（S）：阻止其他dml操作

5：s/row-x 共享行专用（SRX）：阻止其他事务操作

6：exclusive 专用（X）：独立访问使用，是表锁的最具限制性的形式。

Block

当前的锁是否阻塞了其他的锁。

锁定相关的几个参数：

SQL> show parameter DML_LOCKS;

NAME TYPE VALUE

------------------------------------ -----------

dml_locks integer 748

这个参数用来规定TM锁的总数，要获得表锁需要TM锁。

SQL> show parameter ROW_LOCKING;

NAME TYPE VALUE

------------------------------------ -----------

row_locking string always

这个参数指定行锁的方式，ROW_LOCKING该参数指定行封锁方式。若设置为ALWAYS，则在修改表时只实施行封锁。若设置为INTENT时，则行封锁只适用于SELECT FOR UPDATE，而在修改时实施表封锁。

v$transaction

ADDR Address of the transaction state object 事务对象地址

XIDUSN Undo segment number 回滚段编号

XIDSLOT Slot number 槽（slot）编号

XIDSQN Sequence number 序列编号

UBAFIL Undo block address (UBA) filenum 回滚块地址在的文件序号

UBABLK UBA block number uba块数量

UBASQN UBA sequence number uba序列编号

UBAREC UBA record number uba记录数

STATUS Status 事务状态

START_TIME Start time (wall clock)

START_SCNB Start system change number (SCN) base

START_SCNW Start SCN wrap

START_UEXT Start extent number

START_UBAFIL Start UBA file number

START_UBABLK Start UBA block number

START_UBASQN Start UBA sequence number

START_UBAREC Start UBA record number

SES_ADDR User session object address 用户会话对象地址

FLAG Flag

SPACE YES if a space transaction 是否是空间事务

RECURSIVE YES if a recursive transaction 是否是递归事务

NOUNDO YES if a no undo transaction 是否是非撤销事务

PTX YES if parallel transaction 是否是并行事务

NAME Name of a named transaction 是否命名事务

PRV_XIDUSN Previous transaction undo segment number

PRV_XIDSLT Previous transaction slot number

PRV_XIDSQN Previous transaction sequence number

PTX_XIDUSN Rollback segment number of the parent XID

PTX_XIDSLT Slot number of the parent XID

PTX_XIDSQN Sequence number of the parent XID

DSCN-B Dependent SCN base

DSCN-W Dependent SCN wrap

USED_UBLK Number of undo blocks used 使用的撤销块数

USED_UREC Number of undo records used 书用的撤销记录数

LOG_IO Logical I/O 逻辑io

PHY_IO Physical I/O 物理io

CR_GET Consistent gets 一致性读

CR_CHANGE Consistent changes 一致性更改
2，undo和redo

Undo

Oracle将所有的数据更改记录在undo，这些记录信息可以使oracle使用rollback来撤销更改操作。Undo的机制主要有两个目的：一是用来允许读不能阻塞写入，写入不能阻塞读，二是可以使事务能够rollback（回滚）。

事务能够产生undo（回滚），且产生的undo记录数量可以在动态性能视图V$transaction中得到。用一个例子来看看undo的产生：

SQL> set autocommit off;

SQL> insert into t_test_undo values (2,'asd');

1 row created.

SQL>

SQL> select used_urec from v$session s,v$transaction t where

2 s.audsid = sys_context('userenv','sessionid') and

3 s.taddr = t.addr;

USED_UREC

----------

上面的操作产生了1条undo记录，再看看一次插入多条记录的情况：

SQL> insert into t_test_undo

2 select rownum + 50000, object_name from all_objects where rownum < 51;

50 rows created.

SQL> SQL> select used_urec from v$session s, v$transaction t

2 where s.audsid=sys_context('userenv', 'sessionid') and

3 s.taddr = t.addr;

USED_UREC

----------

上面的语句也只产生了一条undo。

然后看看update的情形：

SQL> update t_test_undo a set a.b = 'asdsd' where a.a = 1;

1 row updated.

SQL> select used_urec from v$session s, v$transaction t

2 where s.audsid=sys_context('userenv', 'sessionid') and

3 s.taddr = t.addr;

USED_UREC

----------

这个update语句也产生了1个undo记录，再看看delete的情形：

SQL> delete from t_test_undo a where a.a <10;

2 rows deleted.

SQL> select used_urec from v$session s, v$transaction t

2 where s.audsid=sys_context('userenv', 'sessionid') and

3 s.taddr = t.addr;

USED_UREC

----------

可以看出，delete的每一条记录都产生了一个undo记录。

由此可以大概对比一下不同的语句产生的undo大小。

SQL>

SQL> insert into t_test_undo values (10, 'asd');

1 row created.

SQL> select a.XIDUSN, a.UBAREC, a.UBABLK, a.USED_UBLK, a.USED_UREC

2 from v$transaction a;

XIDUSN UBAREC UBABLK USED_UBLK USED_UREC

---------- ---------- ---------- ---------- ----------

9 17 336 1 1

SQL> commit;

Commit complete.

SQL> select a.XIDUSN, a.UBAREC, a.UBABLK, a.USED_UBLK, a.USED_UREC

2 from v$transaction a;

no rows selected

SQL> update t_test_undo a set a.b = 'qwe' where a.a = 10;

1 row updated.

SQL> select a.XIDUSN, a.UBAREC, a.UBABLK, a.USED_UBLK, a.USED_UREC

2 from v$transaction a;

XIDUSN UBAREC UBABLK USED_UBLK USED_UREC

---------- ---------- ---------- ---------- ----------

7 6 2701 1 1

SQL> commit;

Commit complete.

SQL> delete from t_test_undo a where a.a = 10;

1 row deleted.

SQL> select a.XIDUSN, a.UBAREC, a.UBABLK, a.USED_UBLK, a.USED_UREC

2 from v$transaction a;

XIDUSN UBAREC UBABLK USED_UBLK USED_UREC

---------- ---------- ---------- ---------- ----------

3 25 1057 1 1

SQL> commit;

Commit complete.

SQL> insert into t_test_undo values (10, 'asd');

1 row created.

SQL> update t_test_undo a set a.b = 'qwe' where a.a = 10;

1 row updated.

SQL> delete from t_test_undo a where a.a = 10;

1 row deleted.

SQL> select a.XIDUSN, a.UBAREC, a.UBABLK, a.USED_UBLK, a.USED_UREC

2 from v$transaction a;

XIDUSN UBAREC UBABLK USED_UBLK USED_UREC

---------- ---------- ---------- ---------- ----------

2 38 10021 1 3

SQL>

一般来说，insert产生了最少的undo，因为insert行的反操作是delete行，只记录插入记录的rowid，delete因为需要把整行的前映像记录到undo，所以产生最多的undo，update需要记录行中被更新的字段部分的前映像，因此一般来说介于insert和delete之间。

回滚段可以说是用来保持数据变化前映像而提供一致性读和保证事务完整性的一段存储区域。当一个事务开始的时候，首先把变化前的数据和变化后的数据先写入日志缓冲区，然后吧变化前的数据写入回滚段，最后才在数据缓冲区中修改。

Undo segment头部包含记录了当前事务使用的undo segment信息的一张表。一系列事务只使用一个undo segment存储所有数据。许多并发的事务可以使用同一个undo segment。

再来看看与undo相关的ora-01555：

SQL> host oerr ora 1555

01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"

// *Cause: rollback records needed by a reader for consistent read are

// overwritten by other writers

// *Action: If in Automatic Undo Management mode, increase undo_retention

// setting. Otherwise, use larger rollback segments

在相对稳定的数据库中，出现这个错误大多数时候是代码需要调整，或者由于业务需要在某一时段进行大批量的业务处理，而对一些较大的查询产生了影响。涉及到的undo的优化或者sql的优化在性能调整一章中详述。

Oracle的segment信息可以在dba_segments或者sys_dba_segs中查看，从下面的输出信息中可以看到几个比较重要的段对象，如table，index，rollback。

SQL> select distinct segment_type from sys_dba_segs;

SEGMENT_TYPE

------------------

LOBINDEX

INDEX PARTITION

TABLE PARTITION

NESTED TABLE

ROLLBACK

LOB PARTITION

LOBSEGMENT

INDEX

TABLE

CLUSTER

TYPE2 UNDO

11 rows selected.

Redo

与undo不同，undo是产生使操作撤销的信息，而redo是产生使操作重做的信息。Oracle的redo有两种：online redo log(联机),archivelog(归档)。

Lgwr循环的写入联机日志，当前日志日满后，lgwr写下一个日志。Lgwr总是在一下条件之一满足之时将缓冲重做日志写入磁盘：

1，每三秒；2，每当满1/3或1MB；3碰到任何事务commit；

在事务commit或者rollback之前往往已经完成了一下工作：

已经在sga中产生了回滚段记录，用于撤销

已经在sga中产生了修改数据块

已经在sga中产生了回滚和修改的重做，是的以上2个操作可以重做

根据lgwr的刷新特点，一部分数据可能已经刷新到了磁盘

已经获得了所有的锁定

当commit时，要做的工作如下：

为事务产生scn，lgwr写磁盘，释放锁定，访问修改事务块，并清除之。

当rollback时，要做的工作如下：

使用回滚段的数据来撤销修改，释放锁定。

同样的，dml对redo产生的影响也是能够测定的。

下列语句依次执行：

select a.SID, a.VALUE

from v$mystat a, v$statname b

where a.STATISTIC# = b.STATISTIC#

and b.NAME like 'redo_size';

insert into t_test_undo values (12, '123');

commit;

select a.SID, a.VALUE

from v$mystat a, v$statname b

where a.STATISTIC# = b.STATISTIC#

and b.NAME like 'redo_size';

update t_test_undo a set a.b = '321' where a = 12;

commit;

select a.SID, a.VALUE

from v$mystat a, v$statname b

where a.STATISTIC# = b.STATISTIC#

and b.NAME like 'redo_size';

delete t_test_undo where a.a = 12;

commit;

select a.SID, a.VALUE

from v$mystat a, v$statname b

where a.STATISTIC# = b.STATISTIC#

and b.NAME like 'redo_size';

得到的redo size数值如下表：

操作	Sid	Redo size
初始	144	0
Insert 一行	144	548
Update 一行	144	1140
Delete 一行	144	1732

同样可以测试一次插入多行，更新多行和删除多行对redo的产生量，并且可以测试逐条提交和一次提交对redo的产生量。如果不结合起来看undo和redo的影响而来调整应用，很多时候结果往往是不确定的。值得注意的是触发器的不同类型对redo的产生也有影响，比如对update使用after触发器，将不会影响重做。开发过程中，可以大致了解一下重做的数量：估计事务的大小，需要修改的数据量，提交的频率，dml类型占比（考虑到update产生大约2倍的重做，insert和delete大约为1倍）。而以上的表中似乎看不到不同dml对redo的区别，这是因为b的字段类型为varchar2(20),本身的修改量很小，如果吧字段修改为char(2000),则数字规律很明显，有关字符类型的细节在数据库设计一章节中详述。

另外redo是备份和恢复的一个支撑原理。有关联机日志和归档日志的管理和使用在备份恢复一章中详解。

3，DDL和DML

Oracle的DDL主要有create，alter，drop，truncate等，DML主要有insert，update，select，delete，merge等。Oracle的DDL一个特点是自动提交，这与其他的一些数据库DDL不同。

对于oracle，ddl执行过程可以用一段伪码示意如下：

begin

COMMIT;

do the ddl;

COMMIT;

exception

when others then

ROLLBACK;

RAISE;

end;

也即在执行ddl之前有一个提交动作，执行完成之后也有一个提交动作，而执行失败时，第一个提交动作已经完成，所以会提交当前事务。用一个例子来看：

SQL> create table t_test as select * from dual;

Table created

SQL> update t_test a set a.dummy = 'z';

1 row updated

SQL> create table t_test as select * from dual;

create table t_test as select * from dual

ORA-00955: 名称已由现有对象使用

SQL> rollback;

Rollback complete

SQL> select * from t_test;

DUMMY

-----

对于dml来说也可以设置为AUTOCOMMIT，比如sqlplus中的set autocommit：

SQL> set autocommit on;

SQL> update scott.t_test set dummy = 'v';

1 row updated

Commit complete

或者java的jdbc连接中的SetAutoCommit(boolean isCommit)：

使用java.sql.Connection或者oracle.jdbc.OracleConnection建立的一个新的连接默认为auto-commit模式，代码如下：

// Connect to the database

// You can put a database hostname after the @ sign in the connection URL.

OracleDataSource ods = new OracleDataSource();

ods.setURL("jdbc:oracle:oci:@");//或者也可以使用瘦客户端连接(thin)

ods.setUser("scott");

ods.setPassword("tiger");

Connection conn = ods.getConnection();

// It's faster when auto commit is off

conn.setAutoCommit (false); //一般来讲这里关闭自动提交并不是为了更快，而是为了更好的控制提交的时机。

// Create a Statement

Statement stmt = conn.createStatement ();

...

如果对DDL进行trace，会发现ddl其实就是一系列的dml，这些dml操作了数据字典表。例子如下：

SQL> alter session set events '10046 trace name context forever , level 12';

Session altered.

SQL> create table xxxx (a number);

Table created.

使用tkprof格式化trace文件，可以看到：

Trace file: test1_ora_7449.trc

Sort options: default

********************************************************************************

count = number of times OCI procedure was executed

cpu = cpu time in seconds executing

elapsed = elapsed time in seconds executing

disk = number of physical reads of buffers from disk

query = number of buffers gotten for consistent read

current = number of buffers gotten in current mode (usually for update)

rows = number of rows processed by the fetch or execute call

********************************************************************************

create table xxxx (a number)

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.01 0.02 0 0 0 0

Execute 1 0.03 0.03 0 1 8 0

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 2 0.04 0.06 0 1 8 0

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

SQL*Net message to client 2 0.00 0.00

SQL*Net message from client 1 33.73 33.73

********************************************************************************

select dummy

from

dual where ora_dict_obj_type = 'SYNONYM' AND ora_dict_obj_owner = 'PUBLIC'

.....(以下省略)

看一个具体的DDL create table的完整语句：

SQL> select dbms_metadata.get_ddl('TABLE','T_TEST_UNDO','SYS') from dual;

CREATE TABLE "SYS"."T_TEST_UNDO"

( "A" NUMBER,

"B" VARCHAR2(20)

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "SYSTEM"

注意到以上代码中的一些参数，这些被称作common sql ddl clauses,分别包括了：

Allocate extent clause

用来分配对象的extent，可以指定size子句，指定数据文件（datafile），指定确切的instance

Constraint

用来定义完整性约束，比如not null ,unique,primary key,foreign key,check等，通常可以通过inline或者out-of-line 两种方式来定义约束（not null除外，只能使用inline），比如：

create table t_test_id(

id number unique,--inline的unique约束

name varchar2(20) not null

);

Create table t_test_undo_1(

Id number not null,--not null 只能inline

S_id number ,

Name varchar2(20),

Primary key (s_id),--

constraint fk__t_test_ud1 foreign key (Id)

references t_test_id (ID)

);

或者：

Create table t_test_undo_1(

Id number not null,--

S_id number ,

Name varchar2(20)

);

alter table t_test_undo_1 add constraint pk_sid Primary key (s_id);

alter table t_test_undo_1 add constraint fk__t_test_ud1 foreign key (Id)

references t_test_id (ID);

Deallocate unused clause

可以显示的释放在一个数据库段对象结束时未使用的空间，使得空间可以被其他段对象使用。要注意的是不能同时在一个语句中设置deallocate_unused_clause和allocate_extent_clause。

Logging clause

在创建数据库对象时，这个参数设置为logging或者nologging，用来控制是否记录redo log，要注意的是并不能完全nologging，并且在不同的数据库模式下，影响也不同（archived和unarchived）。DDL中允许nologging模式的语句主要有下列：

Create table ... as select

alter table ... move

alter table ... xxx partition

Create index

Alter index ... xxx

Physical attributes clause

可以用来设置table,cluster,index或者materialized的pctfree,pctused,和initans参数。这些参数的原理和作用详见oracle管理基础一章。

Storage clause

Oracle应该如何储存数据库对象的设置。主要的参数如下：

INITIAL 对象的第一个extent的大小

NEXT 下一扩展的extent大小

MINEXTENTS 最小的extents数量

MAXEXTENTS 最大的extents数量

PCTINCREASE 指定extent较前一个的增长的百分比。

需要注意的是，pctincreate值大于0会造成extent不一致，如果next较多的话，可能会造成空间利用率低下，虽然smon会自动合并表空间中的碎片，但是这些空间也不能很好的利用。（oracle建议设置为0，可以减少碎片和避免产生非常大的临时段，而回滚段不能设置pctincrease，默认是0）

FREELISTS 一个空闲链表组内的空闲链表数

FREELIST GROUPS 空闲链表组的数量

以上两个参数只能在create table,cluster,index时使用

BUFFER_POOL 可以用来为对象设置一个默认的缓冲池，同样的，回滚段不能设置。

有关存储参数的详述见oracle管理基础一章。

再看看DML insert的大致语法：

Insert {direct/parallel/append}

{all/first} into

{schema}[table/view/materialized view]{@dblink}

{partition/subpartition}

{when ..then}

[values/select]

{returning into ..}

{log errors into }

{reject limit {integer/unlmited}}

涉及到的clause有插入路径，方式，schema，是否使用dblink，指定分区或者子分区，插入条件，返回值，错误日志记录，拒绝的限制等等，具体可以参见oracle官方文档《sql reference》。在oracle数据库特性一章中单独对分区，直接路径插入，物化视图等进行了详述。

由此也可以看到oracle的dml扩展相当强，可以满足很多数据操纵的需求，关注这些oracle提供的特性会使得问题的解决有更多的选择。

二，条件和表达式

1，数据类型

Varchar2，nvarchar2，char的简单比较

Varchar2与nvarchar2都是可变长度的字符类型，不同的是nvarchar2与国家字符集相关，而char是固定长度的类型。看看他们之间的区别：

create table t_test_chr (

type_char char(4),

type_varchar2 varchar2(20),

type_nvarchar2 nvarchar2(20)

);

insert into t_test_chr values ('aaaa','aaaa','aaaa');

insert into t_test_chr values ('我','我考','我考');

SQL> select length(type_char),length(type_varchar2),length(type_nvarchar2) from t_test_chr;

LENGTH(TYPE_CHAR) LENGTH(TYPE_VARCHAR2) LENGTH(TYPE_NVARCHAR2)

----------------- --------------------- ----------------------

4 4 4

2 2 2

可以看到对于字符来说三者的长度相同，但对于汉字来说就有区别了，而且不同的字符集也存在差异，一个很常见的问题是汉字乱码问题，这是由于数据库的字符集设置与客户端的差异造成的，一般对于开发人员来说这个不用考虑。另外要注意的，char可以不设置长度，默认为1，而varchar2需要设置长度。例如：

SQL> create table t_testchar (v_char char,v_var varchar2(10));

Table created.

有关字符集的问题详见附录一章。有关在设计时数据类型的选择和特殊字符处理方法在数据库设计一章中详述，有关xmltype在oracle xml一章中详述。有关oracle的基本数据类型，可以参见oracle官方文档的《sql reference》的datatypes一节。

2，数据类型的比较规则

主要看几个常见且比较重要的例子。

Char字符的比较，是比较ascii值的大小：

SQL> select 1 from dual where 'ab' >'ac';

----------

SQL> select 1 from dual where 'ab' <'ac';

----------

SQL> select 1 from dual where 'a' <'b';

----------

字符与数值的比较，有一个隐身转换的过程：

SQL> select 1 from dual where '32' = 32;

----------

但是下面这样的情况并不会将32隐身转换为’32’：

SQL> select 1 from dual where 'a' = 32;

select 1 from dual where 'a' = 32

ORA-01722: invalid number

之所以这里特意提到隐式转换，是因为，往往在sql条件中，由于隐式转换可能会禁用相关的索引，这个也是sql性能的一大隐患，可以简单看一个例子，注意不能使用上面创建的表，drop后新建，插入下列数据：

Insert into t_test_chr values ('1111', '1111', '1111');

SQL> create index t_test_chr_id2 on t_test_chr(type_varchar2);

Index created

SQL> select * from t_test_chr a where a.type_varchar2='aaaa';

Execution Plan

----------------------------------------------------------

Plan hash value: 3150866331

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 40 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST_CHR | 1 | 40 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T_TEST_CHR_ID2 | 1 | | 1 (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

SQL> select * from t_test_chr a where a.type_varchar2=1;

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 217284118

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 40 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T_TEST_CHR | 1 | 40 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------------

可以看到由于隐式转换使得查询走了不同的执行路径，在很多时候这种变化不是开发者所期待的，所以需要留意数据类型。具体可以参加《sql reference》的Datatype Comparison Rules一节。

3，Null详解

一行的一个cell如果没有值，那么就是null的，注意并不是他的值是null，所以col=null与col is null是完全不同的，null有很多特性，可以简单看几个例子来说明：

SQL> insert into t_test_chr values ('1','',null);

1 row created.

SQL> select * from t_test_chr where type_char=1;

TYPE TYPE_VARCHAR2 TYPE_NVARCHAR2

---- -------------------- --------------------

SQL> select * from t_test_chr where type_varchar2 is null and type_nvarchar2 is null;

TYPE TYPE_VARCHAR2 TYPE_NVARCHAR2

---- -------------------- --------------------

可以看出，''与null都是没有值的，可以认为他们相同，但是：

SQL> select * from dual where ''=null;

no rows selected

SQL> select * from dual where '' is null;

同样的情况：

SQL> select * from dual where '' ='';

no rows selected

SQL> select * from dual where null =null;

no rows selected

SQL> select * from dual where 1!=null;

no rows selected

这说明null不等于null，其他任何值与null的比较，结果是未知的。

在一些关联问题中字段包含了null值，通常情况下可以使用外连接来解决null值的影响。

同时也可以利用null的特性来实现一些特定的需求，比如:

SQL> create table t_test_null(id number,send_time date ,recv_time date );

Table created.

SQL> insert into t_test_null

2 select 1, sysdate, '' from dual;

1 row created.

SQL> insert into t_test_null

2 select 1, '', sysdate from dual;

1 row created.

插入的两条数据如果是模拟某种成对的操作，现在要知道某个id下面的send和recv时间，并且在一条中显示（往往情况比这个复杂），那么就可以使用：

SQL> select id ,min(send_time),min(recv_time) from t_test_null group by id;

ID MIN(SEND_ MIN(RECV_

---------- --------- ---------

1 16-MAY-10 16-MAY-10

或者使用max也可以，因为null的“值”比其他都小，或者都大。

一般为了消除控制字段中的null值出现，可以增加一个 not null约束，增加not null约束之后新插入或者更新后的字段值不允许为null，但是要注意的是，这个约束对增加之前已有的数据不会检查。

4，几个常见的伪列

SQL> select ora_rowscn,rowid,rownum from t_test_chr;

ORA_ROWSCN ROWID ROWNUM

---------- ------------------ ----------

6.4107E+12 AAAOG6AAFAAAAAWAAA 1

6.4107E+12 AAAOG6AAFAAAAAWAAB 2

其中ORA_rowscn主要用在闪回查询的flashback_query_clause,有关闪回的详细探讨在闪回一章。

Rowid是每一行的唯一地址。Rowid包含了许多信息，诸如：

数据对象的对象编号；

在数据文件中，该行所在的数据；

行在数据块的位置；

行所在的表空间内的数据文件号。

所以rowid是访问单行最快的途径，不过如果是访问多行的话，则稍稍有些不同，这与被访问的行的相对位置有关，也或者叫聚簇因子。具体的影响情况见cluster一章。

可以看一个rowid的使用，例如删除重复行：

truncate table t_test_chr;

insert into t_test_chr

select 1, '', ''

from dual

union all

select 1, '', '' from dual;

insert into t_test_chr

select 2, '', ''

from dual

union all

select 2, '', '' from dual;

delete from t_test_chr a where a.rowid in

(select min(rowid) from t_test_chr b where b.type_char = a.type_char);

Rownum则是结果集的临时行标。

很多时候需要取表或者集合内的一部分数据来使用，使用rownum很容易达到这个目的。或者在需要对一部分临时数据编号时使用，如果需要对导入数据库的一部分数据按照导入的次序进行排序编号，那么可以使用rownum的特性：

select type_char,type_varchar2,type_nvarchar2,rownum num from t_test_chr order by rowid asc

或者要获得top n的数据：

select * from

(select * from employees order by employee_id)

where rownum < 11;

5，集合运算

Union 与Union all

前者是获取每个查询的所有不重复的行，后者是所有行，包括重复的

Intersect

选择所有不重复的相交行

Minus

第一个查询减去第二个查询所得的结果。

6，Exists, not exsists,in ,not in

先来描述一下oracle的连接和子查询，在很多情况下，连接和子查询可以相互替代，这时候如何选择使用很多是凭个人习惯，不过二者在执行的效率问题上面确并不是完全一样的，往往需要根据具体的数据量的大小结合执行计划来调整。

in和exists同样可以相互替代，也需要根据具体的情况来选择使用。使用in的情况下，oracle有限查询子查询，然后匹配外层查询，exists则刚好相反。

一般来说，后面的子查询结果集如果比较小的话，一般使用in，如果子查询较大，而外层的查询相对较小，则使用exists。

Not in 和not exists则不能完全替换，他们有一些差异。具体先看一个例子：

SQL> select * from t_test_chr a ;

TYPE TYPE_VARCHAR2 TYPE_NVARCHAR2

---- -------------------- --------------------

1111 1111 1111

SQL> select * from t_test_chr a where not exists (select 1 from t_test_chr where type_varchar2 = a.type_varchar2 );

TYPE TYPE_VARCHAR2 TYPE_NVARCHAR2

---- -------------------- --------------------

SQL> select * from t_test_chr a where type_varchar2 not in (select type_varchar2 from t_test_chr);

no rows selected

而exists 和in分别的结果是：

SQL> select * from t_test_chr a where exists (select 1 from t_test_chr where type_varchar2 = a.type_varchar2 );

TYPE TYPE_VARCHAR2 TYPE_NVARCHAR2

---- -------------------- --------------------

1111 1111 1111

SQL> select * from t_test_chr a where type_varchar2 in (select type_varchar2 from t_test_chr);

TYPE TYPE_VARCHAR2 TYPE_NVARCHAR2

---- -------------------- --------------------

1111 1111 1111

这样区别就一目了然了。

7，decode,case when,connect by 语句

decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)

case when 条件1 then 值1

when 条件n then 值n

else 值n+1

end

很多时候这两个语句是类似的，比如：

SQL> select decode(dummy, 'X', 'xx', 'Y') decode_col,

2 case

3 when dummy = 'X' then

4 'xxxx'

5 else

6 'Z'

7 end case_col

8 from dual;

DECODE_COL CASE_COL

---------- --------

xx xxxx

不过一般来说decode用来处理匹配少量数值，且匹配条件比较简单，如果过于复杂的条件则应该使用case语句。

Connect by ..start with通常用来做树形查询。举个简单的树形结构例子，比如全国的机构组织架构，总部设为A，一级分部AXX（AAA，AAB），二级分部AXXXX（AAAAA，AAAAAB，AABAA，AABAB），创建数据如下：

SQL> create table t_test_organ (

2 id char(5),

3 p_id char(5),

4 name varchar2(40)

5 );

Table created

SQL> insert into t_test_organ values ('A','','总公司');

1 row inserted

SQL> insert into t_test_organ values ('AAA','A','分公司1');

1 row inserted

SQL> insert into t_test_organ values ('AAB','A','分公司2');

1 row inserted

SQL> insert into t_test_organ values ('AAAAA','AAA','支公司1');

1 row inserted

SQL> insert into t_test_organ values ('AAAAB','AAA','支公司2');

1 row inserted

SQL> insert into t_test_organ values ('AABAA','AAB','支公司3');

1 row inserted

SQL> insert into t_test_organ values ('AABAB','AAB','支公司4');

1 row inserted

SQL> select level, id, name

2 from t_test_organ

3 connect by prior id = p_id

4 start with id = 'A';

LEVEL ID NAME

---------- ----- ----------------------------------------

1 A 总公司

2 AAA 分公司1

3 AAAAA 支公司1

3 AAAAB 支公司2

2 AAB 分公司2

3 AABAA 支公司3

3 AABAB 支公司4

7 rows selected

8，10g 正则表达式

很多时候需要对数据做处理，10g之前通常会自定义一些通用函数来实现一些规则，10g开始提供了几个函数来支持正则表达式：

REGEXP_LIKE
REGEXP_REPLACE
REGEXP_INSTR
REGEXP_SUBSTR

先来看看oracle10g正则表达式定义的运算符。

\ 反斜杠有四个不同意义：取本身的值，引用下一个字符，介绍一个运算符，什么也不做

示例：

SQL> select * from dual where regexp_like('aa111b','\^') ;

DUMMY

-----

SQL> select * from dual where regexp_like('aa11^1b','\^') ;

DUMMY

-----

SQL> select * from dual where regexp_like('aa11^1b','\a') ;

DUMMY

-----

SQL> select * from dual where regexp_like('aa11^1b','\aa1') ;

DUMMY

-----

SQL> select * from dual where regexp_like('aa11^1b','\aa2') ;

DUMMY

-----

SQL> select * from dual where regexp_like('aa11^1b','\') ;

DUMMY

-----

* 匹配0个或者多个表达式

SQL> select * from dual where regexp_like('aa11^1b','*') ;

DUMMY

-----

SQL> select * from dual where regexp_like('aa11^1b','a*1') ;

DUMMY

-----

+ 匹配一个或者多个表达式

SQL> select * from dual where regexp_like('aa11^1b','a*1+1*1') ;

DUMMY

-----

? 匹配0个或者一个表达式

SQL> select * from dual where regexp_like('aa11^1b','?b') ;

DUMMY

-----

SQL> select * from dual where regexp_like('aa11^1b','?bb') ;

DUMMY

-----

SQL> select * from dual where regexp_like('aa11^1b','?a1') ;

DUMMY

-----

| 指两项之中的选择

SQL> select * from dual where regexp_like('aa11^1b','?a1|?bb') ;

DUMMY

-----

^ 匹配开头的字符

SQL> select * from dual where regexp_like('aa11^1b','^aa1') ;

DUMMY

-----

SQL> select * from dual where regexp_like('aa11^1b','^aa2') ;

DUMMY

-----

$ 匹配结尾的字符

SQL> select * from dual where regexp_like('aa11^1b','1b$') ;

DUMMY

-----

SQL> select * from dual where regexp_like('aa11^1b','11b$') ;

DUMMY

-----

SQL> select * from dual where regexp_like('aa11^1b','a11$') ;

DUMMY

-----

. 匹配任何字符除了null

SQL> select * from dual where regexp_like('aa11^1b','\^1.$') ;

DUMMY

-----

SQL> select * from dual where regexp_like('aa11^1b','\^.$') ;

DUMMY

-----

[] 括号表达式

SQL> select * from dual where regexp_like('aa11^1b','[\^.$]+[\^1.$]') ;

DUMMY

-----

() 子表达式的开始和结尾

SQL> select * from dual where regexp_like('aa11^1b','(^a)+([\^.$]+[\^1.$])') ;

DUMMY

-----

SQL> select * from dual where regexp_like('aa11^1b','(^a)+(.)+([\^.$]+[\^1.$])') ;

DUMMY

-----

SQL> select * from dual where regexp_like('aa11^1b','(^a)+|([\^.$]+[\^1.$])') ;

DUMMY

-----

{m} 精确的匹配次数

{m,} 至少m次匹配

{m,n} m到n次匹配

SQL> Select REGEXP_REPLACE('aabbcc','(aa){1}', 'xx') FROM dual;

REGEXP_REPLACE('AABBCC','(AA){

------------------------------

xxbbcc

SQL> Select REGEXP_REPLACE('aabbcc','(aa){1,}', 'xx') FROM dual;

REGEXP_REPLACE('AABBCC','(AA){

------------------------------

xxbbcc

SQL> Select REGEXP_REPLACE('aabbcc','(a){1,}', 'xx') FROM dual;

REGEXP_REPLACE('AABBCC','(A){1

------------------------------

xxbbcc

SQL> Select REGEXP_REPLACE('aabbcc','(a){2,}', 'xx') FROM dual;

REGEXP_REPLACE('AABBCC','(A){2

------------------------------

xxbbcc

SQL> Select REGEXP_REPLACE('aabbcc','(ab){2,}', 'xx') FROM dual;

REGEXP_REPLACE('AABBCC','(AB){

------------------------------

aabbcc

SQL> Select REGEXP_REPLACE('aabbcc','(ab){1,}', 'xx') FROM dual;

REGEXP_REPLACE('AABBCC','(AB){

------------------------------

axxbcc

SQL> Select REGEXP_REPLACE('aabbcc','(a){1,2}', 'xx') FROM dual;

REGEXP_REPLACE('AABBCC','(A){1

------------------------------

xxbbcc

SQL> Select REGEXP_REPLACE('aabbcc','(ab){1,2}', 'xx') FROM dual;

REGEXP_REPLACE('AABBCC','(AB){

------------------------------

axxbcc

9，group by语句

Group by主要用来分组统计，这个是开发中经常被使用的语句，先来看几个语句的运行情况：

例如求各部门内的最大工钱值，根据部门来分组统计：

SQL> select deptno,max(sal) from scott.emp group by deptno;

DEPTNO MAX(SAL)

---------- ----------

30 2850

20 3000

10 5000

Execution Plan

----------------------------------------------------------

Plan hash value: 4067220884

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 | 21 | 4 (25)| 00:00:01 |

| 1 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |

| 2 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |

---------------------------------------------------------------------------

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

524 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3 rows processed

增加一个order by deptno子句

SQL> select deptno,max(sal) from scott.emp group by deptno order by deptno;

DEPTNO MAX(SAL)

---------- ----------

10 5000

20 3000

30 2850

Execution Plan

----------------------------------------------------------

Plan hash value: 15469362

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 | 21 | 4 (25)| 00:00:01 |

| 1 | SORT GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |

| 2 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |

---------------------------------------------------------------------------

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

524 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

3 rows processed

增加一个order by max(sal)子句

SQL> select deptno,max(sal) from scott.emp group by deptno order by max(sal);

DEPTNO MAX(SAL)

---------- ----------

30 2850

20 3000

10 5000

Execution Plan

----------------------------------------------------------

Plan hash value: 2664716850

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 | 21 | 5 (40)| 00:00:01 |

| 1 | SORT ORDER BY | | 3 | 21 | 5 (40)| 00:00:01 |

| 2 | HASH GROUP BY | | 3 | 21 | 5 (40)| 00:00:01 |

| 3 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |

----------------------------------------------------------------------------

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

524 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

3 rows processed

上述三个语句略有不同，不过都是从全表扫描emp表开始执行，然后在此基础上进行group by或者order by，具体方式有所不同，这里仅仅把执行计划和统计信息给出来，具体在性能调整一章中进行详细测试分析与叙述。

主要来看看group by的增强语句，例如：having 子句，grouping sets,roll up,cube,group_id等

Having 子句

改进上述查询语句，只需要工钱大于等于3000的记录：

SQL> select deptno,max(sal) from scott.emp group by deptno having max(sal) >=3000 order by max(sal);

DEPTNO MAX(SAL)

---------- ----------

20 3000

10 5000

Execution Plan

----------------------------------------------------------

Plan hash value: 3611938775

-----------------------------------------------------------------------------

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 7 | 5 (40)| 00:00:01 |

| 1 | SORT ORDER BY | | 1 | 7 | 5 (40)| 00:00:01 |

|* 2 | FILTER | | | | | |

| 3 | HASH GROUP BY | | 1 | 7 | 5 (40)| 00:00:01 |

| 4 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |

-----------------------------------------------------------------------------

Rollup语句

Rollup可以理解为一维多层的统计，往往在需要按层次统计的时候用到。

修改一下需求，现在需要按照部门，分工作职位来统计工钱的总和，并求全公司之和，可以简单的使用rollup来完成：

SQL> select a.deptno, b.job, sum(b.sal)

2 from scott.dept a, scott.emp b

3 where a.deptno = b.deptno

4 group by rollup(a.deptno, b.job);

DEPTNO JOB SUM(B.SAL)

---------- --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 8750

20 CLERK 1900

20 ANALYST 6000

20 MANAGER 2000

20 9900

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

DEPTNO JOB SUM(B.SAL)

---------- --------- ----------

30 9400

28050

13 rows selected.

可以看到使用rollup使得sum增加了聚合的级别，即实现了对多层进行分组统计计算。

现在稍稍修改一下需求，不需要对全公司进行统计，只需要计算各部门的总和和部门下不同工种的求和，那么修改后的语句可以如下：

SQL> select a.deptno, b.job, sum(b.sal)

2 from scott.dept a, scott.emp b

3 where a.deptno = b.deptno

4 group by a.deptno,rollup( b.job);

DEPTNO JOB SUM(B.SAL)

---------- --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 8750

20 CLERK 1900

20 ANALYST 6000

20 MANAGER 2000

20 9900

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

DEPTNO JOB SUM(B.SAL)

---------- --------- ----------

30 9400

12 rows selected.

再增加一个工头的字段，需要知道不同的工头下面工人的情况：

SQL> select a.deptno, b.job,b.mgr, sum(b.sal)

2 from scott.dept a, scott.emp b

3 where a.deptno = b.deptno

4 group by a.deptno,rollup( b.job,b.mgr);

DEPTNO JOB MGR SUM(B.SAL)

---------- --------- ---------- ----------

10 CLERK 7782 1300

10 CLERK 1300

10 MANAGER 7839 2450

10 MANAGER 2450

10 PRESIDENT 5000

10 8750

20 CLERK 7788 1100

20 CLERK 7902 800

20 CLERK 1900

20 ANALYST 7566 6000

DEPTNO JOB MGR SUM(B.SAL)

---------- --------- ---------- ----------

20 ANALYST 6000

20 MANAGER 7839 2000

20 MANAGER 2000

20 9900

30 CLERK 7698 950

30 CLERK 950

30 MANAGER 7839 2850

30 MANAGER 2850

30 SALESMAN 7698 5600

30 SALESMAN 5600

30 9400

22 rows selected.

可以这样理解上面rollup语句：

group by col1,rollup(col2,col3)会进行如下计算：

(col1,col2,col3)

(col1,col2)

(col1)

Cube语句

Cube的意思是立方，主要是用来进行多维度的统计的，Cube有时也可以代替rollup，比如group by col1,rollup(col2)就可以用group by col1,cube(col2)来代替，可以用下面的语句带实现前面rollup的实现2：

SQL> select a.deptno, b.job, sum(b.sal)

2 from scott.dept a, scott.emp b

3 where a.deptno = b.deptno

4 group by a.deptno,cube( b.job);

DEPTNO JOB SUM(B.SAL)

---------- --------- ----------

10 8750

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

20 9900

20 CLERK 1900

20 ANALYST 6000

20 MANAGER 2000

30 9400

30 CLERK 950

30 MANAGER 2850

DEPTNO JOB SUM(B.SAL)

---------- --------- ----------

30 SALESMAN 5600

12 rows selected.

可以看到除了结果集的排序不同以外，其他均相同。这时候使用rollup和cube分别进行了如下的计算：

Rollup:

(col1,col2)

(col1)

Cube:

(col1)

(col1,col2)

注意上述表达式的顺序

但是cube的group by col1,cube(col2,col3)与rollup差别较大，事实上它的计算如下：

(col1)

(col1,col3)

(col1,col2)

(col1,col2,col3)

而group by cube(col1,col2,col3)则会计算2的3次方次，即维度为3.计算如下：

(col1),(col2),(col3),(col2,col3),(col1,col2),(col1,col3),(col1,col2,col3),()

Grouping sets语句

如果说rollup和cube是oracle预定义了的计算维度，那么grouping sets则可以理解为可以自己设置计算维度的一个表达式，用下面一个例子来看：

SQL> select a.deptno, b.job,b.mgr, sum(b.sal)

2 from scott.dept a, scott.emp b

3 where a.deptno = b.deptno

4 group by a.deptno,cube( b.job,b.mgr)

5 minus

6 select a.deptno, b.job,b.mgr, sum(b.sal)

7 from scott.dept a, scott.emp b

8 where a.deptno = b.deptno

9 group by a.deptno,grouping sets((b.job,b.mgr),(b.job),(b.mgr),());

no rows selected

说明了group by a.deptno,cube( b.job,b.mgr)

与group by a.deptno,grouping sets((b.job,b.mgr),(b.job),(b.mgr),())的结果是完全一样的，根据对cube的理解则grouping sets也很容易理解，不在累述。

Grouping_id和group_id函数

对于使用cube或者rollup合作而后grouping sets的语句，可以使用group_id()或者grouping_id()来获得不同的分组：

SQL> select a.deptno, b.job, sum(b.sal),group_id() g_id

2 from scott.dept a, scott.emp b

3 where a.deptno = b.deptno

4 group by a.deptno,cube( a.deptno,b.job);

DEPTNO JOB SUM(B.SAL) G_ID

---------- --------- ---------- ----------

10 CLERK 1300 0

10 MANAGER 2450 0

10 PRESIDENT 5000 0

20 CLERK 1900 0

20 ANALYST 6000 0

20 MANAGER 2000 0

30 CLERK 950 0

30 MANAGER 2850 0

30 SALESMAN 5600 0

10 CLERK 1300 1

10 MANAGER 2450 1

DEPTNO JOB SUM(B.SAL) G_ID

---------- --------- ---------- ----------

10 PRESIDENT 5000 1

20 CLERK 1900 1

20 ANALYST 6000 1

20 MANAGER 2000 1

30 CLERK 950 1

30 MANAGER 2850 1

30 SALESMAN 5600 1

10 8750 0

20 9900 0

30 9400 0

10 8750 1

DEPTNO JOB SUM(B.SAL) G_ID

---------- --------- ---------- ----------

20 9900 1

30 9400 1

24 rows selected.

SQL> select group_id() gp_id,

2 grouping_id(a.deptno, b.job,b.mgr) gpp_id,

3 a.deptno, b.job,b.mgr,sum(b.sal)

4 from scott.dept a, scott.emp b

5 where a.deptno = b.deptno

6 group by a.deptno,grouping sets( a.deptno,b.job,b.mgr);

GP_ID GPP_ID DEPTNO JOB MGR SUM(B.SAL)

---------- ---------- ---------- --------- ---------- ----------

0 2 20 7839 2000

0 2 10 7839 2450

0 2 30 7698 6550

0 2 20 7566 6000

0 2 10 7782 1300

0 2 20 7902 800

0 2 10 5000

0 2 30 7839 2850

0 2 20 7788 1100

0 1 10 CLERK 1300

0 1 10 MANAGER 2450

GP_ID GPP_ID DEPTNO JOB MGR SUM(B.SAL)

---------- ---------- ---------- --------- ---------- ----------

0 1 10 PRESIDENT 5000

0 3 10 8750

0 1 20 CLERK 1900

0 1 20 ANALYST 6000

0 1 20 MANAGER 2000

0 3 20 9900

0 1 30 CLERK 950

0 1 30 MANAGER 2850

0 1 30 SALESMAN 5600

0 3 30 9400

21 rows selected.

10，dml扩展示例

With as select

先来看个例子，

SQL> with temp as

2 (select a.deptno,a.job,a.sal,sum(a.sal)over(partition by a.deptno) max_sal from scott.emp a

3 )

4 select * from temp

5 ;

DEPTNO JOB SAL MAX_SAL

---------- --------- ---------- ----------

10 MANAGER 2450 8750

10 PRESIDENT 5000 8750

10 CLERK 1300 8750

20 MANAGER 2000 9900

20 ANALYST 3000 9900

20 CLERK 1100 9900

20 CLERK 800 9900

20 ANALYST 3000 9900

30 SALESMAN 1250 9400

30 SALESMAN 1500 9400

30 SALESMAN 1600 9400

DEPTNO JOB SAL MAX_SAL

---------- --------- ---------- ----------

30 CLERK 950 9400

30 MANAGER 2850 9400

30 SALESMAN 1250 9400

14 rows selected.

可以看出with的一般用法，with子句只能用于select，一个比较好的用处是使得代码的逻辑比较清楚。

另外with语句使用系统临时表，一定程度上可以提升性能，因为oracle执行一次WITH子查询，会将结果放到临时表中，如果随后有对子查询的多次访问，那么会从临时表中直接读取数据。有关性能的分析具体给出yangtingkun老师的文章：http://yangtingkun.itpub.net/post/468/202694

Merge into

SQL> select * from test.cust;

CUST_ID CUST_CITY

---------- --------------------------------------------------

10 a

15 b

20 c

25 d

30 e

35 f

40 g

7 rows selected

SQL> merge into test.cust a

2 using (select 10 cust_id from dual ) b

3 on (a.cust_id = b.cust_id)

4 when matched then

5 update set a.cust_city = a.cust_city||'1'

6 when not matched then

7 insert (cust_id) values (b.cust_id);

1 row merged.

Insert all:

可以使用insert all select 同时插入不同的表中，也可以使用带条件的insert all语句，增加条件判断语句：when ..then .into。

使用insert first 则下一个条件将自动不考虑上一个条件被选中的行。

带条件的update:

update t_test_bill a

set a.date =

(case when a.b_no = '000025661847852' then date '2010-4-13'

when a.b_no = '000044155978850' then date '2010-3-25'

else date '2010-4-2' end)

where a.b_no in ('000025661847852', '000028634036852', '000028634143852',

'000028634250852', '000044155978850');

更新视图：

SQL> update (select a.empno, a.comm,b.dname

2 from scott.emp a, scott.dept b

3 where a.deptno = b.deptno

4 and b.loc in ('NEW YORK', 'DALLAS'))

5 set comm = 0.1;

8 rows updated

但要注意，不能同时update多个关联的基表：

SQL> update (select a.empno, a.comm,b.dname

2 from scott.emp a, scott.dept b

3 where a.deptno = b.deptno

4 and b.loc in ('NEW YORK', 'DALLAS'))

5 set comm = 0.1,dname = dname||'';

update (select a.empno, a.comm,b.dname

from scott.emp a, scott.dept b

where a.deptno = b.deptno

and b.loc in ('NEW YORK', 'DALLAS'))

set comm = 0.1,dname = dname||''

ORA-01776: cannot modify more than one base table through a join view

三，分析函数

分析函数计算基于一组行的聚合值，与聚合函数不同的是它为每个分组返回多行值。行的分组被叫做一个窗口，在analytic_clause子句中定义。

具体的句法如下：

Analytic_function (arguments) over (analytic_clause)

其中：

Analytic_function函数名，9i/10g中有26个分析函数，接下来会逐一分析。

analytic_clause:{Query_partition_clause } {order by clause} {windowing_clause}

使用over告知查询分析器函数为分析函数而不是聚合函数，且在随后的子句指出要在那些字段上做分析计算。

Query_partition_clause: partition by {expr}/{(expr)}

使用partition表示对数据进行分组，如果没有指定，则将全部的结果作为一个分组来对待。

Order by clause:order by expr {asc/desc} {null/{first/last}}

Order by添加一个默认的开窗子句，告知当前分组内的计算顺序。后面添加nulls last或者nulls first是正对null值的处理。

Window_clause:{rows/range}

定义分组内用于计算或操作的具体行的集合。

Range:

产生一个滑动窗口，在组中拥有指定的range的行，使用range时对order by限制为一列，使得其滑动窗口的范围为一维。

下面分几组来讲解分析函数的具体使用：

1, 评级函数row_number,rank dense_rank,percent_rank，cume_dist,ntile

先来看看row_number，现在要对scott用户下面的工人表emp按照薪水从小到大排序，看看哪些是困难户：

SQL> select a.empno, a.ename, a.sal, row_number() over(order by a.sal) num

2 from scott.emp a;

EMPNO ENAME SAL NUM

---------- ---------- ---------- ----------

7369 SMITH 800 1

7900 JAMES 950 2

7876 ADAMS 1100 3

7521 WARD 1250 4

7654 MARTIN 1250 5

7934 update 1300 6

7844 TURNER 1500 7

7499 ALLEN 1600 8

7566 JONES 2000 9

7782 CLARK 2450 10

7698 BLAKE 2850 11

EMPNO ENAME SAL NUM

---------- ---------- ---------- ----------

7788 SCOTT 3000 12

7902 FORD 3000 13

7839 KING 5000 14

14 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3145491563

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 196 | 4 (25)| 00:00:01 |

| 1 | WINDOW SORT | | 14 | 196 | 4 (25)| 00:00:01 |

| 2 | TABLE ACCESS FULL| EMP | 14 | 196 | 3 (0)| 00:00:01 |

---------------------------------------------------------------------------

使用row_number()很容易为一个分组里面的行排序并加上序号（注意上面执行计划id为2的window sort。）

上述排序中12和13的工钱一样多，这样排序只是按照行的rowid来排的，默认是从小到大，可以具体看看：

SQL> select row_number() over(order by a.deptno) num, rowid, a.empno,a.deptno

2 from scott.emp a;

NUM ROWID EMPNO DEPTNO

---------- ------------------ ---------- ----------

1 AAANMFAAEAAAABEAAG 7782 10

2 AAANMFAAEAAAABEAAI 7839 10

3 AAANMFAAEAAAABEAAN 7934 10

4 AAANMFAAEAAAABEAAD 7566 20

5 AAANMFAAEAAAABEAAM 7902 20

6 AAANMFAAEAAAABEAAK 7876 20

7 AAANMFAAEAAAABEAAA 7369 20

8 AAANMFAAEAAAABEAAH 7788 20

9 AAANMFAAEAAAABEAAC 7521 30

10 AAANMFAAEAAAABEAAJ 7844 30

11 AAANMFAAEAAAABEAAB 7499 30

NUM ROWID EMPNO DEPTNO

---------- ------------------ ---------- ----------

12 AAANMFAAEAAAABEAAL 7900 30

13 AAANMFAAEAAAABEAAF 7698 30

14 AAANMFAAEAAAABEAAE 7654 30

14 rows selected.

通过看相同deptno的rowid就可以看出来这个规律。

现在需要对分不同部门来看部门内的工钱排名，且从大到小排列：

SQL> select a.empno,

2 a.ename,

3 a.deptno,

4 a.sal,

5 row_number() over(partition by a.deptno order by a.sal desc) num

6 from scott.emp a;

EMPNO ENAME DEPTNO SAL NUM

---------- ---------- ---------- ---------- ----------

7839 KING 10 5000 1

7782 CLARK 10 2450 2

7934 update 10 1300 3

7788 SCOTT 20 3000 1

7902 FORD 20 3000 2

7566 JONES 20 2000 3

7876 ADAMS 20 1100 4

7369 SMITH 20 800 5

7698 BLAKE 30 2850 1

7499 ALLEN 30 1600 2

7844 TURNER 30 1500 3

EMPNO ENAME DEPTNO SAL NUM

---------- ---------- ---------- ---------- ----------

7654 MARTIN 30 1250 4

7521 WARD 30 1250 5

7900 JAMES 30 950 6

14 rows selected.

如果每个部门只要取前三名的话，则可以稍稍修改查询：

SQL> select * from (

2 select a.empno,

3 a.ename,

4 a.deptno,

5 a.sal,

6 row_number() over(partition by a.deptno order by a.sal desc) num

7 from scott.emp a) where num <=3;

EMPNO ENAME DEPTNO SAL NUM

---------- ---------- ---------- ---------- ----------

7839 KING 10 5000 1

7782 CLARK 10 2450 2

7934 update 10 1300 3

7788 SCOTT 20 3000 1

7902 FORD 20 3000 2

7566 JONES 20 2000 3

7698 BLAKE 30 2850 1

7499 ALLEN 30 1600 2

7844 TURNER 30 1500 3

9 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3291446077

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 826 | 4 (25)| 00:00:01

|* 1 | VIEW | | 14 | 826 | 4 (25)| 00:00:01

|* 2 | WINDOW SORT PUSHED RANK| | 14 | 238 | 4 (25)| 00:00:01

| 3 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("NUM"<=3)

2 - filter(ROW_NUMBER() OVER ( PARTITION BY "A"."DEPTNO" ORDER BY

INTERNAL_FUNCTION("A"."SAL") DESC )<=3)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

845 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

9 rows processed

很容易想到用嵌套查询，在外层限定序号num的值不超过3，注意看看执行计划id为2的行，使用的是WINDOW SORT PUSHED RANK

下面就来看看rank是如何实现上面的这个求前三甲的需求的：

SQL> select * from (

2 select a.empno,

3 a.ename,

4 a.deptno,

5 a.sal,

6 rank() over(partition by a.deptno order by a.sal desc) num

7 from scott.emp a) where num <=3;

EMPNO ENAME DEPTNO SAL NUM

---------- ---------- ---------- ---------- ----------

7839 KING 10 5000 1

7782 CLARK 10 2450 2

7934 update 10 1300 3

7788 SCOTT 20 3000 1

7902 FORD 20 3000 1

7566 JONES 20 2000 3

7698 BLAKE 30 2850 1

7499 ALLEN 30 1600 2

7844 TURNER 30 1500 3

9 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3291446077

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 826 | 4 (25)| 00:00:01

|* 1 | VIEW | | 14 | 826 | 4 (25)| 00:00:01

|* 2 | WINDOW SORT PUSHED RANK| | 14 | 238 | 4 (25)| 00:00:01

| 3 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("NUM"<=3)

2 - filter(RANK() OVER ( PARTITION BY "A"."DEPTNO" ORDER BY

INTERNAL_FUNCTION("A"."SAL") DESC )<=3)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

842 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

9 rows processed

好像写法是一样的，但是结果稍稍有所不同，rank会把相同的sal当作并列处理。但看执行机会和统计信息则发现基本一样(这里猜测oracle内部实现row_number和rank的主要算法是一样的)

Dense_rank和rank稍有不同，不过是序号不会像rank一样跳跃。再看一个例子：

SQL> select a.deptno,

2 a.job,

3 sum(a.sal),

4 dense_rank() over(partition by a.deptno order by sum(a.sal) desc) num

5 from scott.emp a

6 group by grouping sets((a.deptno),(a.job),(a.deptno, a.job),());

DEPTNO JOB SUM(A.SAL) NUM

---------- --------- ---------- ----------

10 8750 1

10 PRESIDENT 5000 2

10 MANAGER 2450 3

10 CLERK 1300 4

20 9900 1

20 ANALYST 6000 2

20 MANAGER 2000 3

20 CLERK 1900 4

30 9400 1

30 SALESMAN 5600 2

30 MANAGER 2850 3

DEPTNO JOB SUM(A.SAL) NUM

---------- --------- ---------- ----------

30 CLERK 950 4

28050 1

MANAGER 7300 2

ANALYST 6000 3

SALESMAN 5600 4

PRESIDENT 5000 5

CLERK 4150 6

18 rows selected.

以上是分部门和工种来求工钱总和，并且使用了grouping sets根据需要获得分组的排名。

Percent_rank也很容易理解：

当前行占分组内行的百分比，比如要知道当前的员工的工钱水平占整个部门内的什么水平，可以这样来看：

SQL> select a.empno,

2 a.ename,

3 a.deptno,

4 a.sal,

5 percent_rank() over(partition by a.deptno order by a.sal desc) num

6 from scott.emp a

7 ;

EMPNO ENAME DEPTNO SAL NUM

---------- ---------- ---------- ---------- ----------

7839 KING 10 5000 0

7782 CLARK 10 2450 .5

7934 update 10 1300 1

7788 SCOTT 20 3000 0

7902 FORD 20 3000 0

7566 JONES 20 2000 .5

7876 ADAMS 20 1100 .75

7369 SMITH 20 800 1

7698 BLAKE 30 2850 0

7499 ALLEN 30 1600 .2

7844 TURNER 30 1500 .4

EMPNO ENAME DEPTNO SAL NUM

---------- ---------- ---------- ---------- ----------

7654 MARTIN 30 1250 .6

7521 WARD 30 1250 .6

7900 JAMES 30 950 1

14 rows selected.

显然KING和CLARK都是部门内50%的富人，但是他们相差却非常之大，贫富差距往往是数据看不出来的，前面还没有用avg来求平均值，求了则更是粉饰了真正的现实。想到最近10年或者20年（从记事开始算起）以来的变迁，我们的“涉会注意郭家”是相当优越的，对内喜欢讲人均，对外喜欢将总和，就是转移一下贫富差距的视线，现在的情况是大寡头占有了涉会绝大多数财富资源，很少数的人抢占了大多数人的幸福生活。不要觉得意外，其基本原理本来就是这样：

让一部分人先富起来，带动全民富裕。

这句看着很搞笑了。

下面继续来看看cume_dist,和percent_rank差不多，看一个例子就可以知道了：

SQL> select a.deptno,

2 a.job,

3 sum(a.sal),

4 cume_dist() over(partition by a.deptno order by sum(a.sal) desc) num

5 from scott.emp a

6 group by rollup (a.deptno, a.job);

DEPTNO JOB SUM(A.SAL) NUM

---------- --------- ---------- ----------

10 8750 .25

10 PRESIDENT 5000 .5

10 MANAGER 2450 .75

10 CLERK 1300 1

20 9900 .25

20 ANALYST 6000 .5

20 MANAGER 2000 .75

20 CLERK 1900 1

30 9400 .25

30 SALESMAN 5600 .5

30 MANAGER 2850 .75

DEPTNO JOB SUM(A.SAL) NUM

---------- --------- ---------- ----------

30 CLERK 950 1

28050 1

13 rows selected.

而ntile是将行再分组，修改上述查询也很容易理解，ntile(2)将行分为2组，下面例子中4行的即为2，2，如果是3行，则2，1，其他雷同：

SQL> select a.deptno,

2 a.job,

3 sum(a.sal),

4 ntile(2) over(partition by a.deptno order by sum(a.sal) desc) num

5 from scott.emp a

6 group by rollup (a.deptno, a.job);

DEPTNO JOB SUM(A.SAL) NUM

---------- --------- ---------- ----------

10 8750 1

10 PRESIDENT 5000 1

10 MANAGER 2450 2

10 CLERK 1300 2

20 9900 1

20 ANALYST 6000 1

20 MANAGER 2000 2

20 CLERK 1900 2

30 9400 1

30 SALESMAN 5600 1

30 MANAGER 2850 2

DEPTNO JOB SUM(A.SAL) NUM

---------- --------- ---------- ----------

30 CLERK 950 2

28050 1

13 rows selected.

有关评级函数，再说一个表结构设计时候的问题，现在有一个log表，记录某些操作的具体信息（操作事件，操作人，操作信息，开始时间，结束时间，操作对象id，操作状态等等），现在要对操作人考核，看看这个人总共操作了多少次，在操作中花了多少时间，每个状态值停了多少时间，从第一次开始操作，到最终完成操作又话了多少时间。好像没有什么是sql完成不了的，rank,row_number,或者lag,lead可能都可以用的上，不过个人觉得，像经常性查询的一些关键字段，应该在基表内有一定的冗余字段，关联或者计算在大数据量和高响应要求时给系统带来了负面影响，严格来讲分析函数应该尽可能多用于数据仓库或者分析报表库，而不是oltp的生产系统，但是往往情况不是这样。

2,行筛选相关的函数keep,first,last,first_value,last_value,lag,lead

先来看两个查询，一个实现的是部门内最早雇佣的员工中的工资最多与最小的值，一个是实现工资最高和最低的员工中入职时间最早或最晚的值：

查询1：

SQL> select a.deptno,a.hiredate,a.sal,

2 min(a.sal) keep(dense_rank first order by hiredate desc) over(partition by a.deptno) first,

3 max(a.sal) keep(dense_rank last order by hiredate desc) over(partition by a.deptno) last

4 from scott.emp a;

DEPTNO HIREDATE SAL FIRST LAST

---------- --------- ---------- ---------- ----------

10 09-JUN-81 2450 1300 2450

10 17-NOV-81 5000 1300 2450

10 23-JAN-82 1300 1300 2450

20 02-APR-81 2000 1100 800

20 03-DEC-81 3000 1100 800

20 23-MAY-87 1100 1100 800

20 17-DEC-80 800 1100 800

20 19-APR-87 3000 1100 800

30 22-FEB-81 1250 950 1600

30 08-SEP-81 1500 950 1600

30 20-FEB-81 1600 950 1600

DEPTNO HIREDATE SAL FIRST LAST

---------- --------- ---------- ---------- ----------

30 03-DEC-81 950 950 1600

30 01-MAY-81 2850 950 1600

30 28-SEP-81 1250 950 1600

14 rows selected.

查询2：

SQL> select a.deptno,a.sal,a.hiredate,

2 min(a.hiredate) keep(dense_rank first order by a.sal desc) over(partition by a.deptno) first,

3 max(a.hiredate) keep(dense_rank last order by a.sal desc) over(partition by a.deptno) last

4 from scott.emp a;

DEPTNO SAL HIREDATE FIRST LAST

---------- ---------- --------- --------- ---------

10 2450 09-JUN-81 17-NOV-81 23-JAN-82

10 5000 17-NOV-81 17-NOV-81 23-JAN-82

10 1300 23-JAN-82 17-NOV-81 23-JAN-82

20 2000 02-APR-81 03-DEC-81 17-DEC-80

20 3000 03-DEC-81 03-DEC-81 17-DEC-80

20 1100 23-MAY-87 03-DEC-81 17-DEC-80

20 800 17-DEC-80 03-DEC-81 17-DEC-80

20 3000 19-APR-87 03-DEC-81 17-DEC-80

30 1250 22-FEB-81 01-MAY-81 03-DEC-81

30 1500 08-SEP-81 01-MAY-81 03-DEC-81

30 1600 20-FEB-81 01-MAY-81 03-DEC-81

DEPTNO SAL HIREDATE FIRST LAST

---------- ---------- --------- --------- ---------

30 950 03-DEC-81 01-MAY-81 03-DEC-81

30 2850 01-MAY-81 01-MAY-81 03-DEC-81

30 1250 28-SEP-81 01-MAY-81 03-DEC-81

14 rows selected.

上面的例子中时间没有完全相同的，那么简单修改一下，则可以更好的理解keep中的first和last与min和max的意义：

正对上面的查询1来修改得到

查询3：

SQL> select a.deptno,to_char(hiredate,'yyyy') hiredate,a.sal,

2 min(a.sal) keep(dense_rank first order by to_char(hiredate,'yyyy') desc) over(partition by a.deptno) first,

3 max(a.sal) keep(dense_rank last order by to_char(hiredate,'yyyy') desc) over(partition by a.deptno) last

4 from scott.emp a;

DEPTNO HIRE SAL FIRST LAST

---------- ---- ---------- ---------- ----------

10 1981 2450 1300 5000

10 1981 5000 1300 5000

10 1982 1300 1300 5000

20 1981 2000 1100 800

20 1981 3000 1100 800

20 1987 1100 1100 800

20 1980 800 1100 800

20 1987 3000 1100 800

30 1981 1250 950 2850

30 1981 1500 950 2850

30 1981 1600 950 2850

DEPTNO HIRE SAL FIRST LAST

---------- ---- ---------- ---------- ----------

30 1981 950 950 2850

30 1981 2850 950 2850

30 1981 1250 950 2850

14 rows selected.

first和last很容易理解。Dense_rank的特点上面的keep中的dense_rank first或者dense_rank last都可能会取到多行。

在hiredate修改为to_char(hiredate,'yyyy')以后，有了在同一年份入职的员工，那么first其实会把第一组年份的员工行都取了，外层使用min(a.sal)或者max(a.sal)就是再对组中的员工求最大最小工钱值。

接下来再看看first_value和last_value,有四个查询例子：

这个查询按deptno开窗找第一个和最后一个sal：

SQL> select a.empno,a.deptno,to_char(hiredate,'yyyy') hiredate,a.sal,

2 first_value(a.sal) over(partition by a.deptno ) first,

3 last_value(a.sal) over(partition by a.deptno ) last

4 from scott.emp a;

EMPNO DEPTNO HIRE SAL FIRST LAST

---------- ---------- ---- ---------- ---------- ----------

7782 10 1981 2450 2450 1300

7839 10 1981 5000 2450 1300

7934 10 1982 1300 2450 1300

7566 20 1981 2000 2000 3000

7902 20 1981 3000 2000 3000

7876 20 1987 1100 2000 3000

7369 20 1980 800 2000 3000

7788 20 1987 3000 2000 3000

7521 30 1981 1250 1250 1250

7844 30 1981 1500 1250 1250

7499 30 1981 1600 1250 1250

EMPNO DEPTNO HIRE SAL FIRST LAST

---------- ---------- ---- ---------- ---------- ----------

7900 30 1981 950 1250 1250

7698 30 1981 2850 1250 1250

7654 30 1981 1250 1250 1250

14 rows selected.

下面的查询在上面的基础上增加了order by deptno语句：

SQL> select a.empno,a.deptno,to_char(hiredate,'yyyy') hiredate,a.sal,

2 first_value(a.sal) over(partition by a.deptno order by deptno) first,

3 last_value(a.sal) over(partition by a.deptno order by deptno) last

4 from scott.emp a;

EMPNO DEPTNO HIRE SAL FIRST LAST

---------- ---------- ---- ---------- ---------- ----------

7934 10 1982 1300 1300 5000

7782 10 1981 2450 1300 5000

7839 10 1981 5000 1300 5000

7369 20 1980 800 800 3000

7876 20 1987 1100 800 3000

7566 20 1981 2000 800 3000

7788 20 1987 3000 800 3000

7902 20 1981 3000 800 3000

7900 30 1981 950 950 2850

7654 30 1981 1250 950 2850

7521 30 1981 1250 950 2850

EMPNO DEPTNO HIRE SAL FIRST LAST

---------- ---------- ---- ---------- ---------- ----------

7844 30 1981 1500 950 2850

7499 30 1981 1600 950 2850

7698 30 1981 2850 950 2850

14 rows selected.

下面的查询是按照sal升序排序后的，这样每个部门下面按照sal排序了，每增加一条数据，都会取当前的first和last，因此可以看到每行的last都在变化。

SQL> select a.empno,a.deptno,to_char(hiredate,'yyyy') hiredate,a.sal,

2 first_value(a.sal) over(partition by a.deptno order by sal) first,

3 last_value(a.sal) over(partition by a.deptno order by sal) last

4 from scott.emp a;

EMPNO DEPTNO HIRE SAL FIRST LAST

---------- ---------- ---- ---------- ---------- ----------

7934 10 1982 1300 1300 1300

7782 10 1981 2450 1300 2450

7839 10 1981 5000 1300 5000

7369 20 1980 800 800 800

7876 20 1987 1100 800 1100

7566 20 1981 2000 800 2000

7788 20 1987 3000 800 3000

7902 20 1981 3000 800 3000

7900 30 1981 950 950 950

7654 30 1981 1250 950 1250

7521 30 1981 1250 950 1250

EMPNO DEPTNO HIRE SAL FIRST LAST

---------- ---------- ---- ---------- ---------- ----------

7844 30 1981 1500 950 1500

7499 30 1981 1600 950 1600

7698 30 1981 2850 950 2850

14 rows selected.

再修改一下，order by rowid，效果差不多：

SQL> select a.empno,a.deptno,to_char(hiredate,'yyyy') hiredate,a.sal,

2 first_value(a.sal) over(partition by a.deptno order by rowid) first,

3 last_value(a.sal) over(partition by a.deptno order by rowid) last

4 from scott.emp a;

EMPNO DEPTNO HIRE SAL FIRST LAST

---------- ---------- ---- ---------- ---------- ----------

7782 10 1981 2450 2450 2450

7839 10 1981 5000 2450 5000

7934 10 1982 1300 2450 1300

7369 20 1980 800 800 800

7566 20 1981 2000 800 2000

7788 20 1987 3000 800 3000

7876 20 1987 1100 800 1100

7902 20 1981 3000 800 3000

7499 30 1981 1600 1600 1600

7521 30 1981 1250 1600 1250

7654 30 1981 1250 1600 1250

EMPNO DEPTNO HIRE SAL FIRST LAST

---------- ---------- ---- ---------- ---------- ----------

7698 30 1981 2850 1600 2850

7844 30 1981 1500 1600 1500

7900 30 1981 950 1600 950

14 rows selected.

再来看lag和lead，分别用两个sql来求部门内按照hiredate排序后的每个苦工上一个或者下一个的工钱值：

SQL> select a.empno,

2 a.deptno,

3 a.hiredate,

4 a.sal,

5 lag(sal, 1, 0) over(partition by a.deptno order by hiredate asc) pre_sal

6 from scott.emp a;

EMPNO DEPTNO HIREDATE SAL PRE_SAL

---------- ---------- --------- ---------- ----------

7782 10 09-JUN-81 2450 0

7839 10 17-NOV-81 5000 2450

7934 10 23-JAN-82 1300 5000

7369 20 17-DEC-80 800 0

7566 20 02-APR-81 2000 800

7902 20 03-DEC-81 3000 2000

7788 20 19-APR-87 3000 3000

7876 20 23-MAY-87 1100 3000

7499 30 20-FEB-81 1600 0

7521 30 22-FEB-81 1250 1600

7698 30 01-MAY-81 2850 1250

EMPNO DEPTNO HIREDATE SAL PRE_SAL

---------- ---------- --------- ---------- ----------

7844 30 08-SEP-81 1500 2850

7654 30 28-SEP-81 1250 1500

7900 30 03-DEC-81 950 1250

14 rows selected.

SQL> select a.empno,

2 a.deptno,

3 a.hiredate,

4 a.sal,

5 lead(sal, 1, 0) over(partition by a.deptno order by hiredate asc) next_sal

6 from scott.emp a;

EMPNO DEPTNO HIREDATE SAL NEXT_SAL

---------- ---------- --------- ---------- ----------

7782 10 09-JUN-81 2450 5000

7839 10 17-NOV-81 5000 1300

7934 10 23-JAN-82 1300 0

7369 20 17-DEC-80 800 2000

7566 20 02-APR-81 2000 3000

7902 20 03-DEC-81 3000 3000

7788 20 19-APR-87 3000 1100

7876 20 23-MAY-87 1100 0

7499 30 20-FEB-81 1600 1250

7521 30 22-FEB-81 1250 2850

7698 30 01-MAY-81 2850 1500

EMPNO DEPTNO HIREDATE SAL NEXT_SAL

---------- ---------- --------- ---------- ----------

7844 30 08-SEP-81 1500 1250

7654 30 28-SEP-81 1250 950

7900 30 03-DEC-81 950 0

14 rows selected.

四，几种连接和特定用法

1，连接

从多个表或者视图，物化视图中获得字段和行，通常需要进行连接操作。这也是关系数据库的一大特征。具体来看看oracle的几种连接的概念或方式。(注意：有关oracle优化器提供的表数据连接的方法，详见性能调整篇sql调整一章。)

Equijoins(等值连接)

等值连接是连接条件是相等的运算符的连接。等值连接将列值相等的行结合起来。根据优化算法选择执行连接，一个表内的等值连接条件列的数量可能与数据块的大小有关系。（块大小即db_block_size）

Selfjoin(自连接)

在from后同一个表出现多次，并连接。

Cartesian products(笛卡尔积)

两个表在一个查询中没有连接条件，那么结果将会是两个表的全相连。结果的行数将会是Rows(a)*Rows(b)

比如：

select * from scott.emp a,scott.dept b;

Innerjoin(内连接)

一般的=连接

Outerjoin(外连接)

简单看一个例子：

SQL> select a.empno, a.deptno, b.deptno, b.dname

2 from scott.emp a, scott.dept b

3 where b.deptno = a.deptno(+)

4 and a.deptno is null;

EMPNO DEPTNO DEPTNO DNAME

---------- ---------- ---------- --------------

40 OPERATIONS

Antijoins(反连接)

看一个例子：

select *

from scott.emp a

where a.deptno not in

(select /*+ HASH_AJ(scott.dept)*/deptno from scott.dept b where b.deptno = 20 )

Semijoins

看一个例子：

SQL> select a.*

2 from scott.dept a

3 where exists (select deptno

4 from scott.emp b

5 where b.job = 'SALESMAN'

6 and b.hiredate < sysdate

7 and b.deptno = a.deptno);

DEPTNO DNAME LOC

---------- -------------- -------------

30 SALES CHICAGO

Execution Plan

----------------------------------------------------------

Plan hash value: 1090737117

--------------------------------------------------------------------------------

--------

me |

--------------------------------------------------------------------------------

--------

| 0 | SELECT STATEMENT | | 3 | 117 | 6 (17)| 00

:00:01 |

| 1 | MERGE JOIN SEMI | | 3 | 117 | 6 (17)| 00

:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00

:00:01 |

| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00

:00:01 |

|* 4 | SORT UNIQUE | | 4 | 76 | 4 (25)| 00

:00:01 |

|* 5 | TABLE ACCESS FULL | EMP | 4 | 76 | 3 (0)| 00

:00:01 |

--------------------------------------------------------------------------------

--------

Predicate Information (identified by operation id):

---------------------------------------------------

4 - access("B"."DEPTNO"="A"."DEPTNO")

filter("B"."DEPTNO"="A"."DEPTNO")

5 - filter("B"."JOB"='SALESMAN' AND "B"."HIREDATE"<SYSDATE@!)

Statistics

----------------------------------------------------------

1869 recursive calls

0 db block gets

374 consistent gets

0 physical reads

0 redo size

531 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

55 sorts (memory)

0 sorts (disk)

1 rows processed

2，特定用法或问题

行列转换问题

查漏补缺：

1,rownum

需要增加：rownum固定结果集，改变查询计划等方面的扩展

2,约束问题

Inline 和out line check

约束带来的性能和锁的问题

3,外键问题

外键带来的性能问题

posted @ 2011-09-13 21:34 老猫-DB 阅读(275) 评论(0) 编辑收藏举报

刷新页面返回顶部

独孤岛

虚幻大千两茫茫，一邂逅，终难忘。他日相逢留一笑，不相识，又防？俗尘尽洗！

跟我一起学ORACLE开发之SQL篇