首页 > 代码库 > Oracle SQL优化一（常见方法）

Oracle SQL优化一（常见方法）

2024-07-17 12:31:43   220人阅读

1、表访问方式优化：

a)普通表优先“Index Lookup 索引扫描”，避免全表扫描

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

  • is null 和 is not null

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

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

  • 前导通配符

在使用like时，出现前导通配符，例如

 

oracle11代码
SELECT *
FROM t_sys_conf_midtoresult t
WHERE t.targettable LIKE ‘%mdsp_rpt%‘

 

此时，在targettable上的索引不会生效。

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

  • not

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

not的形式有如下几种：

not (fee = 0)

fee <> 0

fee != 0

 

如果该字段上必须要使用索引，请修改为下列表达方式：

fee > 0 and fee < 0

 

  • 尽量将表达式放在=号右边

在字段上使用表达式，会导致索引不生效。例如：

 

oracle11代码
SELECT *
FROM t_mdsp_rpt_product_revenue t
WHERE TO_CHAR(t.dtime, ‘yyyymmdd‘) = ‘20140701‘

 

一般建议，将表达式放到=号右边，左边字段名称上不要添加任何表达式。例如：

 

oracle11代码
SELECT *
FROM t_mdsp_rpt_product_revenue t
WHERE t.dtime = TO_DATE(‘20140701‘,‘yyyymmdd‘)

 

  • 避免数据类型的隐式转换

在SQL语句中，常见的隐式转换如下：

字符型和数值型比较，Oracle会将字符型隐式转换成数值型；

字符型和date比较，oracle会将字符型隐式转换成date；

 

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

 

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

b)分区表必须要通过分区键检索部分分区，不可全表扫描

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

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

  • 对于list分区

一般使用=判断，例如：

 

oracle11代码
WHERE dtime = ‘20140701‘

 

  • 对于range分区

一般使用范围判断，例如between ... and ...或者>、<、>=、<=等

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

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

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

2、表连接方式优化

a)NESTED LOOPS（嵌套循环NL）优化，在内部表关联字段上创建索引

最常见的表连接方式，连接原理如下：

从外部表（驱动表）中获取第一条数据，去内部表中查询匹配，若匹配到则输出。

取第二条数据匹配，直到外部表数据遍历一遍。

在CBO下，Oracle自动选择数据量较小的表（或者子查询结果集）作为内部表。

在内部表上创建索引，提高每次检索的效率。

b)Hash Join

在绝大多数场景下，Hash Join是效率最高的连接。

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

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

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

d)CARTESIAN JOIN（笛卡尔连接）

这个就不用多说了，必须要避免的。

3、运算方式优化

避免出现不必要的sort和sort group by，一般SQL中只在最终输出结果时进行排序，内部子查询不排序。


联系
我们