ch05 oracle锁与表分区

-----------------------------------事务特性---------------------

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．锁的类型