Oracle教程(三).ppt

第三章锁和分区表,2,回顾,SQL是数据库语言，Oracle使用该语言在数据库中存储和检索信息Oracle支持各种数据类型，如VARCHAR2、NUMBER、LONG、RAW和DATE等数据操纵语言用于查询和修改表中的数据事务控制语言管理事务的一致性SQL操作符包括算术、比较、逻辑、集合和连接操作符SQL函数可以大致分为单行函数、聚合函数和分析函数,3,目标,理解锁定的概念了解和使用表分区,4,锁的概念2-1,锁是数据库用来控制共享资源并发访问的机制。锁用于保护正在被修改的数据直到提交或回滚了事务之后，其他用户才可以更新数据,5,锁的概念2-2,修改表,修改表,拒绝访问,6,锁定的优点,一致性-一次只允许一个用户修改数据完整性-为所有用户提供正确的数据。如果一个用户进行了修改并保存，所做的修改将反映给所有用户,并行性允许多个用户访问同一数据,修改表中的数据,查看表中的数据,允许访问,7,表级锁,行级锁,锁的类型,锁的类型,8,行级锁3-1,更新T002行,更新T001行,行被锁定,对正在被修改的行进行锁定。其他用户可以访问除被锁定的行以外的行,允许访问,9,行级锁3-2,行级锁是一种排他锁，防止其他事务修改此行在使用以下语句时，Oracle会自动应用行级锁：INSERTUPDATEDELETESELECTFORUPDATESELECTFORUPDATE语句允许用户一次锁定多条记录进行更新使用COMMIT或ROLLBACK语句释放锁,10,行级锁3-3,SELECTFORUPDATE语法:SELECTFORUPDATEOFcolumnsWAITn|NOWAIT;,SQLSELECT*FROMorder_masterWHEREvencode=V002FORUPDATEOFodate,del_date;SQLUPDATEorder_masterSETdel_date=28-8月-05WHEREvencode=V002;SQLCOMMIT;,SQLSELECT*FROMorder_masterWHEREvencode=V002FORUPDATEWAIT5;,SQLSELECT*FROMorder_masterWHEREvencode=V002FORUPDATENOWAIT;,11,表级锁3-1,修改表中的行,更新表,拒绝访问,锁定整个表，限制其他用户对表的访问。,12,表级锁3-2,表级锁类型,行共享,行排他,共享,使用命令显示地锁定表，应用表级锁的语法是:LOCKTABLEtable_nameINmodeMODE;,共享行排他,排他,13,表级锁3-3,行共享(ROWSHARE)禁止排他锁定表行排他(ROWEXCLUSIVE)禁止使用排他锁和共享锁共享锁(SHARE)锁定表，仅允许其他用户查询表中的行禁止其他用户插入、更新和删除行多个用户可以同时在同一个表上应用此锁共享行排他(SHAREROWEXCLUSIVE)比共享锁更多的限制，禁止使用共享锁及更高的锁排他(EXCLUSIVE)限制最强的表锁，仅允许其他用户查询该表的行。禁止修改和锁定表,14,死锁,当两个事务相互等待对方释放资源时，就会形成死锁Oracle会自动检测死锁，并通过结束其中的一个事务来解决死锁右边是一个死锁的例子,15,表分区,允许用户将一个表分成多个分区用户可以执行查询，只访问表中的特定分区将不同的分区存储在不同的磁盘，提高访问性能和安全性可以独立地备份和恢复每个分区,更新表,只访问P1,P1分区,P2分区,16,表分区的类型9-1,分区方法,范围分区,散列分区,列表分区,复合分区,17,表分区的类型9-2,范围分区以表中的一个列或一组列的值的范围分区范围分区的语法：,PARTITIONBYRANGE(column_name)(PARTITIONpart1VALUESLESSTHAN(range1),PARTITIONpart2VALUESLESSTHAN(range2),.PARTITIONpartNVALUESLESSTHAN(MAXVALUE);,18,表分区的类型9-3,SQLCREATETABLESales(Product_IDvarchar2(5),Sales_Costnumber(10)PARTITIONBYRANGE(Sales_Cost)(PARTITIONP1VALUESLESSTHAN(1000),PARTITIONP2VALUESLESSTHAN(2000),PARTITIONP3VALUESLESSTHAN(3000);,根据Sales_Cost创建分区,分区的名称,包含销售成本低于1000的所有产品的值,SQLCREATETABLESALES2(PRODUCT_IDVARCHAR2(5),SALES_DATEDATENOTNULL,SALES_COSTNUMBER(10)PARTITIONBYRANGE(SALES_DATE)(PARTITIONP1VALUESLESSTHAN(DATE2003-01-01),PARTITIONP2VALUESLESSTHAN(DATE2004-01-01),PARTITIONP3VALUESLESSTHAN(MAXVALUE);,范围分区示例,19,表分区的类型9-4,散列分区允许用户对不具有逻辑范围的数据进行分区通过在分区键上执行HASH函数决定存储的分区将数据平均地分布到不同的分区散列分区语法,PARTITIONBYHASH(column_name)PARTITIONSnumber_of_partitions;或PARTITIONBYHASH(column_name)(PARTITIONpart1TABLESPACEtbs1,PARTITIONpart2TABLESPACEtbs2,.PARTITIONpartNTABLESPACEtbsN);,20,表分区的类型9-5,散列分区示例,SQLCREATETABLEEmployee(Employee_IDvarchar2(5),Employee_Namevarchar2(20),Departmentvarchar2(10)PARTITIONBYHASH(Department)(PartitionD1,PartitionD2,PartitionD3);,在表Employee上创建分区键Department,分区的名称,创建3个分区,SQLCREATETABLEEMPLOYEE(EMP_IDNUMBER(4),EMP_NAMEVARCHAR2(14),EMP_ADDRESSVARCHAR2(15),DEPARTMENTVARCHAR2(10)PARTITIONBYHASH(DEPARTMENT)PARTITIONS4;,21,表分区的类型9-6,列表分区允许用户将不相关的数据组织在一起列表分区的语法：,PARTITIONBYLIST(column_name)(PARTITIONpart1VALUES(values_list1),PARTITIONpart2VALUES(values_list2),.PARTITIONpartNVALUES(DEFAULT);,22,表分区的类型9-7,SQLCREATETABLEEmployee(Emp_IDnumber(4),Emp_Namevarchar2(14),Emp_Addressvarchar2(15)PARTITIONBYLIST(Emp_Address)(Partitionnorthvalues(芝加哥),Partitionwestvalues(旧金山,洛杉矶),Partitionsouthvalues(亚特兰大,达拉斯,休斯顿),Partitioneastvalues(纽约,波斯顿);,包含住在芝加哥的职员的记录,根据职员住址在表上创建的列表分区,分区的名称,列表分区示例,23,表分区的类型9-8,复合分区范围分区与散列分区或列表分区的组合复合分区的语法：,PARTITIONBYRANGE(column_name1)SUBPARTITIONBYHASH(column_name2)SUBPARTITIONSnumber_of_partitions(PARTITIONpart1VALUELESSTHAN(range1),PARTITIONpart2VALUELESSTHAN(range2),.PARTITIONpartNVALUELESSTHAN(MAXVALUE);,24,表分区的类型9-9,SQLCREATETABLESALES(PRODUCT_IDVARCHAR2(5),SALES_DATEDATENOTNULL,SALES_COSTNUMBER(10)PARTITIONBYRANGE(SALES_DATE)SUBPARTITIONBYHASH(PRODUCT_ID)SUBPARTITIONS5(PARTITIONS1VALUESLESSTHAN(TO_DATE(01/4月/2001,DD/MON/YYYY),PARTITIONS2VALUESLESSTHAN(TO_DATE(01/7月/2001,DD/MON/YYYY),PARTITIONS3VALUESLESSTHAN(TO_DATE(01/9月/2001,DD/MON/YYYY),PARTITIONS4VALUESLESSTHAN(MAXVALUE);,创建的四个范围分区的名称,在表的Sales_Date列中创建范围分区,在表的Product_ID列创建散列子分区,在每个范围分区中创建5个散列子分区,复合分区示例,25,操纵已分区的表,在已分区的表中插入数据与操作普通表完全相同，Oracle会自动将数据保存到对应的分区查询、修改和删除分区表时可以显式指定要操作的分区,INSERTINTOSALES3VALUES(P001,02-3月-2001,2000);INSERTINTOSALES3VALUES(P002,10-5月-2001,2508);INSERTINTOSALES3VALUES(P003,05-7月-2001,780);INSERTINTOSALES3VALUES(P004,12-9月-2001,1080);,SELECT*FROMSALES3PARTITION(P3);,DELETEFROMSALES3PARTITION(P2);,26,分区维护操作,分区维护操作修改已分区表的分区。分区维护的类型：计划事件定期删除最旧的分区非计划事件解决应用程序或系统问题分区维护操作有：添加分区删除分区截断分区合并分区拆分分区,27,维护分区2-1,添加分区在最后一个分区之后添加新分区,SQLALTERTABLESALESADDPARTITIONP4VALUESLESSTHAN(4000);,删除分区删除一个指定的分区，分区的数据也随之删除,SQLALTERTABLESALESDROPPARTITIONP4;,截断分区删除指定分区中的所有记录,SQLALTERTABLESALESTRUNCATEPARTITIONP3;,28,维护分区2-2,合并分区-将范围分区或复合分区的两个相邻分区连接起来,SQLALTERTABLESALESMERGEPART