其实Oracle的优化器有两种优化方式,
基于规则的优化方式(Rule-Based Optimization，简称为RBO)
基于代价的优化方式(Cost-Based Optimization，简称为CBO)
所以hint也不例外，除了/*+rule*/其他的都是CBO优化方式
RBO方式：
优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的，当一个where子句中的一列有索引时去走索引。
CBO方式：
它是看语句的代价(Cost),这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的，是做analyze后才出现的，很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些应及时更新这些信息。
优化模式包括Rule、Choose、First rows、All rows四种方式：
Rule：基于规则的方式。
Choolse：默认的情况下Oracle用的便是这种方式。指的是当一个表或或索引有统计信息，则走CBO的方式，如果表或索引没统计信息，表又不是特别的小，而且相应的列有索引时，那么就走索引，走RBO的方式。
First Rows：它与Choose方式是类似的，所不同的是当一个表有统计信息时，它将是以最快的方式返回查询的最先的几行，从总体上减少了响应时间。
All Rows:也就是我们所说的Cost的方式，当一个表有统计信息时，它将以最快的方式返回表的所有的行，从总体上提高查询的吞吐量。没有统计信息则走RBO的方式
Oracle在那配置默认的优化规则
A、Instance级别我们可以通过在initSID.ora文件中设定OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS如果没设定OPTIMIZER_MODE参数则默认用的是Choose方式。
B、Sessions级别通过ALTER SESSION SET OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS来设定。
C、语句级别用Hint（/*+ ... */）来设定
为什么表的某个字段明明有索引，但执行计划却不走索引？
1、优化模式是all_rows的方式
2、表作过analyze，有统计信息
3、表很小，Oracle的优化器认为不值得走索引。
提示
不区分大小写, 多个提示用空格分开
如：select /*+ hint1(tab1) hint2(TAB1 idx1) */ col1, col2 from tab1 where col1='xxx';
如果表使用了别名, 那么提示里也必须使用别名
如：select /*+ hint1(t1) */ col1, col2 from tab1 t1 where col1='xxx';
如果使用同一个表的多个用,号分开
如： select /*+ index(t1.A,t1.B) */ col1, col2
from tab1 t1
where col1='xxx';
oracle 10g hints知识,
10g数据库可以使用更多新的optimizer hints来控制优化行为。现在让我们快速解析一下这些强大的新hints：
1、spread_min_analysis
使用这一hint，你可以忽略一些关于如详细的关系依赖图分析等电子表格的编译时间优化规则。其他的一些优化，如创建过滤以有选择性的定位电子表格访问结构并限制修订规则等，得到了继续使用。
由于在规则数非常大的情况下，电子表格分析会很长。这一提示可以帮助我们减少由此产生的数以百小时计的编译时间。
例：
SELECT /*+ SPREAD_MIN_ANALYSIS */ ...
2、spread_no_analysis
通过这一hint，可以使无电子表格分析成为可能。同样，使用这一hint可以忽略修订规则和过滤产生。如果存在一电子表格分析，编译时间可以被减少到最低程度。
例：
SELECT /*+ SPREAD_NO_ANALYSIS */ ...
3、use_nl_with_index
这项hint使CBO通过嵌套循环把特定的表格加入到另一原始行。只有在以下情况中，它才使用特定表格作为内部表格：如果没有指定标签，CBO必须可以使用一些标签，且这些标签至少有一个作为索引键值加入判断；反之，CBO必须能够使用至少有一个作为索引键值加入判断的标签。
例：
SELECT /*+ USE_NL_WITH_INDEX (polrecpolrind) */ ...
4、CARDINALITY
此hint定义了对由查询或查询部分返回的基数的评价。注意如果没有定义表格，基数是由整个查询所返回的总行数。
例：
SELECT /*+ CARDINALITY ( [tablespec] card ) */
5、SELECTIVITY
此hint定义了对查询或查询部分选择性的评价。如果只定义了一个表格，选择性是在所定义表格里满足所有单一表格判断的行部分。如果定义了一系列表格，选择性是指在合并以任何顺序满足所有可用判断的全部表格后，所得结果中的行部分。
例：
SELECT /*+ SELECTIVITY ( [tablespec] sel ) */
然而，注意如果hints CARDINALITY 和 SELECTIVITY都定义在同样的一批表格，二者都会被忽略。
6、no_use_nl
Hint no_use_nl使CBO执行循环嵌套，通过把指定表格作为内部表格，把每个指定表格连接到另一原始行。通过这一hint，只有hash join和sort-merge joins会为指定表格所考虑。
例：
SELECT /*+ NO_USE_NL ( employees ) */ ...
7、no_use_merge
此hint使CBO通过把指定表格作为内部表格的方式，拒绝sort-merge把每个指定表格加入到另一原始行。
例：
SELECT /*+ NO_USE_MERGE ( employees dept ) */ ...
8、no_use_hash
此hint使CBO通过把指定表格作为内部表格的方式，拒绝hash joins把每个指定表格加入到另一原始行。
例：
SELECT /*+ NO_USE_HASH ( employees dept ) */ ...
9、no_index_ffs
此hint使CBO拒绝对指定表格的指定标签进行fast full-index scan。
Syntax: /*+ NO_INDEX_FFS ( tablespecindexspec ) */
在SQL优化过程中常见HINT的用法(前10个比较常用, 前3个最常用)：
1. /*+ INDEX */ 和 /*+ INDEX(TABLE INDEX1, index2) */ 和 /*+ INDEX(tab1.col1 tab2.col2) */ 和 /*+ NO_INDEX */ 和 /*+ NO_INDEX(TABLE INDEX1, index2) */
表明对表选择索引的扫描方法. 第一种不指定索引名是让oracle对表中可用索引比较并选择某个最佳索引; 第二种是指定索引名且可指定多个索引; 第三种是10g开始有的, 指定列名, 且表名可不用别名; 第四种即全表扫描; 第五种表示禁用某个索引, 特别适合于准备删除某个索引前的评估操作. 如果同时使用了INDEX和NO_INDEX则两个提示都会被忽略掉.
例如:SELECT /*+ INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';
2. /*+ ORDERED */
FROM子句中默认最后一个表是驱动表,ORDERED将from子句中第一个表作为驱动表. 特别适合于多表连接非常慢时尝试.
例如:SELECT /*+ ORDERED */ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;
3. /*+ PARALLEL(table1,DEGREE) */ 和 /*+ NO_PARALLEL(table1) */
该提示会将需要执行全表扫描的查询分成多个部分（并行度）执行, 然后在不同的操作系统进程中处理每个部分. 该提示还可用于DML语句. 如果SQL里还有排序操作, 进程数会翻倍,此外还有一个一个负责组合这些部分的进程,如下面的例子会产生9个进程. 如果在提示中没有指定DEGREE, 那么就会使用创建表时的默认值. 该提示在默认情况下会使用APPEND提示. NO_PARALLEL是禁止并行操作,否则语句会使用由于定义了并行对象而产生的并行处理.
例如:select /*+ PARALLEL(tab_test,4) */ col1, col2 from tab_test order by col2;
4. /*+ FIRST_ROWS */ 和 /*+ FIRST_ROWS(n) */
表示用最快速度获得第1/n行, 获得最佳响应时间, 使资源消耗最小化.
在update和delete语句里会被忽略, 使用分组语句如group by/distinct/intersect/minus/union时也会被忽略.
例如:SELECT /*+ FIRST_ROWS */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
5. /*+ RULE */
表明对语句块选择基于规则的优化方法.
例如:SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
6. /*+ FULL(TABLE) */
表明对表选择全局扫描的方法.
例如:SELECT /*+ FULL(A) */ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';
7. /*+ LEADING(TABLE) */
类似于ORDERED提示, 将指定的表作为连接次序中的驱动表.
8. /*+ USE_NL(TABLE1,TABLE2) */
将指定表与嵌套的连接的行源进行连接,以最快速度返回第一行再连接,与USE_MERGE刚好相反.
例如:SELECT /*+ ORDERED USE_NL(BSEMPMS) */ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
9. /*+ APPEND */ 和 /*+ NOAPPEND */
直接插入到表的最后,该提示不会检查当前是否有插入操作所需的块空间而是直接添加到新块中, 所以可以提高速度. 当然也会浪费些空间, 因为它不会使用那些做了delete操作的块空间. NOAPPEND提示则相反,所以会取消PARALLEL提示的默认APPEND提示.
例如:insert /*+ append */ into test1 select * from test4;
insert /*+ parallel(test1) noappend */ into test1 select * from test4;
10. /*+ USE_HASH(TABLE1,table2) */
将指定的表与其它行源通过哈希连接方式连接起来.为较大的结果集提供最佳响应时间. 类似于在连接表的结果中遍历每个表上每个结果的嵌套循环, 指定的hash表将被放入内存, 所以需要有足够的内存（hash_area_size或pga_aggregate_target）才能保证语句正确执行, 否则将在磁盘里进行.
例如:SELECT /*+ USE_HASH(BSEMPMS,BSDPTMS) */ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
---------------------------------------------------------------------
11. /*+ USE_MERGE(TABLE) */
将指定的表与其它行源通过合并排序连接方式连接起来.特别适合于那种在多个表大量行上进行集合操作的查询, 它会将指定表检索到的的所有行排序后再被合并, 与USE_NL刚好相反.
例如:SELECT /*+ USE_MERGE(BSEMPMS,BSDPTMS) */ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
12. /*+ ALL_ROWS */
表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化. 可能会限制某些索引的使用.
例如:SELECT /*+ ALL+_ROWS */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
13. /*+ CLUSTER(TABLE) */
提示明确表明对指定表选择簇扫描的访问方法. 如果经常访问连接表但很少修改它, 那就使用集群提示.
例如:SELECT /*+ CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
14. /*+ INDEX_ASC(TABLE INDEX1, INDEX2) */
表明对表选择索引升序的扫描方法. 从8i开始, 这个提示和INDEX提示功能一样, 因为默认oracle就是按照升序扫描索引的, 除非未来oracle还推出降序扫描索引.
例如:SELECT /*+ INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';
15. /*+ INDEX_COMBINE(TABLE INDEX1, INDEX2) */
指定多个位图索引, 对于B树索引则使用INDEX这个提示,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.
例如:SELECT /*+ INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI) */ * FROM BSEMPMS WHERE SAL<5000000 AND HIREDATE<SYSDATE;
16. /*+ INDEX_JOIN(TABLE INDEX1, INDEX2) */
合并索引, 所有数据都已经包含在这两个索引里, 不会再去访问表, 比使用索引并通过rowid去扫描表要快5倍.
例如:SELECT /*+ INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI) */ SAL,HIREDATE FROM BSEMPMS WHERE SAL<60000;
17. /*+ INDEX_DESC(TABLE INDEX1, INDEX2) */
表明对表选择索引降序的扫描方法.
例如:SELECT /*+ INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';
18. /*+ INDEX_FFS(TABLE INDEX_NAME) */
对指定的表执行快速全索引扫描,而不是全表扫描的办法.要求要检索的列都在索引里, 如果表有很多列时特别适用该提示.
例如:SELECT /*+ INDEX_FFS(BSEMPMS IN_EMPNAM) */ * FROM BSEMPMS WHERE DPT_NO='TEC305';
19. /*+ NO_EXPAND */
对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展, 缩短解析时间.
例如:SELECT /*+ NO_EXPAND */ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';
20. /*+ DRIVING_SITE(TABLE) */
强制与ORACLE所选择的位置不同的表进行查询执行.特别适用于通过dblink连接的远程表.
例如:SELECT /*+ DRIVING_SITE(DEPT) */ * FROM BSEMPMS,DEPT@BSDPTMS DEPT WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;
21. /*+ CACHE(TABLE) */ 和 /*+ NOCACHE(TABLE) */
当进行全表扫描时,CACHE提示能够将表全部缓存到内存中,这样访问同一个表的用户可直接在内存中查找数据. 比较适合数据量小但常被访问的表, 也可以建表时指定cache选项这样在第一次访问时就可以对其缓存. NOCACHE则表示对已经指定了CACHE选项的表不进行缓存.
例如:SELECT /*+ FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
22. /*+ PUSH_SUBQ */
当SQL里用到了子查询且返回相对少的行时, 该提示可以尽可能早对子查询进行评估从而改善性能, 不适用于合并连接或带远程表的连接.
例如:select /*+ PUSH_SUBQ */ emp.empno, emp.ename, itemno from emp, orders where emp.empno = orders.empno and emp.deptno = (select deptno from dept where loc='XXX');
远程连接其他数据库，注意判断数据库是否启动，或者是否有需要的表，否则会出错
23. /*+ INDEX_SS(TABLE INDEX1,INDEX2) */
指示对特定表的索引使用跳跃扫描, 即当组合索引的第一列不在where子句中时, 让其使用该索引
参考资料
Oracle SQL hints
/*+ hint */
/*+ hint(argument) */
/*+ hint(argument-1 argument-2) */
All hints except /*+ rule */ cause the CBO to be used. Therefore, it is good practise to analyze the underlying tables if hints are used (or the query is fully hinted. There should be no schema names in hints. Hints must use aliases if alias names are used for table names. So the following is wrong:
select /*+ index(scott.emp ix_emp) */ from scott.emp emp_alias
better:
select /*+ index(emp_alias ix_emp) */ ... from scott.emp emp_alias
Why using hints
It is a perfect valid question to ask why hints should be used. Oracle comes with an optimizer that promises to optimize a query's execution plan. When this optimizer is really doing a good job, no hints should be required at all. Sometimes, however, the characteristics of the data in the database are changing rapidly, so that the optimizer (or more accuratly, its statistics) are out of date. In this case, a hint could help. It must also be noted, that Oracle allows to lock the statistics when they look ideal which should make the hints meaningless again.
Hint categories
Hints can be categorized as follows:
Hints for Optimization Approaches and Goals,
Hints for Access Paths, Hints for Query Transformations,
Hints for Join Orders,
Hints for Join Operations,
Hints for Parallel Execution,
Additional Hints
Documented Hints
Hints for Optimization Approaches and Goals
ALL_ROWS
One of the hints that 'invokes' the Cost based optimizer
ALL_ROWS is usually used for batch processing or data warehousing systems.
FIRST_ROWS
One of the hints that 'invokes' the Cost based optimizer
FIRST_ROWS is usually used for OLTP systems.
CHOOSE
One of the hints that 'invokes' the Cost based optimizer
This hint lets the server choose (between ALL_ROWS and FIRST_ROWS, based on statistics gathered.
RULE
The RULE hint should be considered deprecated as it is dropped from Oracle9i2.
See also the following initialization parameters: optimizer_mode, optimizer_max_permutations, optimizer_index_cost_adj, optimizer_index_caching and
Hints for Access Paths
CLUSTER
Performs a nested loop by the cluster index of one of the tables.
FULL
Performs full table scan.
HASH
Hashes one table (full scan) and creates a hash index for that table. Then hashes other table and uses hash index to find corresponding records. Therefore not suitable for < or > join conditions.
ROWID
Retrieves the row by rowid
INDEX
Specifying that index index_name should be used on table tab_name: /*+ index (tab_name index_name) */
Specifying that the index should be used the the CBO thinks is most suitable. (Not always a good choice).
Starting with Oracle 10g, the index hint can be described: /*+ index(my_tab my_tab(col_1, col_2)) */. Using the index on my_tab that starts with the columns col_1 and col_2.
INDEX_ASC
INDEX_COMBINE
INDEX_DESC
INDEX_FFS
INDEX_JOIN
NO_INDEX
AND_EQUAL
The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes
Hints for Query Transformations
FACT
The FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should be considered as a fact table.
MERGE
NO_EXPAND
NO_EXPAND_GSET_TO_UNION
NO_FACT
NO_MERGE
NOREWRITE
REWRITE
STAR_TRANSFORMATION
USE_CONCAT
Hints for Join Operations
DRIVING_SITE
HASH_AJ
HASH_SJ
LEADING
MERGE_AJ
MERGE_SJ
NL_AJ
NL_SJ
USE_HASH
USE_MERGE
USE_NL
Hints for Parallel Execution
NOPARALLEL
PARALLEL
NOPARALLEL_INDEX
PARALLEL_INDEX
PQ_DISTRIBUTE
Additional Hints
ANTIJOIN
APPEND
If a table or an index is specified with nologging, this hint applied with an insert statement produces a direct path insert which reduces generation of redo.
BITMAP
BUFFER
CACHE
CARDINALITY
CPU_COSTING
DYNAMIC_SAMPLING
INLINE
MATERIALIZE
NO_ACCESS
NO_BUFFER
NO_MONITORING
NO_PUSH_PRED
NO_PUSH_SUBQ
NO_QKN_BUFF
NO_SEMIJOIN
NOAPPEND
NOCACHE
OR_EXPAND
ORDERED
ORDERED_PREDICATES
PUSH_PRED
PUSH_SUBQ
QB_NAME
RESULT_CACHE (Oracle 11g)
SELECTIVITY
SEMIJOIN
SEMIJOIN_DRIVER
STAR
The STAR hint forces a star query plan to be used, if possible. A star plan has the largest table in the query last in the join order and joins it with a nested loops join on a concatenated index. The STAR hint applies when there are at least three tables, the large table's concatenated index has at least three columns, and there are no conflicting access or join method hints. The optimizer also considers different permutations of the small tables.
SWAP_JOIN_INPUTS
USE_ANTI
USE_SEMI
Undocumented hints:
BYPASS_RECURSIVE_CHECK
Workaraound for bug 1816154
BYPASS_UJVC
CACHE_CB
CACHE_TEMP_TABLE
CIV_GB
COLLECTIONS_GET_REFS
CUBE_GB
CURSOR_SHARING_EXACT
DEREF_NO_REWRITE
DML_UPDATE
DOMAIN_INDEX_NO_SORT
DOMAIN_INDEX_SORT
DYNAMIC_SAMPLING
DYNAMIC_SAMPLING_EST_CDN
EXPAND_GSET_TO_UNION
FORCE_SAMPLE_BLOCK
GBY_CONC_ROLLUP
GLOBAL_TABLE_HINTS
HWM_BROKERED
IGNORE_ON_CLAUSE
IGNORE_WHERE_CLAUSE
INDEX_RRS
INDEX_SS
INDEX_SS_ASC
INDEX_SS_DESC
LIKE_EXPAND
LOCAL_INDEXES
MV_MERGE
NESTED_TABLE_GET_REFS
NESTED_TABLE_SET_REFS
NESTED_TABLE_SET_SETID
NO_FILTERING
NO_ORDER_ROLLUPS
NO_PRUNE_GSETS
NO_STATS_GSETS
NO_UNNEST
NOCPU_COSTING
OVERFLOW_NOMOVE
PIV_GB
PIV_SSF
PQ_MAP
PQ_NOMAP
REMOTE_MAPPED
RESTORE_AS_INTERVALS
SAVE_AS_INTERVALS
SCN_ASCENDING
SKIP_EXT_OPTIMIZER
SQLLDR
SYS_DL_CURSOR
SYS_PARALLEL_TXN
SYS_RID_ORDER
TIV_GB
TIV_SSF
UNNEST
USE_TTT_FOR_GSETS
《Oracle物化视图实战手册》
场合：数据变化小，查询出数据还要2次利用，需要数据双向同步的场合
视图：就是一条sql语句，每次查询时都要重新生成执行计划，重新执行，非常消耗时间，放在内存中一次性的
物化视图：执行sql并保留结果，直接放在数据文件中，不放在内存中方便重用【空间换时间】，不受开关机的影响
1.创建基表并插入数据
create table sino_person_address
(
iid NUMBER(16) not null,
ipersonid NUMBER(16),
spin NUMBER(16),
dgettime DATE,
sorgcode VARCHAR2(20),
smsgfilename VARCHAR2(20),
ilineno NUMBER(8),
saddress VARCHAR2(60),
szip CHAR(6),
scondition CHAR(1),
itrust NUMBER(1),
stoporgcode VARCHAR2(14),
istate NUMBER(1),
constraint PK_SINO_PERSON_ADDRESS primary key (iid)
);
插入数据(插入自动增长序列号字段的方法)
INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL,seq_sino_person_address.nextval
insert into sino_person_address values(seq_sino_person_address.nextval,123,to_date('2013-04-08 12:12:12','yyyy-mm-dd hh24:mi:ss'),'110','test_report',111,'beijing
xicheng','100100','1',123,1,'1000',0);
insert into sino_person_address values(seq_sino_person_address.nextval,123,to_date('2013-04-09 12:12:12','yyyy-mm-dd hh24:mi:ss'),'120','test_report2',121,'beijing
xicheng','100200','2',123,1,'1002',2);
insert into sino_person_address values(seq_sino_person_address.nextval,123,to_date('2013-04-10 12:12:12','yyyy-mm-dd hh24:mi:ss'),'130','test_report3',131,'beijing
xicheng','100300','3',123,1,'1003',3);
commit
###################################################################################################
2.创建物化视图日志
意义：记录基表DML操作的变化，实时刷新物化视图
注：包含所有字段
删除物化视图日志
drop materialized view log on t
创建物化视图日志
create materialized view log on t with sequence,rowid (x,y,z) including new values;
参数说明：
with sequence：以序号增1的方式进行变化记录
rowid (x,y,z)：定位哪些数据发生了变化，日志记录rowid指向的数据块的位置和变化
删除物化视图日志
drop materialized view log on sino_person_address;
基于主键方式的刷新，创建物化视图日志
CREATE MATERIALIZED VIEW LOG ON sino_person_address
WITH PRIMARY KEY
INCLUDING NEW VALUES
【TABLESPACE sinojfs2】; 可选项
3.创建物化视图
创建物化视图
create materialized view mv_t build immediate refresh fast on commit enable query rewrite as select x,y,z,count(*) from t group by x,y,z;
删除物化视图
drop materialized view mv_sino_person_address;
create materialized view mv_sino_person_address
tablespace SINOJFS2
build immediate 创建物化视图时，立即刷新基表
refresh fast with primary key 支持基于主键的快速刷新（增量刷新），基表必须有主键
on commit 支持commit动作自动刷新
enable query rewrite
as select * from sino_person_address;
create materialized view mv_sino_person_address
tablespace SINOJFS2
build immediate
refresh fast with primary key refresh complete全部刷新【全表刷新】可选项
on demand 支持需求时手工刷新
enable query rewrite
as select * from sino_person_address;
########################################################################################
参数说明：
build immediate：创建物化视图时，立即刷新基表
refresh fast with primary key：支持基于主键的快速刷新（增量刷新），基表必须有主键
on commit：基于commit动作的自动刷新 on demand：基于需求时的手工刷新
enable query rewrite：支持查询重新（使用物化视图代替基表，查询必须重写，查询重写是透明的并且不需要对物化视图有任何权限，物化视图可以启用和禁用查询重写）
查询重写：select * from t基表，执行计划走的是mv_t物化视图，禁用后，执行计划走的就是t基表了
tablespace SINOJFS2 创建于SINOJFS2表空间
(1)创建方式：BUILD IMMEDIATE（立即生成数据）， BUILD DEFERRED（下一次刷新时生新数据）， ON PREBUILD TABLE（不创建新的数据段，用已存在的含有当前物化视图数据的表来代替）；
(2)ENABLE | DISABLE QUERY REWRITE指定是否启用当前物化视图用于查询重写，启用该选项时，系统会检查以保证查询的可确定性（不允许有如序列数，USER， DATE等不确定的返回值），DISABLE时物化视图照样可以被刷新；
与物化视图生效相关的设置
(1)初始化参数JOB_QUEUE_PROCESSES设置大于零，物化的自动刷新操作需要JOB QUEUE进程来执行；
(2)初始化参数OPTIMIZER_MODE要设成某种CBO优化模式；
(3)用户会话有QUERY_REWRITE（优化器能将查询重写到本方案物化视图）或GLOBAL_QUERY_REWRITE（优化器能将查询重写到其它方案的物化视图）系统权限；
(4)初始化参数QUERY_REWRITE_ENABLED 指示优化器是否动态重写查询来使用物化视图，这个参数可以在四个级别上进行设置（参数文件，ALTER SYSTEM， ALTER SESSION， HINTS）；
(5)初始化参数QUERY_REWRITE_INTEGRITY 指示优化器在不同的数据一致性情况下决定是否使用物化视图来重写查询，ENFORCED（只有在能确保数据一致的前提下才使用物化视图）， TRUSTED（数据不一定一致，只要有用维度对象定义的关系存在，就可使用物化视图）， STALE_TOLERATED（数据不一致，也没有相关的维度定义时仍可使用物化视图），这个参数可以在三个级别上进行设置（参数文件，ALTER SYSTEM， ALTER SESSION）；
4. 物化视图DML操作测试
（1）验证物化视图是否随记录增加而增加
insert into sino_person_address values(seq_sino_person_address.nextval,123,to_date('2013-04-11 13:13:13','yyyy-mm-dd hh24:mi:ss'),'140','test_report4',141,'beijing
xicheng','100400','4',123,1,'1004',4);
select * from sino_person_address order by dgetdate;
select * from mv_sino_person_address order by dgetdate; 随记录增加而木有刷新，必须commit之后才触发物化视图刷新，没有问题
exec dbms_mview.refresh('mv_sino_person_address','c'); 还可以手动全部刷新【全表刷新】（先清除，再重装数据）
exec dbms_mview.refresh('mv_sino_person_address','f'); 也可以快速刷新【增量刷新】借助物化视图日志，只检查自上次刷新后改变了的数据来进行刷新）
（2）验证物化视图是否随记录删除而减少
delete from sino_person_address where iid=21;
select * from sino_person_address order by dgetdate;
select * from mv_sino_person_address order by dgetdate; 随记录删除而木有刷新，必须commit之后才触发物化视图刷新，没有问题
exec dbms_mview.refresh('mv_sino_person_address','c'); 还可以手动全部刷新【全表刷新】（先清除，再重装数据）
exec dbms_mview.refresh('mv_sino_person_address','f'); 也可以快速刷新【增量刷新】借助物化视图日志，只检查自上次刷新后改变了的数据来进行刷新）
（3）验证物化视图是否随记录修改而更新
update sino_person_address set sorgcode='200' where sorgcode='120';
select * from sino_person_address order by dgetdate;
select * from mv_sino_person_address order by dgetdate; 随记录修改而木有刷新，必须commit之后才触发物化视图刷新，没有问题
exec dbms_mview.refresh('mv_sino_person_address','c'); 还可以手动全部刷新【全表刷新】（先清除，再重装数据）
exec dbms_mview.refresh('mv_sino_person_address','f'); 也可以快速刷新【增量刷新】（借助物化视图日志，只检查自上次刷新后改变了的数据来进行刷新）
（4）验证物化视图是否随truncate而清空
truncate table sino_person_address;
select * from sino_person_address order by dgetdate;
select * from mv_sino_person_address order by dgetdate; 随记录truncate而木有清空，必须手动truncate table mv_sino_person_address;才能清空（两者是没有关联的），没有问题
5.物化视图刷新
根据业务需求，每月定时刷新。根据以上条件，选择使用ORACLE自带工具DBMS_MVIEW工具包中REFRESH方法对物化视图进行刷新。该方法有两个参数，第一个参数是需要刷新的物化视图名称，第二个参数是刷新方式。我们可以写存储过程，对每个物化视图调用一次REFRESH方法，也可以使用“，”把物化视图连接以来，一次刷新。
定义存储过程
create or replace procedure pro_mview_refresh
as
begin
dbms_mview.refresh('mv_sino_person_address','f');
end;
/
执行存储过程
execute pro_mview_refresh;
还可以刷新所有物化视图 dbms_mview.refresh_all_mviews;
创建存储过程
drop procedure pro_refresh_all_mviews;
create or replace procedure pro_refresh_all_mviews
as
i number;
begin
dbms_mview.refresh_all_mviews(number_of_failures=>i);
dbms_output.put_line('number_of_failures=>'||i);
end;
/
执行
executepro_refresh_all_mviews;
set serveroutput on;不可放在存储过程中，因为这是sqlplus命令，如果你怕忘记或者嫌麻烦可以把set serveroutput on;
写入/opt/oracle/product/11.2.0/dbhome_1/sqlplus/admin/glogin.sql中，每次使用sqlplus时自动加载这个文件
如果想用PL/SQL Developer工具访问数据库，请在C:\Program Files\PLSQL Developer\Login.sql 文件里添加
-- Autostart Command Window script
set serveroutput on;
这样以后再使用PL/SQL Developer工具访问数据库就可以自动加载这条命令了
###############################################################################################
研发人员专用，手动刷新，想刷就刷
set serveroutput on; 打开屏幕显示功能，就可以看到number_of_failures=>0结果
PL/SQL 匿名块
declare
i number;
begin
dbms_mview.refresh_all_mviews(number_of_failures=>i);
dbms_output.put_line('number_of_failures=>'||i);
end;
/
number_of_failures=>0
Number_of_failures 表示刷新物化视图失败个数
采用默认refresh force 刷新方式：先试图用FAST方式刷新，如果失败再用COMPLETE方式刷新，这是默认的刷新方式
注意:
1、 如果需要同时刷新多个物化视图，必须用逗号把各个物化视图名称连接起来，并对每个视图都要指明刷新方式（f、增量刷新，c、完全刷新，?、强制刷新，从不刷新）。
NEVER REFRESH（不刷新）
REFREST FAST（借助物化视图日志，只检查自上次刷新后改变了的数据来进行刷新）
REFRESH COMPLETE（先清除，再重装数据）
REFRESH FORCE（先试图用FAST方式刷新，如果失败再用COMPLETE方式刷新，这是默认的刷新方式）
确定刷新时机：
ON COMMIT（事务提交时刷新），
ON DEMAND（用DBMS_MVIEW.REFRESH， DBMS_MVIEW.REFRESH_DEPENDENT， DBMS_MVIEW.REFRESH_ALL_MVIEWS来手工刷新），
By Time（用START WITH 和 NEXT 子句创建的job来定时自动刷新）；
[dbms_mview.refresh('mv_sino_person_address,mv_person_address_his','ff');]
2、当日志和物化视图创建好后，删除日志，则需要重新创建物化视图，否则无法增量刷新。
drop materialized view log on sino_person_address; 删除日志
SQL> exec dbms_mview.refresh('mv_sino_person_address','c'); 删除物化视图日志，只可以支持物化视图全部刷新
PL/SQL procedure successfully completed
#################################################################################
SQL> exec dbms_mview.refresh('mv_sino_person_address','f'); 无法增量刷新
begin dbms_mview.refresh('mv_sino_person_address','f'); end;
ORA-23413: 表 "SINOJFS"."SINO_PERSON_ADDRESS" 没有实体化视图日志
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: 在 line 2
SQL> create materialized view log on sino_person_employment 重新创建物化视图日志
2 with primary key
3 including new values;
Materialized view log created
SQL> exec dbms_mview.refresh('mv_sino_person_employment','f'); 但还是不支持增量刷新，因为日志内容和原表内容不一致了
begin dbms_mview.refresh('mv_sino_person_employment','f'); end;
ORA-12034: "SINOJFS"."SINO_PERSON_EMPLOYMENT" 上的实体化视图日志比上次刷新后的内容新
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: 在 line 2
因为：丢失了删除日志那一点->重建日志那一点之间的原表DML变化，因此日志内容和原表内容不一致了
解决方案：重建物化视图日志 重新【增量刷新】和【全表刷新】一遍
SQL> drop materialized view log on sino_loan_compact; 删除日志
Materialized view log dropped
SQL> create materialized view log on sino_loan_compact 重建日志
2 with primary key
3 including new values;
Materialized view log created
SQL> exec dbms_mview.refresh('mv_sino_loan_compact','c'); 必须先全表刷新
PL/SQL procedure successfully completed
SQL> exec dbms_mview.refresh('mv_sino_loan_compact','f'); 再增量刷新，否则ORA-12034: "SINOJFS"."SINO_LOAN_COMPACT" 上的实体化视图日志比上次刷新后的内容新
PL/SQL procedure successfully completed
小结：只要能够增量刷新，说明日志没有问题了
简述所有视图的快速刷新和全表刷新命令（测试使用） 10张视图
select owner,table_name,tablespace_name,status from dba_tables where table_name in ('SINO_LOAN_APPLY');
update SINO_LOAN_APPLY set sorgcode ='1000' where iid =858;
exec dbms_mview.refresh('mv_sino_loan_compact','c');
exec dbms_mview.refresh('mv_sino_loan_compact','f');
exec dbms_mview.refresh('mv_sino_loan_apply','c');
exec dbms_mview.refresh('mv_sino_loan_apply','f');
exec dbms_mview.refresh('mv_sino_loan_spec_trade','c');
exec dbms_mview.refresh('mv_sino_loan_spec_trade','f');
exec dbms_mview.refresh('mv_sino_loan','c');
exec dbms_mview.refresh('mv_sino_loan','f');
exec dbms_mview.refresh('mv_sino_loan_guarantee','c');
exec dbms_mview.refresh('mv_sino_loan_guarantee','f');
exec dbms_mview.refresh('mv_sino_loan_investor','c');
exec dbms_mview.refresh('mv_sino_loan_investor','f');
###############################################################################
exec dbms_mview.refresh('mv_sino_person_employment','c');
exec dbms_mview.refresh('mv_sino_person_employment','f');
exec dbms_mview.refresh('mv_sino_person_address','c');
exec dbms_mview.refresh('mv_sino_person_address','f');
exec dbms_mview.refresh('mv_sino_person_certification','c');
exec dbms_mview.refresh('mv_sino_person_certification','f');
exec dbms_mview.refresh('mv_sino_person','c');
exec dbms_mview.refresh('mv_sino_person','f');
3.基表增加字段后对应物化视图不能自动同步结构
业务表增加 上报状态 字段 ipbcstate number(1) 可以为空
文档 建模 脚本 物化视图
sino_person_certification 完成 完成 完成 完成
sino_person 完成 完成 完成 完成
sino_person_address 完成 完成 完成 完成
sino_person_employment 完成 完成 完成 完成
sino_person_address_his 完成 完成 完成
sino_person_employment_his 完成 完成 完成
sino_person_his 完成 完成 完成
sino_loan 完成 完成 完成 完成
sino_loan_compact 完成 完成 完成 完成
sino_loan_spec_trade 完成 完成 完成 完成
sino_loan_guarantee 完成 完成 完成 完成
sino_loan_investor 完成 完成 完成 完成
sino_loan_apply 完成 完成 完成 完成
对比IPBCSTATE 字段基表有，但物化视图没有，需要重建物化视图解决
select * from mv_sino_loan_compact where rownum<2;
select * from sino_loan_compact where rownum<2;
select * from mv_sino_loan where rownum < 2;
select * from sino_loan where rownum < 2;
select * from mv_sino_loan_apply where rownum < 2;
select * from sino_loan_apply where rownum < 2;
select * from mv_sino_loan_guarantee where rownum < 2;
select * from sino_loan_guarantee where rownum < 2;
select * from mv_sino_loan_guarantee where rownum < 2;
select * from sino_loan_guarantee where rownum < 2;
select * from mv_sino_loan_investor where rownum < 2;
select * from sino_loan_investor where rownum < 2;
select * from mv_sino_loan_spec_trade where rownum < 2;
select * from sino_loan_spec_trade where rownum < 2;
################################################################################
select * from mv_sino_person where rownum < 2;
select * from sino_person where rownum < 2;
select * from mv_sino_person_address where rownum < 2;
select * from sino_person_address where rownum < 2;
select * from mv_sino_person_certification where rownum < 2;
select * from sino_person_certification where rownum < 2;
select * from mv_sino_person_employment where rownum < 2;
select * from sino_person_employment where rownum < 2;
##################################################################################
4.因为上面写的物化视图是基于主键进行刷新的，因此原表必须要有主键
6.定时刷新JOB
确定执行时间间隔
1)、 每分钟执行
Interval => TRUNC(sysdate,'mi') + 1 / (24*60)
2)、 每天定时执行
例如：每天下午2点执行一次pro_mview_refresh存储过程
Interval => TRUNC(sysdate) + 1 +14/ (24)
3)、 每周定时执行
例如：每周一凌晨2点执行
Interval => TRUNC(next_day(sysdate,2))+2/24 --星期一,一周的第二天
4)、 每月定时执行
例如：每月1日凌晨2点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24
5)、 每季度定时执行
例如每季度的第一天凌晨2点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24
6)、 每半年定时执行
例如：每年7月1日和1月1日凌晨2点
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24
7)、 每年定时执行
例如：每年1月1日凌晨2点执行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+2/24
通过jobs的使用就能实现每天或每月的指定时间执行一个函数、过程与命令
set serveroutput on 启动屏幕输出功能
SQL> execute dbms_output.put_line('This is'); 已经可以正常输出
This is
创建作业
variable job_num number; 定义存储job编号的变量
declare job_num number; pro_refresh_all_mviews
begin
dbms_job.submit
(job=>:job_num,
what=>'pro_refresh_all_mviews;',
next_date=>sysdate,
interval=>'sysdate+1/1440'); 每天1440分钟，每一分钟运行pro_mview_refresh过程一次
dbms_output.put_line('Job Number is'||to_char(job_num));
commit;
end;
/
############################################################################################
绑定变量版，必须先定义变量
variable job_num number;
declare job_num number;
begin
dbms_job.submit
(job=>:job_num,
what=>'pro_refresh_all_mviews;',
next_date=>sysdate,
interval=>'trunc(SYSDATE+5/1440,''MI'')'); 每5分钟运行一次job
dbms_output.put_line('Job Number is'||to_char(job_num));
commit;
end;
/
例如：每天上午10点执行一次pro_refresh_all_mviews存储过程
Interval => TRUNC(sysdate) + 1 +10/ (24)
declare job_num number;
begin
dbms_job.submit
(job=>:job_num,
what=>'pro_refresh_all_mviews;',
next_date=>sysdate,
interval=>'trunc(SYSDATE)+1+10/24'); 每天上午10点运行一次job
dbms_output.put_line('Job Number is'||to_char(job_num));
commit;
end;
/
Job Number is
PL/SQL procedure successfully completed
job_num
---------
1
####################################################################################
PL/SQL 匿名块版，可以直接在块中定义变量，比较方面现在采用这种
declare
job_num number;
begin
dbms_job.submit
(job=>job_num,
what=>'pro_refresh_all_mviews;',
next_date=>sysdate,
interval=>'trunc(SYSDATE)+1+10/24');
dbms_output.put_line('Job Number is '||job_num);
commit;
end;
/
Job Number is 4
PL/SQL procedure successfully completed
####################################################################################
dbms_job.submit( job out binary_integer,
what in varchar2,
next_date in date，
interval in varchar2,
no_parse in boolean)
●job：输出变量，这是作业在作业队列中的编号；
●what：执行作业的存储过程及其输入参数；
●next_date：作业初次执行的时间；
●interval：作业执行的时间间隔。指上一次执行结束到下一次开始执行的时间间隔
其中Interval这个值是决定Job何时，被重新执行的关键；当interval设置为null时，该job执行结束后，就被从队列中删除。假如我们需要该job周期性地执行，则要用‘sysdate＋m’表示。如何更好地确定执行时间的间隔需要我们掌握一个函数TRUNC。
SQL> show parameter job_queue_process 作业队列进程数，oracle能够并发job数量，0~1000
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 1000
Oracle提供的数据字典user_jobs监控作业状态
SQL> select job,log_user,what,last_date,last_sec,next_date,next_sec,failures,broken from user_jobs;
Job 作业唯一编号
Log_user 提交作业的用户
What 作业执行的存储过程
Last_date 最后一次成功运行作业的日期
Last_sec 最后一次成功运行作业的时间
Next_date 下一次运行作业日期
Next_sec 下一次运行作业时间
Failures 执行失败次数，当执行job出现错误时，Oracle将其记录在日志里，失败次数每次自动加1，加到16之后Oracle就不在执行它了
Broken 是否是异常作业，当执行失败次数达到16时，Oracle就将该job标志为broken。此后，Oracle不再继续执行它，直到用户调用过程dbms_job.broken，重新设置为not broken，或强制调用dbms_job.run来重新执行它。Y标示作业中断，以后不会运行，N表示作业正常，可以运行
运行作业
begin
dbms_job.run(:job_num); job_num是存储job编号的变量
end;
查询作业状态
SQL> select job,log_user,what,last_date,last_sec,next_date,next_sec,failures,broken from user_jobs;
JOB LOG_USER WHAT LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC FAILURES BROKEN
---------- --------- ---------------------------------------------------- ----------- ---------------- -----------
1 SINOJFS pro_refresh_all_mviews; 2013-4-26 1 11:27:38 2013-4-27 1 10:00:00 0 N
Job 作业唯一编号
Log_user 提交作业的用户
What 作业执行的存储过程
Last_date 最后一次成功运行作业的日期
Last_sec 最后一次成功运行作业的时间
Next_date 下一次运行作业日期
Next_sec 下一次运行作业时间
Failures 执行失败次数，当执行job出现错误时，Oracle将其记录在日志里，失败次数每次自动加1，加到16之后Oracle就不在执行它了
Broken 是否是异常作业，当执行失败次数达到16时，Oracle就将该job标志为broken。此后，Oracle不再继续执行它，直到用户调用过程dbms_job.broken，重新设置为not broken；
或强制调用dbms_job.run来重新执行它。Y标示作业中断，以后不会运行，N表示作业正常，可以运行
删除作业
begin
dbms_job.remove(:job_num);
end;
修改作业
dbms_job.remove(jobno); 删除job号
例 execute dbms_job.remove(1);
######################################################################
dbms_job.what(jobno,what); 修改执行的存储过程
dbms_job.next_date(job,next_date)修改下次执行的时间
例 exec dbms_job.next_date(46,sysdate+2/(24*60)); 46作业号
#####################################################################
dbms_job.interval(job,interval) :修改间隔时间
例 exec dbms_job.interval(46,sysdate+3/(24*60));
######################################################################
dbms_job.broken(job,true) 中断job
例 exec dbms_job.broken(46,true); 46作业号 exec dbms_job.broken(2,true) BROKEN=Y
#######################################################################
dbms_job.broken(job,false,next_date) next_date:下次执行时间，如果不填则马上启动job
例 exec dbms_job.broken(46,false); 启动job exec dbms_job.broken(2,false); BROKEN=N
########################################################################
dbms_job.run(jobno); 运行作业
例子 execute dbms_job.run(1);
一． 物化视图概述
Oracle的物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。
物化视图可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。物化视图有很多方面和索引很相似：使用物化视图的目的是为了提高查询性能；物化视图对应用透明，增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性；物化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新。
物化视图可以查询表，视图和其它的物化视图。
通常情况下，物化视图被称为主表（在复制期间）或明细表（在数据仓库中）。
对于复制，物化视图允许你在本地维护远程数据的副本,这些副本是只读的。如果你想修改本地副本，必须用高级复制的功能。当你想从一个表或视图中抽取数据时，你可以用从物化视图中抽取。
对于数据仓库，创建的物化视图通常情况下是聚合视图，单一表聚合视图和连接视图。
在复制环境下，创建的物化视图通常情况下主键，rowid,和子查询视图。
物化视图由于是物理真实存在的，故可以创建索引。
1.1 物化视图可以分为以下三种类型
（1） 包含聚集的物化视图；
（2） 只包含连接的物化视图；
（3） 嵌套物化视图。
三种物化视图的快速刷新的限制条件有很大区别，而对于其他方面则区别不大。创建物化视图时可以指定多种选项，下面对几种主要的选择进行简单说明：
（1）创建方式（BuildMethods）：包括BUILD IMMEDIATE和BUILD DEFERRED两种。
BUILD IMMEDIATE是在创建物化视图的时候就生成数据。
BUILD DEFERRED则在创建时不生成数据，以后根据需要在生成数据。默认为BUILD IMMEDIATE。
（2）查询重写（QueryRewrite）：包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。
分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时，Oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。默认为DISABLEQUERY REWRITE。
（3）刷新（Refresh）：指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种：ON DEMAND和ON COMMIT。
ON DEMAND和ON COMMIT物化视图的区别在于其刷新方法的不同，ON DEMAND指物化视图在用户需要的时候进行刷新，可以手工通过DBMS_MVIEW.REFRESH等方法来进行刷新，也可以通过JOB定时进行刷新,即更新物化视图，以保证和基表数据的一致性;而ON COMMIT是说，一旦基表有了COMMIT，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。
对基表，平常的COMMIT在0.01秒内可以完成，但在有了ON COMMIT视图后，居然要6秒。速度减低了很多倍。ON COMMIT视图对基表的影响可见一斑。
1.2 物化视图，根据不同的着重点可以有不同的分类:
1) 按刷新方式分：FAST/COMPLETE/FORCE
2) 按刷新时间的不同：ON DEMAND/ON COMMIT
3) 按是否可更新：UPDATABLE/READ ONLY
4) 按是否支持查询重写：ENABLE QUERY REWRITE/DISABLEQUERY REWRITE
默认情况下，如果没指定刷新方法和刷新模式，则Oracle默认为FORCE和DEMAND。
注意：设置REFRESH ON COMMIT的物化视图不能访问远端对象。
在建立物化视图的时候可以指定ORDER BY语句，使生成的数据按照一定的顺序进行保存。不过这个语句不会写入物化视图的定义中，而且对以后的刷新也无效。
1.3 物化视图有三种刷新方式：COMPLETE、FAST和 FORCE。
1) 完全刷新（COMPLETE）会删除表中所有的记录（如果是单表刷新，可能会采用TRUNCATE的方式），然后根据物化视图中查询语句的定义重新生成物化视图。
2) 快速刷新（FAST）采用增量刷新的机制，只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去。FAST必须创建基于主表的视图日志。
对于增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用。
3) 采用FORCE方式，Oracle会自动判断是否满足快速刷新的条件，如果满足则进行快速刷新，否则进行完全刷新。
Oracle物化视图的快速刷新机制是通过物化视图日志完成的。Oracle通过一个物化视图日志还可以支持多个物化视图的快速刷新。
物化视图日志根据不同物化视图的快速刷新的需要，可以建立为ROWID或PRIMARY KEY类型的。还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。
1.4 物化视图Refresh子句的其他说明与示例
REFRESH 子句可以包含如下部分：
[refresh [fast|complete|force]
[on demand | commit]
[start with date] [next date]
[with {primary key|rowid}]]
1.4.1 主键和ROWD子句：
WITH PRIMARY KEY选项生成主键物化视图,也就是说物化视图是基于主表的主键，而不是ROWID(对应于ROWID子句). PRIMARY KEY是默认选项,为了生成PRIMARY KEY子句，应该在主表上定义主键，否则应该用基于ROWID的物化视图.
基于ROWID物化视图只有一个单一的主表，不能包括下面任何一项:
（1）.Distinct 或者聚合函数.
（2） .Group by，子查询，连接和SET操作
--主键（PrimaryKey）物化视图示例：
在远程数据库表emp上创建主键物化视图：
- CREATEMATERIALIZEDVIEW mv_emp_pk
- REFRESHFASTSTARTWITHSYSDATE
- NEXT SYSDATE + 1/48
- WITHPRIMARYKEY
- ASSELECT * FROM emp@remote_db
CREATEMATERIALIZEDVIEW mv_emp_pk
REFRESHFASTSTARTWITHSYSDATE
NEXT SYSDATE + 1/48
WITHPRIMARYKEY
ASSELECT * FROM emp@remote_db
--当用FAST选项创建物化视图，必须创建基于主表的视图日志,如下:
- CREATEMATERIALIZEDVIEWLOGON emp;
CREATEMATERIALIZEDVIEWLOGON emp;
--Rowid物化视图示例：
下面的语法在远程数据库表emp上创建Rowid物化视图
- CREATEMATERIALIZEDVIEW mv_emp_rowid
- REFRESHWITHROWID
- ASSELECT * FROM emp@remote_db;
- Materializedviewlog created.
CREATEMATERIALIZEDVIEW mv_emp_rowid
REFRESHWITHROWID
ASSELECT * FROM emp@remote_db;
Materializedviewlog created.
--子查询物化视图示例：
在远程数据库表emp上创建基于emp和dept表的子查询物化视图
- CREATEMATERIALIZEDVIEW mv_empdept
- ASSELECT * FROM emp@remote_db e
- WHEREEXISTS
- (SELECT * FROM dept@remote_db d
- WHEREe.dept_no = d.dept_no)
CREATEMATERIALIZEDVIEW mv_empdept
ASSELECT * FROM emp@remote_db e
WHEREEXISTS
(SELECT * FROM dept@remote_db d
WHEREe.dept_no = d.dept_no)
1.4.2 刷新时间
START WITH子句通知数据库完成从主表到本地表第一次复制的时间,应该及时估计下一次运行的时间点, NEXT 子句说明了刷新的间隔时间.
- CREATEMATERIALIZEDVIEW mv_emp_pk
- REFRESHFAST
- STARTWITHSYSDATE
- NEXT SYSDATE + 2
- WITHPRIMARYKEY
- ASSELECT * FROM emp@remote_db;
CREATEMATERIALIZEDVIEW mv_emp_pk
REFRESHFAST
STARTWITHSYSDATE
NEXT SYSDATE + 2
WITHPRIMARYKEY
ASSELECT * FROM emp@remote_db;
在上面的例子中，物化视图数据的第一个副本在创建时生成，以后每两天刷新一次.
- creatematerializedviewMV_LVY_LEVYDETAILDATA
- TABLESPACE ZGMV_DATA
- BUILDDEFERRED
- refreshforce
- ondemand
- startwith to_date('24-11-200518:00:10', 'dd-mm-yyyyhh24:mi:ss')
- nextTRUNC(SYSDATE+1)+18/24
- as
- SELECT * FROM emp@remote_db;
creatematerializedviewMV_LVY_LEVYDETAILDATA
TABLESPACE ZGMV_DATA --保存表空间
BUILDDEFERRED--延迟刷新不立即刷新
refreshforce--如果可以快速刷新则进行快速刷新，否则完全刷新
ondemand--按照指定方式刷新
startwith to_date('24-11-200518:00:10', 'dd-mm-yyyyhh24:mi:ss') --第一次刷新时间
nextTRUNC(SYSDATE+1)+18/24--刷新时间间隔
as
SELECT * FROM emp@remote_db;
1.5 ON PREBUILD TABLE 说明
在创建物化视图时指明ON PREBUILD TABLE语句，可以将物化视图建立在一个已经存在的表上。这种情况下，物化视图和表必须同名。当删除物化视图时，不会删除同名的表。
这种物化视图的查询重写要求参数QUERY_REWRITE_INTEGERITY必须设置为trusted或者stale_tolerated。
1.6 物化视图分区
物化视图可以进行分区。而且基于分区的物化视图可以支持分区变化跟踪（PCT）。具有这种特性的物化视图，当基表进行了分区维护操作后，仍然可以进行快速刷新操作。对于聚集物化视图，可以在GROUP BY列表中使用CUBE或ROLLUP，来建立不同等级的聚集物化视图。
相关内容参考：
Oracle 物化视图日志 与 快速刷新 说明
http://blog.csdn.net/tianlesoftware/article/details/7720580
Oracle 物化视图 详细错误描述 查看方法
http://blog.csdn.net/tianlesoftware/article/details/7719789
Oracle 物化视图 快速刷新 限制 说明
http://blog.csdn.net/tianlesoftware/article/details/7719679
二． 物化视图操作示例
1. 创建物化视图需要的权限：
- GRANT CREATE MATERIALIZED VIEW TO USER_NAME;
GRANT CREATE MATERIALIZED VIEW TO USER_NAME;
2. 在源表建立物化视图日志
- CREATE MATERIALIZED VIEW LOG ON DAVE
- TABLESPACE&BISONCU_SPACE
- WITH PRIMARY KEY;
CREATE MATERIALIZED VIEW LOG ON DAVE
TABLESPACE&BISONCU_SPACE -- 日志空间
WITH PRIMARY KEY; -- 指定为主键类型
3. 授权给中间用户
- GRANT SELECT ON DAVE TO ANQING;
- GRANT SELECT ON MLOG$_DAVE TO ANQING;
GRANT SELECT ON DAVE TO ANQING;
GRANT SELECT ON MLOG$_DAVE TO ANQING;
4. 在目标数据库上创建MATERIALIZED VIEW
- CREATE MATERIALIZED VIEW AICS_DAVE
- TABLESPACE&BISONCS_SPACE
- REFRESH FAST
- ON DEMAND
-
-
- START WITH SYSDATE
-
-
- NEXT SYSDATE+1/24/20
- WITH PRIMARY KEY
-
- DISABLE QUERY REWRITE AS
- SELECT MODEL_ID, STATUS,MODEL_NAME, MANU_ID, DESCRIPTION, CREATE_TIME, UPDATE_TIME, SW_VERSION
- FROM AICS_DAVE@LINK_DAVE;
CREATE MATERIALIZED VIEW AICS_DAVE
TABLESPACE&BISONCS_SPACE
REFRESH FAST
ON DEMAND
--第一次刷新时间
--START WITH to_date('2012-01-01 20:00:00', 'yyyy-mm-dd hh24:mi:ss')
START WITH SYSDATE
--刷新时间间隔。每1天刷新一次，时间为凌晨2点
--NEXT TRUNC(SYSDATE,'dd')+1+2/24
NEXT SYSDATE+1/24/20
WITH PRIMARY KEY
--USING DEFAULT LOCAL ROLLBACKSEGMENT
DISABLE QUERY REWRITE AS
SELECT MODEL_ID, STATUS,MODEL_NAME, MANU_ID, DESCRIPTION, CREATE_TIME, UPDATE_TIME, SW_VERSION
FROM AICS_DAVE@LINK_DAVE;
5. 在目标物化视图上创建索引
- CREATE INDEX IDX_T_DV_CT
- ON AICS_DEV_INFO (CREATE_TIME, UPDATE_TIME)
- TABLESPACE &BISON_IDX;
-
- CREATE INDEX IDX_T_DV_UT
- ON AICS_DEV_INFO (UPDATE_TIME)
- TABLESPACE &BISON_IDX;
-
- CREATE INDEX I_T_DV_MSISDN
- ON AICS_DEV_INFO (MSISDN)
- TABLESPACE &BISON_IDX;
CREATE INDEX IDX_T_DV_CT
ON AICS_DEV_INFO (CREATE_TIME, UPDATE_TIME)
TABLESPACE &BISON_IDX;
CREATE INDEX IDX_T_DV_UT
ON AICS_DEV_INFO (UPDATE_TIME)
TABLESPACE &BISON_IDX;
CREATE INDEX I_T_DV_MSISDN
ON AICS_DEV_INFO (MSISDN)
TABLESPACE &BISON_IDX;
6. 物化视图刷新说明
（1）使用dbms_mview.refresh 手工刷新
如：
- EXEC DBMS_MVIEW.REFRESH('MV_DAVE');
-
-
- EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_DAVE',METHOD => 'c');
- EXEC DBMS_MVIEW.REFRESH('MV_DAVE','C');
-
-
- EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_DAVE',METHOD => 'f');
- EXEC DBMS_MVIEW.REFRESH('MV_DAVE','F');
EXEC DBMS_MVIEW.REFRESH('MV_DAVE');
--完全刷新
EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_DAVE',METHOD => 'c');
EXEC DBMS_MVIEW.REFRESH('MV_DAVE','C');
--快速刷新
EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_DAVE',METHOD => 'f');
EXEC DBMS_MVIEW.REFRESH('MV_DAVE','F');
（2）使用dbms_refresh.refresh 过程来批量刷新MV
如果我们在创建物化视图的过程指定start 和next time的刷新时间，那么Oracle 会自动创建刷新的job，并采用dbms_refresh.refresh 的方式。
使用这种方式刷新之前需要先make refresh group，然后才可以刷新。
Refreshmake 的语法可以参考：
http://docs.oracle.com/cd/B19306_01/server.102/b14227/rarrefreshpac.htm#i94057
示例：
假设存在物化视图MV_T1, MV_T2, MV_T3. 创建refresh group的语法如下：
- SQL> EXEC DBMS_REFRESH.MAKE('REP_TEST', 'MV_T1,MV_T2,MV_T3', SYSDATE, 'SYSDATE+ 1')
-
-
- SQL> EXEC DBMS_REFRESH.REFRESH('REP_TEST')
SQL> EXEC DBMS_REFRESH.MAKE('REP_TEST', 'MV_T1,MV_T2,MV_T3', SYSDATE, 'SYSDATE+ 1')
--刷新整个refresh group 组：
SQL> EXEC DBMS_REFRESH.REFRESH('REP_TEST')
7. 删除物化视图及日志
-
- DROP MATERIALIZED VIEW LOG ON DAVE;
-
- DROP MATERIALIZED VIEW MV_DAVE;
--删除物化视图日志：
DROP MATERIALIZED VIEW LOG ON DAVE;
--删除物化视图
DROP MATERIALIZED VIEW MV_DAVE;
8. 查看物化视图刷新状态信息
- SQL> SELECT MVIEW_NAME, LAST_REFRESH_DATE, STALENESS FROMUSER_MVIEWS;
- SQL> SELECT NAME, LAST_REFRESH FROM USER_MVIEW_REFRESH_TIMES;
SQL> SELECT MVIEW_NAME, LAST_REFRESH_DATE, STALENESS FROMUSER_MVIEWS;
SQL> SELECT NAME, LAST_REFRESH FROM USER_MVIEW_REFRESH_TIMES;
9. 查询物化视图日志：
- SELECT * FROM MLOG$_DAVE;
SELECT * FROM MLOG$_DAVE;