总结笔记之【开发篇sql】

yellowlee

一些自己之前的总结，希望大家指出问题，错误之处，遗漏之处，可深入或者扩展的地方，希望大家支持！！

把文档传上来方便下载，太长了估计没几个人仔细看。。。不过记得，下载的兄弟要顶帖啊，呵呵！ 2.1 sql.doc (316.5 KB, 下载次数: 1630)

2010-5-31 18:59 上传

点击文件名下载附件

一，基础概述
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
----
MR
RT
TS
XR
与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
----------
        10

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
Share
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 一致性更改

[[i] 本帖最后由 yellowlee 于 2010-5-31 18:59 编辑 [/i]]

yellowlee

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
上面的操作产生了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
----------
         2
上面的语句也只产生了一条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
----------
         3
这个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
----------
         5
可以看出，delete的每一条记录都产生了一个undo记录。
由此可以大概对比一下不同的语句产生的undo大小。
SQL>
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是备份和恢复的一个支撑原理。有关联机日志和归档日志的管理和使用在备份恢复一章中详解。

yellowlee

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
-----
z

对于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提供的特性会使得问题的解决有更多的选择。

yellowlee

二，条件和表达式
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';

         1
----------

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

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

         1
----------
         1
字符与数值的比较，有一个隐身转换的过程：
SQL> select 1 from dual where '32' = 32;

         1
----------
         1
但是下面这样的情况并不会将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

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   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

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   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
---- -------------------- --------------------
1
SQL> select * from t_test_chr where type_varchar2 is null and type_nvarchar2 is null;

TYPE TYPE_VARCHAR2        TYPE_NVARCHAR2
---- -------------------- --------------------
1
可以看出，''与null都是没有值的，可以认为他们相同，但是：
SQL> select * from dual where ''=null;

no rows selected

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

D
-
X

同样的情况：
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，但是要注意的是，这个约束对增加之前已有的数据不会检查。

yellowlee

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
1

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
---- -------------------- --------------------
1
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

这样区别就一目了然了。

dingjun123

不错，支持原创！~~~

yellowlee

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
-----
X
SQL> select * from dual where  regexp_like('aa11^1b','\a') ;

DUMMY
-----
X

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

DUMMY
-----
X

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

DUMMY
-----

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

DUMMY
-----
X
*        匹配0个或者多个表达式
SQL> select * from dual where  regexp_like('aa11^1b','*') ;

DUMMY
-----
X

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

DUMMY
-----
X
+        匹配一个或者多个表达式
SQL> select * from dual where  regexp_like('aa11^1b','a*1+1*1') ;

DUMMY
-----
X
?        匹配0个或者一个表达式
SQL> select * from dual where  regexp_like('aa11^1b','?b') ;

DUMMY
-----
X

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

DUMMY
-----

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

DUMMY
-----
X
|        指两项之中的选择
SQL> select * from dual where  regexp_like('aa11^1b','?a1|?bb') ;

DUMMY
-----
X
^        匹配开头的字符
SQL> select * from dual where  regexp_like('aa11^1b','^aa1') ;

DUMMY
-----
X

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

DUMMY
-----

$        匹配结尾的字符
SQL> select * from dual where  regexp_like('aa11^1b','1b$') ;

DUMMY
-----
X

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
-----
X

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

DUMMY
-----
[]        括号表达式
SQL> select * from dual where  regexp_like('aa11^1b','[\^.$]+[\^1.$]') ;

DUMMY
-----
X
()        子表达式的开始和结尾
SQL> select * from dual where  regexp_like('aa11^1b','(^a)+([\^.$]+[\^1.$])') ;

DUMMY
-----

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

DUMMY
-----
X

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

DUMMY
-----
X

{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

yellowlee

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

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   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

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   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

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   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

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   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 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.

yellowlee

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

dingjun123

好帖子，授予精华，我想大家肯定没有异议，嘿嘿！~期待后续好内容！~