oracle鎖和表分區(qū)

1、第三章第三章鎖和表分區(qū)回顧q Oracle函數(shù)q Oracle偽列目標(biāo)q 理解鎖定的概念q 了解和使用表分區(qū)鎖的概念 2-1q 鎖是數(shù)據(jù)庫用來控制共享資源并發(fā)訪問的機(jī)制。q 鎖用于保護(hù)正在被修改的數(shù)據(jù)q 直到提交或回滾了事務(wù)之后，其他用戶才可以更新數(shù)據(jù)鎖的概念 2-2修改表修改表拒絕訪問Toy_IDNamePriceT001Barbie20T002GI Joe45鎖定的優(yōu)點(diǎn)q一致性一致性 - 一次只允許一個(gè)用戶修改數(shù)據(jù)q完整性完整性 - 為所有用戶提供正確的數(shù)據(jù)。如果一個(gè)用戶進(jìn)行了修改并保存，所做的修改將反映給所有用戶q并行性并行性 允許多個(gè)用戶訪問同一數(shù)據(jù)Toy_IDNamePriceT00

2、1Barbie 20T002GI Joe 45修改表中的數(shù)據(jù)查看表中的數(shù)據(jù)允許訪問表級(jí)鎖行級(jí)鎖鎖的類型鎖的類型行級(jí)鎖 3-1Toy_IDNamePriceT001Barbie20T002GI Joe45更新 T002 行更新 T001 行行被鎖定q對(duì)正在被修改的行進(jìn)行鎖定。其他用戶可以訪問除被鎖定的行以外的行允許訪問行級(jí)鎖 3-2q行級(jí)鎖是一種排他鎖，防止其他事務(wù)修改此行q在使用以下語句時(shí)，Oracle會(huì)自動(dòng)應(yīng)用行級(jí)鎖：qINSERTqUPDATEqDELETEqSELECT FOR UPDATEqSELECT FOR UPDATE語句允許用戶一次鎖定多條記錄進(jìn)行更新q使用COMMIT或ROL

3、LBACK語句釋放鎖行級(jí)鎖 3-3q SELECT FOR UPDATE語法:SELECT FOR UPDATE OF columnsWAIT n | NOWAIT;SQL SELECT * FROM order_master WHERE vencode=V002 FOR UPDATE OF odate, del_date;SQL UPDATE order_master SET del_date=28-8月-05 WHERE vencode=V002;SQL COMMIT;SQL SELECT * FROM order_master WHERE vencode=V002 FOR UPDATE

4、WAIT 5;SQL SELECT * FROM order_master WHERE vencode=V002 FOR UPDATE NOWAIT;表級(jí)鎖 3-1Toy_IDNamePriceT001Barbie20T002GI Joe45修改表中的行 更新表 拒絕訪問鎖定整個(gè)表，限制其他用戶對(duì)表的訪問。表級(jí)鎖 3-2表級(jí)鎖類型行共享行排他共享使用命令顯示地鎖定表，應(yīng)用表級(jí)鎖的語法是: LOCK TABLE table_name IN mode MODE;共享行排他排他表級(jí)鎖 3-3q 共享(SHARE)：其他用戶只能select，不能insert、update、delete，多個(gè)用戶可以對(duì)

5、同一張表加共享鎖。 排他(EXCLUSIVE)：其他用戶只能select，不能insert、update、delete，不能加任何類型鎖。 行共享(ROW SHARE)：其他用戶可以select并加除排他鎖以外的其他類型鎖。 行排他(ROW EXCLUSIVE)：其他用戶可以select并加除排他鎖和共享鎖以外的其他類型鎖。 共享行排他(SHARE ROW EXCLUSIVE):其他用戶只能select，不能insert、update、delete，不能加共享鎖、共享行排他鎖和排他鎖。 死鎖q 當(dāng)兩個(gè)事務(wù)相互等待對(duì)方釋放資源時(shí)，就會(huì)形成死鎖q Oracle會(huì)自動(dòng)檢測(cè)死鎖，并通過結(jié)束其中的一個(gè)事

6、務(wù)來解決死鎖q 右邊是一個(gè)死鎖的例子T1T2lock (D1)時(shí)間時(shí)間lock (D2)lock (D2)lock (D1)等待等待等待等待表分區(qū)q 允許用戶將一個(gè)表分成多個(gè)分區(qū)q 用戶可以執(zhí)行查詢，只訪問表中的特定分區(qū)q 將不同的分區(qū)存儲(chǔ)在不同的磁盤，提高訪問性能和安全性q 可以獨(dú)立地備份和恢復(fù)每個(gè)分區(qū)NameAddressStateCountryJohn Smith34th Rd.California USMicheal ClarkeLK Rd. New York USJack Jones9th RoadCalifornia USBob Simmons12th StreetNew York

7、 USJim Taylor53rd RoadNew York US更新表只訪問 P1P1 分區(qū)P2 分區(qū)表分區(qū)的類型 9-1分區(qū)方法分區(qū)方法范圍分區(qū)散列分區(qū)列表分區(qū)復(fù)合分區(qū)表分區(qū)的類型 9-2q 范圍分區(qū)q 以表中的一個(gè)列或一組列的值的范圍分區(qū)q 范圍分區(qū)的語法：PARTITION BY RANGE (column_name)( PARTITION part1 VALUE LESS THAN(range1), PARTITION part2 VALUE LESS THAN(range2), . PARTITION partN VALUE LESS THAN(MAXVALUE);表分區(qū)的類型 9

8、-3SQL CREATE TABLE Sales( Product_ID varchar2 (5), Sales_Cost number (10)PARTITION BY RANGE (Sales_Cost)( PARTITION P1 VALUES LESS THAN (1000), PARTITION P2 VALUES LESS THAN (2000), PARTITION P3 VALUES LESS THAN (3000);根據(jù) Sales_Cost 創(chuàng)建分區(qū)分區(qū)的名稱包含銷售成本低于1000 的所有產(chǎn)品的值 SQL CREATE TABLE SALES2 (PRODUCT_ID V

9、ARCHAR2(5),SALES_DATE DATE NOT NULL,SALES_COST NUMBER(10)PARTITION BY RANGE (SALES_DATE)( PARTITION P1 VALUES LESS THAN (DATE 2003-01-01), PARTITION P2 VALUES LESS THAN (DATE 2004-01-01), PARTITION P3 VALUES LESS THAN (MAXVALUE);q 范圍分區(qū)示例表分區(qū)的類型 9-4q 散列分區(qū)q允許用戶對(duì)不具有邏輯范圍的數(shù)據(jù)進(jìn)行分區(qū) q通過在分區(qū)鍵上執(zhí)行HASH函數(shù)決定存儲(chǔ)的分區(qū)q將數(shù)

10、據(jù)平均地分布到不同的分區(qū)q 散列分區(qū)語法PARTITION BY HASH (column_name)PARTITIONS number_of_partitions;或PARTITION BY HASH (column_name)( PARTITION part1, PARTITION part2, . PARTITION partN);表分區(qū)的類型 9-5q 散列分區(qū)示例SQL CREATE TABLE Employee( Employee_ID varchar2 (5), Employee_Name varchar2(20), Department varchar2 (10)PARTITI

11、ON BY HASH (Department)( Partition D1, Partition D2, Partition D3); 在表 Employee上創(chuàng)建分區(qū)鍵 Department分區(qū)的名稱創(chuàng)建 3 個(gè)分區(qū)SQL CREATE TABLE EMPLOYEE( EMP_ID NUMBER(4), EMP_NAME VARCHAR2(14), EMP_ADDRESS VARCHAR2(15), DEPARTMENT VARCHAR2(10)PARTITION BY HASH (DEPARTMENT)PARTITIONS 4; 表分區(qū)的類型 9-6q 列表分區(qū)q允許用戶將不相關(guān)的數(shù)據(jù)組織

12、在一起q 列表分區(qū)的語法：PARTITION BY LIST (column_name)( PARTITION part1 VALUES (values_list1), PARTITION part2 VALUES (values_list2), . PARTITION partN VALUES (DEFAULT);表分區(qū)的類型 9-7SQL CREATE TABLE Employee( Emp_ID number (4), Emp_Name varchar2 (14), Emp_Address varchar2 (15)PARTITION BY LIST (Emp_Address)( Par

13、tition north values (芝加哥), Partition west values (舊金山, 洛杉磯), Partition south values (亞特蘭大, 達(dá)拉斯, 休斯頓), Partition east values (紐約, 波斯頓);包含住在芝加哥的職員的記錄根據(jù)職員住址在表上創(chuàng)建的列表分區(qū)分區(qū)的名稱q 列表分區(qū)示例表分區(qū)的類型 9-8q復(fù)合分區(qū)q范圍分區(qū)與散列分區(qū)的組合q復(fù)合分區(qū)的語法：PARTITION BY RANGE (column_name1)SUBPARTITION BY HASH (column_name2)SUBPARTITIONS numbe

14、r_of_partitions( PARTITION part1 VALUE LESS THAN(range1), PARTITION part2 VALUE LESS THAN(range2), . PARTITION partN VALUE LESS THAN(MAXVALUE);表分區(qū)的類型 9-9SQL CREATE TABLE SALES( PRODUCT_ID VARCHAR2 (5), SALES_DATE DATE NOT NULL, SALES_COST NUMBER (10)PARTITION BY RANGE (SALES_DATE)SUBPARTITION BY HAS

15、H (PRODUCT_ID)SUBPARTITIONS 5( PARTITION S1 VALUES LESS THAN (TO_DATE(01/4月/2001, DD/MON/YYYY), PARTITION S2 VALUES LESS THAN (TO_DATE(01/7月/2001, DD/MON/YYYY), PARTITION S3 VALUES LESS THAN (TO_DATE(01/9月/2001, DD/MON/YYYY), PARTITION S4 VALUES LESS THAN (MAXVALUE);創(chuàng)建的四個(gè)范圍分區(qū)的名稱在表的 Sales_Date 列中創(chuàng)建范圍

16、分區(qū)在表的 Product_ID 列創(chuàng)建散列子分區(qū)在每個(gè)范圍分區(qū)中創(chuàng)建 5 個(gè)散列子分區(qū)q 復(fù)合分區(qū)示例操縱已分區(qū)的表 q在已分區(qū)的表中插入數(shù)據(jù)與操作普通表完全相同，Oracle會(huì)自動(dòng)將數(shù)據(jù)保存到對(duì)應(yīng)的分區(qū)q查詢、修改和刪除分區(qū)表時(shí)可以顯式指定要操作的分區(qū) INSERT INTO SALES3 VALUES (P001, 02-3月-2001, 2000);INSERT INTO SALES3 VALUES (P002, 10-5月-2001, 2508);INSERT INTO SALES3 VALUES (P003, 05-7月-2001, 780);INSERT INTO SALES3

17、VALUES (P004, 12-9月-2001, 1080);SELECT * FROM SALES3 PARTITION (P3);DELETE FROM SALES3 PARTITION (P2);分區(qū)維護(hù)操作 q分區(qū)維護(hù)操作修改已分區(qū)表的分區(qū)。q分區(qū)維護(hù)的類型：q計(jì)劃事件 定期刪除最舊的分區(qū)q非計(jì)劃事件 解決應(yīng)用程序或系統(tǒng)問題q分區(qū)維護(hù)操作有：q添加分區(qū)q刪除分區(qū)q截?cái)喾謪^(qū)q合并分區(qū)q拆分分區(qū)維護(hù)分區(qū) 2-1q 添加分區(qū) 在最后一個(gè)分區(qū)之后添加新分區(qū)SQL ALTER TABLE SALES ADD PARTITION P4 VALUES LESS THAN (4000);q 刪除分區(qū) 刪除一個(gè)指定的分區(qū)，分區(qū)的數(shù)據(jù)也隨之刪除SQL ALTER TABLE SALES DROP PARTITION P4;q 截?cái)喾謪^(qū) 刪除指定分區(qū)中的所有記錄 SQL ALTER TABLE SALES TRUNCATE PARTITION P3;維護(hù)分區(qū) 2-2q 合并分區(qū) - 將范圍分區(qū)或復(fù)合分區(qū)的兩個(gè)相鄰分區(qū)連接起來SQL ALTER TABLE SALES