oracle鎖和表分區(qū)

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

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

3、LBACK語句釋放鎖行級鎖 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;表級鎖 3-1Toy_IDNamePriceT001Barbie20T002GI Joe45修改表中的行 更新表 拒絕訪問鎖定整個表，限制其他用戶對表的訪問。表級鎖 3-2表級鎖類型行共享行排他共享使用命令顯示地鎖定表，應(yīng)用表級鎖的語法是: LOCK TABLE table_name IN mode MODE;共享行排他排他表級鎖 3-3q 共享(SHARE)：其他用戶只能select，不能insert、update、delete，多個用戶可以對

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

6、務(wù)來解決死鎖q 右邊是一個死鎖的例子T1T2lock (D1)時間時間lock (D2)lock (D2)lock (D1)等待等待等待等待表分區(qū)q 允許用戶將一個表分成多個分區(qū)q 用戶可以執(zhí)行查詢，只訪問表中的特定分區(qū)q 將不同的分區(qū)存儲在不同的磁盤，提高訪問性能和安全性q 可以獨立地備份和恢復(fù)每個分區(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 以表中的一個列或一組列的值的范圍分區(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允許用戶對不具有邏輯范圍的數(shù)據(jù)進行分區(qū) q通過在分區(qū)鍵上執(zhí)行HASH函數(shù)決定存儲的分區(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 個分區(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 (亞特蘭大, 達拉斯, 休斯頓), 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)建的四個范圍分區(qū)的名稱在表的 Sales_Date 列中創(chuàng)建范圍

16、分區(qū)在表的 Product_ID 列創(chuàng)建散列子分區(qū)在每個范圍分區(qū)中創(chuàng)建 5 個散列子分區(qū)q 復(fù)合分區(qū)示例操縱已分區(qū)的表 q在已分區(qū)的表中插入數(shù)據(jù)與操作普通表完全相同，Oracle會自動將數(shù)據(jù)保存到對應(yīng)的分區(qū)q查詢、修改和刪除分區(qū)表時可以顯式指定要操作的分區(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ū)維護操作 q分區(qū)維護操作修改已分區(qū)表的分區(qū)。q分區(qū)維護的類型：q計劃事件 定期刪除最舊的分區(qū)q非計劃事件 解決應(yīng)用程序或系統(tǒng)問題q分區(qū)維護操作有：q添加分區(qū)q刪除分區(qū)q截斷分區(qū)q合并分區(qū)q拆分分區(qū)維護分區(qū) 2-1q 添加分區(qū) 在最后一個分區(qū)之后添加新分區(qū)SQL ALTER TABLE SALES ADD PARTITION P4 VALUES LESS THAN (4000);q 刪除分區(qū) 刪除一個指定的分區(qū)，分區(qū)的數(shù)據(jù)也隨之刪除SQL ALTER TABLE SALES DROP PARTITION P4;q 截斷分區(qū) 刪除指定分區(qū)中的所有記錄 SQL ALTER TABLE SALES TRUNCATE PARTITION P3;維護分區(qū) 2-2q 合并分區(qū) - 將范圍分區(qū)或復(fù)合分區(qū)的兩個相鄰分區(qū)連接起來SQL ALTER TABLE SALES