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

一、表完整性與約束

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

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



二、管理索引
    索引是建立在表字段上的一種存儲結(jié)構(gòu)，利用索引可以加快表的查詢速度。當(dāng)索引鍵列用于 SQL 語句的 WHERE 子句時，該索引將直接指向包含這些值得行位置，合理使用索引是減少磁盤 I/O 的主要方法，它只影響執(zhí)行的速度。

    創(chuàng)建索引時，Oracle 將獲取要創(chuàng)建索引的列，并對其進(jìn)行排序。然后將 ROWID 連同每一行的索引值存儲起來。使用時，Oracle 先通過已排序的索引值執(zhí)行快速搜索，然后使用相關(guān)聯(lián)的 ROWID 值來定位具有所要查找的記錄行。

    一旦創(chuàng)建索引，Oracle 會自動維護(hù)和使用它們。更新行記錄時，會自動更新索引，所以為表創(chuàng)建過多的索引會降低表的性能。

    獲得索引信息 user_indexes 視圖。

    重建現(xiàn)有的索引性能要優(yōu)于刪除重建索引。  ALTER INDEX item_index REBUILD;
  

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


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


2.2、組合索引：在表中多個列上創(chuàng)建的索引。如果 SELECT 語句中 WHERE 子句引用了組合索引中的所有列或大多數(shù)列，則組合索引可以提高數(shù)據(jù)檢索速度。通常創(chuàng)建索引時最頻繁訪問的列應(yīng)該放在列表的最前面。
CREATE INDEX comp_index ON itemfile(p_category, itemrate);


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

ps.可以使用 NOREVERSE 將反向鍵索引重建為標(biāo)準(zhǔn)索引，但不能將標(biāo)準(zhǔn)索引重建為反向鍵索引。
ALTER INDEX rev_index REBUILD NOREVERSE;


2.4、位圖索引：表里數(shù)據(jù)量大，但表的唯一值少。位圖索引使用每個鍵值的位圖，而不是用 ROWID 列表。
CREATE BITMAP INDEX bit_emp ON emp(sex);


2.5、索引組織表

2.6、基于函數(shù)的索引

2.7、索引中的分區(qū)



三、鎖定
鎖定是數(shù)據(jù)庫用來控制共享資源并發(fā)訪問的機(jī)制。當(dāng)更新數(shù)據(jù)的時候，Oracle 會自動將其鎖定，其他用戶只能查看該數(shù)據(jù)，直至回滾或提交事務(wù)后，鎖才會釋放，其他用戶才能編輯。

3.1、行級鎖
行級鎖只對用戶正在訪問的行進(jìn)行鎖定。在使用 INSERT、UPDATE、DELETE 和 SELECT ... FOR UPDATE 等語句時，Oracle 會自動應(yīng)用行鎖定。

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

3.2、表級鎖
表級所將保護(hù)數(shù)據(jù)，在事務(wù)處理過程中，表級鎖會限制對整個表的增、刪、改操作。

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、死鎖
兩個事務(wù)相互等待對方完成任務(wù)，就會出現(xiàn)死鎖。Oracle 將自動檢測死鎖，并通過終止兩個事務(wù)處理之一來解決問題。



四、表分區(qū)
    Oracle 提供表分區(qū)技術(shù)，以來改善大型應(yīng)用系統(tǒng)的性能。表分區(qū)允許用戶把一個表中所有的行分為幾個部分，并將這些部分存儲在不同的位置。在大型表中利用表分區(qū)，能改善表查詢性能、方便管理表，以及備份\恢復(fù)、提高數(shù)據(jù)安全性。

    表分區(qū)對用戶使透明的，用戶在更新和查詢時把分區(qū)表當(dāng)作普通表使用。CREATE TABLE 語句中增加 PARTTION 子句以創(chuàng)建表分區(qū)。

ps.要分區(qū)的表不能具有 LONG 和 LONG RAW 數(shù)據(jù)類型的列。


4.1、范圍分區(qū)
根據(jù)表某一個列或一組列的值范圍，決定該數(shù)據(jù)存儲在哪個分區(qū)上。
例如：對 sales 表的 sales_cost 列值進(jìn)行分區(qū)

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的其他值都存儲于分區(qū) P4 */
);

4.2、散列分區(qū)
散列分區(qū)通過在分區(qū)鍵值上執(zhí)行一個散列函數(shù)來決定數(shù)據(jù)的物理位置。散列分區(qū)把記錄平均地分布到不同的分區(qū)，減少了磁盤 I/O 爭用的可能性。
例如：創(chuàng)建 employer 表按照 department 列分區(qū)成 4個散列分區(qū)

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

4.3、復(fù)合分區(qū)
復(fù)合分區(qū)是范圍分區(qū)和散列分區(qū)的結(jié)合。在創(chuàng)建復(fù)合分區(qū)時，先根據(jù)范圍對數(shù)據(jù)進(jìn)行分區(qū)，然后再散列分區(qū)內(nèi)創(chuàng)建散列子分區(qū)。
例如：創(chuàng)建 sales3 表，分區(qū)為復(fù)合分區(qū)，先根據(jù) sales_date 列創(chuàng)建范圍分區(qū)，然后再分區(qū)內(nèi)根據(jù) product_id 創(chuàng)建子分區(qū)。

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、列表分區(qū)
列表分區(qū)允許用戶明確地控制行到分區(qū)的映射。列表分區(qū)允許按自然方式對無序和不相關(guān)的數(shù)據(jù)集進(jìn)行分組和組織。
例如：根據(jù)職員住址 emp_address 列對 employee 表進(jìn)行分區(qū)，并針對不同地區(qū)不同存放。

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

插入數(shù)據(jù)時 Oracle 會根據(jù)分區(qū)列的值將記錄存儲到對應(yīng)的分區(qū)。
查詢分區(qū)數(shù)據(jù)：    SELECT * FROM sales3 PARTTION (P3);


分區(qū)的維護(hù)操作：
分區(qū)的維護(hù)操作時修改分區(qū)表的分區(qū)，以獲得更佳的 I/O 負(fù)載平衡。

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

2、刪除分區(qū)：刪除分區(qū)時，分區(qū)中數(shù)據(jù)也隨之刪除。
ALTER TABLE sales DROP PARTTION P4;

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

4、合并分區(qū)：可將分范圍分區(qū)或復(fù)合分區(qū)表的兩個相鄰分區(qū)連接起來。結(jié)果分區(qū)將繼承兩個分區(qū)的上界。
ALTER TABLE sales MERGE PARTTIONS P1, P2 INTO PARTTION P2;

5、拆分分區(qū)：允許用戶將一個分區(qū)拆分成兩個分區(qū)。
/* 以 '2005-01-01' 為分界拆分一個分區(qū)數(shù)據(jù) */
ALTER TABLE sales SPLIT PARTTION P3 AT (DATE '2005-01-01') 
  INTO (PARTTION P31, PARTTION P32);