SQL编写规范和优化

SQL编写规范
目录
1共享SQL语句 (3)
2减少访问数据库的次数 (3)
3使用DECODE函数 (3)
1减少ORACLE事务操作 (4)
2减少对表的查询，特别是重复的查询： (4)
3如何合理使用索引 (7)
3.1要使用索引 (7)
3.2分离表和索引 (7)
3.3尽量建好和使用好索引 (7)
3.4索引使用的注意事项 (9)
3.4.1索引列值必须匹配 (9)
3.4.2索引列不能加操作 (9)
3.4.3索引列不能以通配符作词首 (9)
3.4.4索引列不能使用空值 (9)
3.4.5使用不等于操作（包括<>、！=和not =）不会使用索引 (10)
3.4.6索引使用的顺序 (10)
3.4.7强制索引失效 (11)
3.4.8无法预知位置的索引列作关联查询，不会用到索引 (11)
3.4.9使用HINTS（提示） (12)
3.5删除无用的索引 (13)
4使用BETWEEN而不是>=和<= (14)
5使用COUNT会造成效率的降低。

(14)
6使用DISTINCT会造成效率的降低 (15)
7尽量不用EXIST，IN等耗时的关键字，最好使用多表关联查询。

(16)
8尽量少使用基于大表生成的视图 (17)
9减少网络访问次数 (17)
10SQL中NOWAIT 的使用 (17)
附件1：如何在VB程序中构造使用变量的查询语句（作者：李生龙） (18)
附件2：建议：通过内部函数提高SQL效率 (18)
附件3：HINT的使用 (19)
1共享SQL语句
ORACLE将已执行的SQL语句的分析放入共享池中，执行SQL语句前，ORACLE先将其与共享池中的SQL做比较，若完全匹配则直接取结果，不再进行分析，可大大提高效率。

但要注意：
•ORACLE只对简单的表提供高速缓冲
•ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包括空格,换行等)（当前设定的Cursor_Sharing的匹配模式为EXACT）
•必须满足的三个条件
当前被执行的语句和共享池中的语句必须完全相同
两个语句所指的对象必须完全相同
两个SQL语句中必须使用相同的名字的绑定变量
因此在书写SQL语句的时候请注意：
（1）对完成相同功能的SQL语句尽量使用COPY的方式，以使得SQL语句保持“一模一样”
（2）在SQL中尽量使用相同的变量的方式，保持变量名字相同。

（在SQL中使用变量的方式参见附件）
2减少访问数据库的次数
当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量。

如：
select id from tr_ddm where id=’12344’
select state from tr_ddm where id=’12344’
应合并成一条。

但要注意不是所有的情况都是放在一条语句中能够提高效率，有时一条太大的SQL 语句若没有组织好会严重影响效率。

在后面的章节中会对此进行分析。

3使用DECODE函数
DECODE函数可以避免重复扫描相同记录或重复连接相同的表。

例如：
select a.a_resource_type A端类型,a.a_device_id A端ID,a.row_num A端行号,a.col_num A 端列号, a.a_device_start_serial A端起始端子号,a.a_device_end_serial A端终止端子号,
b.a_resource_type B端类型,B.a_device_id B端ID,B.row_num B端行号,B.col_num B端列
号,B.a_device_start_serial B端起始端子号, B.a_device_end_serial B端终止端子号,
a.cable_id 缆,a.cable_first_num A端缆起止序号,a.cable_last_num A端缆终止序号,
B.cable_first_num B端缆起止序号,B.cable_last_num B端缆终止序号,
a.a_device_start_serial+decode(sign(B.cable_first_num-a.cable_first_num),-1,0,B.cable_first
_num-a.cable_first_num) A端起始,
a.a_device_end_serial-decode(sign(a.cable_last_num-B.cable_last_num),1,a.cable_last_num-
B.cable_last_num,0) A端终止,
B.a_device_start_serial B端起始,
B.a_device_end_serial-decode(sign(B.cable_last_num-a.cable_last_num),1,B.cable_last_nu
m-a.cable_last_num,0) B端终止,
decode(sign(a.cable_first_num-B.cable_first_num),1,a.cable_first_num,B.cable_first_num) 缆起始,
decode(sign(a.cable_last_num-B.cable_last_num),1,B.cable_last_num,a.cable_last_num) 缆终止
from CABLE_SERIAL_CONNECT a,CABLE_SERIAL_CONNECT b
where a.cable_id=B.CABLE_ID(+)
and a.cable_first_num<=B.cable_last_num(+) and a.cable_last_num>=B.cable_first_num(+) and a.id<>b.id(+)
and (a.A_Device_ID='3577684' AND a.A_Resource_Type=14 AND a.AB_LABEL='A') order by A端行号,A端列号,A端起始端子号,A端起始
该SQL语句是查找电缆连接到的交接设备的起止端子号，利用decode函数巧妙解决了线序正序排列和反序排列两种情况，减少了访问表的次数。

4减少ORACLE事务操作
•用TRUNCATE替代DELETE
•TRUNCATE不能做回滚，所以不需要存放任何可被恢复的信息。

•多记录时非常有效
注意：如果使用时需要保存事务信息，不能使用Truncate替代Delete
5减少对表的查询，特别是重复的查询：
很多SQL语句中大量重复使用了固定的查询，即查询语句本身比较复杂，有可能耗时较长。

造成每查一次需重复执行一遍。

例如：
update an_onu_port set
sequence_v5=sequence_id+decode(
(select max(a.sequence_v5) from an_onu_port a,an_v5id b,a n_ne_device c,an_onu_subrack d where a.v5id=b.v5id and
a.v5id is not null and a.subrack_id = d.id and ((d.sh
elf_id = c.rack_id and c.subrack_id is null) or (d.id
= c.subrack_id)) and b.onu_id=c.ne_id and b.v5id='6' a
nd b.olt_id='376193798'),
null,-1,
(select max(a.sequence_v5) from an_onu_port ,an_v5id b,an_ne_device c,an_onu_subrack dwhere a.v5id=b.v5
id and a.v5id is not null and a.subrack_id = d.id a
nd ((d.shelf_id = c.rack_id and c.subrack_id is nul
l) or (d.id = c.subrack_id)) and b.onu_id=c.ne_id a
nd b.v5id='6' and b.olt_id='376193798')
),
v5id='6'
where card_id='379370706';
又比如：
select d.accessnbr
from des_service d,des_serviceorder_service e,des_serviceorder where d.serviceid=e.serviceid and e.serviceorderid=f.serviceord erid
and ((select unique i.jx jx
from sw_number_res_bas i
where start_number<=
(select unique h.accessnbr
from des_service h
where e.isoldservice='0' and h.serviceid=d.servicei d and d.serviceidseq=
(select max(g.serviceidseq)
from des_service g
where g.serviceid=d.serviceid and rownum=1)
and rownum=1)
and end_number>=
(select unique h.accessnbr
from des_service h
where e.isoldservice='0' and h.serviceid=d.serviceid and d.serviceidseq=
(select max(g.serviceidseq)
from des_service g
where g.serviceid=d.serviceid and rownum=1)
and rownum=1)
and length(start_number)=8 and rownum=1 )
<>
(select unique i.jx jx
from sw_number_res_bas i
where start_number<=
(select unique h.accessnbr
from des_service h
where e.isoldservice='1' and h.serviceid=d.servicei
d and d.serviceidseq=
(select max(g.serviceidseq)-1
from des_service g
where g.serviceid=d.serviceid and rownum=1)
and rownum=1)
and end_number>=
(select unique h.accessnbr
from des_service h
where e.isoldservice='1' and h.serviceid=d.servicei
d and d.serviceidseq=
(select max(g.serviceidseq)-1
from des_service g
where g.serviceid=d.serviceid and rownum=1)
and rownum=1)
and length(start_number)=8 and rownum=1))
and e.isoldservice='0' and d.serviceidseq=
(select max(g.serviceidseq)
from des_service g
where g.serviceid=d.serviceid)
and d.servicetype in (111010,111020,112010,113010,11302 0,113060,113070,113025,122010)
and f.status in (10,11)
and f.sotypeid in ('402','414','504','513','527','316', '317','229','230', '231','232','322','323','324','325','427','4 28','115')
在这个例子中，蓝体字部分重复执行了五次之多（MAX(…)-1实质是重复了MAX(…)查询），而SELECT MAX 本身就是耗时的操作！！
原句在EXPLAIN WINDOW中分析的结果消耗为14881，若将这些蓝体部分单查（消耗2），再查绿体部分（消耗767，但因为有rownum=1所以去掉UNIQUE后,消耗2），再将结果带入S QL，消耗大约为2000多！
6如何合理使用索引：
6.1 要使用索引
ORACLE访问表的方式（两种）
•a. 全表扫描
全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描.
•b. 通过ROWID访问表
采用基于ROWID的访问方式情况,提高访问表的效率。

ROWID包含了表中记录的物理位置信息.
ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高
索引是表的一个概念部分,用来提高检索数据的效率. 实际上,ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.
除了那些LONG或LONG RAW数据类型,可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,扫描小表时,使用索引同样能提高效率.
虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.
6.2 分离表和索引
总是将表和索引建立在不同的表空间内(TABLESPACES). 决不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里. 同时,确保数据表空间和索引表空间置于不同的硬盘上.
为了保证脚本的通用性，当前共享各个组提交的脚本没有增加表空间指定，在辽宁项目部这部分工作由数据库管理员完成。

6.3 尽量建好和使用好索引
●建索引也是有讲究的，在建索引时，也不是索引越多越好，当一个表的索引达到4个以
上时，对于表进行维护的时候性能可能会下降很多，因为OLTP系统每表超过5个索引即会降低性能，而且在一个sql 中， Oracle 从不能使用超过 5个索引；
●要建立选择性高的索引。

索引的选择性是指索引列里不同值的数目与表中记录数的比。

如果表有1000个记录，表索引列有950个不同值，那么这个索引的选择性就是950/1000或者0.95。

最好的可能性选择是1.0。

依据非空值列的唯一索引，通常其选择性为1.0。

有文章表明，如果检索数据量超过30%的表中记录数.使用索引将没有显著的效率提高。

如果使用基于开销的最优化，优化器不应该使用选择性不好的索引。

如果索引列无选择性，基于开销的优化器可能决定使用全表扫描。

当我们用到GROUP BY和ORDER BY时,ORACLE就会自动对数据进行排序,而ORACLE在INIT.ORA中决定了sort_area_size区的大小,当排序不能在我们给定的排序区完成时,ORACLE就会在磁盘中进行排序,也就是我们讲的临时表空间中排序, 过多的磁盘排序将会令 free buffer waits 的值变高,而这个区间并不只是用于排序的,由此会导致性能下降；
当与Oracle建立起一个session时，在内存中就会为该session分配一个私有的排序区域。

如果该连接是一个专用的连接(dedicated connection)，那么就会根据init.ora中sort_area_size参数的大小在内存中分配一个Program Global Area (PGA) 。

如果连接是通过多线程服务器建立的，那么排序的空间就在large_pool中分配。

不幸的是，对于所有的session，用做排序的内存量都必须是一样的，我们不能为需要更大排序的操作分配额外的排序区域。

因此，设计者必须作出一个平衡，在分配足够的排序区域以避免发生大的排序任务时出现磁盘排序（disk sorts）的同时，对于那些并不需要进行很大排序的任务，就会出现一些浪费。

当然，当排序的空间需求超出了sort_area_size的大小时，这时将会在TEMP 表空间中分页进行磁盘排序。

磁盘排序要比内存排序大概慢14,000倍。

在开发中应该注意:
（1）、select,update,delete 语句中的子查询应当有规律地查找少于20%的表行.如果一个语句查找的行数超过总行数的20%,它将不能通过使用索引获得性能上的提高.
（2）索引可能产生碎片,因为记录从表中删除时,相应也从表的索引中删除.表释放的空间可以再用,而索引释放的空间却不能再用.频繁进行删除操作的被索引的表,应当阶段性地重建索引,以避免在索引中造成空间碎片,影响性能.在许可的条件下,也可以阶段性地truncate 表,truncate命令删除表中所有记录,也删除索引碎片.
（3）、在使用索引时一定要按索引对应字段的顺序进行引用。

（4）、用（+）比用NOT IN更有效率。

（5）、不要对查询中用到的每个列都建立索引，只建立选择性高的索引。

例如：表CABLE_CONNECTOR_ROUTE对列USE_STATUS建有索引IDX_USE_STATUS，但其选择性约为几比几百万；列SPECLINENO建有索引IDX_SPECLINENO，选择性要高得多。

Select * From CABLE_CONNECTOR_ROUTE Where
Use_STATUS='2'And SPECLINENO Like'30946%'
分析：使用索引IDX_USE_STATUS，成本很低（为2），但执行需耗费76秒多。

Select * From CABLE_CONNECTOR_ROUTE Where
Use_STATUS||''='2'And SPECLINENO Like'30946%'
分析：使用USE_STATUS||’’=’2’，强制IDX_USE_STATUS索引无效后，使用了索引
IDX_SPECLINENO，成为比上一句略增（为10），但实际执行时仅耗费0.5秒不到！
6.4 索引使用的注意事项
6.4.1索引列值必须匹配
SQL语句中所使用的查询索引列值的类型必须与列的类型匹配。

例如：
select a.CODE , a.SEQUENCE , a.COOR , a."ROWID" from sw_slot a
where a.PANEL_ID=406657623 order by to_number(substr((substr(a.
coor,1,(instr(a.coor,';')-1))),1,(instr((substr(a.coor,1,(instr(
a.coor,';')-1))),',')-1))), to_number(substr((substr(a.coor,1,(
instr(a.coor,';')-1))),(instr((substr(a.coor,1,(instr(a.coor,';')-1))),',' )+1)));
因为PANEL_ID是字符型，所以该SQL语句不能使用PANEL_ID索引。

6.4.2索引列不能加操作
要使用某列索引，不能对该列作任何操作，包括函数、运算符等。

例：
SELECT SERVICEORDERID,REQUESTID,MESSAGETIME,A1
FROM DES_INTERFACEMESSAGE
WHERE INTERFACETYPE='C1306' AND TO_CHAR(MESSAGETIME,'YYYYMMDD') ='20050403' ORDER BY MESSAGETIME
中MESSAGETIME列虽做了索引却不会用到，应改为：
…AND MESSAGETIME=TO_DATE('20050403','YYYYMMDD') …
6.4.3索引列不能以通配符作词首
对索引主列作条件查询，但条件使用Like操作且值以‘％’开始时，无法使用索引。

例如：TR_DDM表对列DDF_ID建有索引，但
SELECT * FROM TR_DDM WHERE DDF_ID LIKE ‘%01%’
由于使用了LIKE ‘%01%’不会用到索引，而是做全表扫描。

6.4.4索引列不能使用空值
在索引列上使用空值，将无法使用索引，因为索引中不含空值。

最常用的如IS NOT NULL。

例如：
select * from tr_ddm_term t Where LOGIC_ID IS NOT NULL
全表扫描
select * from tr_ddm_term t Where LOGIC_ID>'0'
使用IDX_LOGIC_ID
6.4.5使用不等于操作（包括<>、！=和not =）不会使用索引
6.4.6索引使用的顺序
✓当SQL语句的执行路径可以使用分布在多个表上的多个索引时, ORACLE会同时使用多个索引并在运行时对它们的记录进行合并, 检索出仅对全部索引有效的记录.
✓在ORACLE选择执行路径时,唯一性索引的等级高于非唯一性索引. 然而这个规则只有当WHERE子句中索引列和常量比较才有效.如果索引列和其他表的索引类相比较.
这种子句在优化器中的等级是非常低的.
✓如果不同表中两个相同等级的索引将被引用, FROM子句中表的顺序将决定哪个会被率先使用.
✓FROM子句中最后的表的索引将有最高的优先级.
✓如果相同表中两个相同等级的索引将被引用, WHERE子句中最先被引用的索引将有最高的优先级.
注意：索引使用顺序可能与优化器选择策略有关，当前Oracle9i缺省参数是choose，在做过表分析以后的使用顺序可能会发生变化，使用时请在测试环境进行测试。

例子:
TR_DDM_TERM表对ID建有主键PK_ID，对DDM_ID建有非唯一性索引IDX_DDM_ID，对LOGIC_ID 建有非唯一性索引IDX_LOGIC_ID。

下述SQL语句对索引的使用情况：
SELECT * FROM TR_DDM_TERM T WHERE DDM_ID ='42641216' AND LOGIC_ID='42641217' 先使用IDX_DDM_ID，后使用IDX_LOGIC_ID；
SELECT * FROM TR_DDM_TERM T WHERE DDM_ID <>'42641216' AND LOGIC_ID='42641217' 只使用IDX_LOGIC_ID；
SELECT * FROM TR_DDM_TERM T WHERE DDM_ID<='42641216' AND LOGIC_ID>='42641218' 只使用IDX_DDM_ID；
SELECT * FROM TR_DDM_TERM T WHERE DDM_ID >'42641216' AND LOGIC_ID>'42641217' 只使用IDX_DDM_ID；
SELECT * FROM TR_DDM_TERM T WHERE DDM_ID <>'42641216' AND LOGIC_ID>'42641217' 只使用IDX_LOGIC_ID；
SELECT * FROM TR_DDM_TERM T WHERE DDM_ID <>'42641216' AND LOGIC_ID<>'42641217' 全表扫描；
select * from tr_ddm_term t Where Id='12345' And logic_id>='2' And ddm_id <>'42641216'
使用PK_ID
select * from tr_ddm_term t Where Id>='12345' And logic_id>='2' And ddm_id <>'42641216'
只使用IDX_LOGIC_ID
6.4.7强制索引失效
在有些情况下，选择条件有多个，ORACLE分析后认定要用的索引并不是我们所希望使用的，这时可以强制索引失效。

如上一语句希望强制使用PK_ID，可以在索引列上加操作：
select * from tr_ddm_term t Where Id>='12345' And logic_id||’’>='2' And ddm_id <>'42641216'
只使用PK_ID（通过在索引列logic_id上加操作，迫使该列不能使用索引）
6.4.8无法预知位置的索引列作关联查询，不会用到索引
两表关联查询时，若索引列无法确定位置，用于关联条件时不会使用索引。

例如：SELECT distinct E_STATUS,DECODE( E_STATUS,'1','空闲','2','实占','3','预占','4','预拆','5','实拆','6','预留') 端子使用状态,CONNECTOR.ID AS ID
FROM CABLE_CONNECTOR CONNECTOR,CABLE_BOX_BLOCK CBLOCK ,UPJUNCTION_BOX UPJ Where Connector.Module_ID = CBLOCK.ID
And CBLOCK.ID = upj.UPDEVICEMODULEID
AND CONNECTOR.SERIAL_NUM>=UPJ.UPDEVICESTARTSERIAL
AND CONNECTOR.SERIAL_NUM<=UPJ.UPDEVICEENDSERIAL
AND CBLOCK.MAIN_FLAG='1'
AND CONNECTOR.CABLE_DEVICE_ID='297804451'
在该例子中，虽然CABLE_BOX_BLOCK表有主键ID，UPJUNCTION_BOX表对UPDEVICEMODULEID列作了索引，但CBLOCK.ID = upj.UPDEVICEMODULEID由于两表均不能定位，所以不会用到索引，而是对两个表做了全表扫描，分析结果如下所示：
如果将其改成：
SELECT distinct E_STATUS,DECODE( E_STATUS,'1','空闲','2','实占','3','
预占','4','预拆','5','实拆','6','预留') 端子使用状态,CONNECTOR.ID AS ID
FROM CABLE_CONNECTOR CONNECTOR,CABLE_BOX_BLOCK CBLOCK ,UPJUNCTION_BOX UPJ
Where Connector.Module_ID = CBLOCK.ID
And CBLOCK.ID = upj.UPDEVICEMODULEID
AND CONNECTOR.SERIAL_NUM>=UPJ.UPDEVICESTARTSERIAL
AND CONNECTOR.SERIAL_NUM<=UPJ.UPDEVICEENDSERIAL
AND CBLOCK.MAIN_FLAG='1'
AND CONNECTOR.CABLE_DEVICE_ID='297804451'
And CBLOCk.Cable_Box_Id='297804451'
CBLOCk.Cable_Box_Id='297804451'为CABLE_BOX_BLOCK定了位，所以对两表均使用了索引，效率大大提高。

一句看似废话的语句却能够极大地提高效率。

6.4.9使用HINTS（提示）
我们可以使用HINT来人工指定ORACLE进行优化的原则，常用的有RULE、INDEX、FIRST_ROWS等。

下面列出了一些HINT：
使用hint , 表示我们对ORACLE优化器缺省的执行路径不满意,需要手工修改. 这是一个很有技巧性的工作.
✓/*+ALL_ROWS*/
表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.例如:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';
✓/*+FIRST_ROWS*/
表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.例如:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';
✓/*+CHOOSE*/
表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;例如:
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';
/*+RULE*/
表明对语句块选择基于规则的优化方法.例如:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';
更多Hint的使用参见附件3
6.5 删除无用的索引
DML 性能低下，其中最严重的原因之一是无用索引的存在。

所有SQL的插入，更新和删除操作在它们需要在每一行数据被改变时修改大量索引的时候会变得更慢。

许多Oracle 管理人员只要看见在一个SQL 查询的WHERE语句出现了一列的话就会为它分配索引。

虽然这个方法能够让SQL运行得更快速，但是基于功能的Oracle 索引使得数据库管理人员有可能在数据表的行上过度分配索引。

过度分配索引会严重影响关键Oracle 数据表的性能。

在Oracle9i出现以前，没有办法确定SQL查询没有使用的索引。

让我们看看Oracle9i 提供了什么样的方法让你找到这些索引并删除它们。

过程是相当简单的。

Oracle9i有一个工具能够让你使用ALTER INDEX命令监视索引的使用。

然后你可以查找这些没有使用的索引并从数据库里删除它们。

下面是一段脚本，它能够打开一个系统中所有索引的监视功能：
set pages 999;
set heading off;
spoolrun_monitor.sql
select
'alter index '||owner||'.'||index_name||' monitoring usage;'
from
dba_indexes
where
owner not in ('SYS','SYSTEM','PERFSTAT')
;
spool off;
@run_monitor
需要等待一段时间直到在数据库上运行了足够多的SQL语句以后，然后你就可以查询新的V$OBJECT_USAGE视图。

select
index_name,
table_name,
mon,
used
from
v$object_usage;
可以看见V$OBJECT_USAGE有一列被称作USED，它的值是YES或者NO。

可以找出没有使用的索引。

INDEX_NAME TABLE_NAME MON USED
--------------- --------------- --- ----
CUSTOMER_LAST_NAME_IDX CUSTOMER YES NO
对于没有使用过的索引确认后就可以删除。

7使用Between而不是>=和<=
若条件中含有类似于：
TableA.Column1>=XXX and TableA.Column1<=YYY（其中，X XX<YYY）
其中，XXX和YYY可能是常量，也可能是某表中的列
应将其写为TableA.Column1 BETWEEN XXX and YYY，执行效率更高一些。

同理，若条件中含有：
TableA.Column1<=XXX and TableA.Column1>=YYY（其中，XXX<YYY）
可以写成TableA.Column1 NOT BETWEEN XXX and YYY
8使用COUNT会造成效率的降低。

若已知从一个数据量巨大的表中选取的记录数很小（数量级为2位数），可以使用程序（存储过程）循环记数效率要高一些。

尤其是为了判断是否存在记录时，更不要使用count(*)，直接选取（如select a.id，即选择一个字段）后判断数据集指针即可（NOT BOF AND NOT EOF）
另：
Count(*)、Count(字段名)、count(distinct 字段名)的区别
Select count(*) from testtab
得到表testtab的记录数
select count(id) from testtab
得到表testtab id字段非空记录数
select count(distinct id) from testtab
得到表testtab id字段值非相同记录数
9使用DISTINCT会造成效率的降低
DISTINCT会启动SQL引擎执行耗费资源的排序(SORT)功能，根据经验，它不是对结果集的排序。

尤其是从子查询与多表关联后查询记录时，会严重降低效率。

建议在已知数据量不会很大（2位数及以内）且只选取一个字段的复杂查询直接出记录集，然后使用存储过程或VB程序循环得到所有唯一值。

例如：
SELECT E_STATUS, E_STATUS 端子使用状态,CONNECTOR.ID AS Id FROM CABLE_BOX_BLOCK CBLOCK ,CABLE_CONNECTOR CONNECTOR
Where CONNECTOR.CABLE_DEVICE_ID='297804451'
AND Connector.Module_ID = CBLOCK.Id
SELECT Distinct E_STATUS,E_STATUS 端子使用状态,CONNECTOR.ID AS Id FROM CABLE_BOX_BLOCK CBLOCK ,CABLE_CONNECTOR CONNECTOR
Where CONNECTOR.CABLE_DEVICE_ID='297804451'
AND Connector.Module_ID = CBLOCK.Id
SELECT Distinct E_STATUS,E_STATUS 端子使用状态,CONNECTOR.ID AS Id FROM CABLE_BOX_BLOCK CBLOCK ,CABLE_CONNECTOR CONNECTOR
Where CONNECTOR.CABLE_DEVICE_ID='297804451'
AND Connector.Module_ID = CBLOCK.Id
And CBLOCK.Main_Flag='1'
SELECT E_STATUS,E_STATUS 端子使用状态,CONNECTOR.ID AS Id,CBLOCK.MAIN_FLAG
FROM CABLE_BOX_BLOCK CBLOCK ,CABLE_CONNECTOR CONNECTOR
Where CONNECTOR.CABLE_DEVICE_ID='297804451'
AND Connector.Module_ID = CBLOCK.Id
SELECT Distinct E_STATUS,E_STATUS 端子使用状态,CONNECTOR.ID AS Id,CBLOCK.MAIN_FLAG
FROM CABLE_BOX_BLOCK CBLOCK ,CABLE_CONNECTOR CONNECTOR
Where CONNECTOR.CABLE_DEVICE_ID='297804451'
AND Connector.Module_ID = CBLOCK.Id
10尽量不用EXIST，IN等耗时的关键字，最好使用多表关联查询。

例如：
select A.ID , A."ROWID" from sw_slot A WHERE EXISTS (SELECT 1 FROM sw_subrack_pan WHERE subrack_id=’162665239’ AND ID=A.PANEL_ID)
不会用到sw_slot表的索引，而对SW_SLOT表做了全表扫描，耗时较长。

如果修改为：
select A.ID , A."ROWID" from sw_slot A,sw_subrack_pan B
Where b.Subrack_Id='162665239' AND b.ID=A.PANEL_ID
则效率大大提高。

在可能的情况下，应将EXISTS尽量改为多表关联查询。

需要时，应尽量使用EXISTS而不是IN
在子查询数据量大时，EXISTS比IN的效率高。

因为IN是用外层查询结果逐一匹配内层查询（子查询）结果，而EXISTS是直接使用内层查询结果与外层结果匹配，所以当内层查询筛选出的数据较少，而外层查询结果量大时，两者的效率就会有比较明显的差别。

另外要特别注意，当存在NULL值时，IN实质上是“=”操作（得不到你想要的结果！），当你使用IN时，相当于你告诉SQL接受一个值，并将它与某个清单中使用＝的每一个值或一组值进行比较。

只要存在了任何NULL值，就不会返回任何行，纵使两个值都是NULL
也不行。

当逻辑被转变成使用NOT IN和NOT EXISTS时，问题就出现了，这两个语句会返回不同的行：
例如：第一个查询会返回0行；第二个返回意想的数据－它们是不同的查询）
selectename from emp where empno not in (select mgr from emp);
返回0行
selectename from emp e where not exists (select 0 from emp where mgr =e.empno);
返回所需的结果。

11尽量少使用基于大表生成的视图
视图实际上是动态执行的SQL语句，即视图只保存SQL查询语句，并没有数据，每次执行时动态查询。

所以基于大量数据生成的视图每次使用时均会执行一次查询，效率会很低。

例如：
基于端口、端子等生成了视图，用于关联查找端口（或端子）所在设备、机房、机楼等等，这种查询是对所有端口或端子做的，而端口（或端子）的数量级动辙上千万，所以数据量非常大。

实际上使用时往往会指定端口或端子的ID，即只对一个端子作查询，因此可将生成视图的查询语句做为VB程序中的常量，在查询时以常量加“AND ID=’XXX’”的方式引用，这样速度可以提高若干倍。

12减少网络访问次数
网络速度会影响访问效率，有时会成为瓶颈。

所以在程序设计时一定要注意不要在程序中多次频繁访问数据库，可以多利用存储过程直接在ORACLE中实现功能，外部调用一次即可。

例如：
在交接箱中增加端子，端子的数量级一般为几千。

这时若在VB程序中频繁使用RDO 对象（如RecordSet、Command等）作插入操作，就会数千次地连接数据库，每次连接均要通过网络发出请求、建立连接、发送数据包等等，严重影响效率。

可以在数据库中作一个存储过程或函数，在VB程序中直接调用，只需一次连接即可。

13SQL中nowait 的使用
DML语句发现发生互相等待情况时会严重影响效率。

这时可在原来的update或delete 语句中前增加。

Select * from…where.. for update nowait语句，其中条件句应与update或delete 句中的条件完全一致。

这样在select时若遇锁会直接返回异常，而不会发生死锁等待。

注意：在数据库连接数量有限（主要是使用中间件等情况下），为避免发生等待占用数据库连接的情况，建议使用nowait。

附件1：如何在VB程序中构造使用变量的查询语句（作者：李生龙）
任务，试验能否在VB中构造使用变量的查询语句，使得ORACLE可以缓存这些语句，当改变查询条件时仍然使用缓存中编译过的语句（将常量替换变量），用以提高系统效率。

在ORACLE中可以使用“&”字符使得查询时出现录入对话框，例如：
select * from tbl_configuration t where t.tablename=&dd;（我也是偶然发现的）我拿在VB中使用，行不通。

网上也没有找到相关的例子，只能时刻惦记着这个问题。

偶然总会在等待中出现。

偶然查到一个ADO的例子，得到如下构造查询语句的方法：
Dim Cmd As New mand
Dim i As Integer
For i = 0 To 1000
Set Cmd = New mand
Cmd.ActiveConnection = g_ADOConn
Cmd.Prepared = True
mandText = "select * from tbl_configuration where tablename=? AND ATTRIBUTENAME=?"
Cmd.Parameters.Append Cmd.CreateParameter("A", adVarChar, adParamInput, 16, CStr(i))
Cmd.Parameters.Append Cmd.CreateParameter("B", adVarChar, adParamInput, 16, CStr(i))
Cmd.Execute , , adCmdText
Next
上面的例子中，每次执行，条件都会改变，但是它们都使用了同一条编译过的语句，因此效率得以提高。

大家可以查询视图V$SQLTEXT看看效果。

select * from V$SQLTEXT where sql_text like'select * from tbl_configuration where %'
附件2：建议：通过内部函数提高SQL效率
经常在论坛中看到如 '能不能用一个SQL写出….' 的贴子, 殊不知复杂的SQL往往牺牲了执行效率. 能够掌握运用函数解决问题的方法在实际工作中是非常有意义的。

（有些文章特别提到尽量不要使用pl/SQL函数，因为其解析和执行过程会降低效率，所以此种做法有待商榷。

笔者做的试验，函数并未明显提高效率）
SELECT H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*)
FROM HISTORY_TYPE T,EMP E,EMP_HISTORY H
WHERE H.EMPNO = E.EMPNO
AND H.HIST_TYPE = T.HIST_TYPE
GROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;
通过调用下面的函数可以提高效率.
FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2 AS
TDESC VARCHAR2(30);
CURSOR C1 IS
SELECT TYPE_DESC
FROM HISTORY_TYPE
WHERE HIST_TYPE = TYP;
BEGIN
OPEN C1;
FETCH C1 INTO TDESC;
CLOSE C1;
RETURN (NVL(TDESC,'?'));
END;
FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2
AS
ENAME VARCHAR2(30);
CURSOR C1 IS
SELECT ENAME
FROM EMP
WHERE EMPNO=EMP;
BEGIN
OPEN C1;
FETCH C1 INTO ENAME;
CLOSE C1;
RETURN (NVL(ENAME,'?'));
END;
SELECT H.EMPNO,LOOKUP_EMP(H.EMPNO),
H.HIST_TYPE,LOOKUP_HIST_TYPE(H.HIST_TYPE),COUNT(*)
FROM EMP_HISTORY H
GROUP BY H.EMPNO , H.HIST_TYPE;
附件3：Hint的使用
/*+FULL(TABLE)*/
表明对表选择全局扫描的方法.例如:
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='CCBZZP';
✓/*+ROWID(TABLE)*/
提示明确表明对指定表根据ROWID进行访问.例如:
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'
AND EMP_NO='CCBZZP';
✓/*+CLUSTER(TABLE)*/
提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效.例如: SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
✓/*+INDEX(TABLE INDEX_NAME)*/
表明对表选择索引的扫描方法.例如:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';
✓/*+INDEX_ASC(TABLE INDEX_NAME)*/
表明对表选择索引升序的扫描方法.例如:
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='CCBZZP';
✓/*+INDEX_COMBINE*/
为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.例如:
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS
WHERE SAL<5000000 AND HIREDA TE
✓/*+INDEX_JOIN(TABLE INDEX_NAME)*/
提示明确命令优化器使用索引作为访问路径.例如:
SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATE FROM BSEMPMS WHERE SAL<60000;
✓/*+INDEX_DESC(TABLE INDEX_NAME)*/
表明对表选择索引降序的扫描方法.例如:
SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='CCBZZP';
✓/*+INDEX_FFS(TABLE INDEX_NAME)*/
对指定的表执行快速全索引扫描,而不是全表扫描的办法.例如:
SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';
✓/*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/
提示明确进行执行规划的选择,将几个单列索引的扫描合起来.例如: SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO='CCBZZP' AND DPT_NO='TDC306';
✓/*+USE_CONCAT*/
对查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询.例如: SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';
✓/*+NO_EXPAND*/
对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展.例如:
SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';
✓/*+NOWRITE*/
禁止对查询块的查询重写操作.
✓/*+REWRITE*/
可以将视图作为参数.
✓/*+MERGE(TABLE)*/
能够对视图的各个查询进行相应的合并.例如:
SELECT /*+MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO。