Oracle事务和对象上集（视图、索引）

杨叔叔

　　一、Oracle事务
　　·事务的含义：事务是业务上的一个逻辑单元，为了保证数据的所有操作要么全部完成，要么全部失败。
　　1、事务的开始是从一条SQL语句开始，结束于下面的几种情况：
　　1）显示提交：输入commit指令，事务完成提交
　　2）显示回滚：输入rollback指令，未提交的事务丢掉，回滚到事务开始时的状态。
　　3）DDL语句：即create、drop等语句，这些语句会使事务自动隐式提交
　　4）结束程序：输入exit退出数据库，则自动提交事务；或者意外终止、出现程序崩溃，则事务自动回滚。
　　2、事务的特点-ACID特性
　　1）原则性：要么同时成功，要么同时失败的原则
　　2）一致性：如，a转账给b，最总结果a+b的金钱总数是不变的
　　3）隔离性：当出现多个事务出现，它们之间是互相隔离、互不影响的
　　4）持久性：事务一旦提交，则数据永久修改。
　　3、关于事务的三个命令
　　commit                      ：立即提交事务
　　rollback                        ：回滚事务
　　set autocommit on/off           ：设置/关闭自动提交
　　二、索引
　　·索引是Oracle的一个对象，是与表关联的可选结构，用于加快查询速度，提高检索性能。
　　1、特点
　　1）适当使用索引可以提高查询速度、建立索引的数量无限制
　　2）可以对表的一列或者多列建立索引
　　3）索引是需要磁盘空间，可以指定表空间存储索引。
　　4）是否使用索引有Oracle决定
　　2、索引的分类
　　B树索引：从顶部为根，逐渐向下一级展开
　　唯一索引：定义索引的列没有任何重复
　　非唯一索引：与唯一索引相反
　　反向键索引：对与数字列作用较大，会将1234生成4321进行查询的索引
　　位图索引：应用于数据仓库和决策支持系统中。优点是相对于b树索引，可以减少响应时间；相对于其他索引，其空间占用少。
　　函数索引：使用函数涉及正在创建索引的列的索引
　　3、创建索引
　　操作时我们可以使用Oracle的scott用户进行测试，首先解锁，在改一个密码，登陆进去就可以操作了

　　create [unique] index i_name on t_name(c_list) [tablespace ts_name];  
　　注释： create ... index ... on                 创建索引
　　unique                          唯一索引
　　i_name                          创建的索引名称
　　t_name                          在哪个表创建
　　(c_list)                            列名
　　ts_name                     表空间名
　　举例：
　　create index emp_ename_idx on emp(ename);
　　：b树索引
　　create unique index emp_ename_unique_idx on emp(ename);
　　：唯一索引
　　create index emp_ename_reverse_idx on emp(ename) reverse;
　　：反向索引
　　create index emp_ename_upper_idx on emp(upper(ename));
　　：函数索引
　　4、创建索引的原则
　　1）频繁检索的列
　　2）经常排序、分组的列
　　3）主键/外键
　　4）大型索引使用nologing子句创建
　　5）需定期进行组织索引、碎片整理
　　5、维护索引的命令
　　1）重建索引
　　alter index i_table rebuild [tablespace ts_name];
　　2）合并索引碎片
　　alter index i_name coalesce;
　　3）删除索引
　　drop index i_name;
　　4）查看索引（使用user_indexes、user_ind_columns字典）
　　select index_name,index_tyep,table_name,column_name from user_indexes;或者
　　select index_name,table_name,_column_name from user_ind_columns where
　　index_name like ‘emp%’;
　　三、视图
　　·视图是一个虚表，不占用物理空间，视图本身的定义语句存放于字典里，可以由一个或者多个表中获得数据。目的是为了方便查询，而不需要在敲繁琐的查询语句。
　　1、视图的作用
　　1）通过限制对表中预定的一组行和列进行查看，可以防止用户看到无权限数据，提供了安全性
　　2）简化了用户的命令、隐藏了数据的复杂性，方便操作
　　3）视图可以对列进行重命名，提升了数据库的灵活性和人性化
　　4）视图将应用程序与基表定义的修改进行了隔离，表结构的修改不会影响视图结构。
　　2、创建视图
　　create [or replace] [force] view vname [(alias[,alias]...)] as SQL
　　[with check option] [with read only];
　　注释： or replace              ：覆盖
　　force                   ：强制的；视图中的基表是否存在，都会创建
　　vname                   ：创建的视图名称
　　[(alias[,alias]...)]            ：可选项，视图别名，可以由多个
　　SQL                     ：SQL查询语句
　　with check option       ：用于更改视图时的约束
　　with read only          ：只读
　　举例：
　　create view vname as select  from tname where ‘a=b’;
　　简单的视图
　　create view vname as select  from tname where ‘a=b’ with read only;
　　只读视图
　　create force view vname as select  from tname where ‘a=b’;
　　强制创建视图，假如‘tname’表不存在，也强制创建，也就是错误视图。
　　create table tname (a (char));
　　alter view vname compile;
　　查看错误视图（使用字典）
　　show errors view;
　　show
　　创建tname表；然后编译错误视图，使其生效
　　3、在视图中使用DML语句（insert、update、delete）的限制
　　1）DML语句只能修改视图中的一个基表
　　2）如果修改违反了check option约束，则无法修改
　　3）如果视图包括连接运算符、DISTINCT运算符、集合运算符、聚合函数和group by子句，则无法更新视图
　　4）如果包含伪列、表达式，也无法更新视图
　　4、维护视图
　　1）删除视图
　　drop view vname;
　　2）查询已有视图(user_views字典)
　　select viewname from user_views;
　　5、物化视图
　　·顾名思义，物化视图就是将视图物理化存到磁盘，它与基表实时同步。物化视图可以避免order by子句或者多表连接查询带来的效率降低和时耗，但是会占用用磁盘空间。
　　1）物化视图同步的刷新方式可分为：
　　on commit：即提交事务以后
　　on demand：简单讲就是用户手动刷新
　　2）物化视图同步的刷新类型
　　complete：完全刷新
　　fast：追求速度，增量刷新
　　force：Oracle自动判断使用complete还是fast类型刷新
　　never：从不刷新
　　·关于以上四个类型，Oracle默认选择force刷新。工作中无特殊需求，无需更改
　　6、创建物化视图
　　1）首先用sys登陆，为scott用户授权

　　2）创建物化视图日志

　　3）正式创建

　　注释： build immediate                 ：立即创建
　　refresh fast                        ：刷新类型为fast
　　enable query rewrite                ：启用查询重写
　　4）删除物化视图
　　**drop materialized view materview;***