SG_007_CHAPTER 6

Using DML Statements：

INSERT

Update

Merge

Delete

select for update

lock table

Inserting Rows into a Table:

Inserting into a Single Table:

Insert into (column) values(value);

注意：（column）是可選的，預設是全部column。

可以從ALL_TAB_COLUMNS , USER_TAB_COLUMNS, DBA_TAB_COLUMNS中檢視所有表的column。

不可以在檢視中插入：匯聚函式，distinct，set operator ，group by ，order by，

connect by，子查詢。

Inserting into Multiple Tables：

Insert (all | first) WHEN condition THEN INTO clause ELSE INTO clause ;

All:判斷每一個condition

First：判斷第一個結果為‘true’，就結束。下面的condition就不判斷了。

INTO clause既

into schema.table(column) values (value);

Updating Rows in a Table：

Update table

Set column=xxx

Where condition;

Merging Rows into a Table：

此命令用來在表中升級以及插入條目資料。

MERGE INTO schema.table1

USING schema.(table2 | subsquery)

on (condition)

WHEN MATCHED THEN UPDATE SET column = expression

WHEN NOT MATCHED THEN INSERT(column) VALUES (expression)

向table1中做操作，對table2中的資料與table1中的資料，做一個判斷（on condition）。如果符合，那麼更新table1，如果不符合插入table1

Deleting Rows from a Table：

Delete from table

Where condition

Truncating a Table:

TRUNCATE是刪除表中的資料，但是屬於DDL命令。

Ddl和dml區別，直接commit，無法rollback。

DDL: CREATE ALTER DROP TRUNCATE

TRUNCATE TABLE schema.table ( DROP REUSE) STORAGE;

DROP STORAGE 是預設的，收縮表的extent

REUSE 不收縮表的extent，不進行回收。高水位線復位。

TRUNCATE 和DELETE的區別：

1.T不產生undo information

2.T是ddl ，delete是dml

3. truncate重新設定表和索引的HWM（高水標記），由於全表掃描和索引快速掃描都要讀取所有的資料塊知道HWM為止
所以全表掃描的效能不會因為delete而提高，但是經過truncate操作後速度會很快

4. T does fire（觸發） any delete triggers

5.一個使用者不可能會被賦予使用truncate刪除其他使用者表的許可權。

6.當表被T時，表和索引的儲存空間設定為初始大小。Delete不會收縮表或索引的大小。

7.T父表之前，先要disable掉referential integrity constraint。

TRUNCATE 和 DROP 的區別：

1. delete/truncate 只刪除資料不刪除表，索引的結構。

2. drop 將表所佔用的空間全部釋放，segment 不存在，無所謂 HWM 的概念。

Selecting Rows FOR UPDATE：

鎖住特定行，防止其他會話改變或者刪除那些被鎖住的行資料，當這些行被鎖住的時候，其他會話可以選擇這些行，但是不能改變或者鎖住這些行。

這個狀態，直到該事務結束才終止。

Locking a Table：

鎖可以控制併發操作。

鎖的型別： 排他鎖（exclusive locks）X鎖。此時其他事務不能對它讀取和修改。

共享鎖（Share locks）S鎖。其他事務可以讀取，但是不能修改。

根據保護物件不同，鎖的型別：

DML鎖（data locks，資料鎖） 保護資料完整性

DDL鎖 （dictionary locks，字典鎖）保護資料庫物件結構

內部鎖和閂（internal locks and latcher）保護資料庫內部結構

DML鎖包括：TM鎖和TX鎖，TM鎖為表級鎖。TX鎖為行級鎖。

當oracle執行DML語句時，自動在所要操作的表上申請TM鎖（SS,SX,S,X），當TM鎖獲得之後，自動申請TX鎖。在加鎖檢查TX鎖的相容性的時候，直接檢查TM鎖模式相容性即可。

表級鎖具有以下幾種模式：

行共享（row share，RS），

行排他（row exclusive，RX），

共享（share，S），

共享行排他（share row exclusive，SRX），

及排他（exclusive，X）。

各種模式的表級鎖具有的限制級別決定了其是否能與其他表級鎖共處於同一資料表上。

行共享表級鎖（row share table lock）（SS）：

SELECT ... FROM table ... FOR UPDATE OF ... ;

LOCK TABLE table IN ROW SHARE MODE

鎖限制程度最低，保證表的最大的併發訪問能力。某個事務擁有某個表的行共享表級鎖之後，其他事務依然可以併發地對相同資料表執行查詢，插入，更新，刪除操作，換句話說，其他事務也可以獲得相同表上的行共享，行排他，共享，以及共享行排他模式的表級鎖。

禁止：當某個事務擁有了某個表的行共享表級鎖之後，會禁止其他事務透過以下語句排他的對相同的表進行寫操作：

LOCK TABLE table IN EXCLUSIVE MODE;

行排他表級鎖（row exclusive table lock）（SX）

INSERT INTO table ... ;

UPDATE table ... ;

DELETE FROM table ... ;

LOCK TABLE table IN ROW EXCLUSIVE MODE;

這個鎖比行共享表級鎖（RS）的限制程度略高。

允許操作：某個事務擁有了某個表的行排他表級鎖後，其他事務依然可以併發地對相同資料表執行查詢，插入，更新，刪除。或對錶內資料行加鎖的操作。這個鎖允許其他多個事務同時獲得表上的行共享表級鎖或行排他表級鎖。

禁止操作：擁有某個表的行排他表級鎖之後，將禁止其他事務手工地對錶加鎖，進行排他地讀寫操作。下列語句不能用來對相同的表加鎖：

LOCK TABLE table IN SHARE MODE;

LOCK TABLE table IN SHARE EXCLUSIVE MODE;

LOCK TABLE table IN EXCLUSIVE MODE;

共享表級鎖（share table lock）（S）：

LOCK TABLE table IN SHARE MODE;

允許操作：某個事務擁有了此鎖之後，其他事務可以查詢表，可以使用SELECT..FROM UODATE鎖定選中的資料行，可以LOCK TABLE…IN SHRE MODE語句。但是其他事務不能對錶進行更新操作。多個事務可以併發地獲得同一個表上的共享表級鎖，在此種情況下，任何事務都不能對錶進行更新。因此，擁有共享表級鎖的事務只有在此表上沒有其他事務的共享表級鎖時，才能對錶進行更新操作。

禁止的操作：某個事務擁有了某個表的共享表級鎖後，將禁止其他事務修改此表，同時禁止其他事務執行以下語句：

LOCK TABLE table IN ROW EXCLUSIVE MODE;

LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;

LOCK TABLE table IN EXCLUSIVE MODE;

共享行排他表級鎖（share row exclusive table lock）（SRX）

LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;

允許的操作：同一時間只有一個事物能夠獲得表的共享行排他表級鎖。某個事務擁有了某個表的共享行排他表級鎖後，其他事務可以查詢表，可以使用 SELECT ... FOR UPDATE 語句鎖定選中的資料行，但不能對錶進行更新操作。

禁止的操作：擁有共享行排他表級鎖的事務將阻止其他事務獲取行排他表級鎖（row exclusive table lock）來修改資料。共享行排他表級鎖還能阻止其他事務在相同表上獲取共享表級鎖（share table lock），共享行排他表級鎖（share row exclusive table lock），及排他表級鎖（exclusive table lock）。概括來說，其他事務不能執行以下語句。

LOCK TABLE table IN ROW EXCLUSIVE MODE;

LOCK TABLE table IN SHARE MODE;

LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;

LOCK TABLE table IN EXCLUSIVE MODE;

排他表級鎖（exclusive table lock）（X）：

LOCK TABLE table IN EXCLUSIVE MODE;

允許的操作：同一時間只有一個事務能獲得表上的排他表級鎖。一個事務獲得排他表級鎖後，其他事務只能對錶進行查詢操作。

禁止的操作：一個事務獲得排他表級鎖後，將禁止其他事務對錶執行任何 DML 操作，其他事務也無法獲取表上任何型別的鎖。

Understanding Transaction Control:

COMMIT : 結束當前事務，把資料永久地寫入。使其他會話對此資料可見。

ROLLBACK: undo當前事務所有改變的資料。

ROLLBACK TO SAVEPOINT: 返回到儲存點。

SET TRANSATION : 保證事務或者命令一致，為事務指定rollback 段。

Enabling Transaction-Level or Statement-Level Consistency

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;（事務級的）

返回的資料總是最新的。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;（預設的語句級的）

所返回的資料是在語句執行（執行時候那一刻的資料），處理的資料。

Specifying a Rollback Segment for a Transaction：

Oracle自動為事務指定一個回滾段。通常對小的事務這種方法很平常。

使用set transaction 分配一個大的回滾斷段。

SET TRANSACTION USE ROLLBACK SEGMENT rb_large