ch05 oracle锁与表分区

3bl7f5ps76

　　-----------------------------------事务特性---------------------
　　1．原子性(atomicity)  --事务处理要么全部进行，要么不进行。
　　2．一致性(consistency) –事务处理要将数据库从一种状态转变为另一种状态。
　　3．隔离性(isolation) –在事务处理提交之前，事务处理的效果不能由系统中的其他事务看到。
　　4．持久性(durability) –一旦提交了事务，他就永远生效。
　　5．set autocommit on--设置数据库系统环境为自动提交事务：
　　SQL> set autocommit on;
　　SQL> insert into dept values(50,'test','sz');
　　已创建 1 行。
　　提交完成。
　　SQL> set autocommit off;
　　SQL> insert into dept values(60,'test','wh');
　　已创建 1 行。
　　SQL> commit;
　　提交完成。
　　--------------------------------------死锁---------------------
当两个事务相互等待对方释放资源时，就会形成死锁
Oracle会自动检测死锁，并通过结束其中的一个事务来解决死锁
　　
　　--------------------------------------锁------------------------
1．what
锁:锁是数据库用来控制共享资源并发访问的机制。
锁用于保护正在被修改的数据
直到提交或回滚了事务之后，其他用户才可以更新数据
（防止进程之间因为抢占资源，产生死锁而设定一种预防死锁产生的机制）
　　2．why
并行性 －允许多个用户访问同一数据
一致性 - 一次只允许一个用户修改数据
完整性 - 为所有用户提供正确的数据。如果一个用户进行了修改并保存，所做的修改将反映给所有用户
　　
3．锁的类型
行级锁 -- 对正在被修改的行进行锁定。其他用户可以访问除被锁定的行以外的行
　　表级锁 -- 锁定整个表，限制其他用户对表的访问。
　　4. 行级锁
行级锁是一种排他锁，防止其他事务修改此行
在使用以下语句时，Oracle会自动应用行级锁：
INSERT
UPDATE
DELETE
SELECT … FOR UPDATE
SELECT … FOR UPDATE语句允许用户一次锁定多条记录进行更新
使用COMMIT或ROLLBACK语句释放锁
　　格式: SELECT … FOR UPDATE语法:
　SELECT … FOR UPDATE [OF columns][WAIT n | NOWAIT];
　　例：锁定dept编号为40的记录，更新地址为'sz'
　　SQL> select * from dept where deptno = 40 for update of dname,loc;
　　    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON
　　SQL> update dept set loc ='sz' where deptno=40;
　　已更新 1 行。
　　SQL> commit;
　　提交完成。
　　SQL> select * from dept where deptno = 40 for update wait 5;--等待用户释放更新锁的时间为5秒，否则超时。
　　    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     sz
　　SQL> select * from dept where deptno = 40 for update nowait;
　　    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     sz
　　
5. 表级锁
　　格式: Lock table < table_name> in  <mode>;
　　类型:
　　共享锁(SHARE)
--锁定表，仅允许其他用户查询表中的行
--禁止其他用户插入、更新和删除行
--多个用户可以同时在同一个表上应用此锁
语法:
Lock  table  table_name in share  mode   [nowait];
　　rollback   和commit命令释放锁
　　Nowait  关键字告诉其他用户不用等待
　　例:部门表建立共享锁
SQL> select * from dept;
　　    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     sz
　　SQL> insert into dept values(50,'test','sz');
　　已创建 1 行。
　　SQL> lock table dept in share mode;
　　表已锁定。
　　
共享更新锁(SHARE UPDATE)
--锁定要被更新的行
　　--允许其他用户同时查询、插入、更新未被锁定的行
　　--在 SELECT 语句中使用“FOR UPDATE”子句，可以强制使用共享更新锁
　　--允许多个用户同时锁定表的不同行
　　加锁的两种方法
　　(1)lock table tab_name in share update mode;
　　(2)Select column1, column2 From  goods Where gid=1001  For update of column1, column2
　　lock table<tabale_name>[,<table_name>,....] in share update mode [nowait]
例:
SQL> lock table dept in share update mode nowait;
　　表已锁定。
　　
排他(EXCLUSIVE) – 限制最强的表锁，仅允许其他用户查询该表的行。禁止修改和锁定表,共享锁与此相反.
　　lock table<tabale_name>[,<table_name>,....]
　　in exclusive mode [nowait]
　　例:
　　SQL> lock table dept in exclusive mode;
　　表已锁定。
　　
　　--------------------------------------表分区------------------------
1．what
ORACLE的分区是一种处理超大型表、索引等的技术。分区是一种“分而治之”的技术，通过将大表和索引分成可以管理的小块，从而避免了对每个表作为一个大的、单独的对象进行管理，为大量数据提供了可伸缩的性能。分区通过将操作分配给更小的存储单元，减少了需要进行管理操作的时间，并通过增强的并行处理提高了性能，通过屏蔽故障数据的分区，还增加了可用性。
　　2．why
增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能；
改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
　　
3．分区表的类型
范围分区 --就是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。如根据序号分区，根据业务记录的创建日期进行分区等
语法:
PARTITION BY RANGE (column_name)
(
  PARTITION part1 VALUE LESS THAN(range1),
  PARTITION part2 VALUE LESS THAN(range2),
  ...
  [PARTITION partN VALUE LESS THAN(MAXVALUE)]
);
例:根据 money 创建分区,包含销售成本低于1000的所有产品的值
　　
SQL> create table sales(
    pid varchar2(5),
    money number(10)
    )
    partition by range(money)
    (
    partition p1 values less than(1000),
    partition p2 values less than(2000),
    partition p3 values less than(3000)
   );
表已创建。
　　
散列分区 --允许用户对不具有逻辑范围的数据进行分区 ,通过在分区键上执行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]);
　　例:在表 Employee上创建分区键 Department,创建 3 个分区
SQL> CREATE TABLE Employee
    (
        id varchar2 (5),
        name varchar2(20),
        department varchar2 (10)
    )
    PARTITION BY HASH (department)
    (
       Partition D1,
       Partition D2,
       Partition D3
  )
　　列表分区 -- 允许用户将不相关的数据组织在一起
　　语法:
PARTITION BY LIST (column_name)
(
  PARTITION part1 VALUES (values_list1),
  PARTITION part2 VALUES (values_list2),
  ...
  PARTITION partN VALUES (DEFAULT)
);
　　
例:根据职员住址在表上创建的列表分区
SQL>CREATE TABLE employees
(
    id number(4),
    name varchar2 (14),
    address varchar2 (15)
)
PARTITION BY LIST (address)
(
    Partition north values ('芝加哥'),
    Partition west values ('旧金山','洛杉矶'),
    Partition south values ('亚特兰大','达拉斯','休斯顿'),
    Partition east values ('纽约','波斯顿')
)
　　
复合分区 -- 范围分区与散列分区或列表分区的组合（注意：先一定要进行范围分区）
　　语法:
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)
);
　　例:在表的 SALES 列中创建范围分区,在表的 pid列创建散列子分区,在每个范围分区中创建 5个散列子分区
SQL>CREATE TABLE SALES
(
    pid VARCHAR2 (5),
    SALES_DATE DATE NOT NULL,
    SALES_COST NUMBER (10)
)
PARTITION BY RANGE (SALES_DATE)
SUBPARTITION BY HASH (pid)
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 列中创建范围分区,在表的 SALES_COST列创建列表分区
SQL>CREATE TABLE SALES
(
    pid VARCHAR2 (5),
    SALES_DATE DATE NOT NULL,
    SALES_COST NUMBER (10)
)
PARTITION BY RANGE (SALES_DATE)
SUBPARTITION BY list(SALES_COST)
SUBPARTITION template
(
subpartition aa values(2000,780),
subpartition other values(default)
)
(
    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)
)
　　4. 查询表上有多少分区
select * from user_tab_partitions where table_name='表名';
　　5. 操纵已分区的表
在已分区的表中插入数据与操作普通表完全相同，Oracle会自动将数据保存到对应的分区
查询、修改和删除分区表时可以显式指定要操作的分区
INSERT INTO SALES VALUES ('P001','02-3月-2001',2000);
INSERT INTO SALES VALUES ('P002','10-5月-2001',2508);
INSERT INTO SALES VALUES ('P002','11-5月-2001',2508);
INSERT INTO SALES VALUES ('P002','12-5月-2001',2508);
INSERT INTO SALES VALUES ('P003','05-7月-2001',780);
INSERT INTO SALES VALUES ('P004','12-9月-2001',1080);
P002  2001-5-10          2508
　　SQL> select * from sales partition (S1);
　　PID   SALES_DATE     SALES_COST
----- -------------- ----------
P001  02-3月 -01           2000
P001  02-3月 -01           2000
　　SQL> delete from sales partition (S1);
　　已删除2行。
　　SQL> select * from sales partition (S1);
　　未选定行
　　-----速度比较
SQL>  select * from sales partition (S2) where sales_date >  to_date('2000-5-10','YYYY-MM-DD');
　　PID   SALES_DATE   SALES_COST
----- ----------- -----------
P002  2001-5-10          2508
P002  2001-5-10          2508
　　SQL>  select * from sales  where sales_date >  to_date('2000-5-10','YYYY-MM-DD');
　　PID   SALES_DATE   SALES_COST
----- ----------- -----------
P001  2001-3-2           2000
P001  2001-3-2           2000
P002  2001-5-10          2508
P002  2001-5-10          2508
P003  2001-7-5            780
P004  2001-9-12          1080
　　6 rows selected
　　
6. 分区维护操作
分区维护操作修改已分区表的分区。
分区维护的类型：
计划事件 － 定期删除最旧的分区
非计划事件 － 解决应用程序或系统问题
分区维护操作有：
添加分区
删除分区
截断分区
合并分区
拆分分区
　　--添加分区 – 在最后一个分区之后添加新分区
SQL> ALTER TABLE SALES
     ADD PARTITION S5 VALUES LESS THAN (TO_DATE('01/4月/2001','DD/MON/YYYY'));
　　
--删除分区 – 删除一个指定的分区，分区的数据也随之删除
SQL> ALTER TABLE SALES DROP PARTITION S4;
　　--截断分区 – 删除指定分区中的所有记录
SQL> ALTER TABLE SALES TRUNCATE PARTITION S3;
　　--合并分区 - 将范围分区或复合分区的两个相邻分区连接起来
SQL> ALTER TABLE SALES
MERGE PARTITIONS S1, S2 INTO PARTITION S2;
　　--拆分分区 - 将一个大分区中的记录拆分到两个分区中
SQL> ALTER TABLE SALES SPLIT PARTITION S2 AT (TO_DATE('11/5月/2001','DD/MON/YYYY'))
INTO (PARTITION S21, PARTITION S22);
　　SQL> select * from sales partition (S2);
　　PID   SALES_DATE   SALES_COST
----- ----------- -----------
P002  2001-5-10          2508
P002  2001-5-10          2508
P002  2001-5-11          2508
P002  2001-5-12          2508
　　SQL>
SQL> ALTER TABLE SALES SPLIT PARTITION S2 AT (TO_DATE('11/5月/2001','DD/MON/YYYY'))
  2  INTO (PARTITION S21, PARTITION S22);
　　Table altered
　　SQL> select * from sales partition (S21);
　　PID   SALES_DATE   SALES_COST
----- ----------- -----------
P002  2001-5-10          2508
P002  2001-5-10          2508
　　SQL> select * from sales partition (S22);
　　PID   SALES_DATE   SALES_COST
----- ----------- -----------
P002  2001-5-11          2508
P002  2001-5-12          2508
　　
--------------------------------------Oracle索引分区表操作------------------------
　　
7. 总结
锁用于保护多用户环境下被修改的数据
锁分为两种级别，即行级锁和表级锁
表分区允许将一个表划分成几部分，以改善大型应用系统的性能
分区方法包括范围分区、散列分区、复合分区和列表分区
分区维护操作包括添加、删除、截断、合并和拆分分区
　　
　　
--------------------------------------索引组织表（index organized table）------------------------
　　1．what
　　
2．why
　　
　　3．锁的类型