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 負載平衡。

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);