Oracle（5）事务、表、视图、序列、索引、同义词

1 事务，DML，DDL，DCL的概念

1.1事务

事务的官方解释是:事务是包含一个或多个SQL语句的逻辑单元.事务中的SQL语句能够全不提交(COMMIT),也可以全不回滚(Rollback).
从上面的解释看,事务类似于操作系统中的进程,执行时不可分割,要么一起提交,要么一起回滚,可以将事务理解为于Oracle服务器的一次交互,Oracle中有很多的事务类型,包括:读写事务,可读事务,离散事务.默认的是读写事务.

• 读写事务
  事务可以包含查询,插入,更新,和删除表记录的SQL语句,即能包含改变数据库的语句.
• 只读事务
  顾名思议,事务中不能包含改变数据库的语句,只能读数据库不能写.
• 逻辑事务
  主要用来改进数据库的性能,在离散事务中将一些耗时的操作集中起来一起执行.

1.2 DML

数据库中将SQL语句分成三类:DML语句,DDL语句,DCL语句
数据库维护语言(Data Manipulation Language 或 Data Modification Language,DML)包括SELECT,INSERT,UPDATE,DELETE语句,后三个用来更改表中的数据.
DML语句用于对表数据的操作.

1.3 DDL

数据定义语言(Data Definition Language,DDL),指一些创建,修改,删除数据库对象的语言,相应的语句有CREATE,ALTER,DROP.

1.4 DCL

数据库控制语言(Data Control Language ,DCL),用于控制用户对数据库的访问.常见的三条命令是GRANT,REVOKE,SET ROLE

2 处理数据

2.1 数据库事务

1.组成：
一个或多个DML语句/一个DDL语句/一个DCL语句

2.开始和结束：
以第一个DML语句（insert，update，delete，select）的执行作为开始
以commit或rollback语句/DDL或DCL语句（create table，drop table，truncate table）（自动提交）/SQL Developer or SQL*Plus 用户退出/系统崩溃（大多数情况下会被回滚掉），以上其中之一作为结束

3.commit和rollback的优点：
1）确保数据完整性
2）数据改变在被提交之前可预览
3）将逻辑上相关的操作分组

4.保存点
使用savepoint语句在当前事务中创建保存点
使用rollback to savepoint语句回滚到创建的保存点
保存点只在当前事务中生效
语法：


5.隐式事务处理
自动提交在以下情况中执行：DDL语句/DCL语句/不使用commit或rollback语句提交或回滚，正常结束会话
自动回滚：会话异常结束或系统异常

6.提交或回滚前的数据状态
1）改变前的数据状态是可以恢复的
2）执行DML操作的用户可以通过SELECT语句查询之前的修正
数据库的一致性读：
3）其他用户不能看到当前用户所做的改变，直到当前用户结束事物
4）DML语句所涉及到的行被锁定，其他用户不能操作（光标会一直闪，而不能操作）

必须是在同一个会话操作，如果不保留，另一个会话是看不到的。这种数据保护非常重要。如果两个会话同时删除相同数据，后操作的会话光标会闪，因为第一个会话有行排它锁，第二个会话获取不到这个锁。

7.commit后数据的状态
1）数据的改变被保存在数据库中
2）以前的数据被覆盖
3）所有用户都可以查看结果
4）受影响的行上的锁被释放，可供其他用户来操作的那些行
5）所有保存点都将被删除

8.rollback后数据的状态
1）回滚后可使数据变化失效
2）数据更改都会被撤消
3）数据恢复到以前的状态
4）锁被释放

无论commit还是rollback，只要事务结束了，锁就释放了

2.2 死锁

执行程序中两个或多个线程发生永久堵塞（等待），每个线程都在等待且被其他线程占用并堵塞了的资源。
比如线程A锁住了记录1并等待记录2，而线程B锁住了记录2并等待记录1.

能做的就是找出互相锁住的事务，并且等待系统自动解锁。并且在以后避免死锁的产生。

2.3 一致性读

1）一致性读为数据提供一个一致的视图
2）一个用户对数据的改变不会影响其他用户的改变
3）对于相同的数据读一致性保证：查询不等待修改（看到的还是以前的状态）、修改不等待查询、修改等待修改

2.4 select语句中的for update字句

1）select语句后给dept2加锁，占着它，但不推荐。
2）发出rollback或commit命令时，锁即被释放。
3）如果select语句试图锁定被另一个用户锁定的行，那么数据库将等待，直到该行可用，才返回select语句的结果。

3 创建和管理表

• 数据库对象
  table：基本的数据存储集合，由行和列组成
  view：从一张表或多张表中抽出的逻辑上相关的数据集合
  sequence：生成规律的数值index：提高查询性能
  synonym：给对象起的别名
• 命名规则
  表名和列名：
  必须以字母开头
  必须在1-30个字符之间
  必须只能包含A-Z，a-z，0-9，_，$，和#
  必须不能和用户定义的其他对象重名
  必须不能是Oracle的保留字

3.1 CREATE TABLE语句

1.要求：
必须具有create table权限
有足够的存储空间

2.语法：

3.必须指定表名，字段名称，字段数据类型和字段大小

4.引用其他用户的表（要有权限）
其他用户定义的表不在当前用户的方案中
应该使用用户名作为前缀，引用其他用户定义的对象

5.default选项
1）在插入的过程中，为列指定一个默认值

（如果不给create_date设定值，那么它将默认写入当前系统时间）

2）字符串、算术表达式、或SQL函数都是合法的

3）其它列的列名和伪列是非法的
4）默认值必须满足列的数据类型定义

3.1.1 数据类型

varchar类型：存几个字符就分配几个字符的存储空间；先判断长短再运行，消耗计算。
char类型：不论存多少字符都分配一定的存储空间；直接计算，快。
rowid：伪列
number（4，2）：一共4位，小数点后有2位

rowid不会改变，但rownum是会发生改变的

3.1.2 日期数据类型

timestamp：时间戳，但并不常用

3.1.3 约束

约束为表级的强制规则。如果表有依赖关系，约束能防止表的删除。

1.有效的约束类型：
NOT NULL
UNIQUE
PRIMARY KEY 主键，非空且唯一
FOREIGN KEY 外键，依赖于主键
CHECK

2.约束准则
在建表的同时和建表之后可以创建约束。
如果不指定约束名，系统自动按照SYS_Cn的格式指定约束名。
可以在表级或列级定义约束
可以通过数据字典视图查看约束

3.PRIMARY KEY约束：
列级约束：

表级约束：

复合主键:

复合主键联合保证唯一索引。
主键名不能重复，即使是在不同的表里。

4.NOT NULL约束
语法：

not null不能通过表级定义

5.UNIQUE约束
可以定义在表级或列级
语法：

unique约束是让此列的值是唯一的，因此不能有重复：

6.FOREIGN KEY约束
可以定义在表级或列级

创建的时候要依赖于主键，可以是本表，也可以是另一个表
跟随主键列的值，可以空值
外键的值是依据主键的，连接前需要校验，如果在PK中出现过，就是符合条件的。如果是主键里没有的，就是非法的。

• foreign key约束：关键字
  1）foreign key：在表级指定子表中的列
  2）references：标识在父表中的列
  3）on delete cascade：当父表中的行被删除时，子表中相对应的行也被删除
  4）on delete set null：子表中相应的列置空

7.CHECK约束
定义每一行必须满足的条件
语法：

不允许的表达式：
出现CURRVAL,NEXTVAL,LEVEL,ROWNUM使用SYSDATE,UID,USER,USERENV函数
在查询中涉及到其它行的值

8.违反约束
如果是主键里没有的内容，则不能自己瞎添加
如果主键行被另一张表外键引用，那么不能删除主键行

3.1.4 子查询建表

使用create table语句和AS 子查询选项，将创建表和插入数据结合起来完成指定的列和子查询中的列要一一对应

通过列名和默认值定义列

查看一个表是否创建成功的首选方式是查看表结构，其次是select count（*)，或者select * from tab where tname=‘新建表’

索引跟表的关系是一对一，表跟索引的关系是一对多。

3.2 ALTER TABLE

1.alter table用来修改表属性：表名、列、数据类型、列的长度等和表相关的内容
比如：添加一个新列，修改现有的列定义，新的列定义默认值，删除一列，重命名列，将表更改为只读状态

2.Read-Only 表
语法：

只读模式：表修改过程中阻止DDL和DML的更改

3.编辑
1）add：增加一个新列：



2）modify：更改一个现存列：

可更改一个现存列的数据类型、长度、以及缺省值（default，默认的）对于缺省值的修改影响到

3）drop：
删除一个现存列：

删除主键：

4）SET UNUSED：标记为不可用
当表中数据量非常大时，如果在业务高峰时间直接执行alter table drop column会报错。推荐使用SET UNUSED选项标记一列或多列，使该列不可用。然后再业务量下降后再使用DROP UNUSED column选项删除被标记为不可用的列。
这种方法开销比较小，效率高，并且保险。

5）RENAME：修改列名/表名
修改列名

修改表名

3.3 删除表

1）drop语句可以将表移动到回收站

2）使用purge子句可以彻底删除表

表上依赖的对象失效同时，删除对象权限

3）purge recyclebin：清空回收站


4）FLASHBACK TABLE：闪回
把回收站里的表复原回来

4 视图

• 优点：限制数据访问，简化查询，数据独立性，避免重复访问相同的数据

4.1 创建视图

• CREATE VIEW：创建视图
  
• 命名：一般用v_xxx命名
• 子查询可以包含复杂的select语法
• 简单视图和复杂视图
  
• 赋权：
  
• 创建复杂视图：
  
  如果使用列别名创建视图，在选择视图中的列时要用别名

4.2 视图和表的区别

表的数据是真实存在在磁盘上的，但是视图只是原表的内容。
而表是占空间的，但视图只占一点点空间，就只是它的定义。
任何对视图的查询，Oracle都实际上转换为视图SQL语句的查询。这样对整体查询性能的提高，并没有实质上的好处。

4.3 修改视图

CREATE OR REPLACE VIEW：修改视图

4.4 物化视图

普通视图不存数据，物化视图才存数据。
子查询查出来的数据真实存在在磁盘上，不会根据基表发生变化而实时发生变化。

1）Oracle的物化视图是包括一个查询结果的数据库对象，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。
物化视图可以查询表，视图和其它的物化视图。

2）创建时是否生成数据
分为两种：build immediate 和 build deferred

• build immediate是在创建物化视图的时候就生成数据。
• build deferred则在创建时不生成数据，以后根据需要在生成数据。

如果不指定，则默认为build immediate。

3）刷新模式
在创建时refresh mode是on demand还是on commit。

• on demand顾名思义，仅在该物化视图“需要”被刷新了，才进行刷新(REFRESH)，即更新物化视图，以保证和基表数据的一致性;
• on commit提交触发，一旦基表有了commit，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。一般用这种方法在操作基表时速度会比较慢。

创建物化视图时未作指定，则Oracle按 on demand 模式来创建。

4）如何刷新

• 完全刷新（COMPLETE）： 会删除表中所有的记录（如果是单表刷新，可能会采用TRUNCATE的方式），然后根据物化视图中查询语句的定义重新生成物化视图。
• 快速刷新（FAST）： 采用增量刷新的机制，只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去。FAST必须创建基于主表的视图日志。对于增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用。
• FORCE方式：这是默认的数据刷新方式。Oracle会自动判断是否满足快速刷新的条件，如果满足则进行快速刷新，否则进行完全刷新

5）操作
创建物化视图需要的权限：
grant create materialized view to user_name;
在基表建立物化视图日志：

create materialized view log on test_table  
tablespace test_space 			-- 日志空间  
with primary key;				-- 指定为主键类型

在目标数据库上创建MATERIALIZED VIEW：

create materialized view mv_materialized_test refresh force on demand start with sysdate next
to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),'10:25:00'),'dd-mm-yyyy hh24:mi:ss') 
as
select * from user_info; --这个物化视图在每天10：25进行刷新

修改刷新时间：

alter materialized view mv_materialized_test 
refresh force on demand 
start with sysdatenext to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),' 23:00:00'),'dd-mm-yyyy hh24:mi:ss');

或

alter materialized view mv_materialized_test 
refresh force on demand 
start with sysdatenext trunc(sysdate,'dd')+1+1/24; -- 每天1点刷新

建立索引：

create index IDX_MMT_IU_TEST
on mv_materialized_test(ID,UNAME)  
tablespace test_space;

删除物化视图及日志：

drop materialized view log on test_table;    --删除物化视图日志
drop materialized view mv_materialized_test; --删除物化视图

4.5 查看视图结构

4.6 对简单视图进行DML操作（增删改查）

修改视图会让基表也发生改变，因为相当于是在基表上进行更改，然后反映在视图中。

当视图定义中包含以下元素之一时不能删除行：
组函数、group by子句、distinct关键字、rownum伪列

4.6.1 WITH CHECK OPTION子句

确保DML只能在特定的范围内执行，任何违反WITH CHECK OPTION约束的请求都会失败

4.6.2 WITH READ ONLY子句

使用WITH READ ONLY选项屏蔽对视图的DML操作，任何DML操作都会返回一个Oracle server错误。

4.6.3 删除视图

删除视图只是删除视图的定义，并不会删除基表的数据。

4.6.4 查看用户视图表结构：

4.6.5 查看当前用户创建的视图的定义：

5 序列

• 特点：
  1）自动提供唯一的数值
  2）共享对象
  3）主要用于提供主键值
  4）可代替应用程序生成序号
  5）将序列值缓存到内存中，可以提高访问效率

• 语法
  
  increment by 间隔
  默认是nomaxvalue、nominvalue、nocycle和nocache
  cycle：达到最大值后从最小值再开始循环。
  cache：缓存，根据序列实时变化
  初始值不能小于最小值
  
  如果突然死机，是从101开始

• 命名：一般采用seq_xxx

• NEXTVAL和CURRVAL伪列
  nextval返回下一个可用的序列值。它返回一个唯一的值每次引用它的时候，任何用户都可以引用它。
  currval得到当前的序列值。
  使用currval之前必须发出nextval
  
  
  

• 缓存序列值
  将序列值缓存到内存中，可提高访问效率

• 断号
  序列在下列情况下出现“断号”
  1）发生回滚
  
  2）系统崩溃
  3）序列用于其他的表
  
  4）之前输入了错误的SQL

• 修改序列
  1）可以修改序列的增量、最大值、最小值，循环选项或缓存：
  不能修改初始值
  2）注意事项：
  必须是序列的拥有者或对序列有ALTER权限
  只有将来的序列值会被改变
  改变序列的初始值只能通过删除序列之后重建序列的方法实现

• 删除序列：
  使用drop语句删除序列
  

6 同义词

通过创建同义词简化对象访问（给一个对象起另一个名字），使用同义词可以方便访问其他用户的对象，或缩短对象名字的长度。

• 创建同义词
  
  public：公共的，任何一个数据库上的用户都对这个同义词有权限，可以查看。
  
  • 删除同义词
    
    sysdba用户的objects
    

7 索引

7.1 概念

1.索引是数据库对象之一，用于加快数据的检索。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量
2.索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率。
3.索引在逻辑上和物理上都与相关的表和数据无关，当创建或删除一个索引时，不会影响基本的表。
4.索引一旦建立，在表上进行DML操作时（例如在执行插入、修改或者删除相关操作时），oracle会自动管理索引，删除索引，不会对表产生影响
5.索引对用户是透明的，无论表上是否有索引，sql语句的用法不变（只要满足条件，客户调用时，索引将会被使用）
6.oracle创建主键时会自动在该列上创建索引

• 创建索引
  1）自动创建：在定义primary key或unique约束后系统自动在相应的列上创建唯一性索引
  2）手动创建：用户可以在其它列上创建非唯一的索引，以加速查询
  在一列或多列上创建索引：
  
  
  
  
  查看执行计划：
  
  
  创建索引后
  
  

• 命名：一般用ind_xxx

• 使用索引
  
  有where语句才有索引。

• 复合索引
  
  使用复合索引
  
  OLAP 分析型系统：一般都是insert数据，update和delete都很少，适合位图索引。
  OLTP 事务型系统：data少，快，读一致，例如提款机

• 创建索引注意事项
  
  数值分布范围广：选择度高
  列中包含大量空值：因为空值不参与索引，所以索引非常小。where ind_col is null不会扫索引，直接扫表。

7.1.1 索引的原理

1.若没有索引，搜索某个记录时（例如查找id=371）需要搜索所有的记录，因为不能保证只有一个371，必须全部搜索一遍
2.若在id上建立索引，oracle会对全表进行一次搜索，将每条记录的id值按照升序排列，然后构建索引条目id和rowid，存储到索引段中，查询id=371时即可直接查找到对应地方
3.创建了索引并不一定就会使用，oracle自动统计表的信息后，决定是否使用索引，表中数据很少时使用全表扫描速度已经很快，没有必要使用索引

7.1.2 索引的特点

相当于目录，可以提高查询的性能。
是一个对象
通过指针加速Oracle服务器的查询速度
索引与表相互独立
索引只扫一列，而查询要扫整个表
是存在硬盘上的，和view不同

7.1.3 索引的类型

1.唯一和非唯一索引：
唯一索引是基于唯一列的索引。当使用了唯一约束（PK，unique）时，由oracle自动创建。其余都是非唯一索引
2.主索引和次索引：
主索引是表的唯一索引，它是非空且唯一的
次索引是表中其它列上的索引，不是唯一索引
3.组合索引：
是包含了表中的两个列或者更多列，还成为拼接索引

7.2 索引相关视图

1.索引的相关视图
user_indexes 查看索引名字、类型、表名、是否唯一
distinct_keys 列：索引键值列去重后的条数
num_rows列：索引键值列行数
user_ind_columns 查看索引名、表名、列名

7.2 索引的种类

7.2.1 B-tree索引（默认）

B-tree：适用于选择度高的，需要维护。事务型系统都是用B-tree。

• 升序或降序
  叶节点中的键值排列默认是升序的

create index deptno_job_idx on emp1(deptno desc, job asc);

7.2.2 位图索引

bitmap：位图，适用于选择度低的列，DML操作的代价非常高，会锁整张表

7.2.3 单列索引和复合索引

7.2.4 函数索引

使用函数后：

这种情况是因为因为加了upper函数后，所有的列都变成了大写，但是要搜索t1，他不属于建立在的索引的范围内，所以搜索的时候就不扫索引而是扫全表了。但这种不推荐使用。

7.2.5 反键索引

反键索引除了在索引过程中键值和列数据的字节是相反的以外，在本质上与B-tree是相同的。
频繁使用的叫做热块，会造成资源的争抢

键值	反键索引
100	001
101	101
102	201
103	301
104	401
105	501
111	111
115	511
120	021
140	041
146	641
190	091

创建反键索引后能降低索引叶子块单独争用，减少热块，提高系统性能
但不适用于范围（between、<、>）扫描，只能用于精确的扫描

create index mgr_idx on emp1(mgr) reverse;

7.2.6 压缩索引

重复键值只存储一次，就是说重复的键值在叶块中就存一次，后跟所有与之匹配的rowid字符串

create index comp_idx on emp1(sal) compress;

目的是为了减少空间的使用

7.3 索引的维护

• 查看表上有哪些索引
  

7.3.1 重建/维护索引

索引的数据是跟着基表的DML活动而经常发生变化的
基表删除数据后，对应的索引叶节点中空间不会被释放、不会被重用
在DML操作十分频繁的表上的索引，有可能会变得非常庞大
1.重建索引
方法一：删除原来的索引，重新建立索引
drop index ind_obj_id;
必须是索引的拥有者或者拥有drop any index权限

方法二：
alter index ind_obj_id rebuild;
重建的时候，基表会被锁

alter index ind_obj_id rebuild online;
重建的时候，基表不被锁，但消耗时间

alter index ind_obj_id rebuild online nologging;
不记录日志

方法三：合并索引
表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更好些，无需额外存储空间，代价较低
alter index index_sno coalesce;

3.重命名索引
alter index old_name rename to new_name;

7.3.2 监控索引的使用

监控索引的有效性，可请求Oracle对索引进行监控
索引的拥有者执行以下命令：

关闭监控

7.3.3 隐藏索引

11g版本后 ,可将索引设置为隐藏模式。主要是对优化器隐藏索引，用于调试。
在删除index前，设为隐藏模式

create index ind_xxx on xxx(id) invisible;
alter index ind_xxx invisible;
alter index ind_xxx visible;

查看索引是否为隐藏模式

select index_name , status , visibility from dba_indexes;

不对优化器隐藏索引

alter session set optimizer_use_invisible_indexes=true;

7.4 创建索引的准则

1.需要访问的数据不超过表中额4%-5%（选择度高的数据）。使用索引需要进行两次数据读取的操作，即读索引和读表。
2.相对较小的表可以不使用索引。
3.为所有表创建主键，在指定一列作为主键时，Oracle自动创建唯一索引。
4.对包括使用多表连接操作中使用的列，建立索引
5.在where子句中频繁使用的列上创建索引
6.在包括order by 、group by操作的列，以及涉及排序操作的union、distinct操作中涉及到的列上创建索引。（因为索引是已经被排序的，就不用在排序，就可以在已经排好序的列上体现出来）
order by/group by/distinct排序会占用内存，因此不会用到索引
7.被频繁更改的列由于涉及开销问题，不建议创建索引
8.只对高选择性的表建立索引。即对几乎没有相同值的表建立索引

7.5 注意事项

1.通配符在搜索词首出现时，oracle不能使用索引
–我们在name上创建索引；
create index index_name on emp('empno');
–下面的方式oracle不适用name索引
select * from emp where name like ‘%wish%’;
–如果通配符出现在字符串的其他位置时，优化器能够利用索引；如下：
select * from emp where name like 'wish%';
2.不要在索引列上使用not，可以采用其他方式代替如下
select * from emp where not (score=100);
select * from emp where score <> 100;
–替换为
select * from emp where score>100 or score <100
3.索引上使用空值比较将停止使用索引
select * from emp where score is not null;

• 手动收集统计信息
  analyze table table_name compute statistics;

user_tables 表信息
user_indexes 索引信息

• 字典表
  user_indexes
  user_ind_columns
  user_tables
  user_tab_columns