Oracle编程语言锁和表分区.ppt
2回顾qSQL 是数据库语言，Oracle 使用该语言在数据库中存储和检索信息qOracle 支持各种数据类型，如 VARCHAR2、NUMBER、LONG、RAW 和 DATE 等q数据操纵语言用于查询和修改表中的数据q事务控制语言管理事务的一致性qSQL 操作符包括算术、比较、逻辑、集合和连接操作符qSQL 函数可以大致分为单行函数、聚合函数和分析函数3目标q理解锁定的概念q了解和使用表分区4锁的概念 2-1q锁是数据库用来控制共享资源并发访问的机制。q锁用于保护正在被修改的数据q直到提交或回滚了事务之后，其他用户才可以更新数据5锁的概念 2-2修改表修改表拒绝访问Toy_IDNamePriceT001Barbie20T002GI Joe456锁定的优点q 一致性一致性 - 一次只允许一个用户修改数据q 完整性完整性 - 为所有用户提供正确的数据。如果一个用户进行了修改并保存，所做的修改将反映给所有用户q并行性并行性 允许多个用户访问同一数据Toy_IDNamePriceT001Barbie 20T002GI Joe 45修改表中的数据查看表中的数据允许访问7表级锁行级锁锁的类型锁的类型8行级锁 3-1Toy_IDNamePriceT001Barbie20T002GI Joe45更新 T002 行更新 T001 行行被锁定q对正在被修改的行进行锁定。其他用户可以访问除被锁定的行以外的行允许访问9行级锁 3-2q行级锁是一种排他锁，防止其他事务修改此行q在使用以下语句时，Oracle会自动应用行级锁：qINSERTqUPDATEqDELETEqSELECT FOR UPDATEqSELECT FOR UPDATE语句允许用户一次锁定多条记录进行更新q使用COMMIT或ROLLBACK语句释放锁10行级锁 3-3qSELECT 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 WAIT 5;SQL SELECT * FROM order_master WHERE vencode=V002 FOR UPDATE NOWAIT;11表级锁 3-1Toy_IDNamePriceT001Barbie20T002GI Joe45修改表中的行 更新表 拒绝访问锁定整个表，限制其他用户对表的访问。12表级锁 3-2表级锁类型行共享行排他共享使用命令显示地锁定表，应用表级锁的语法是: LOCK TABLE table_name IN mode MODE;共享行排他排他13表级锁 3-3q行共享 (ROW SHARE) 禁止排他锁定表q行排他(ROW EXCLUSIVE) 禁止使用排他锁和共享锁q共享锁(SHARE)q锁定表，仅允许其他用户查询表中的行q禁止其他用户插入、更新和删除行q多个用户可以同时在同一个表上应用此锁q共享行排他(SHARE ROW EXCLUSIVE) 比共享锁更多的限制，禁止使用共享锁及更高的锁q排他(EXCLUSIVE) 限制最强的表锁，仅允许其他用户查询该表的行。禁止修改和锁定表14死锁q当两个事务相互等待对方释放资源时，就会形成死锁qOracle会自动检测死锁，并通过结束其中的一个事务来解决死锁q右边是一个死锁的例子T1T2lock (D1)时间时间lock (D2)lock (D2)lock (D1)等待等待等待等待15表分区q允许用户将一个表分成多个分区q用户可以执行查询，只访问表中的特定分区q将不同的分区存储在不同的磁盘，提高访问性能和安全性q可以独立地备份和恢复每个分区NameAddressStateCountryJohn Smith34th Rd.California USMicheal ClarkeLK Rd. New York USJack Jones9th RoadCalifornia USBob Simmons12th StreetNew York USJim Taylor53rd RoadNew York US更新表只访问 P1P1 分区P2 分区16表分区的类型 9-1分区方法分区方法范围分区散列分区列表分区复合分区17表分区的类型 9-2q范围分区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);18表分区的类型 9-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);根据 Sales_Cost 创建分区分区的名称包含销售成本低于1000 的所有产品的值 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范围分区示例19表分区的类型 9-4q散列分区q允许用户对不具有逻辑范围的数据进行分区 q通过在分区键上执行HASH函数决定存储的分区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表分区的类型 9-5q散列分区示例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上创建分区键 Department分区的名称创建 3 个分区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; 21表分区的类型 9-6q列表分区q允许用户将不相关的数据组织在一起q列表分区的语法：PARTITION BY LIST (column_name)( PARTITION part1 VALUES (values_list1), PARTITION part2 VALUES (values_list2), . PARTITION partN VALUES (DEFAULT);22表分区的类型 9-7SQL 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 (纽约, 波斯顿);包含住在芝加哥的职员的记录根据职员住址在表上创建的列表分区分区的名称q列表分区示例23表分区的类型 9-8q复合分区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(MAXVALUE);24表分区的类型 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 HASH (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);创建的四个范围分区的名称在表的 Sales_Date 列中创建范围分区在表的 Product_ID 列创建散列子分区在每个范围分区中创建 5 个散列子分区q复合分区示例25操纵已分区的表 q在已分区的表中插入数据与操作普通表完全相同，Oracle会自动将数据保存到对应的分区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分区维护操作 q分区维护操作修改已分区表的分区。q分区维护的类型：q计划事件 定期删除最旧的分区q非计划事件 解决应用程序或系统问题q分区维护操作有：q添加分区q删除分区q截断分区q合并分区q拆分分区27维护分区 2-1q添加分区 在最后一个分区之后添加新分区SQL ALTER TABLE SALES ADD PARTITION P4 VALUES LESS THAN (4000);q删除分区 删除一个指定的分区，分区的数据也随之删除SQL ALTER TABLE SALES DROP PARTITION P4;q截断分区 删除指定分区中的所有记录 SQL ALTER TABLE SALES TRUNCATE PARTITION P3;28维护分区 2-2q合并分区 - 将范围分区或复合分区的两个相邻分区连接起来SQL ALTER TABLE SALES MERGE PARTITIONS S1, S2 INTO PARTITION S2;q拆分分区 - 将一个大分区中的记录拆分到两个分区中SQL ALTER TABLE SALES SPLIT PARTITION P2 AT (1500)INTO (PARTITION P21, PARTITION P22);29总结q锁用于保护多用户环境下被修改的数据q锁分为两种级别，即行级锁和表级锁q表分区允许将一个表划分成几部分，以改善大型应用系统的性能q分区方法包括范围分区、散列分区、复合分区和列表分区q分区维护操作包括添加、删除、截断、合并和拆分分区30结束语结束语
