oracle创建视图和索引,oracle视图、同义词和索引

一.视图

视图就是封装了一条复杂查询的语句，是一个虚表。

CREATE [OR REPLACE] [FORCE] VIEWview_nameASsubquery[WITH CHECK OPTION]

[WITH READ ONLY]

OR REPLACE ：若所创建的试图已经存在，ORACLE 自动重建该视图；

FORCE ：不管基表是否存在 ORACLE 都会自动创建该视图(错误视图)；

subquery ：一条完整的 SELECT 语句，可以在该语句中定义别名；

WITH CHECK OPTION ：插入或修改的数据行必须满足视图定义的约束；

WITH READ ONLY ：该视图上不能进行任何 DML 操作(只读视图)。

---视图的概念：视图就是提供一个查询的窗口，所有数据来自于原表。

---查询语句创建表

create table emp as select * fromscott.emp;select * fromemp;---创建视图【必须有dba权限】

create view v_emp as select ename, job fromemp;---查询视图

select * fromv_emp;---修改视图[不推荐]，原表的数据也会跟着改变

update v_emp set job='CLERK' where ename='ALLEN';commit;---创建只读视图

create view v_emp1 as select ename, job from emp with read only;

删除视图语法

DROP VIEW view_name

物化视图

视图是一个虚拟表(也可以认为是一条语句)，基于它创建时指定的查询语句返回的结果集。

每次访问它都会导致这个查询语句被执行一次。为了避免每次访问都执行这个查询，可以将这个查询结果集存储到一个物化视图(也叫实体化视图)。

物化视图与普通的视图相比的区别是物化视图是建立的副本，它类似于一张表，需要占用存储空间。而对一个物化视图查询的执行效率与查询一个表是一样的。

创建物化视图语法：

CREATE METERIALIZED VIEWview_name[BUILD IMMEDIATE | BUILD DEFERRED]REFRESH[FAST|COMPLETE|FORCE]

[ON [COMMIT |DEMAND] | START WITH (start_time) NEXT(next_time)

]ASsubquery

BUILD IMMEDIATE 是在创建物化视图的时候就生成数据

BUILD DEFERRED 则在创建时不生成数据，以后根据需要再生成数据，默认为 BUILD IMMEDIATE。

刷新(REFRESH)：指当基表发生了 DML 操作后，物化视图何时采用哪种方式和基表进行同步。

REFRESH 后跟着指定的刷新方法有三种：FAST、COMPLETE、FORCE。

FAST 刷新采用增量刷新，只刷新自上次刷新以后进行的修改。

COMPLETE 刷新对整个物化视图进行完全的刷新。

如果选择 FORCE 方式，则 Oracle 在刷新时会去判断是否可以进行快速刷新，如果可以则采用 FAST 方式，否则采用 COMPLETE的方式。FORCE 是默认的方式。

刷新的模式有两种：ON DEMAND 和 ON COMMIT。ON DEMAND 指需要手动刷新物化视图(默认)。ON COMMIT 指在基表发生 COMMIT 操作时自动刷新。

如果创建物化视图设置不生成数据或者设置手动刷新，需要通过下面的语句(PL/SQL)，刷新物化视图：

beginDBMS_MVIEW.refresh('物化视图名','C');end;

或者通过下面的命令手动刷新物化视图：

EXEC DBMS_MVIEW.refresh('物化视图名','C');

注意：此语句需要在命令窗口中执行。

DBMS_MVIEW.refresh 实际上是系统内置的存储过程

创建增量刷新的物化视图：

如果创建增量刷新的物化视图，必须首先创建物化视图日志，创建的物化视图日志默认名称为 MLOG$_表名称

create materialized view log on t_address withrowid；create materialized view log on t_area with rowid

表结构为：

SNAPTIME$$：用于表示刷新时间。

DMLTYPE$$：用于表示 DML 操作类型，I 表示 INSERT，D 表示 DELETE，U 表示 UPDATE。

OLD_NEW$$：用于表示这个值是新值还是旧值。N(EW)表示新值，O(LD)表示旧值

CHANGE_VECTOR$$：表示修改矢量，用来表示被修改的是哪个或哪几个字段。

此列是 RAW 类型(二进制)，其实 Oracle 采用的方式就是用每个 BIT 位去映射一个列。

插入操作显示为：FE, 删除显示为：OO 更新操作则根据更新字段的位置而显示不同的值。

当我们手动刷新物化视图后，物化视图日志被清空，物化视图更新。

创建物化视图，语句中必须有基表的 rowid，和上面创建视图日志的rowid对应，作为标识

create materialized viewmv_address

refresh fastas

selectad.rowid adrowid ,ar.rowid arrowid, ad.id,ad.name adname,ar.name ar_namefromt_address ad,t_area arwhere ad.areaid=ar.id;

二.同义词

同义词实质上是指定方案对象的一个别名。通过屏蔽对象的名称和所有者以及对分布式数据库的远程对象提供位置透明性，同义词可以提供一定程度的安全性。同时，同义词的易用性较好，降低了数据库用户的 SQL 语句复杂度。同义词允许基对象重命名或者移动，这时，只需对同义词进行重定义，基于同义词的应用程序可以继续运行而无需修改。

你可以创建公共同义词和私有同义词。其中，公共同义词属于 PUBLIC 特殊用户组，数据库的所有用户都能访问；而私有同义词包含在特定用户的方案中，只允许特定用户或者有基对象访问权限的用户进行访问。

同义词本身不涉及安全，当你赋予一个同义词对象权限时，你实质上是在给同义词的基对象赋予权限，同义词只是基对象的一个别名。

创建与使用同义词：

语法：create [public] SYNONYM synooym for object;

其中 synonym 表示要创建的同义词的名称，object 表示表，视图，序列等我们要创建同义词的对象的名称。

1.私有同义词

--需求：为表 T_OWNERS 创建(私有)同义词 名称为 OWNERS

create synonym OWNERS forT_OWNERS;--使用同义词

select * from OWNERS;

2.公有同义词

--需求：为表 T_OWNERS 创建(公有)同义词 名称为 OWNERS2

create public synonym OWNERS2 forT_OWNERS;--以另外的用户登陆，也可以使用公有同义词

select * from OWNERS2;

三.索引

索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次数,从而提高数据访问性能。

索引的使用原则：

在大表上建立索引才有意义

在 where 子句后面或者是连接条件上的字段建立索引

表中数据修改频率高时不建议建立索引

--索引的概念：索引就是在表的列上构建一个二叉树----达到大幅度提高查询效率的目的，但是索引会影响增删改的效率。---单列索引---创建单列索引

create index idx_ename onemp(ename);---单列索引触发规则，条件必须是索引列中的原始值。---单行函数、模糊查询，都会影响索引的触发。

select * from emp where ename='SCOTT'

---复合索引---创建复合索引

create index idx_enamejob onemp(ename, job);---复合索引中第一列为优先检索列---如果要触发复合索引，必须包含有优先检索列中的原始值。

select * from emp where ename='SCOTT' and job='xx';---触发复合索引

select * from emp where ename='SCOTT' or job='xx';---不触发索引

select * from emp where ename='SCOTT';---触发单列索引。

--创建唯一索引

create unique index 索引名称 on 表名(列名);

反向键索引：

应用场景：当某个字段的值为连续增长的值，如果构建标准索引，会形成歪脖子树。这样会增加查询的层数，性能会下降。建立反向键索引，可以使索引的值变得不规则，从而使索引树能够均匀分布。

语法：create index 索引名称 on 表名(列名) reverse;

位图索引：

使用场景：位图索引适合创建在低基数列(字段的值只有几个可选的值，比如性别字段的取值为男和女)上，

只有在 比如where sex="男" 这种等值比较查询时才会发挥作用，在 >,< 等范围查询时无效

位图索引不直接存储 ROWID，而是存储字节位到 ROWID 的映射

优点：减少响应时间，节省空间占用

语法：create bitmap index 索引名称 on 表名(列名);