-----------------------------------事务特性---------------------
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.锁的类型