一，基礎概述

1，oracle的鎖定與事務

僅僅來看下oracle的一些鎖和事務的相關檢視或引數，基本原理見基礎篇的事務原理一節。（本節內容可以參考9i或10g的）

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

682BE69C682BE6ACMR 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

682BE56C682BE57CMR Media Recovery 2 4

682BE520 682BE530 MR Media Recovery 1 4

682BE43C682BE44CRT Redo Thread 1 6

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

-----

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提供的特性會使得問題的解決有更多的選擇。

二，條件和表示式

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'

1

----------

1

SQL> select 1 from dual where 'a'

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

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

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

| 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

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

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，但是要注意的是，這個約束對增加之前已有的資料不會檢查。

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

這樣區別就一目瞭然了。

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

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

7876ADAMS 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

7782CLARK 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

7782CLARK 10 2450 2

7934 update 10 1300 3

7788 SCOTT 20 3000 1

7902 FORD 20 3000 2

7566 JONES 20 2000 3

7876ADAMS 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

7782CLARK 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

7782CLARK 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

7782CLARK 10 2450 .5

7934 update 10 1300 1

7788 SCOTT 20 3000 0

7902 FORD 20 3000 0

7566 JONES 20 2000 .5

7876ADAMS 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"

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

提出者：dingjun123

需要增加：rownum固定結果集，改變查詢計劃等方面的擴充套件

2,約束問題

提出者：dingjun123

Inline 和out line check

約束帶來的效能和鎖的問題

3,外來鍵問題

提出者：dingjun123

外來鍵帶來的效能問題

ORACLE-開發大全-前輩創

相關文章