物化视图概念类似于discoverer中的summary table。在discoverer的管理端，可以创建不同的summary table。在discoverer进行查询的时候，discoverer首先对查询进行解析，判断查询是否可以使用对应的summary table，如果可以，将会改写查询去查询对应的summary table。
一个例子
通过如下的例子，对比统计数据需求的情况下，使用物化视图会有更快的访问速度。
--创建一张大表
SQL> create table my_all_objects
2 nologging
3 as
4 select * from all_objects
5 union all
6 select * from all_objects
7 union all
8 select * from all_objects
9 /
Table created.
SQL> insert /*+ APPEND */ into my_all_objects
2 select * from my_all_objects;
87945 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ APPEND */ into my_all_objects
2 select * from my_all_objects;
175890 rows created.
SQL> commit;
Commit complete.
SQL> analyze table my_all_objects compute statistics;
Table analyzed.
SQL> set autotrace traceonly
--通过执行计划可以得知直接对数据进行count计算，需要访问4800数据块
SQL> select owner, count(*) from my_all_objects group by owner;
28 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=989 Card=28 Bytes=14
0)
1 0 SORT (GROUP BY) (Cost=989 Card=28 Bytes=140)
2 1 TABLE ACCESS (FULL) OF 'MY_ALL_OBJECTS' (Cost=471 Card=3
51780 Bytes=1758900)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4804 consistent gets
3004 physical reads
0 redo size
973 bytes sent via SQL*Net to client
510 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
28 rows processed
SQL> set autotrace off
--赋予测试用户query rewrite的权限
SQL> grant query rewrite to scott;
Grant succeeded.
--更改当前session能够query rewrite
SQL> alter session set query_rewrite_enabled=true;
Session altered.
--设定query_rewrite_integrity的值为enforced，有三个置可选，enforced，trusted，STALE_TOLERATED
SQL> alter session set query_rewrite_integrity=enforced;
Session altered.
--创建物化视图
SQL> create materialized view my_all_objects_aggs
2 build immediate
3 refresh on commit
4 enable query rewrite
5 as
6 select owner, count(*)
7 from my_all_objects
8 group by owner
9 /
Materialized view created.
--分析物化视图
SQL> analyze table my_all_objects_aggs compute statistics;
Table analyzed.
--通过执行计划可以得知再进行同样的count计算，只需要访问5个数据块。同时，通过执行路径也可以看到，查询是通过物化视图来完成的。如果业务上对于这种count的计算比较频繁的话，采用物化视图将会节省更多的资源。是一种以空间换取时间的方法。
SQL> set autotrace traceonly
SQL> select owner, count(*)
2 from my_all_objects
3 group by owner;
28 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=28 Bytes=252)
1 0 TABLE ACCESS (FULL) OF 'MY_ALL_OBJECTS_AGGS' (Cost=2 Card= 28 Bytes=252)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
973 bytes sent via SQL*Net to client
510 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
28 rows processed
SQL> set autotrace off
--插入新的纪录
SQL> insert into my_all_objects
2 ( owner, object_name, object_type, object_id )
3 values
4 ( 'New Owner', 'New Name', 'New Type', 1111111 );
1 row created.
SQL> commit;
Commit complete.
SQL> set timing on
--对新纪录的count计算仍然通过物化视图来访问。由于创建物化视图的时候，使用“refresh on commit”语句，表的新增纪录已经刷新到物化视图。
SQL> select owner, count(*)
2 from my_all_objects
3 where owner = 'New Owner'
4 group by owner;
OWNER COUNT(*)
------------------------------ ----------
New Owner 1
Elapsed: 00:00:00.00
SQL> set timing off
SQL>
SQL> set autotrace traceonly
SQL> select owner, count(*)
2 from my_all_objects
3 where owner = 'New Owner'
4 group by owner;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=9)
1 0 TABLE ACCESS (FULL) OF 'MY_ALL_OBJECTS_AGGS' (Cost=2 Card= 1 Bytes=9)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
442 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off
--更改sql，不查询owner字段，只count，发现Oracle足够聪明，即使没有创建物化视图的group语句，还是从物化视图来访问
SQL> set autotrace traceonly
SQL> select count(*)
2 from my_all_objects
3 where owner = 'New Owner';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=9)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'MY_ALL_OBJECTS_AGGS' (Cost=2 Card=1 Bytes=9)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
379 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off
对于事物频繁的OLTP系统，尽量少使用物化视图。
设置参数
如下参数可以在数据库层或者Session层设置QUERY_REWRITE_ENABLED和
QUERY_REWRITE_INTEGRITY值。对于QUERY_REWRITE_INTEGRITY有三种值可设：
Enforced：重写只使用数据库中定义的约束和关系。
Trusted：除了数据库中定义的约束和关系，Oracle还会使用其他我们告知Oracle表的某些关系，从而使得数据库能够重写更多的查询。
Stale_Tolerated：最弱的参数，及时物化视图没有同步更新，也会使用物化视图来重写SQL。
查询重写
全文精确匹配
如果查询的语句与存储在数据词典中物化视图字符串精确匹配，那么将会重写查询。这里的精确匹配相对于共享池的比较而言更友好，它会忽略空格，大小写以及其他的一些格式。
部分文本匹配
比较From因子后面的文本，即使Select部分的不匹配。例如：
接前面的例子，物化视图的查询部分的SQL：
Select owner, count(*) from my_all_objects group by owner
如下的查询能够被重写：
Select lower(owner) from my_all_objects group by owner
一般性重写方法
a. 数据满足：查询的数据列在物化视图的查询列中
b. 连接兼容：查询语句中的关联列需要在物化视图的查询列中
c. 分组兼容：查询语句和物化视图都必须有Group by语句，同时物化视图的Group by分组层次应该高于或者等于查询的语句。
d. 聚集兼容：查询语句和物化视图都必须包含聚集语句。如果物化视图包含SUM ()/COUNT () 函数，对于同样列采用AGE () 函数进行计算可以被重写。
确保使用物化视图
如下的例子将会说明在怎样的条件下可以确保采用物化视图重写查询，也会比较QUERY_REWRITE_INTEGRITY值Enforced和Trusted的不同。
--这一部分语句验证如果在数据库中相关约束，而这种约束在定义物化视图
--使用。那么，如果查询即使满足其它被重写的条件（数据满足，关联兼容，
--分组兼容等），也不会被重写。
--创建测试表和物化视图
SQL> create table emp as select * from scott.emp;
Table created.
SQL> create table dept as select * from scott.dept;
Table created.
SQL> alter session set query_rewrite_enabled=true;
Session altered.
--注意这里使用的参数值为enforced，只有查询中的约束和关系在物化视图中存在，才会重写查询。
SQL> alter session set query_rewrite_integrity=enforced;
Session altered.
--创建物化视图，注意这里是“refresh on demand”，需要手动刷新物化视图
SQL> create materialized view emp_dept
2 build immediate
3 refresh on demand
4 enable query rewrite
5 as
6 select dept.deptno, dept.dname, count (*)
7 from emp, dept
8 where emp.deptno = dept.deptno
9 group by dept.deptno, dept.dname
10 /
Materialized view created.
SQL> alter session set optimizer_goal=all_rows;
Session altered.
SQL> set autotrace on
--通过执行计划可以看到这个查询并没有被重写，而是直接访问表“EMP”。这是因为我们并没有定义表emp和dept的之间的主外健之间的关系，这种关系物化视图中使用，例如“emp.deptno = dept.deptno”
SQL> select count(*) from emp;
COUNT(*)
----------
14
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82)
Statistics
----------------------------------------------------------
….略
--这一部分语句验证增加相关约束后，查询被重写。
--增加相关的约束和关系
SQL> alter table dept
2 add constraint dept_pk primary key(deptno);
Table altered.
SQL> alter table emp
2 add constraint emp_fk_dept
3 foreign key(deptno) references dept(deptno);
Table altered.
SQL> alter table emp modify deptno not null;
Table altered.
SQL> set autotrace on
--由于增加了约束，Oracle能够重写查询使其通过物化视图“EMP_DEPT”来访问数据。可以查看下面的highlight部分的执行计划。
SQL> select count(*) from emp;
COUNT(*)
----------
14
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=13)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMP_DEPT' (Cost=2 Card=82 Bytes=
1066)
Statistics
----------------------------------------------------------
略….
SQL> set autotrace off
--下面这部分SQL将会在比较query_rewrite_integrity在取值为Enforced和
--Trusted情况下，是否被重写。
--删除上述步骤建立的约束
SQL> alter table emp drop constraint emp_fk_dept;
Table altered.
SQL> alter table dept drop constraint dept_pk;
Table altered.
SQL> alter table emp modify deptno null;
Table altered.
--插入数据
SQL> insert into emp (empno,deptno) values ( 1, 1 );
1 row created.
--手工刷新物化视图，这是因为物化视图创建的时候使用的是“refresh on demand”
SQL> exec dbms_mview.refresh( 'EMP_DEPT' );
PL/SQL procedure successfully completed.
--再次增加约束，但是这次增加了NOVALIDATE因子。这样即使数据不符合约束也能够成功创建。
SQL> alter table dept
2 add constraint dept_pk primary key(deptno)
3 rely enable NOVALIDATE
4 /
Table altered.
SQL> alter table emp
2 add constraint emp_fk_dept
3 foreign key(deptno) references dept(deptno)
4 rely enable NOVALIDATE
5 /
Table altered.
SQL> alter table emp modify deptno not null NOVALIDATE;
Table altered.
SQL> set autotrace on
--设置query_rewrite_integrity值为enforced
SQL> alter session set query_rewrite_integrity=enforced;
Session altered.
--检查增加约束但数据不满足约束的情况下，如果是query_rewrite_integrity的值为enforcedenforced，那么物化视图不会被利用来重写查询。
SQL> select count(*) from emp;
COUNT(*)
----------
15
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=164)
Statistics
----------------------------------------------------------
略….
--设置query_rewrite_integrity值为trusted
SQL> alter session set query_rewrite_integrity=trusted;
Session altered.
--检查增加约束但数据不满足约束的情况下，如果是query_rewrite_integrity的值为trusted，那么物化视图将会被利用来重写查询。
SQL> select count(*) from emp;
COUNT(*)
----------
14
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=13)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMP_DEPT' (Cost=2 Card=82 Bytes=
1066)
Statistics
----------------------------------------------------------
略….
Dimension
创建纬度映射不同列之间的父子关系，类似于在discoverer中创建的维度，使得在查询的时候根据需求下钻上卷。在这里可以为Oracle提供更多的信息，从而使得重写查询的可能性增大。非常类似discoverer中的hierarchy的概念。
create dimension sales_dimension
--这一部分定义类似于数据库字段的别名
level cust_id is customer_hierarchy.cust_id
level zip_code is customer_hierarchy.zip_code
level region is customer_hierarchy.region
level day is time_hierarchy.day
level mmyyyy is time_hierarchy.mmyyyy
level qtr_yyyy is time_hierarchy.qtr_yyyy
level yyyy is time_hierarchy.yyyy
--定义其中一个层次结构
hierarchy cust_rollup
(
cust_id child of
zip_code child of
region
)
--定义另外一个层次结构
hierarchy time_rollup
(
day child of
mmyyyy child of
qtr_yyyy child of
yyyy
)
--mmyyyy和mon_yyyy是同义词
attribute mmyyyy
determines mon_yyyy;
DBMS_OLAP
通过DBMS_OLAP，可以完成如下工作：
a. 估算物化视图的大小
b. 验证维度对象是否有效
c. 建议建立起它物化视图，找出需要删除的视图并重命名
d. 评估物化视图的使用状况
2007年8月10日星期五
2007年8月6日星期一
《Expert one on one Oracle》- 索引- 笔记-2
位图索引（略）
基于函数的索引（略）
使用索引常见问题：
a. 能否在视图上创建索引？只能在视图所基于的基表上创建。
b. B树索引不存储完全为NULL的条目，对于索引至少其中有一列定义为NOT NULL的时候，查询才能使用索引。
对于表T1，create table t1( x int, y int)，创建索引create unique index t_inx(x,y)之后，如果执行select * from t1 where x is null，执行计划显示不会利用索引。
如果对于表T2，create table t1( x int, y int not null)，创建索引create unique index t_inx(x,y)之后，如果执行select * from t2 where x is null，执行计划显示不会利用索引。
c. 为何不使用索引
查询的列超出索引的列的范围。例如，在T(x,y)上创建索引，而实际的查询为Select x, y, z from t where x = 5；那么，由于查询的z列必须访问数据块才能得到，这种情况下，有可能不使用索引而效率更高。
索引的列包含NULL值。执行Select count(*) from T查询，在索引表上建有B树索引。对于NULL值，不在索引中记录，所以不能通过索引来计算count。而会通过全表扫描的方式。
列上建有索引，但是查询的时候在列上使用了函数。例如：select * from t where f(indexed_column)=value
错误的使用条件。例如，对于建有索引的字符列，这列中只包含数字，使用如下查询将不会使用索引，select * from t where indexed_column = 5，将会被转换成select * from t where to_number(indexed_column) = 5。还有，对于这种TRUNC(DATE_COL) = TRUNC(SYSDATE)条件，改写成date_col between trunc(sysdate) and trunc(sysdate)+1‐1/(1*24*60*60)。
表未分析，或者统计数据错误。
基于函数的索引（略）
使用索引常见问题：
a. 能否在视图上创建索引？只能在视图所基于的基表上创建。
b. B树索引不存储完全为NULL的条目，对于索引至少其中有一列定义为NOT NULL的时候，查询才能使用索引。
对于表T1，create table t1( x int, y int)，创建索引create unique index t_inx(x,y)之后，如果执行select * from t1 where x is null，执行计划显示不会利用索引。
如果对于表T2，create table t1( x int, y int not null)，创建索引create unique index t_inx(x,y)之后，如果执行select * from t2 where x is null，执行计划显示不会利用索引。
c. 为何不使用索引
查询的列超出索引的列的范围。例如，在T(x,y)上创建索引，而实际的查询为Select x, y, z from t where x = 5；那么，由于查询的z列必须访问数据块才能得到，这种情况下，有可能不使用索引而效率更高。
索引的列包含NULL值。执行Select count(*) from T查询，在索引表上建有B树索引。对于NULL值，不在索引中记录，所以不能通过索引来计算count。而会通过全表扫描的方式。
列上建有索引，但是查询的时候在列上使用了函数。例如：select * from t where f(indexed_column)=value
错误的使用条件。例如，对于建有索引的字符列，这列中只包含数字，使用如下查询将不会使用索引，select * from t where indexed_column = 5，将会被转换成select * from t where to_number(indexed_column) = 5。还有，对于这种TRUNC(DATE_COL) = TRUNC(SYSDATE)条件，改写成date_col between trunc(sysdate) and trunc(sysdate)+1‐1/(1*24*60*60)。
表未分析，或者统计数据错误。
2007年8月3日星期五
《Expert one on one Oracle》- 索引- 笔记-1
B树索引
对于非唯一性索引，rowid和索引key组成唯一性。唯一性索引，oracle不将rowid加入索引的key。
索引压缩
可以对索引进行压缩，例如all_objects中有大量重复的owner，object_type的值。增加CPU的时间，减少I/O的时间。
create index t_idx on all_objects(owner,object_type,object_name);
create index t_idx on all_objects(owner,object_type,object_name) compress 1;
create index t_idx on all_objects(owner,object_type,object_name) compress 2;
Reserve Key indexes
对于那些邻近得值，如果对它们创建索引，索引也是序列递增的，非常可能存放在同一个块上，这样增加冲突的可能性。通过反转，可以让索引更好的分布。但是对于条件where X>5，X列上的反转索引就会失效。
SQL> select 90101, dump(90101,16), dump(reverse(90101),16) from dual
2 union all
3 select 90102, dump(90102,16),dump(reverse(90102),16) from dual
4 union all
5 select 90103, dump(90103,16),dump(reverse(90103),16) from dual
6 /
90101 DUMP(90101,16) DUMP(REVERSE(90101),1
---------- --------------------- ---------------------
90101 Typ=2 Len=4: c3,a,2,2 Typ=2 Len=4: 2,2,a,c3
90102 Typ=2 Len=4: c3,a,2,3 Typ=2 Len=4: 3,2,a,c3
90103 Typ=2 Len=4: c3,a,2,4 Typ=2 Len=4: 4,2,a,c3
降序索引
索引创建的时候，是按照索引字段的值升序排列，查询的时候排序因子的字段使用不同的排序方式（例如一个字段升序，一个字段降序），那么通过执行计划可以看到数据库会多执行一个Sort的步骤。这种情况下，可以建立降序索引。感觉类似于基于函数的索引。
SQL> CREATE TABLE T AS select * from all_objects;
Table created.
--建立多个字段的索引
SQL> create index t_idx on t(owner,object_type,object_name);
Index created.
--结果升序排列，使用索引，执行计划中无排序步骤
SQL> select owner, object_type
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 order by owner ASC,object_type ASC;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)
--结果降序排列，使用索引，有排序步骤
SQL> select owner, object_type
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 order by owner DESC, object_type DESC;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)
--分析表
SQL> exec dbms_stats.gather_TABLE_stats( user, 'T' );
PL/SQL procedure successfully completed.
--分析表之后，结果降序排列，使用索引，无排序步骤
SQL> select owner, object_type
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 order by owner DESC, object_type DESC;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=7018 Bytes=10
5270)
1 0 INDEX (RANGE SCAN DESCENDING) OF 'T_IDX' (NON-UNIQUE) (Cos
t=7 Card=7018 Bytes=105270)
--两个字段排序方式不一致，有排序步骤
SQL> select owner, object_type
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 order by owner ASC,object_type DESC;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=7018 Bytes=1
05270)
1 0 SORT (ORDER BY) (Cost=34 Card=7018 Bytes=105270)
2 1 INDEX (FAST FULL SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=4 C
ard=7018 Bytes=105270)
--建立降序索引
SQL> create index desc_t_idx on t(owner ASC,object_type DESC);
Index created.
--根据排序语句建立对应索引后，无排序步骤，使用降序索引
SQL> select owner, object_type
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 order by owner ASC,object_type DESC;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=7018 Bytes=10
5270)
1 0 INDEX (RANGE SCAN) OF 'DESC_T_IDX' (NON-UNIQUE) (Cost=7 Ca
rd=7018 Bytes=105270)
使用B树索引的两个原则
a. 如果访问的记录数占整个表纪录数百分比较少。
b. 如果索引包含足够的信息，而查询的时候需要再去查询标的数据块。
继续上面的例子：
--使用T-IDX索引，因为这个查询中多了object_name字段，通过T-IDX索引就不需要访问表的数据块，通过FAST FULL SCAN可以完成
SQL> select owner, object_type,object_name
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 order by owner ASC,object_type DESC;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=62 Card=7018 Bytes=2
73702)
1 0 SORT (ORDER BY) (Cost=62 Card=7018 Bytes=273702)
2 1 INDEX (FAST FULL SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=4 C
ard=7018 Bytes=273702)
--再增加一个查询字段created，这两个索引中都没有这个字段，所以都需要访问表的数据块。而，查询返回的结果占表纪录行的大部分（owner between 'T' and 'Z'），通过先访问索引再访问数据块，效率更低。Oracle选择全表扫描。
SQL> select owner, object_type,object_name,created
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 order by owner ASC,object_type DESC;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=108 Card=7018 Bytes=
329846)
1 0 SORT (ORDER BY) (Cost=108 Card=7018 Bytes=329846)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=40 Card=7018 Bytes=3298
46)
--限定只返回少量的纪录，发现又重新开始使用索引。
SQL> select owner, object_type,object_name,created
2 from t
3 where owner = 'T'
4 and object_type is not null
5 order by owner ASC,object_type DESC;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=31 Card=1046 Bytes=4
9162)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=31 Card=1046 By
tes=49162)
2 1 INDEX (RANGE SCAN) OF 'DESC_T_IDX' (NON-UNIQUE) (Cost=2
Card=1046)
当然上述两条原则并不是适用于任何情况，有许多因素影响到执行计划。下面这个例子说明数据的存储对索引的影响。通过建立两张表，一张无序存储，一张有序存储，来比较两种情况下使用索引所消耗的资源和时间。
--创建有序存储的表，相邻记录存储在同一个数据块
SQL> create table colocated ( x int, y varchar2(2000) ) pctfree 0;
Table created.
SQL> begin
2 for i in 1 .. 100000
3 loop
4 insert into colocated values ( i, rpad(dbms_random.random,75,'*'
) );
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
--同一个数据块储存数据不相邻
SQL> create table disorganized nologging pctfree 0
2 as
3 select x, y from colocated ORDER BY y
4 /
Table created.
--创建主健，同时也会创建索引
SQL> alter table colocated add constraint colocated_pk primary key(x);
Table altered.
SQL> alter table disorganized add constraint disorganized_pk primary key(x);
Table altered.
SQL> commit;
Commit complete.
SQL> set timing on
SQL> set autotrace traceonly
--对于有序存储的数据，查询的时候只有3000的逻辑I/O，时间为0.03秒
SQL> select * from COLOCATED where x between 20000 and 40000;
20001 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'COLOCATED'
2 1 INDEX (RANGE SCAN) OF 'COLOCATED_PK' (UNIQUE)
Statistics
----------------------------------------------------------
156 recursive calls
0 db block gets
2908 consistent gets
43 physical reads
0 redo size
1805701 bytes sent via SQL*Net to client
15162 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
20001 rows processed
--对于无序存储的数据，查询的时候只有将近20000的逻辑I/O，时间为8秒。同样的数据和索引，相对而言，无序存储的数据使用索引查询消耗更多的时间。
SQL> select * from DISORGANIZED where x between 20000 and 40000;
20001 rows selected.
Elapsed: 00:00:08.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'DISORGANIZED'
2 1 INDEX (RANGE SCAN) OF 'DISORGANIZED_PK' (UNIQUE)
Statistics
----------------------------------------------------------
156 recursive calls
0 db block gets
21388 consistent gets
1119 physical reads
0 redo size
1805701 bytes sent via SQL*Net to client
15162 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
20001 rows processed
--对于无序存储的数据，强制用全表扫描只需要0.04秒，说明全表扫描比使用索引更有效。那为什么Oracle执行计划中为什么不使用全表扫描来查询呢？在CBO的优化模式下，因为没有对表进行分析，Oracle并没有足够的信息来选择最优的执行路径。下面对表进行分析。
SQL> select /*+ FULL(DISORGANIZED) */ * from DISORGANIZED where x between 20000
and 40000;
20001 rows selected.
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=105 Card=220 Bytes=2
23300)
1 0 TABLE ACCESS (FULL) OF 'DISORGANIZED' (Cost=105 Card=220 B
ytes=223300)
Statistics
----------------------------------------------------------
60 recursive calls
0 db block gets
2407 consistent gets
1 physical reads
0 redo size
1805701 bytes sent via SQL*Net to client
15162 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
20001 rows processed
--分析表
SQL> set autotrace off;
SQL> set timing off;
SQL> analyze table colocated
2 compute statistics
3 for table
4 for all indexes
5 for all indexed columns
6 /
Table analyzed.
--分析表
SQL> analyze table disorganized
2 compute statistics
3 for table
4 for all indexes
5 for all indexed columns
6 /
Table analyzed.
--一旦分析完成，可以查询user_indexes表中的CLUSTERING_FACTOR字段的值。如果这个值接近数据块的数量，证明这张表是存储相当好。单个索引叶子节点的索引项通常指向同一个数据块。如果这个值接近数据行的数量，说明这张表是随机存储的。单个索引叶子节点的索引项通常不指向同一个数据块。如上的两张表中，表'COLOCATED_PK'的主健的CLUSTERING_FACTOR字段的值为1073，接近块的数量，存储有序。表'DISORGANIZED_PK'的主健CLUSTERING_FACTOR字段的值为99907，接近行的数量，存储无序。
SQL> select a.index_name,
2 b.num_rows,
3 b.blocks,
4 a.clustering_factor
5 from user_indexes a, user_tables b
6 where index_name in ('COLOCATED_PK', 'DISORGANIZED_PK' )
7 and a.table_name = b.table_name
8 /
INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ---------- -----------------
COLOCATED_PK 100000 1073 1073
DISORGANIZED_PK 100000 1076 99907
SQL> set timing on
SQL> set autotrace traceonly
--经过表分析之后，Oracle拥有足够的信息知道通过全表访问更有效，这个时候不用通过增加hint，Oracle会自动选择全表扫描的方式查询。
SQL> select * from DISORGANIZED where x between 20000 and 30000;
10001 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=105 Card=9995 Bytes=
839580)
1 0 TABLE ACCESS (FULL) OF 'DISORGANIZED' (Cost=105 Card=9995
Bytes=839580)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1743 consistent gets
0 physical reads
0 redo size
903094 bytes sent via SQL*Net to client
7825 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10001 rows processed
对于非唯一性索引，rowid和索引key组成唯一性。唯一性索引，oracle不将rowid加入索引的key。
索引压缩
可以对索引进行压缩，例如all_objects中有大量重复的owner，object_type的值。增加CPU的时间，减少I/O的时间。
create index t_idx on all_objects(owner,object_type,object_name);
create index t_idx on all_objects(owner,object_type,object_name) compress 1;
create index t_idx on all_objects(owner,object_type,object_name) compress 2;
Reserve Key indexes
对于那些邻近得值，如果对它们创建索引，索引也是序列递增的，非常可能存放在同一个块上，这样增加冲突的可能性。通过反转，可以让索引更好的分布。但是对于条件where X>5，X列上的反转索引就会失效。
SQL> select 90101, dump(90101,16), dump(reverse(90101),16) from dual
2 union all
3 select 90102, dump(90102,16),dump(reverse(90102),16) from dual
4 union all
5 select 90103, dump(90103,16),dump(reverse(90103),16) from dual
6 /
90101 DUMP(90101,16) DUMP(REVERSE(90101),1
---------- --------------------- ---------------------
90101 Typ=2 Len=4: c3,a,2,2 Typ=2 Len=4: 2,2,a,c3
90102 Typ=2 Len=4: c3,a,2,3 Typ=2 Len=4: 3,2,a,c3
90103 Typ=2 Len=4: c3,a,2,4 Typ=2 Len=4: 4,2,a,c3
降序索引
索引创建的时候，是按照索引字段的值升序排列，查询的时候排序因子的字段使用不同的排序方式（例如一个字段升序，一个字段降序），那么通过执行计划可以看到数据库会多执行一个Sort的步骤。这种情况下，可以建立降序索引。感觉类似于基于函数的索引。
SQL> CREATE TABLE T AS select * from all_objects;
Table created.
--建立多个字段的索引
SQL> create index t_idx on t(owner,object_type,object_name);
Index created.
--结果升序排列，使用索引，执行计划中无排序步骤
SQL> select owner, object_type
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 order by owner ASC,object_type ASC;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)
--结果降序排列，使用索引，有排序步骤
SQL> select owner, object_type
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 order by owner DESC, object_type DESC;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)
--分析表
SQL> exec dbms_stats.gather_TABLE_stats( user, 'T' );
PL/SQL procedure successfully completed.
--分析表之后，结果降序排列，使用索引，无排序步骤
SQL> select owner, object_type
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 order by owner DESC, object_type DESC;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=7018 Bytes=10
5270)
1 0 INDEX (RANGE SCAN DESCENDING) OF 'T_IDX' (NON-UNIQUE) (Cos
t=7 Card=7018 Bytes=105270)
--两个字段排序方式不一致，有排序步骤
SQL> select owner, object_type
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 order by owner ASC,object_type DESC;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=7018 Bytes=1
05270)
1 0 SORT (ORDER BY) (Cost=34 Card=7018 Bytes=105270)
2 1 INDEX (FAST FULL SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=4 C
ard=7018 Bytes=105270)
--建立降序索引
SQL> create index desc_t_idx on t(owner ASC,object_type DESC);
Index created.
--根据排序语句建立对应索引后，无排序步骤，使用降序索引
SQL> select owner, object_type
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 order by owner ASC,object_type DESC;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=7018 Bytes=10
5270)
1 0 INDEX (RANGE SCAN) OF 'DESC_T_IDX' (NON-UNIQUE) (Cost=7 Ca
rd=7018 Bytes=105270)
使用B树索引的两个原则
a. 如果访问的记录数占整个表纪录数百分比较少。
b. 如果索引包含足够的信息，而查询的时候需要再去查询标的数据块。
继续上面的例子：
--使用T-IDX索引，因为这个查询中多了object_name字段，通过T-IDX索引就不需要访问表的数据块，通过FAST FULL SCAN可以完成
SQL> select owner, object_type,object_name
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 order by owner ASC,object_type DESC;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=62 Card=7018 Bytes=2
73702)
1 0 SORT (ORDER BY) (Cost=62 Card=7018 Bytes=273702)
2 1 INDEX (FAST FULL SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=4 C
ard=7018 Bytes=273702)
--再增加一个查询字段created，这两个索引中都没有这个字段，所以都需要访问表的数据块。而，查询返回的结果占表纪录行的大部分（owner between 'T' and 'Z'），通过先访问索引再访问数据块，效率更低。Oracle选择全表扫描。
SQL> select owner, object_type,object_name,created
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 order by owner ASC,object_type DESC;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=108 Card=7018 Bytes=
329846)
1 0 SORT (ORDER BY) (Cost=108 Card=7018 Bytes=329846)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=40 Card=7018 Bytes=3298
46)
--限定只返回少量的纪录，发现又重新开始使用索引。
SQL> select owner, object_type,object_name,created
2 from t
3 where owner = 'T'
4 and object_type is not null
5 order by owner ASC,object_type DESC;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=31 Card=1046 Bytes=4
9162)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=31 Card=1046 By
tes=49162)
2 1 INDEX (RANGE SCAN) OF 'DESC_T_IDX' (NON-UNIQUE) (Cost=2
Card=1046)
当然上述两条原则并不是适用于任何情况，有许多因素影响到执行计划。下面这个例子说明数据的存储对索引的影响。通过建立两张表，一张无序存储，一张有序存储，来比较两种情况下使用索引所消耗的资源和时间。
--创建有序存储的表，相邻记录存储在同一个数据块
SQL> create table colocated ( x int, y varchar2(2000) ) pctfree 0;
Table created.
SQL> begin
2 for i in 1 .. 100000
3 loop
4 insert into colocated values ( i, rpad(dbms_random.random,75,'*'
) );
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
--同一个数据块储存数据不相邻
SQL> create table disorganized nologging pctfree 0
2 as
3 select x, y from colocated ORDER BY y
4 /
Table created.
--创建主健，同时也会创建索引
SQL> alter table colocated add constraint colocated_pk primary key(x);
Table altered.
SQL> alter table disorganized add constraint disorganized_pk primary key(x);
Table altered.
SQL> commit;
Commit complete.
SQL> set timing on
SQL> set autotrace traceonly
--对于有序存储的数据，查询的时候只有3000的逻辑I/O，时间为0.03秒
SQL> select * from COLOCATED where x between 20000 and 40000;
20001 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'COLOCATED'
2 1 INDEX (RANGE SCAN) OF 'COLOCATED_PK' (UNIQUE)
Statistics
----------------------------------------------------------
156 recursive calls
0 db block gets
2908 consistent gets
43 physical reads
0 redo size
1805701 bytes sent via SQL*Net to client
15162 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
20001 rows processed
--对于无序存储的数据，查询的时候只有将近20000的逻辑I/O，时间为8秒。同样的数据和索引，相对而言，无序存储的数据使用索引查询消耗更多的时间。
SQL> select * from DISORGANIZED where x between 20000 and 40000;
20001 rows selected.
Elapsed: 00:00:08.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'DISORGANIZED'
2 1 INDEX (RANGE SCAN) OF 'DISORGANIZED_PK' (UNIQUE)
Statistics
----------------------------------------------------------
156 recursive calls
0 db block gets
21388 consistent gets
1119 physical reads
0 redo size
1805701 bytes sent via SQL*Net to client
15162 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
20001 rows processed
--对于无序存储的数据，强制用全表扫描只需要0.04秒，说明全表扫描比使用索引更有效。那为什么Oracle执行计划中为什么不使用全表扫描来查询呢？在CBO的优化模式下，因为没有对表进行分析，Oracle并没有足够的信息来选择最优的执行路径。下面对表进行分析。
SQL> select /*+ FULL(DISORGANIZED) */ * from DISORGANIZED where x between 20000
and 40000;
20001 rows selected.
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=105 Card=220 Bytes=2
23300)
1 0 TABLE ACCESS (FULL) OF 'DISORGANIZED' (Cost=105 Card=220 B
ytes=223300)
Statistics
----------------------------------------------------------
60 recursive calls
0 db block gets
2407 consistent gets
1 physical reads
0 redo size
1805701 bytes sent via SQL*Net to client
15162 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
20001 rows processed
--分析表
SQL> set autotrace off;
SQL> set timing off;
SQL> analyze table colocated
2 compute statistics
3 for table
4 for all indexes
5 for all indexed columns
6 /
Table analyzed.
--分析表
SQL> analyze table disorganized
2 compute statistics
3 for table
4 for all indexes
5 for all indexed columns
6 /
Table analyzed.
--一旦分析完成，可以查询user_indexes表中的CLUSTERING_FACTOR字段的值。如果这个值接近数据块的数量，证明这张表是存储相当好。单个索引叶子节点的索引项通常指向同一个数据块。如果这个值接近数据行的数量，说明这张表是随机存储的。单个索引叶子节点的索引项通常不指向同一个数据块。如上的两张表中，表'COLOCATED_PK'的主健的CLUSTERING_FACTOR字段的值为1073，接近块的数量，存储有序。表'DISORGANIZED_PK'的主健CLUSTERING_FACTOR字段的值为99907，接近行的数量，存储无序。
SQL> select a.index_name,
2 b.num_rows,
3 b.blocks,
4 a.clustering_factor
5 from user_indexes a, user_tables b
6 where index_name in ('COLOCATED_PK', 'DISORGANIZED_PK' )
7 and a.table_name = b.table_name
8 /
INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ---------- -----------------
COLOCATED_PK 100000 1073 1073
DISORGANIZED_PK 100000 1076 99907
SQL> set timing on
SQL> set autotrace traceonly
--经过表分析之后，Oracle拥有足够的信息知道通过全表访问更有效，这个时候不用通过增加hint，Oracle会自动选择全表扫描的方式查询。
SQL> select * from DISORGANIZED where x between 20000 and 30000;
10001 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=105 Card=9995 Bytes=
839580)
1 0 TABLE ACCESS (FULL) OF 'DISORGANIZED' (Cost=105 Card=9995
Bytes=839580)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1743 consistent gets
0 physical reads
0 redo size
903094 bytes sent via SQL*Net to client
7825 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10001 rows processed
2007年6月26日星期二
《Expert one on one Oracle》- 重做回滚- 笔记-2
块清除
当Oracle更改数据块时，会对这个数据块进行跟踪。在提交的时候，将会重新访问这个块并将更改标记为已经提交。但是只标示那些还没有从db_block_buffers中清除的块。而且，如果更改了太多的块，那么也只有前面少数的块（db_block_buffers的10%）被重新访问。
当提交的时候，如果这些块还在缓存中，那么Oracle将会进行fast commit，清除块头部的事务信息。对于那些已经从db_block_buffers中清除，被写入磁盘的数据块，Oracle将会忽略这些块。当下次的DML或者查询语句再访问这些块的时候，由这些语句负责清除，这就是所谓的delayed block cleanout。所以，即使是查询语句也可能产生重做日志。
通过如下的方法来验证：
a. 创建表，保证一块只能存储一行，在8K大小数据块中，创建每行大小为6K的数据库；
b. 插入499行纪录，使用499个数据块，其大小已经超过30（也即300的10%）；
c. 提交；
d. 对于在db_block_buffers的数据块（同时保证小于db_block_buffers 10%），Oracle执行fast commit，对于其他的数据块Oracle将忽略；
e. 执行全表扫描的查询语句。这条语句将会重新访问步骤d中没有被清除的块，对这些块头的事务信息进行清除，这个步骤将会产生重做日志。
f. 检查通过步骤e查询所产生的重做日志。
g. 执行新的查询，由于步骤e已经清除块头的事务信息，这个时候查询将不会需要清除块，也不会产生重做日志。
SQL> create table t --保证一块只能存储一行
2 ( x char(2000) default 'x',
3 y char(2000) default 'y',
4 z char(2000) default 'z' )
5 /
Table created.
SQL>
SQL> insert into t --插入499行纪录，使用499个数据块
2 select 'x','y','z'
3 from all_objects where rownum <500
SQL> commit;-- 提交，插入过程中部分快会执行fast commit，部分块被忽略
Commit complete.
SQL>
SQL> column value new_value old_value
SQL>
SQL> select * from redo_size;
VALUE
----------
3332424
SQL>
SQL> select *
2 from t
3 where x = y;
no rows selected
SQL> select value-&old_value REDO_GENERATED from redo_size;
old 1: select value-&old_value REDO_GENERATED from redo_size
new 1: select value- 3332424 REDO_GENERATED from redo_size
REDO_GENERATED
--------------
10740 --查询过程产生了重做日志，证明进行了块清除操作
SQL> commit;
Commit complete.
SQL>
SQL> select value from redo_size;
VALUE
----------
3343164
SQL>
SQL> select *
2 from t
3 where x = y;
no rows selected
SQL>
SQL> select value-&old_value REDO_GENERATED from redo_size;
old 1: select value-&old_value REDO_GENERATED from redo_size
new 1: select value- 3343164 REDO_GENERATED from redo_size
REDO_GENERATED
--------------
0--查询过程没有产生重做日志，前面的查询已经清除块，第二次查询不再有清除动作
上面的第一个查询产生了重做日志，有可能促使这些块被DBWR重写。Oracle必须采用delayed clear out的策略，否则在提交的时候必须重新访问更新块，还可能从磁盘中读取快，这将会是相当耗时的工作。
有些事务创建“干净的”数据块，例如：CREATE TABLE AS SELECT。
在某些情况下，进行大量的数据更新之后，也可以主动访问数据块，让最终用户访问的时候速度会更快。例如通过ANALYZE命令，可以清除块。
参考：
Block cleanout - fast or delayed.
当Oracle更改数据块时，会对这个数据块进行跟踪。在提交的时候，将会重新访问这个块并将更改标记为已经提交。但是只标示那些还没有从db_block_buffers中清除的块。而且，如果更改了太多的块，那么也只有前面少数的块（db_block_buffers的10%）被重新访问。
当提交的时候，如果这些块还在缓存中，那么Oracle将会进行fast commit，清除块头部的事务信息。对于那些已经从db_block_buffers中清除，被写入磁盘的数据块，Oracle将会忽略这些块。当下次的DML或者查询语句再访问这些块的时候，由这些语句负责清除，这就是所谓的delayed block cleanout。所以，即使是查询语句也可能产生重做日志。
通过如下的方法来验证：
a. 创建表，保证一块只能存储一行，在8K大小数据块中，创建每行大小为6K的数据库；
b. 插入499行纪录，使用499个数据块，其大小已经超过30（也即300的10%）；
c. 提交；
d. 对于在db_block_buffers的数据块（同时保证小于db_block_buffers 10%），Oracle执行fast commit，对于其他的数据块Oracle将忽略；
e. 执行全表扫描的查询语句。这条语句将会重新访问步骤d中没有被清除的块，对这些块头的事务信息进行清除，这个步骤将会产生重做日志。
f. 检查通过步骤e查询所产生的重做日志。
g. 执行新的查询，由于步骤e已经清除块头的事务信息，这个时候查询将不会需要清除块，也不会产生重做日志。
SQL> create table t --保证一块只能存储一行
2 ( x char(2000) default 'x',
3 y char(2000) default 'y',
4 z char(2000) default 'z' )
5 /
Table created.
SQL>
SQL> insert into t --插入499行纪录，使用499个数据块
2 select 'x','y','z'
3 from all_objects where rownum <500
SQL> commit;-- 提交，插入过程中部分快会执行fast commit，部分块被忽略
Commit complete.
SQL>
SQL> column value new_value old_value
SQL>
SQL> select * from redo_size;
VALUE
----------
3332424
SQL>
SQL> select *
2 from t
3 where x = y;
no rows selected
SQL> select value-&old_value REDO_GENERATED from redo_size;
old 1: select value-&old_value REDO_GENERATED from redo_size
new 1: select value- 3332424 REDO_GENERATED from redo_size
REDO_GENERATED
--------------
10740 --查询过程产生了重做日志，证明进行了块清除操作
SQL> commit;
Commit complete.
SQL>
SQL> select value from redo_size;
VALUE
----------
3343164
SQL>
SQL> select *
2 from t
3 where x = y;
no rows selected
SQL>
SQL> select value-&old_value REDO_GENERATED from redo_size;
old 1: select value-&old_value REDO_GENERATED from redo_size
new 1: select value- 3343164 REDO_GENERATED from redo_size
REDO_GENERATED
--------------
0--查询过程没有产生重做日志，前面的查询已经清除块，第二次查询不再有清除动作
上面的第一个查询产生了重做日志，有可能促使这些块被DBWR重写。Oracle必须采用delayed clear out的策略，否则在提交的时候必须重新访问更新块，还可能从磁盘中读取快，这将会是相当耗时的工作。
有些事务创建“干净的”数据块，例如：CREATE TABLE AS SELECT。
在某些情况下，进行大量的数据更新之后，也可以主动访问数据块，让最终用户访问的时候速度会更快。例如通过ANALYZE命令，可以清除块。
参考：
Block cleanout - fast or delayed.
2007年6月25日星期一
《Expert one on one Oracle》- 重做回滚- 笔记-1
“重做”允许Oracle重做事务。“回滚”则允许Oracle撤销或者回滚事务。
重做
重做日志文件是数据库的事务日志，只用来恢复数据库。Oracle有两种类型重做日志文件：在线重做日志和归档重做日志。在线重做日志以循环的方式来写入，当其中一个日志文件写满，就会切换写另外的日志文件，循环往复。归档日至是当在线日志写满的时候，将在线日志拷贝到另外一个地方。
COMMIT
COMMIT语句的执行并不消耗太多时间，而且并不是事务越大COMMIT所消耗的时间越多。实际的情况是，在COMMIT执行之前，Oracle已经完成所要提交事务的大部分工作。
SQL> create table t ( x int );
Table created.
SQL> set serveroutput on
SQL>
SQL> declare
2 l_start number default dbms_utility.get_time;
3 begin
4 for i in 1 .. 10000
5 loop
6 insert into t values ( 1 );
7 end loop;
8 commit;
9 dbms_output.put_line
10 ( dbms_utility.get_time-l_start ' hsecs' );
11 end;
12 /
135 hsecs
PL/SQL procedure successfully completed.
SQL>
SQL> declare
2 l_start number default dbms_utility.get_time;
3 begin
4 for i in 1 .. 10000
5 loop
6 insert into t values ( 1 );
7 commit;
8 end loop;
9 dbms_output.put_line
10 ( dbms_utility.get_time-l_start ' hsecs' );
11 end;
12 /
161 hsecs
PL/SQL procedure successfully completed.
从上面可以看到，插入多样的数据一次提交比进行多次提交消耗更少的时间。所以，提交的响应时间和事务大小并无太大关系。因为提交之前，Oracle已经完成99%的工作，具体为：
a. 回滚段的纪录已经在SGA中产生。
b. 更改的数据块已经在SGA中产生。
c. 上述两项的缓存的REDO已经在SGA产生。
d. 基于如上三项的的大小以及所消耗时间，上述数据的部分已经写入磁盘。
e. 已经获取所有需要的锁。
那么提交的时候，需要完成如下工作：
a. 为事务产生SCN。
b. LGWR将余下的缓存的重做日志写入磁盘，同时将SCN好记入到重做日志文件。完成这一步之后，事务实际上已经提交。事务实体被移除。在V$TRANSACTION表中找不到事物的相关纪录。
c. Session所持有的锁被释放。
d. 如果被事务所修改的快还在缓存中，那么大多数将被访问以及被“清除”。
真正消耗时间的在于LGWR将重做日志写入磁盘，因为这是物理的I/O访问。但是COMMIT之后，LGWR并不是将所有的 内容写入重做日志。在COMMIT之前，LGWR已经持续的写重做日志。当如下条件满足的时候，LGWR写重做日志：
a. 每三秒
b. 当达到1/3或者1MB大小
c. 当COMMIT的时候
SCN是ORACLE用户保证事务的顺序，确保数据库能被正确恢复。同时，它也用来保证数据库的读一至性以及检查点（checkpointing）。每当事务提交，SCN号增一。
SQL> create table t
2 as
3 select * from all_objects
4 /
Table created.
SQL> insert into t select * from t;
29257 rows created.
SQL> insert into t select * from t;
58514 rows created.
SQL> insert into t select * from t where rownum < 12000;
11999 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> create or replace procedure do_commit( p_rows in number )
2 as
3 l_start number;
4 l_after_redo number;
5 l_before_redo number;
6 begin
7 select v$mystat.value into l_before_redo
8 from v$mystat, v$statname
9 where v$mystat.statistic# = v$statname.statistic#
10 and v$statname.name = 'redo size';
11
12 l_start := dbms_utility.get_time;
13 insert into t select * from t where rownum < name =" 'redo">
SQL> set serveroutput on format wrapped
SQL> begin
2 for i in 1 .. 5
3 loop
4 do_commit( power(10,i) );
5 end loop;
6 end;
7 /
9 rows created
Time to INSERT: .05 seconds
Time to COMMIT: .00 seconds
Generated 1,368 bytes of redo
99 rows created
Time to INSERT: .00 seconds
Time to COMMIT: .00 seconds
Generated 11,596 bytes of redo
999 rows created
Time to INSERT: .03 seconds
Time to COMMIT: .00 seconds
Generated 116,732 bytes of redo
9999 rows created
Time to INSERT: 1.34 seconds
Time to COMMIT: .00 seconds
Generated 1,079,844 bytes of redo
99999 rows created
Time to INSERT: 5.56 seconds
Time to COMMIT: .00 seconds
Generated 11,175,056 bytes of redo
PL/SQL procedure successfully completed.
从上述运行结果可以看到，随着插入数据量的增大，产生的重做日志也逐渐增大，插入所消耗的时间也增多，但是COMMIT所消耗的时间仍然相当的少。所以，也验证了上述结论：在COMMIT之前，ORACLE已经完成了绝大多数的工作，所以COMMIT锁耗用的时间是相当少的。
回滚
将上述程序的COMMIT（23，25行）替换成ROLLBACK，再执行察看回滚所消耗的时间。
Time to INSERT: .04 seconds
Time to ROLLBACK: .00 seconds
Generated 1,504 bytes of redo
99 rows created
Time to INSERT: .00 seconds
Time to ROLLBACK: .00 seconds
Generated 12,288 bytes of redo
999 rows created
Time to INSERT: .03 seconds
Time to ROLLBACK: .00 seconds
Generated 123,736 bytes of redo
9999 rows created
Time to INSERT: .42 seconds
Time to ROLLBACK: .00 seconds
Generated 1,146,652 bytes of redo
99999 rows created
Time to INSERT: 3.99 seconds
Time to ROLLBACK: 1.64 seconds
Generated 11,883,064 bytes of redo
PL/SQL procedure successfully completed.
从上述运行结果可知，与COMMIT不同，随着数据量的增大，回滚所消耗的时间也增加。这是因为回滚是一个消耗时间的操作。
回滚之前，数据库所完成的工作与提交部分（可以参考上述COMMIT部分）相同。
回滚时，ORACLE所需要完成的工作为：
a. 回滚所有的更改。从回滚段中读取数据，并执行与原来相反的操作。例如，如果原来插入一行，回滚时便删除一行。如果更新一行，回滚时候必须更新回去。
b. Session所持有的锁被释放。
测量产生的日志数量
创建如下的一个视图，可以方便的查询系统中的重做日志。
create or replace view redo_size
as
select value
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';
通过测试脚本，可以看到：通过一条语句更新N行，与通过N条语句来更新N行，所产生的重做日志的数量大致相同。对于删除操作，结果也类似。但对于更新操作，批量更新产生的重做日志更小一些。
还有，如果我们插入2000bytes的行，实际每行产生的重做日志要高于2000bytes。对于删除操作也类似。对于更新操作，将会是2000betys的两倍数量（需要记录数据以及回滚）。
触发器对重做日志的影响：
a. 删除操作的BEFORE和AFTER触发器不会增加额外的重做日志。
b. 插入操作的BEFORE和AFTER触发器都会增加额外的重做日志。
c. 更下操作的BEFORE触发器产生额外重做日志，而AFTER触发器不产生。
d. 行的大小影响插入过程额外重做日志，更新操作不受影响。
能否关闭重做日志
尽量不要关闭重做日志功能，有些语句NOLOGGING因子，但是实际上还是会产生重做日志。这些日志因为记录数据词典更改而产生的。多于NOLOGGING，有如下几点需要注意：
a. 即使使用NOLOGGING，还是会产生少量的重做日志，这用来保护数据词典。
b. NOLOGGING之影响当前操作。例如如果在创建表的语句中使用了NOLOGGING，那么只是在创建表的过程中不产生重做日志。后续对于标的插入删除以及更新还是会产生日志。其他的特殊操作例如INSERT /*+ APPEND */和SQLLDR插入数据不会产生日志。
c. 在以归档模式运行的数据库中，一旦使用NOLOGGING，那么尽快将受影响的文件备份。
待续....
重做
重做日志文件是数据库的事务日志，只用来恢复数据库。Oracle有两种类型重做日志文件：在线重做日志和归档重做日志。在线重做日志以循环的方式来写入，当其中一个日志文件写满，就会切换写另外的日志文件，循环往复。归档日至是当在线日志写满的时候，将在线日志拷贝到另外一个地方。
COMMIT
COMMIT语句的执行并不消耗太多时间，而且并不是事务越大COMMIT所消耗的时间越多。实际的情况是，在COMMIT执行之前，Oracle已经完成所要提交事务的大部分工作。
SQL> create table t ( x int );
Table created.
SQL> set serveroutput on
SQL>
SQL> declare
2 l_start number default dbms_utility.get_time;
3 begin
4 for i in 1 .. 10000
5 loop
6 insert into t values ( 1 );
7 end loop;
8 commit;
9 dbms_output.put_line
10 ( dbms_utility.get_time-l_start ' hsecs' );
11 end;
12 /
135 hsecs
PL/SQL procedure successfully completed.
SQL>
SQL> declare
2 l_start number default dbms_utility.get_time;
3 begin
4 for i in 1 .. 10000
5 loop
6 insert into t values ( 1 );
7 commit;
8 end loop;
9 dbms_output.put_line
10 ( dbms_utility.get_time-l_start ' hsecs' );
11 end;
12 /
161 hsecs
PL/SQL procedure successfully completed.
从上面可以看到，插入多样的数据一次提交比进行多次提交消耗更少的时间。所以，提交的响应时间和事务大小并无太大关系。因为提交之前，Oracle已经完成99%的工作，具体为：
a. 回滚段的纪录已经在SGA中产生。
b. 更改的数据块已经在SGA中产生。
c. 上述两项的缓存的REDO已经在SGA产生。
d. 基于如上三项的的大小以及所消耗时间，上述数据的部分已经写入磁盘。
e. 已经获取所有需要的锁。
那么提交的时候，需要完成如下工作：
a. 为事务产生SCN。
b. LGWR将余下的缓存的重做日志写入磁盘，同时将SCN好记入到重做日志文件。完成这一步之后，事务实际上已经提交。事务实体被移除。在V$TRANSACTION表中找不到事物的相关纪录。
c. Session所持有的锁被释放。
d. 如果被事务所修改的快还在缓存中，那么大多数将被访问以及被“清除”。
真正消耗时间的在于LGWR将重做日志写入磁盘，因为这是物理的I/O访问。但是COMMIT之后，LGWR并不是将所有的 内容写入重做日志。在COMMIT之前，LGWR已经持续的写重做日志。当如下条件满足的时候，LGWR写重做日志：
a. 每三秒
b. 当达到1/3或者1MB大小
c. 当COMMIT的时候
SCN是ORACLE用户保证事务的顺序，确保数据库能被正确恢复。同时，它也用来保证数据库的读一至性以及检查点（checkpointing）。每当事务提交，SCN号增一。
SQL> create table t
2 as
3 select * from all_objects
4 /
Table created.
SQL> insert into t select * from t;
29257 rows created.
SQL> insert into t select * from t;
58514 rows created.
SQL> insert into t select * from t where rownum < 12000;
11999 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> create or replace procedure do_commit( p_rows in number )
2 as
3 l_start number;
4 l_after_redo number;
5 l_before_redo number;
6 begin
7 select v$mystat.value into l_before_redo
8 from v$mystat, v$statname
9 where v$mystat.statistic# = v$statname.statistic#
10 and v$statname.name = 'redo size';
11
12 l_start := dbms_utility.get_time;
13 insert into t select * from t where rownum < name =" 'redo">
SQL> set serveroutput on format wrapped
SQL> begin
2 for i in 1 .. 5
3 loop
4 do_commit( power(10,i) );
5 end loop;
6 end;
7 /
9 rows created
Time to INSERT: .05 seconds
Time to COMMIT: .00 seconds
Generated 1,368 bytes of redo
99 rows created
Time to INSERT: .00 seconds
Time to COMMIT: .00 seconds
Generated 11,596 bytes of redo
999 rows created
Time to INSERT: .03 seconds
Time to COMMIT: .00 seconds
Generated 116,732 bytes of redo
9999 rows created
Time to INSERT: 1.34 seconds
Time to COMMIT: .00 seconds
Generated 1,079,844 bytes of redo
99999 rows created
Time to INSERT: 5.56 seconds
Time to COMMIT: .00 seconds
Generated 11,175,056 bytes of redo
PL/SQL procedure successfully completed.
从上述运行结果可以看到，随着插入数据量的增大，产生的重做日志也逐渐增大，插入所消耗的时间也增多，但是COMMIT所消耗的时间仍然相当的少。所以，也验证了上述结论：在COMMIT之前，ORACLE已经完成了绝大多数的工作，所以COMMIT锁耗用的时间是相当少的。
回滚
将上述程序的COMMIT（23，25行）替换成ROLLBACK，再执行察看回滚所消耗的时间。
Time to INSERT: .04 seconds
Time to ROLLBACK: .00 seconds
Generated 1,504 bytes of redo
99 rows created
Time to INSERT: .00 seconds
Time to ROLLBACK: .00 seconds
Generated 12,288 bytes of redo
999 rows created
Time to INSERT: .03 seconds
Time to ROLLBACK: .00 seconds
Generated 123,736 bytes of redo
9999 rows created
Time to INSERT: .42 seconds
Time to ROLLBACK: .00 seconds
Generated 1,146,652 bytes of redo
99999 rows created
Time to INSERT: 3.99 seconds
Time to ROLLBACK: 1.64 seconds
Generated 11,883,064 bytes of redo
PL/SQL procedure successfully completed.
从上述运行结果可知，与COMMIT不同，随着数据量的增大，回滚所消耗的时间也增加。这是因为回滚是一个消耗时间的操作。
回滚之前，数据库所完成的工作与提交部分（可以参考上述COMMIT部分）相同。
回滚时，ORACLE所需要完成的工作为：
a. 回滚所有的更改。从回滚段中读取数据，并执行与原来相反的操作。例如，如果原来插入一行，回滚时便删除一行。如果更新一行，回滚时候必须更新回去。
b. Session所持有的锁被释放。
测量产生的日志数量
创建如下的一个视图，可以方便的查询系统中的重做日志。
create or replace view redo_size
as
select value
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';
通过测试脚本，可以看到：通过一条语句更新N行，与通过N条语句来更新N行，所产生的重做日志的数量大致相同。对于删除操作，结果也类似。但对于更新操作，批量更新产生的重做日志更小一些。
还有，如果我们插入2000bytes的行，实际每行产生的重做日志要高于2000bytes。对于删除操作也类似。对于更新操作，将会是2000betys的两倍数量（需要记录数据以及回滚）。
触发器对重做日志的影响：
a. 删除操作的BEFORE和AFTER触发器不会增加额外的重做日志。
b. 插入操作的BEFORE和AFTER触发器都会增加额外的重做日志。
c. 更下操作的BEFORE触发器产生额外重做日志，而AFTER触发器不产生。
d. 行的大小影响插入过程额外重做日志，更新操作不受影响。
能否关闭重做日志
尽量不要关闭重做日志功能，有些语句NOLOGGING因子，但是实际上还是会产生重做日志。这些日志因为记录数据词典更改而产生的。多于NOLOGGING，有如下几点需要注意：
a. 即使使用NOLOGGING，还是会产生少量的重做日志，这用来保护数据词典。
b. NOLOGGING之影响当前操作。例如如果在创建表的语句中使用了NOLOGGING，那么只是在创建表的过程中不产生重做日志。后续对于标的插入删除以及更新还是会产生日志。其他的特殊操作例如INSERT /*+ APPEND */和SQLLDR插入数据不会产生日志。
c. 在以归档模式运行的数据库中，一旦使用NOLOGGING，那么尽快将受影响的文件备份。
待续....
订阅：
博文 (Atom)
