Oracle筆記（六）、表的管理（約束、索引、鎖定、表分區(qū)）
一、表完整性與約束
實(shí)體完整性：主鍵約束
域完整性
參照完整性：外鍵約束
SELECT constraint_name, constraint_type FROM user_constriants WHERE table_name = 'EMP';
二、管理索引
索引是建立在表字段上的一種存儲(chǔ)結(jié)構(gòu)，利用索引可以加快表的查詢速度。當(dāng)索引鍵列用于 SQL 語(yǔ)句的 WHERE 子句時(shí)，該索引將直接指向包含這些值得行位置，合理使用索引是減少磁盤 I/O 的主要方法，它只影響執(zhí)行的速度。
創(chuàng)建索引時(shí)，Oracle 將獲取要?jiǎng)?chuàng)建索引的列，并對(duì)其進(jìn)行排序。然后將 ROWID 連同每一行的索引值存儲(chǔ)起來(lái)。使用時(shí)，Oracle 先通過(guò)已排序的索引值執(zhí)行快速搜索，然后使用相關(guān)聯(lián)的 ROWID 值來(lái)定位具有所要查找的記錄行。
一旦創(chuàng)建索引，Oracle 會(huì)自動(dòng)維護(hù)和使用它們。更新行記錄時(shí)，會(huì)自動(dòng)更新索引，所以為表創(chuàng)建過(guò)多的索引會(huì)降低表的性能。
獲得索引信息 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、組合索引：在表中多個(gè)列上創(chuàng)建的索引。如果 SELECT 語(yǔ)句中 WHERE 子句引用了組合索引中的所有列或大多數(shù)列，則組合索引可以提高數(shù)據(jù)檢索速度。通常創(chuàng)建索引時(shí)最頻繁訪問的列應(yīng)該放在列表的最前面。
CREATE INDEX comp_index ON itemfile(p_category, itemrate);
2.3、反向鍵索引：特殊的索引，在索引含有序數(shù)的列時(shí)非常有用，反向鍵索引通過(guò)簡(jiǎn)單的反向被索引中的數(shù)據(jù)來(lái)解決問題。
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ù)量大，但表的唯一值少。位圖索引使用每個(gè)鍵值的位圖，而不是用 ROWID 列表。
CREATE BITMAP INDEX bit_emp ON emp(sex);
2.5、索引組織表
2.6、基于函數(shù)的索引
2.7、索引中的分區(qū)
三、鎖定
鎖定是數(shù)據(jù)庫(kù)用來(lái)控制共享資源并發(fā)訪問的機(jī)制。當(dāng)更新數(shù)據(jù)的時(shí)候，Oracle 會(huì)自動(dòng)將其鎖定，其他用戶只能查看該數(shù)據(jù)，直至回滾或提交事務(wù)后，鎖才會(huì)釋放，其他用戶才能編輯。
3.1、行級(jí)鎖
行級(jí)鎖只對(duì)用戶正在訪問的行進(jìn)行鎖定。在使用 INSERT、UPDATE、DELETE 和 SELECT ... FOR UPDATE 等語(yǔ)句時(shí)，Oracle 會(huì)自動(dòng)應(yīng)用行鎖定。
創(chuàng)新互聯(lián)公司10多年成都企業(yè)網(wǎng)站建設(shè)服務(wù);為您提供網(wǎng)站建設(shè),網(wǎng)站制作,網(wǎng)頁(yè)設(shè)計(jì)及高端網(wǎng)站定制服務(wù),成都企業(yè)網(wǎng)站建設(shè)及推廣,對(duì)花箱等多個(gè)方面擁有豐富建站經(jīng)驗(yàn)的網(wǎng)站建設(shè)公司。
SELECT * FROM order_master WHERE vencode = 'V002'
FOR UPDATE WAIT 5;
UPDATE .....
COMMIT;
3.2、表級(jí)鎖
表級(jí)所將保護(hù)數(shù)據(jù)，在事務(wù)處理過(guò)程中，表級(jí)鎖會(huì)限制對(duì)整個(gè)表的增、刪、改操作。
LOCK TABLEIN MODE [NOWAIT];
● 行共享（ROW SHARE, RS）
● 行排他（ROW EXCLUSIVE, RX）
● 共享（SHARE, S）
● 共享行排他（SHARE ROW EXCLUSIVE, SRX）
● 排他（EXCLUSIVE, X）
3.3、死鎖
兩個(gè)事務(wù)相互等待對(duì)方完成任務(wù)，就會(huì)出現(xiàn)死鎖。Oracle 將自動(dòng)檢測(cè)死鎖，并通過(guò)終止兩個(gè)事務(wù)處理之一來(lái)解決問題。
四、表分區(qū)
Oracle 提供表分區(qū)技術(shù)，以來(lái)改善大型應(yīng)用系統(tǒng)的性能。表分區(qū)允許用戶把一個(gè)表中所有的行分為幾個(gè)部分，并將這些部分存儲(chǔ)在不同的位置。在大型表中利用表分區(qū)，能改善表查詢性能、方便管理表，以及備份\恢復(fù)、提高數(shù)據(jù)安全性。
表分區(qū)對(duì)用戶使透明的，用戶在更新和查詢時(shí)把分區(qū)表當(dāng)作普通表使用。CREATE TABLE 語(yǔ)句中增加 PARTTION 子句以創(chuàng)建表分區(qū)。
ps.要分區(qū)的表不能具有 LONG 和 LONG RAW 數(shù)據(jù)類型的列。
4.1、范圍分區(qū)
根據(jù)表某一個(gè)列或一組列的值范圍，決定該數(shù)據(jù)存儲(chǔ)在哪個(gè)分區(qū)上。
例如：對(duì) 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的其他值都存儲(chǔ)于分區(qū) P4 */
);
4.2、散列分區(qū)
散列分區(qū)通過(guò)在分區(qū)鍵值上執(zhí)行一個(gè)散列函數(shù)來(lái)決定數(shù)據(jù)的物理位置。散列分區(qū)把記錄平均地分布到不同的分區(qū)，減少了磁盤 I/O 爭(zhēng)用的可能性。
例如：創(chuàng)建 employer 表按照 department 列分區(qū)成 4個(gè)散列分區(qū)
CREATE TABLE employer
(
...
)
PAERTTION BY HASH (department) PARTTIONS 4;
4.3、復(fù)合分區(qū)
復(fù)合分區(qū)是范圍分區(qū)和散列分區(qū)的結(jié)合。在創(chuàng)建復(fù)合分區(qū)時(shí)，先根據(jù)范圍對(duì)數(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ū)允許按自然方式對(duì)無(wú)序和不相關(guān)的數(shù)據(jù)集進(jìn)行分組和組織。
例如：根據(jù)職員住址 emp_address 列對(duì) employee 表進(jìn)行分區(qū)，并針對(duì)不同地區(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) /* 其他的地址存儲(chǔ) aa 分區(qū)，不指定則不接受其他地址輸入 */
);
插入數(shù)據(jù)時(shí) Oracle 會(huì)根據(jù)分區(qū)列的值將記錄存儲(chǔ)到對(duì)應(yīng)的分區(qū)。
查詢分區(qū)數(shù)據(jù)： SELECT * FROM sales3 PARTTION (P3);
分區(qū)的維護(hù)操作：
分區(qū)的維護(hù)操作時(shí)修改分區(qū)表的分區(qū)，以獲得更佳的 I/O 負(fù)載平衡。
1、添加分區(qū)：在最后一個(gè)分區(qū)之后添加新的分區(qū)。
ALTER TABLE sales ADD PARTTION P4 VALUES LESS THEN (4000);
2、刪除分區(qū)：刪除分區(qū)時(shí)，分區(qū)中數(shù)據(jù)也隨之刪除。
ALTER TABLE sales DROP PARTTION P4;
3、截?cái)喾謪^(qū)：將刪除表分區(qū)的所有記錄。
ALTER TABLE sales TRUNCATE PARTTION P3;
4、合并分區(qū)：可將分范圍分區(qū)或復(fù)合分區(qū)表的兩個(gè)相鄰分區(qū)連接起來(lái)。結(jié)果分區(qū)將繼承兩個(gè)分區(qū)的上界。
ALTER TABLE sales MERGE PARTTIONS P1, P2 INTO PARTTION P2;
5、拆分分區(qū)：允許用戶將一個(gè)分區(qū)拆分成兩個(gè)分區(qū)。
/* 以 '2005-01-01' 為分界拆分一個(gè)分區(qū)數(shù)據(jù) */
ALTER TABLE sales SPLIT PARTTION P3 AT (DATE '2005-01-01')
INTO (PARTTION P31, PARTTION P32);
網(wǎng)站標(biāo)題：Oracle筆記（六）、表的管理（約束、索引、鎖定、表分區(qū)）
文章源于：http://m.jiaotiyi.com/article/ghcpdj.html