ORACLESQL性能优化地53个重要准则补充
- 格式：doc
- 大小：233.50 KB
- 文档页数：8
实用
文档 数据库开发优化和版本比较工具的使用方法
1. 在什么情况下索引会失效或丢失
（1）用户显式地执行命令：alter index disable或 drop index ;
（2）迁移表到其他表空间：alter table move tablespace ，如果不重建索引，此时索引失效。
（3）数据导入，如：imp导入数据时，显式地添加参数INDEXES=N或在导入过程中条件不满足创建索引失败等。
2. MIS版本比较修正工具使用
版本比较工具主要用来比较客户的数据库和标准的数据库中的表、字段、约束、索引、视图和存储过程等对象进行比较并生成相应的sql语句，执行这些sql可以使客户的数据库和标准数据库达到数据结构一致。
利用安装包创建标准库
打开版本比较工具，如下图
实用
文档 （1）连接标准库和客户数据库，如下图
（2）点击比较表、比较主键、比较索引、比较存储过程、比较序列等按钮，可以比较相应的数据库对象，并生成比较日志和相应sql语句。
（3） 另存脚本 实用
文档 （4） 执行脚本
（5） 执行相应sql
注：本工具还提供了模块版本比较功能。
3. 临时表不能利用索引的研究
前言：Oracle临时表能避免并发，提高程序的可伸缩性等诸多优点，但在查询时，有时不能有效利用索引。
问题描述：有如下两张表，其中yd_yhjbda是普通表，有记录数10万条，TMP_JSHH_X为Oracle基于会话的
临时表，有一条记录：
表TMP_JSHH_X
CREATE GLOBAL TEMPORARY TABLE TMP_JSHH_X
(
HH NUMBER(8),
DFNY VARCHAR2(6),
SFPC NUMBER(2)
)
ON COMMIT PRESERVE ROWS;
CREATE TABLE YD_YHJBDA
(
HH NUMBER(8) NOT
NULL,
HM VARCHAR2(100),
YDDZ VARCHAR2(50),
SFZH VARCHAR2(18)
);
CREATE UNIQUE INDEX YD_YHJBDA_PK ON YD_YHJBDA
(HH);
(1)进行如下关联查询
select a.hh,a.hm,a.YDDZ,a.sfzh
from tmp_jshh_x b,yd_yhjbda a
where a.hh=b.hh
执行计划
Operation Object 实用
文档 Name Rows Bytes Cost Object
Node In/Out PStart
PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 7
M 4677
HASH JOIN 7 M 373
M 4677
TABLE ACCESS FULL TMP_JSHH_X 8 K 103
K 10
TABLE ACCESS FULL YD_YHJBDA 94 K 3
M 3624
发现没有利用索引
(2)改用如下查询（查询字段减少）：
select a.hh
from tmp_jshh_x b,yd_yhjbda a
where a.hh=b.hh
或：
select b.*
from tmp_jshh_x b,yd_yhjbda a
where a.hh=b.hh
执行计划：
Operation Object
Name Rows Bytes Cost Object
Node In/Out PStart
PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 7
M 10
实用
文档 NESTED LOOPS 7 M 131
M 10
TABLE ACCESS FULL TMP_JSHH_X 8 K 103
K 10
INDEX UNIQUE SCAN YD_YHJBDA_PK 94 K 459
K
发现成功利用了YD_YHJBDA_PK索引
(3)改写查询，利用hint
select /*+ ORDERED use_nl(a)*/a.hh,a.hm,a.yddz,a.sfzh
from tmp_jshh_x b,yd_yhjbda a
where a.hh=b.hh
执行计划
Operation Object
Name Rows Bytes Cost Object
Node In/Out PStart
PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 7
M 8178
NESTED LOOPS 7 M 373
M 8178
TABLE ACCESS FULL TMP_JSHH_X 8 K 103
K 10
TABLE ACCESS BY INDEX ROWID YD_YHJBDA 94
K 3
M 1
INDEX UNIQUE SCAN YD_YHJBDA_PK 94 实用
文档 K
发现成功利用索引
后来把TMP_JSHH_X改成普通表能够正确使用索引YD_YHJBDA_PK。
总结：在使用Oracle临时表时有时不能使用索引，可能是和临时表的特性有关系，不能收集统计数据，执行计划不是最优，此时需要注意使用hint。
4. 不同sql写法对性能的影响
关键字：sql优化 minus/not in/not exist/左连接
问题描述：
如表：
CREATE TABLE YD_YHJBDA
(
HH NUMBER(8)
NOT NULL,
HM VARCHAR2(100),
YDDZ VARCHAR2(50),
SFZH VARCHAR2(18)
);
CREATE UNIQUE INDEX YD_YHJBDA_PK ON YD_YHJBDA
(HH);
CREATE TABLE YD_KHXXDA
(
KHDM NUMBER(7)
DEFAULT 0 CONSTRAINT NL_YD_KHXXDA_1 NOT
NULL,
KHLB CHAR(2),
YWBH NUMBER(12)
DEFAULT (NULL),
HH NUMBER(13)
DEFAULT 0 CONSTRAINT NL_YD_KHXXDA NOT NULL,
XM VARCHAR2(28),
XB VARCHAR2(2),
TXMC VARCHAR2(10),
DWMC VARCHAR2(24), 实用
文档 ZZDH VARCHAR2(30),
BGDH VARCHAR2(13),
YDDH VARCHAR2(13),
XHHM VARCHAR2(13),
CZHM VARCHAR2(13),
YJDZ VARCHAR2(20),
YZBM VARCHAR2(6),
ZP LONG RAW,
GRZY VARCHAR2(30),
CS VARCHAR2(10),
DQ VARCHAR2(14),
BZ VARCHAR2(40)
);
CREATE UNIQUE INDEX PK_YD_KHXXDA ON YD_KHXXDA(HH);
求hh在yd_yhjbda中而不在YD_KHXXDA表中的hh
方法一：
select hh from yd_yhjbda where not in (select hh from
yd_khxxda)
方法二：
select hh from yd_yhjbda j where not exists (select 'X' from
yd_khxxda where hh=j.hh)
方法三：
select hh from yd_yhjbda
minus
select hh from yd_khxxda 实用
文档 方法四：
select a.hh
from yd_yhjbda a,yd_khxxda b
where a.hh=b.hh(+) and b.hh is null
