Oracle教程(三)

ACCP V4.0,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,*,ACCP V4.0,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,第三章 锁和分区表,2,回顾,SQL 是数据库语言，Oracle 使用该语言在数据库中存储和检索信息,Oracle 支持各种数据类型，如 VARCHAR2、NUMBER、LONG、RAW 和 DATE 等,数据操纵语言用于查询和修改表中的数据,事务控制语言管理事务的一致性,SQL 操作符包括算术、比较、逻辑、集合和连接操作符,SQL 函数可以大致分为单行函数、聚合函数和分析函数,3,目标,理解锁定的概念,了解和使用表分区,4,锁的概念 2-1,锁是数据库用来控制共享资源并发访问的机制。,锁用于保护正在被修改的数据,直到提交或回滚了事务之后，其他用户才可以更新数据,5,锁的概念 2-2,修改表,修改表,拒绝访问,Toy_ID,Name,Price,T001,Barbie,20,T002,GI Joe,45,6,锁定的优点,一致性,-,一次只允许一个用户修改数据,完整性,-,为所有用户提供正确的数据。如果一个用户进行了修改并保存，所做的修改将反映给所有用户,并行性,允许多个用户访问同一数据,Toy_ID,Name,Price,T001,Barbie,20,T002,GI Joe,45,修改表中,的数据,查看表中的数据,允许访问,7,表级锁,行级锁,锁的类型,锁的类型,8,行级锁 3-1,Toy_ID,Name,Price,T001,Barbie,20,T002,GI Joe,45,更新,T002,行,更新,T001,行,行被锁定,对正在被修改的行进行锁定。其他用户可以访问除被锁定的行以外的行,允许访问,9,行级锁 3-2,行级锁是一种排他锁，防止其他事务修改此行,在使用以下语句时，,Oracle,会自动应用行级锁：,INSERT,UPDATE,DELETE,SELECT FOR 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;,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,Toy_ID,Name,Price,T001,Barbie,20,T002,GI Joe,45,修改表中的行,更新表,拒绝访问,锁定整个表，限制其他用户对表的访问。,12,表级锁 3-2,表级锁类型,行共享,行排他,共享,使用命令显示地锁定表，应用表级锁的语法是,:,LOCK TABLE table_name IN mode MODE;,共享行排他,排他,13,表级锁 3-3,行共享,(ROW SHARE) ,禁止排他锁定表,行排他,(ROW EXCLUSIVE) ,禁止使用排他锁和共享锁,共享锁,(SHARE),锁定表，仅允许其他用户查询表中的行,禁止其他用户插入、更新和删除行,多个用户可以同时在同一个表上应用此锁,共享行排他,(SHARE ROW EXCLUSIVE) ,比共享锁更多的限制，禁止使用共享锁及更高的锁,排他,(EXCLUSIVE) ,限制最强的表锁，仅允许其他用户查询该表的行。禁止修改和锁定表,14,死锁,当两个事务相互等待对方释放资源时，就会形成死锁,Oracle,会自动检测死锁，并通过结束其中的一个事务来解决死锁,右边是一个死锁的例子,T,1,T,2,lock,(D,1,),时间,lock,(D,2,),lock,(D,2,),lock,(D,1,),等待,等待,15,表分区,允许用户将一个表分成多个分区,用户可以执行查询，只访问表中的特定分区,将不同的分区存储在不同的磁盘，提高访问性能和安全性,可以独立地备份和恢复每个分区,Name,Address,State,Country,John Smith,34,th,Rd.,California,US,Micheal Clarke,LK Rd.,New York,US,Jack Jones,9,th,Road,California,US,Bob Simmons,12,th,Street,New York,US,Jim Taylor,53,rd,Road,New York,US,更新表,只访问,P1,P1,分区,P2,分区,16,表分区的类型 9-1,分区方法,范围分区,散列分区,列表分区,复合分区,17,表分区的类型 9-2,范围分区,以表中的一个列或一组列的值的范围分区,范围分区的语法：,PARTITION BY RANGE (column_name),(,PARTITION part1 VALUES LESS THAN(range1),PARTITION part2 VALUES LESS THAN(range2),.,PARTITION partN VALUES LESS THAN(MAXVALUE),);,18,表分区的类型 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,创建分区,分区的名称,包含销售成本低于,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),);,范围分区示例,19,表分区的类型 9-4,散列分区,允许用户对不具有逻辑范围的数据进行分区,通过在分区键上执行,HASH,函数决定存储的分区,将数据平均地分布到不同的分区,散列分区语法,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-5,散列分区示例,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-6,列表分区,允许用户将不相关的数据组织在一起,列表分区的语法：,PARTITION BY LIST (column_name),(,PARTITION part1 VALUES (values_list1),PARTITION part2 VALUES (values_list2),.,PARTITION partN VALUES (DEFAULT),);,22,表分区的类型 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 (,纽约, ,波斯顿,),);,包含住在芝加哥的职员的记录,根据职员住址在表上创建的列表分区,分区的名称,列表分区示例,23,表分区的类型 9-8,复合分区,范围分区与散列分区或列表分区的组合,复合分区的语法：,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-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 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,个散列子分区,复合分区示例,25,操纵已分区的表,在已分区的表中插入数据与操作普通表完全相同，Oracle会自动将数据保存到对应的分区,查询、修改和删除分区表时可以显式指定要操作的分区,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,分区维护操作,分区维护操作修改已分区表的分区。,分区维护的类型：,计划事件 定期删除最旧的分区,非计划事件 解决应用程序或系统问题,分区维护操作有：,添加分区,删除分区,截断分区,合并分区,拆分分区,27,维护分区 2-1,添加分区 在最后一个分区之后添加新分区,SQL ALTER TABLE SALES,ADD PARTITION,P4 VALUES LESS THAN (4000);,删除分区,删除一个指定的分区，分区的数据也随之删除,SQL ALTER TABLE SALES,DROP PARTITION,P4;,截断分区,删除指定分区中的所有记录,SQL ALTER TABLE SALES,TRUNCATE PARTITION,P3;,28,维护分区 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);,29,总结,锁用于保护多用户环境下被修改的数据,锁分为两种级别，即行级锁和表级锁,表分区允许将一个表划分成几部分，以改善大型应用系统的性能,分区方法包括范围分区、散列分区、复合分区和列表分区,分区维护操作包括添加、删除、截断、合并和拆分分区,