1、第三章,锁和表分区,2,回顾,SQL 是数据库语言，Oracle 使用该语言在数据库中存储和检索信息 Oracle 支持各种数据类型，如 VARCHAR2、NUMBER、LONG、RAW 和 DATE 等 数据操纵语言用于查询和修改表中的数据 事务控制语言管理事务的一致性 SQL 操作符包括算术、比较、逻辑、集合和连接操作符 SQL 函数可以大致分为单行函数、聚合函数和分析函数,3,目标,理解锁定的概念 了解和使用表分区,4,锁的概念 2-1,锁是数据库用来控制共享资源并发访问的机制。 锁用于保护正在被修改的数据 直到提交或回滚了事务之后，其他用户才可以更新数据,5,锁的概念 2-2,修改表,
2、修改表,拒绝访问,6,锁定的优点,一致性 - 一次只允许一个用户修改数据 完整性 - 为所有用户提供正确的数据。如果一个用户进行了修改并保存，所做的修改将反映给所有用户,并行性 允许多个用户访问同一数据,修改表中 的数据,查看表中的数据,允许访问,7,表级锁,行级锁,锁的类型,锁的类型,8,行级锁 3-1,更新 T002 行,更新 T001 行,行被锁定,对正在被修改的行进行锁定。其他用户可以访问除被锁定的行以外的行,允许访问,9,行级锁 3-2,行级锁是一种排他锁，防止其他事务修改此行 在使用以下语句时，Oracle会自动应用行级锁： INSERT UPDATE DELETE SELECT
3、FOR UPDATE SELECT FOR UPDATE语句允许用户一次锁定多条记录进行更新 使用COMMIT或ROLLBACK语句释放锁,10,行级锁 3-3,SELECT FOR UPDATE语法:SELECT FOR UPDATE OF columns WAIT n | NOWAIT;,SQL SELECT * FROM order_master WHERE vencode=V002FOR UPDATE OF odate, del_date; SQL UPDATE order_master SET del_date=28-8月-05WHERE vencode=V002; SQL COMM
4、IT;,SQL SELECT * FROM order_master WHERE vencode=V002FOR UPDATE WAIT 5;,SQL SELECT * FROM order_master WHERE vencode=V002FOR UPDATE NOWAIT;,11,表级锁 3-1,修改表中的行,更新表,拒绝访问,锁定整个表，限制其他用户对表的访问。,12,表级锁 3-2,表级锁类型,行共享,行排他,共享,使用命令显示地锁定表，应用表级锁的语法是:LOCK TABLE table_name IN mode MODE;,共享行排他,排他,13,共享(SHARE)：其他用户只能s
5、elect，不能insert、update、delete，多个用户可以对同一张表加共享锁。 排他(EXCLUSIVE)：其他用户只能select，不能insert、update、delete，不能加任何类型锁。 行共享(ROW SHARE)：其他用户可以select并加除排他锁以外的其他类型锁。 行排他(ROW EXCLUSIVE)：其他用户可以select并加除排他锁和共享锁以外的其他类型锁。 共享行排他(SHARE ROW EXCLUSIVE):其他用户只能select，不能insert、update、delete，不能加共享锁、共享行排他锁和排他锁。,14,表级锁 3-3,行共享 (RO
6、W SHARE) 禁止排他锁定表 行排他(ROW EXCLUSIVE) 禁止使用排他锁和共享锁 共享锁(SHARE) 锁定表，仅允许其他用户查询表中的行 禁止其他用户插入、更新和删除行 多个用户可以同时在同一个表上应用此锁 共享行排他(SHARE ROW EXCLUSIVE) 比共享锁更多的限制，禁止使用共享锁及更高的锁 排他(EXCLUSIVE) 限制最强的表锁，仅允许其他用户查询该表的行。禁止修改和锁定表,15,死锁,当两个事务相互等待对方释放资源时，就会形成死锁 Oracle会自动检测死锁，并通过结束其中的一个事务来解决死锁 右边是一个死锁的例子,16,表分区,允许用户将一个表分成多个分
7、区 用户可以执行查询，只访问表中的特定分区 将不同的分区存储在不同的磁盘，提高访问性能和安全性 可以独立地备份和恢复每个分区,更新表,只访问 P1,P1 分区,P2 分区,17,在大型的企业应用或企业级的数据库应用中，要处理的数据量通常可以达到几十到几百GB，有的甚至可以到TB级。虽然存储介质和数据处理技术的发展也很快，但是仍然不能满足用户的需求，为了使用户的大量的数据在读写操作和查询中速度更快，Oracle提供了对表和索引进行分区的技术，以改善大型应用系统的性能。 使用分区的优点： 增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用； 维护方便：如果表的某个分区出现故障，需要修
8、复数据，只修复该分区即可； 均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能； 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。,18,表分区的类型 9-1,分区方法,范围分区,散列分区,列表分区,复合分区,19,表分区的类型 9-2,范围分区 以表中的一个列或一组列的值的范围分区 范围分区的语法：,PARTITION BY RANGE (column_name) (PARTITION part1 VALUE LESS THAN(range1),PARTITION part2 VALUE LESS THAN(range2),.PARTITION par
9、tN VALUE LESS THAN(MAXVALUE) );,20,表分区的类型 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) );,根据 Sales_Cost 创建分区,分区的名称,包含销售成本低于1
10、000 的所有产品的值,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) );,范围分区示例,21,例题： CR
11、EATE TABLESPACE USER01 DATAFILE c:USER01.dbf SIZE 2M; CREATE TABLESPACE USER02 DATAFILE c:USER02.dbf SIZE 2M; CREATE TABLESPACE USER03 DATAFILE c:USER03.dbf SIZE 2M; CREATE TABLESPACE USER04 DATAFILE c:USER04.dbf SIZE 2M;,分区表类型1（范围分区RANGE）将数据分配至指定范围的分区段 CREATE TABLE T51 (customer_id number(3), sale_
12、date date) PARTITION BY RANGE(customer_id) ( PARTITION S1 VALUES LESS THAN (3) TABLESPACE USER01, PARTITION S2 VALUES LESS THAN (6) TABLESPACE USER02, PARTITION S3 VALUES LESS THAN (9) TABLESPACE USER03, PARTITION S4 VALUES LESS THAN (maxvalue) TABLESPACE USER04 );,22,-查找表分区的 表名，分区表空间，分区名 ，分区名值 sele
13、ct table_name,tablespace_name,partition_name,high_value from USER_TAB_PARTITIONS,ALTER SESSION SET nls_date_format=YYYY-MM-DD;设置当前会话参数 INSERT INTO T51 VALUES(1,2005-03-04); INSERT INTO T51 VALUES(2,sysdate);SELECT * FROM T51 PARTITION(S1);,23,表分区的类型 9-4,散列分区 允许用户对不具有逻辑范围的数据进行分区 通过在分区键上执行HASH函数决定存储的分
14、区 将数据平均地分布到不同的分区 散列分区语法,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);,24,表分区的类型 9-5,散列分区示例,SQL CREATE TABLE Employee (Employee_ID varchar2 (5),Emp
15、loyee_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 H
16、ASH (DEPARTMENT) PARTITIONS 4;,25,分区表类型2（散列分区HASH）,CREATE TABLE T52 (product_id number(10), product_name varchar2(20) PARTITION BY HASH(product_id) ( PARTITION P1 TABLESPACE USER01, PARTITION P2 TABLESPACE USER02, PARTITION P3 TABLESPACE USER03, PARTITION P4 TABLESPACE USER04 );,SELECT * FROM T52 PA
17、RTITION(P1);,26,表分区的类型 9-6,列表分区 允许用户将不相关的数据组织在一起 列表分区的语法：,PARTITION BY LIST (column_name) (PARTITION part1 VALUES (values_list1),PARTITION part2 VALUES (values_list2),.PARTITION partN VALUES (DEFAULT) );,27,表分区的类型 9-7,SQL CREATE TABLE Employee (Emp_ID number (4),Emp_Name varchar2 (14),Emp_Address va
18、rchar2 (15) ) PARTITION BY LIST (Emp_Address) (Partition north values (芝加哥),Partition west values (旧金山, 洛杉矶),Partition south values (亚特兰大, 达拉斯, 休斯顿),Partition east values (纽约, 波斯顿) );,包含住在芝加哥的职员的记录,根据职员住址在表上创建的列表分区,分区的名称,列表分区示例,28,表分区的类型 9-8,复合分区 范围分区与散列分区或列表分区的组合 复合分区的语法：,PARTITION BY RANGE (column
19、_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) );,29,表分区的类型 9-9,SQL CREATE TABLE SALES (PRODUCT_ID VARCHAR2 (5),SALES_DATE DATE NOT NULL,SALES_C
20、OST 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
21、S4 VALUES LESS THAN (MAXVALUE) );,创建的四个范围分区的名称,在表的 Sales_Date 列中创建范围分区,在表的 Product_ID 列创建散列子分区,在每个范围分区中 创建 5 个散列子分区,复合分区示例,30,create table test2(transaction_id number primary key,item_description varchar2(300) ) partition by range(transaction_id)subpartition by hash(item_description)subpartitions 3
22、store in (USER001,USER002,USER003)(partition part_01 values less than(3) tablespace tbs2,partition part_02 values less than(6) tablespace tbs3, partition part_03 values less than(maxvalue) tablespace tbs4);,31,操纵已分区的表,在已分区的表中插入数据与操作普通表完全相同，Oracle会自动将数据保存到对应的分区 查询、修改和删除分区表时可以显式指定要操作的分区,INSERT INTO SA
23、LES3 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);,delete from test2 partition(part_02
24、) t where t.transaction_id=4;,32,分区维护操作,分区维护操作修改已分区表的分区。 分区维护的类型： 计划事件 定期删除最旧的分区 非计划事件 解决应用程序或系统问题 分区维护操作有： 添加分区 删除分区 截断分区 合并分区 拆分分区,33,维护分区 2-1,添加分区 在最后一个分区之后添加新分区,SQL ALTER TABLE SALESADD PARTITION P4 VALUES LESS THAN (4000);,删除分区 删除一个指定的分区，分区的数据也随之删除,SQL ALTER TABLE SALES DROP PARTITION P4;,截断分区
25、删除指定分区中的所有记录,SQL ALTER TABLE SALES TRUNCATE PARTITION P3;,34,维护分区 2-2,合并分区 - 将范围分区或复合分区的两个相邻分区连接起来,SQL ALTER TABLE SALES MERGE PARTITIONS S1, S2 INTO PARTITION S2;,拆分分区 - 将一个大分区中的记录拆分到两个分区中,SQL ALTER TABLE SALES SPLIT PARTITION P2 AT (1500) INTO (PARTITION P21, PARTITION P22);,35,显示分区表信息 显示数据库所有分区表的
26、信息：DBA_PART_TABLES 显示当前用户可访问的所有分区表信息：ALL_PART_TABLES 显示当前用户所有分区表的信息：USER_PART_TABLES 显示表分区信息 显示数据库所有分区表的详细分区信息：DBA_TAB_PARTITIONS 显示当前用户可访问的所有分区表的详细分区信息：ALL_TAB_PARTITIONS 显示当前用户所有分区表的详细分区信息：USER_TAB_PARTITIONS 显示子分区信息 显示数据库所有组合分区表的子分区信息：DBA_TAB_SUBPARTITIONS 显示当前用户可访问的所有组合分区表的子分区信息：ALL_TAB_SUBPARTI
27、TIONS,36,显示当前用户所有组合分区表的子分区信息：USER_TAB_SUBPARTITIONS 显示分区列 显示数据库所有分区表的分区列信息：DBA_PART_KEY_COLUMNS 显示当前用户可访问的所有分区表的分区列信息：ALL_PART_KEY_COLUMNS 显示当前用户所有分区表的分区列信息：USER_PART_KEY_COLUMNS 显示子分区列 显示数据库所有分区表的子分区列信息：DBA_SUBPART_KEY_COLUMNS 显示当前用户可访问的所有分区表的子分区列信息：ALL_SUBPART_KEY_COLUMNS 显示当前用户所有分区表的子分区列信息：USER_SUBPART_KEY_COLUMNS,37,总结,锁用于保护多用户环境下被修改的数据 锁分为两种级别，即行级锁和表级锁 表分区允许将一个表划分成几部分，以改善大型应用系统的性能 分区方法包括范围分区、散列分区、复合分区和列表分区 分区维护操作包括添加、删除、截断、合并和拆分分区,
