Oracle編程語言鎖和表分區(qū).ppt

1、鎖和表分區(qū),2,回顧,SQL 是數(shù)據(jù)庫語言，Oracle 使用該語言在數(shù)據(jù)庫中存儲和檢索信息 Oracle 支持各種數(shù)據(jù)類型，如 VARCHAR2、NUMBER、LONG、RAW 和 DATE 等 數(shù)據(jù)操縱語言用于查詢和修改表中的數(shù)據(jù) 事務控制語言管理事務的一致性 SQL 操作符包括算術(shù)、比較、邏輯、集合和連接操作符 SQL 函數(shù)可以大致分為單行函數(shù)、聚合函數(shù)和分析函數(shù),3,目標,理解鎖定的概念 了解和使用表分區(qū),4,鎖的概念 2-1,鎖是數(shù)據(jù)庫用來控制共享資源并發(fā)訪問的機制。 鎖用于保護正在被修改的數(shù)據(jù) 直到提交或回滾了事務之后，其他用戶才可以更新數(shù)據(jù),5,鎖的概念 2-2,修改表,修改表,

2、拒絕訪問,6,鎖定的優(yōu)點,一致性 - 一次只允許一個用戶修改數(shù)據(jù) 完整性 - 為所有用戶提供正確的數(shù)據(jù)。如果一個用戶進行了修改并保存，所做的修改將反映給所有用戶,并行性 允許多個用戶訪問同一數(shù)據(jù),修改表中 的數(shù)據(jù),查看表中的數(shù)據(jù),允許訪問,7,表級鎖,行級鎖,鎖的類型,鎖的類型,8,行級鎖 3-1,更新 T002 行,更新 T001 行,行被鎖定,對正在被修改的行進行鎖定。其他用戶可以訪問除被鎖定的行以外的行,允許訪問,9,行級鎖 3-2,行級鎖是一種排他鎖，防止其他事務修改此行 在使用以下語句時，Oracle會自動應用行級鎖： INSERT UPDATE DELETE SELECT FOR

3、UPDATE SELECT FOR UPDATE語句允許用戶一次鎖定多條記錄進行更新 使用COMMIT或ROLLBACK語句釋放鎖,10,行級鎖 3-3,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

4、;,SQL SELECT * FROM order_master WHERE vencode=V002 FOR UPDATE WAIT 5;,SQL SELECT * FROM order_master WHERE vencode=V002 FOR UPDATE NOWAIT;,11,表級鎖 3-1,修改表中的行,更新表,拒絕訪問,鎖定整個表，限制其他用戶對表的訪問。,12,表級鎖 3-2,表級鎖類型,行共享,行排他,共享,使用命令顯示地鎖定表，應用表級鎖的語法是: LOCK TABLE table_name IN mode MODE;,共享行排他,排他,13,表級鎖 3-3,行共享 (ROW

5、 SHARE) 禁止排他鎖定表 行排他(ROW EXCLUSIVE) 禁止使用排他鎖和共享鎖 共享鎖(SHARE) 鎖定表，僅允許其他用戶查詢表中的行 禁止其他用戶插入、更新和刪除行 多個用戶可以同時在同一個表上應用此鎖 共享行排他(SHARE ROW EXCLUSIVE) 比共享鎖更多的限制，禁止使用共享鎖及更高的鎖 排他(EXCLUSIVE) 限制最強的表鎖，僅允許其他用戶查詢該表的行。禁止修改和鎖定表,14,死鎖,當兩個事務相互等待對方釋放資源時，就會形成死鎖 Oracle會自動檢測死鎖，并通過結(jié)束其中的一個事務來解決死鎖 右邊是一個死鎖的例子,15,表分區(qū),允許用戶將一個表分成多個分區(qū)

6、 用戶可以執(zhí)行查詢，只訪問表中的特定分區(qū) 將不同的分區(qū)存儲在不同的磁盤，提高訪問性能和安全性 可以獨立地備份和恢復每個分區(qū),更新表,只訪問 P1,P1 分區(qū),P2 分區(qū),16,表分區(qū)的類型 9-1,分區(qū)方法,范圍分區(qū),散列分區(qū),列表分區(qū),復合分區(qū),17,表分區(qū)的類型 9-2,范圍分區(qū) 以表中的一個列或一組列的值的范圍分區(qū) 范圍分區(qū)的語法：,PARTITION BY RANGE (column_name) ( PARTITION part1 VALUE LESS THAN(range1), PARTITION part2 VALUE LESS THAN(range2), . PARTITION

7、partN VALUE LESS THAN(MAXVALUE) );,18,表分區(qū)的類型 9-3,SQL 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ū)的名稱,包

8、含銷售成本低于1000 的所有產(chǎn)品的值,SQL CREATE TABLE SALES2 ( PRODUCT_ID VARCHAR2(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ū)示例

9、,19,表分區(qū)的類型 9-4,散列分區(qū) 允許用戶對不具有邏輯范圍的數(shù)據(jù)進行分區(qū) 通過在分區(qū)鍵上執(zhí)行HASH函數(shù)決定存儲的分區(qū) 將數(shù)據(jù)平均地分布到不同的分區(qū) 散列分區(qū)語法,PARTITION BY HASH (column_name) PARTITIONS number_of_partitions; 或 PARTITION BY HASH (column_name) ( PARTITION part1 TABLESPACE tbs1, PARTITION part2 TABLESPACE tbs2, . PARTITION partN TABLESPACE tbsN);,20,表分區(qū)的類型 9-

10、5,散列分區(qū)示例,SQL CREATE TABLE Employee ( Employee_ID varchar2 (5), Employee_Name varchar2(20), Department varchar2 (10) ) PARTITION 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 VARCHA

11、R2(14), EMP_ADDRESS VARCHAR2(15), DEPARTMENT VARCHAR2(10) ) PARTITION BY HASH (DEPARTMENT) PARTITIONS 4;,21,表分區(qū)的類型 9-6,列表分區(qū) 允許用戶將不相關(guān)的數(shù)據(jù)組織在一起 列表分區(qū)的語法：,PARTITION BY LIST (column_name) ( PARTITION part1 VALUES (values_list1), PARTITION part2 VALUES (values_list2), . PARTITION partN VALUES (DEFAULT) );,

12、22,表分區(qū)的類型 9-7,SQL CREATE TABLE Employee ( Emp_ID number (4), Emp_Name varchar2 (14), Emp_Address varchar2 (15) ) PARTITION BY LIST (Emp_Address) ( Partition north values (芝加哥), Partition west values (舊金山, 洛杉磯), Partition south values (亞特蘭大, 達拉斯, 休斯頓), Partition east values (紐約, 波斯頓) );,包含住在芝加哥的職員的記錄

13、,根據(jù)職員住址在表上創(chuàng)建的列表分區(qū),分區(qū)的名稱,列表分區(qū)示例,23,表分區(qū)的類型 9-8,復合分區(qū) 范圍分區(qū)與散列分區(qū)或列表分區(qū)的組合 復合分區(qū)的語法：,PARTITION BY RANGE (column_name1) SUBPARTITION BY HASH (column_name2) SUBPARTITIONS number_of_partitions ( PARTITION part1 VALUE LESS THAN(range1), PARTITION part2 VALUE LESS THAN(range2), . PARTITION partN VALUE LESS THAN(

14、MAXVALUE) );,24,表分區(qū)的類型 9-9,SQL CREATE TABLE SALES ( PRODUCT_ID VARCHAR2 (5), SALES_DATE DATE NOT NULL, SALES_COST NUMBER (10) ) PARTITION BY RANGE (SALES_DATE) SUBPARTITION BY HASH (PRODUCT_ID) SUBPARTITIONS 5 ( PARTITION S1 VALUES LESS THAN (TO_DATE(01/4月/2001, DD/MON/YYYY), PARTITION S2 VALUES LES

15、S 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)建范圍分區(qū),在表的 Product_ID 列創(chuàng)建散列子分區(qū),在每個范圍分區(qū)中 創(chuàng)建 5 個散列子分區(qū),復合分區(qū)示例,25,操縱已分區(qū)的表,在已分區(qū)的表中插入數(shù)據(jù)與操作普通表完全相同，Oracle會自動將數(shù)據(jù)保存到對應的分區(qū) 查詢、修改和

16、刪除分區(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 VALUES (P004, 12-9月-2001, 1080);,SELECT * FROM SALES3 PARTITION (P3);,DELETE FROM SALES3 PARTITION (P2);,26

17、,分區(qū)維護操作,分區(qū)維護操作修改已分區(qū)表的分區(qū)。 分區(qū)維護的類型： 計劃事件 定期刪除最舊的分區(qū) 非計劃事件 解決應用程序或系統(tǒng)問題 分區(qū)維護操作有： 添加分區(qū) 刪除分區(qū) 截斷分區(qū) 合并分區(qū) 拆分分區(qū),27,維護分區(qū) 2-1,添加分區(qū) 在最后一個分區(qū)之后添加新分區(qū),SQL ALTER TABLE SALES ADD PARTITION P4 VALUES LESS THAN (4000);,刪除分區(qū) 刪除一個指定的分區(qū)，分區(qū)的數(shù)據(jù)也隨之刪除,SQL ALTER TABLE SALES DROP PARTITION P4;,截斷分區(qū) 刪除指定分區(qū)中的所有記錄,SQL ALTER TABLE SALES TRUNCATE PARTITION P3;,28,維護分區(qū) 2-2,合并分區(qū) - 將范圍分區(qū)或復合分區(qū)的兩個相鄰分區(qū)連接起來,SQL ALTER TABLE SALES MERGE