oraclesql优化方式,OracleSQL优化一（常见方法）

1、表访问方式优化&＃xff1a;

a)普通表优先“Index Lookup 索引扫描”&＃xff0c;避免全表扫描

大多数场景下&＃xff0c;通过“Index Lookup 索引扫描”要比“Full Table Scan (FTS) 全表扫描”效率要高的多。在编写SQL时&＃xff0c;为了保证查询能够使用索引&＃xff0c;需要避免出现如下场景:

is null 和 is not null

在oracle中null是不能够作为索引的&＃xff0c;如果某列数据中有“null”&＃xff0c;不要在该列上创建索引&＃xff0c;即使创建&＃xff0c;也不会提高查询性能。

而在SQL语句中&＃xff0c;如果使用is null和is not null&＃xff0c;oracle的SQL优化器是不允许走索引的。

前导通配符

在使用like时&＃xff0c;出现前导通配符&＃xff0c;例如

oracle11代码

SELECT *

FROM

t_sys_conf_midtoresult t

WHERE

t.targettable LIKE

‘%mdsp_rpt%‘

此时&＃xff0c;在targettable上的索引不会生效。

通配符包括‘%‘和‘_‘。

not

not会导致查询索引不生效。

not的形式有如下几种&＃xff1a;

not (fee &＃61; 0)

fee <> 0

fee !&＃61; 0

如果该字段上必须要使用索引&＃xff0c;请修改为下列表达方式&＃xff1a;

fee > 0 and fee <0

尽量将表达式放在&＃61;号右边

在字段上使用表达式&＃xff0c;会导致索引不生效。例如&＃xff1a;

oracle11代码

SELECT

*

FROM

t_mdsp_rpt_product_revenue t

WHERE

TO_CHAR(t.dtime,

‘yyyymmdd‘) &＃61; ‘20140701‘

一般建议&＃xff0c;将表达式放到&＃61;号右边&＃xff0c;左边字段名称上不要添加任何表达式。例如&＃xff1a;

oracle11代码

SELECT

*

FROM

t_mdsp_rpt_product_revenue t

WHERE

t.dtime &＃61; TO_DATE(‘20140701‘,‘yyyymmdd‘)

避免数据类型的隐式转换

在SQL语句中&＃xff0c;常见的隐式转换如下&＃xff1a;

字符型和数值型比较&＃xff0c;Oracle会将字符型隐式转换成数值型&＃xff1b;

字符型和date比较&＃xff0c;oracle会将字符型隐式转换成date&＃xff1b;

隐式转换如果发生在字段上&＃xff0c;会造成索引失效。原理同“字段上加表达式”

注意&＃xff1a;若避免了上述情况&＃xff0c;仍然不走索引&＃xff0c;不可轻易的使用hint方式强制走索引&＃xff0c;需要根据实际情况具体分析。

b)分区表必须要通过分区键检索部分分区&＃xff0c;不可全表扫描

在解释计划中&＃xff0c;对于分区表的扫描&＃xff0c;不可为“partition range all”。

where条件中&＃xff0c;一定要加入对分区键列的条件过滤&＃xff0c;同样要避免字段上加表达式和隐式转换

对于list分区

一般使用&＃61;判断&＃xff0c;例如&＃xff1a;

oracle11代码

WHERE

dtime &＃61; ‘20140701‘

对于range分区

一般使用范围判断&＃xff0c;例如between ... and ...或者>、&＃61;、<&＃61;等

c)分区表上的索引一般使用本地索引

需要注意的原则与普通表上的索引一致。

在查询分区表时&＃xff0c;Oracle先通过分区键找到需要查询的分区&＃xff0c;在分区内部使用本地索引进行检索。

2、表连接方式优化

a)NESTED LOOPS(嵌套循环NL)优化&＃xff0c;在内部表关联字段上创建索引

最常见的表连接方式&＃xff0c;连接原理如下&＃xff1a;

从外部表(驱动表)中获取第一条数据&＃xff0c;去内部表中查询匹配&＃xff0c;若匹配到则输出。

取第二条数据匹配&＃xff0c;直到外部表数据遍历一遍。

在CBO下&＃xff0c;Oracle自动选择数据量较小的表(或者子查询结果集)作为内部表。

在内部表上创建索引&＃xff0c;提高每次检索的效率。

b)Hash Join

在绝大多数场景下&＃xff0c;Hash Join是效率最高的连接。

一般用于大表和小表的连接&＃xff0c;在小表数据能够全部放到内存中时&＃xff0c;效率最高。

c)排序合并连接(Sort Merge Join (SMJ))

它的出现必然伴随着排序操作&＃xff0c;效率较低&＃xff0c;在Oracle 10g版本以后基本不会出现&＃xff0c;若出现&＃xff0c;需要确认是否在子查询中是否使用了不必要的排序。

d)CARTESIAN JOIN(笛卡尔连接)

这个就不用多说了&＃xff0c;必须要避免的。

3、运算方式优化

避免出现不必要的sort和sort

group by&＃xff0c;一般SQL中只在最终输出结果时进行排序&＃xff0c;内部子查询不排序。

原文&＃xff1a;http://www.cnblogs.com/trhimily/p/3911781.html