Oracle 筆記（六）、表的管理（約束、索引、鎖定、表分區）

一、表完整性與約束

實體完整性：主鍵約束
值域完整性
參照完整性：外鍵約束

SELECT constraint_name, constraint_type FROM user_constriants WHERE table_name = 'EMP';

二、管理索引
    索引是建立在表欄位上的一種儲存結構，利用索引可以加快表的查詢速度。當索引鍵列用於 SQL 陳述式的 WHERE 子句時，該索引將直接指向包含這些值得行位置，合理使用索引是減少磁碟 I/O 的主要方法，它隻影響執行的速度。

    建立索引時，Oracle 將擷取要建立索引的列，並對其進行排序。然後將 ROWID 連同每一行的索引值儲存起來。使用時，Oracle 先通過已排序的索引值執行快速搜尋，然後使用相關聯的 ROWID 值來定位具有所要尋找的記錄行。

    一旦建立索引，Oracle 會自動維護和使用它們。更新行記錄時，會自動更新索引，所以為表建立過多的索引會降低表的效能。

    獲得索引資訊 user_indexes 視圖。

    重建現有的索引效能要優於刪除重建索引。  ALTER INDEX item_index REBUILD;
 

2.0、傳統的 B樹索引：
CREATE INDEX ix_mm ON mm(m1);

2.1、唯一索引：唯一約束就是唯一索引，但唯一索引未必是唯一約束。
CREATE UNIQUE INDEX myidx ON mm(m2);

2.2、複合式索引：在表中多個列上建立的索引。如果 SELECT 語句中 WHERE 子句引用了複合式索引中的所有列或大多數列，則複合式索引可以提高資料檢索速度。通常建立索引時最頻繁訪問的列應該放在列表的最前面。
CREATE INDEX comp_index ON itemfile(p_category, itemrate);

2.3、反向鍵索引：特殊的索引，在索引含有序數的列時非常有用，反向鍵索引通過簡單的反向被索引中的資料來解決問題。
CREATE INDEX rev_index ON itemfile(itemcode) REVERSE;

ps.可以使用 NOREVERSE 將反向鍵索引重建為標準索引，但不能將標準索引重建為反向鍵索引。
ALTER INDEX rev_index REBUILD NOREVERSE;

2.4、位元影像索引：表裡資料量大，但表的唯一值少。位元影像索引使用每個索引值的位元影像，而不是用 ROWID 列表。
CREATE BITMAP INDEX bit_emp ON emp(sex);

2.5、索引組織表

2.6、基於函數的索引

2.7、索引中的分區

三、鎖定
鎖定是資料庫用來控制共用資源並發訪問的機制。當更新資料的時候，Oracle 會自動將其鎖定，其他使用者只能查看該資料，直至復原或提交事務後，鎖才會釋放，其他使用者才能編輯。

3.1、行級鎖
行級鎖只對使用者正在訪問的行進行鎖定。在使用 INSERT、UPDATE、DELETE 和 SELECT ... FOR UPDATE 等語句時，Oracle 會自動應用行鎖定。

SELECT * FROM order_master WHERE vencode = 'V002'
  FOR UPDATE WAIT 5;
UPDATE .....
COMMIT;

3.2、表級鎖
表級所將保護資料，在交易處理過程中，表級鎖會限制對整個表的增、刪、改操作。

LOCK TABLE <table_name> IN <look_mode> MODE [NOWAIT];
● 行共用（ROW SHARE, RS）
● 行排他（ROW EXCLUSIVE, RX）
● 共用（SHARE, S）
● 共用行排他（SHARE ROW EXCLUSIVE, SRX）
● 排他（EXCLUSIVE, X）

3.3、死結
兩個事務相互等待對方完成任務，就會出現死結。Oracle 將自動檢測死結，並通過終止兩個交易處理之一來解決問題。

四、表分區
    Oracle 提供表分區技術，以來改善大型應用系統的效能。表分區允許使用者把一個表中所有的行分為幾個部分，並將這些部分儲存在不同的位置。在大型表中利用表分區，能改善表查詢效能、方便管理表，以及備份\恢複、提高資料安全性。

    表分區對使用者使透明的，使用者在更新和查詢時把分區表當作普通表使用。CREATE TABLE 語句中增加 PARTTION 子句以建立表分區。

ps.要分區的表不能具有 LONG 和 LONG RAW 資料類型的列。

4.1、定界分割
根據表某一個列或一組列的值範圍，決定該資料存放區在哪個分區上。
例如：對 sales 表的 sales_cost 列值進行分區

CREATE TABLE sales
(
  ...
)
PARTTION BY RANGE (sales_cost)
(
  PARTTION P1 VALUES LESS THAN (1000),
  PARTTION P2 VALUES LESS THAN (2000),
  PARTTION P3 VALUES LESS THAN (3000),
  PARTTION P4 VALUES LESS THAN (MAXVALUE)    /* 大於3000的其他值都儲存於分區 P4 */
);

4.2、散列分區
散列分區通過在分區索引值上執行一個散列函數來決定資料的物理位置。散列分區把記錄平均地分布到不同的分區，減少了磁碟 I/O 爭用的可能性。
例如：建立 employer 表按照 department 列分區成 4個散列分區

CREATE TABLE employer
(
  ...
)
PAERTTION BY HASH (department) PARTTIONS 4;

4.3、複合分區
複合分區是定界分割和散列分區的結合。在建立複合分區時，先根據範圍對資料進行分區，然後再散列分區內建立散列子分區。
例如：建立 sales3 表，分區為複合分區，先根據 sales_date 列建立定界分割，然後再分區內根據 product_id 建立子分區。

CREATE TABLE sales
(
  ...
)
PARTTION BY RANGE (sales_date)
SUBPARTTION BY HASH (product_id) SUBPARTTION 5
(
  PARTTION P1 VALUES LESS THEN (DATE '2001-04-01'),
  PARTTION P2 VALUES LESS THEN (DATE '2001-07-01'),
  PARTTION P3 VALUES LESS THEN (DATE '2001-09-01'),
  PARTTION P4 VALUES LESS THEN (MAXVALUE)
);

4.4、列表分區
列表分區允許使用者明確地控制行到分區的映射。列表分區允許按自然方式對無序和不相關的資料集進行分組和組織。
例如：根據職員住址 emp_address 列對 employee 表進行分區，並針對不同地區不同存放。

CREATE TABLE employee
(
  ...
)
PARTTION BY LIST (emp_address )
(
  PARTTION north VALUES ('芝加哥'),
  PARTTION west VALUES ('舊金山', '洛杉磯'),
  PARTTION south VALUES ('亞特蘭大', '達拉斯', '休斯頓'),
  PARTTION east VALUES ('紐約' ,'波士頓'),
  PARTTION aa VALUES (DEFAULT)            /* 其他的地址儲存 aa 分區，不指定則不接受其他地址輸入 */
);

插入資料時 Oracle 會根據分區列的值將記錄儲存到對應的分區。
查詢分區資料：    SELECT * FROM sales3 PARTTION (P3);

分區的維護操作：
分區的維護操作時修改分區表的分區，以獲得更佳的 I/O Server Load Balancer。

1、添加分區：在最後一個分區之後添加新的分區。
ALTER TABLE sales ADD PARTTION P4 VALUES LESS THEN (4000);

2、刪除分區：刪除分區時，分區中資料也隨之刪除。
ALTER TABLE sales DROP PARTTION P4;

3、截斷分區：將刪除表分區的所有記錄。
ALTER TABLE sales TRUNCATE PARTTION P3;

4、合并分區：可將分定界分割或複合分區表的兩個相鄰分區串連起來。結果分區將繼承兩個分區的上界。
ALTER TABLE sales MERGE PARTTIONS P1, P2 INTO PARTTION P2;

5、拆分分區：允許使用者將一個分區拆分成兩個分區。
/* 以 '2005-01-01' 為分界拆分一個分區資料 */
ALTER TABLE sales SPLIT PARTTION P3 AT (DATE '2005-01-01')
  INTO (PARTTION P31, PARTTION P32);