Gooply: 八月 2007

2007年8月25日星期六

2000 servers终于可以通过网络访问了

自从在HP dv9207us机器被我从Vista降级安装windows 2000 server之后，问题一直不断。先是硬件驱动器的问题，后来是无限局域网内其他机器不能访问它的问题。所以，又作服务器有做客户端，够辛苦的。网络上有很多关于这种能够Ping通，但是不能通过网上邻居访问的解决方法，主要有：
1、是否添加了“文件和打印共享”
2、组策略中是否禁止了你用来访问的用户？

发现都不是这些问题所在。无意在组策略的安全设置中，发现“IP安全策略，在本地机器中-〉安全服务器”是选中的，将其去掉就OK。

现在终于可以从其他两台机器来访问这台服务器了。

2007年8月17日星期五

数据库导入导出乱码问题

项目数据需要清理，DBA将数据按照用户导出发过了。导入数据的时候，发现数据库中文全部变成问号。上网找了一下资料，发现原来是数据库字符集不兼容。导入数据库的字符集WE8ISO8859P9，而导出数据库的字符集为ZHS16GBK。

第一个想法就是更改导入数据库的字符集，通过如下语句：ALTER DATABASE CHARACTER SET ZHS16GBK进行更改，系统提示“ORA-12712 new character set must be a superset of old character set”。原来使用这个语句还需要考虑更改的字符集与原来数据库的字符集兼容。Metalink Note:119164.1上定义了不同字符集的兼容关系。

只有重新建立一个库，设定字符集为ZHS16GBK/AL16UTF16。Imp的时候，系统显示的日志为：
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
export client uses ZHS16CGB231280 character set (possible charset conversion)
export server uses ZHS16GBK NCHAR character set (possible ncharset conversion)

可以看到DBA导出的时候客户端字符集为“ZHS16CGB231280”，导出的数据库字符集为“ZHS16GBK”，这两个字符集也不完全兼容，以后导出的时候需要注意更改客户端字符集。导入的数据库的字符集为“ZHS16GBK”。导入完成之后，没有发现乱码问题。

总结：
1. 导入导出过程中涉及4个字符集，需要保证字符集的兼容性，才可能避免汉字乱码问题。
源数据库字符集
Export过程中用户会话字符集（通过NLS_LANG设定）
Import过程中用户会话字符集（通过NLS_LANG设定）
目标数据库字符集
2. 字符集兼容可以通过Metalink Note:119164.1查询。
3. 同时，在安装数据库的时候，应该根据业务需求选定好字符集，而不是采用安装过程的默认设置。

参考：
网上非常好的一篇文章：http://silverw0396.javaeye.com/blog/90554

2007年8月14日星期二

Trace Discoverer Workbook

两种方法：修改注册表，或者通过SQL。

修改注册表的值

\\HKEY_CURRENT_USER\Software\Oracle\Discoverer\Database\SqlTrace
type DWORD
numeric value 1

使用这个的前提是需要trace的工作簿查询尚未运行。如果已经运行了，可以通过方法2来完成。

另，完成trace之后，别忘记更改回去。

通过SQL

a. 通过如下SQL定位discoverer的session以及spid。从而可以对对应的session进行跟踪。从而可以通过spid知道trace文件的名称。

SELECT s.SID, s.serial#, s.username, s.program, p.spid
FROM v$session s, v$process p
WHERE p.addr = s.paddr
AND s.program LIKE '%DIS4USR.EXE%'

得到

SID:72 serial#49228 SPID:5865
SID:132 serial#56294 SPID:5877

b. ENABLE TRACE
exec dbms_system.set_sql_trace_in_session(72, 49228, TRUE)
exec dbms_system.set_sql_trace_in_session(132, 56294, TRUE)

c. 执行discoverer相关操作，例如运行工作簿，共享工作簿等。

d. 从文件菜单中推出discoverer，这一步比较重要，如果是进行性能分析话，保证执行计划相关资源数据的完整性。

e. Disable trace.

f. 通过步骤a得到的spid，从数据库下载对应的trace文件。格式化trace文件，判断哪一个是需要的trace文件，对正确的trace文件进行分析。

tkprof onidau1_ora_5865.trc 14_2_1.txt sys = no;
tkprof onidau1_ora_5877.trc 14_2_2.txt sys=no;

参考：metalink. Note:273928.1

2007年8月10日星期五

《Expert one on one Oracle》- 物化视图 - 笔记

物化视图概念类似于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月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)。
表未分析，或者统计数据错误。

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

2007年8月1日星期三

Personalization: 从并发定义Form打开可执行Form

Oracle Form Personalization的Demo，从用户定义界面的职责列表中，选中某一个职责行然后从Tools菜单中，选中子菜单打开职责定义界面察看职责的详细信息。仿照这个Demo，练习了一个从并发程序定义界面，打开并发可执行界面，察看和这个并发程序关联的可执行的详细信息。

Personalization分为两部分，一部分是针对并发程序Form的，主要功能是定义打开可执行的菜单，打开可执行的Form。另一部分是针对可执行Form的，主要功能是根据从并发程序传递过来的可执行的参数，进行查询显示。

并发程序Form的Personalization

1. 打开Personalization的界面

选择“Application Developer”职责，打开“Concurrent->Program”。然后从Help菜单打开Personalize。

2. 定义Executable Details的菜单

设置条件：
定义只对职责“Application Developer”有效。

设置Action：
新增一个菜单“Executions”，指定Menu Entry为SPECIAL1，这样菜单会挂在TOOLS下。

3. 设置判断逻辑
如果当前的Execution Name域没有可执行的名称，那么如果用户这个时候点击“Executions”菜单，提示错误。

设置Condition：
根据Item项CONC_PROG.EXECUTABLE_ID是否有值来判断。
注意，这里的Trigger为所定义菜单“SPECIAL1”。

设置Action：
通过Message类型的错误，显示提示信息。

4. 打开菜单

设置Condition：
根据Item项CONC_PROG.EXECUTABLE_ID是否有值来判断。
注意，这里的Trigger为所定义菜单“SPECIAL1”。

设置Action1：
将CONC_PROG.EXECUTABLE_ID值放入全局变量XX_EXECUTION_ID保存起来。

设置Action2：
通过执行FND_FNDCPMPE，将可执行的界面

可执行Form的Personalization：

5. 打开Personalization的界面
选择“Application Developer”职责，打开“Concurrent->Executable”。然后从Help菜单打开Personalize。

6. 初始化查询变量

设置Condition：

设置Action：
初始化全局变量XX_EXECUTION_ID。这里比较关键的一点是“Property Name”一定要选择“INITIAL VALUE”而不是“VALUE”。选择“INITIAL VALUE”表示，如果原来全局变量XX_EXECUTION_ID已经初始化，那么这里不会对其再次赋值。这种设置适应于从并发程序Form打开可执行Form的情况，因为在并发程序Form中已经对XX_EXECUTION_ID初始化，在可执行的Form中就需要保持这个变量原来值，而不是将其覆盖。如果设置“VALUE”，那么这里的赋值语句将会执行。设置为“VALUE”适应于直接打开可执行Form的情况。