从代码角度优化oracle数据数据库访问_代码一键美化 oracle-优快云博客

本文探讨了导致Oracle优化器不使用索引扫描而进行全表扫描的原因，并提供了提高SQL查询性能的建议，包括限制返回行数、规避NULL字段、合理安排WHERE子句顺序等内容。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

从网上看了一下内容，收集并整理、摘抄了一些：

第一部分、导致Oracle优化器不使用索引扫描而直接全表扫描的情况

1、使用不等于操作符（<>、!=）

下面的查询即使在cust_rating列有一个索引，查询语句仍然执行一次全表扫描。

select cust_Id,cust_name from customers where cust_rating <> 'aa';
把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。

select cust_Id,cust_name from customers where cust_rating < 'aa' or cust_rating > 'aa';

特别注意：通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描。

2、使用IS NULL或IS NOT NULL

使用IS NULL或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引）。

3、使用函数

如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。下面的查询不会使用索引（只要它不是基于函数的索引）：

select empno,ename,deptno from emp where trunc(hiredate) = '01-MAY-81';

把上面的语句改成下面的语句，这样就可以通过索引进行查找。

select empno,ename,deptno from emp where hiredate < (to_date('01-MAY-81') + 0.9999);

4、比较不匹配的数据类型

注意下面查询的例子，account_number是一个VARCHAR2类型，在account_number字段上有索引。下面的语句将执行全表扫描。

select bank_name,address,city,state,zip from banks where account_number = 990354;

Oracle可以自动把where子句变成to_number(account_number) = 990354，这样就限制了索引的使用,改成下面的查询就可以使用索引：

select bank_name,address,city,state,zip from banks where account_number = '990354';

特别注意：不匹配的数据类型之间比较会让Oracle自动限制索引的使用，即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描”。

5、模糊匹配（LIKE或者MATCHES)

WHERE子句中，如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始，索引将不被采用，自动转换为顺序扫描。

所以，在数据库系统设计中，要尽量避免采用Like或者Matches关键字。有时候，我们可以利用其他运算符号来代替。如我们可以利用〉(大于)或者<(小于)符号来达到类似的需求。若真的要采用这两个关键字的话，则就需要做好查询优化方面的工作。如不要在基础表中直接利用这个两个关键字，而是通过报表视图、或者临时表等来查询，以减少其不良影响。

第二部分、性能调优对代码编写的建议

1、限制返回的行数

常规查询时不返回所有的查询结果，比如只返回前100条记录，并显示一个需要返回更多的选项，用户可以通过这个选项控制返回所有的查询结果。做到常规查询高效率，但又存在完整查询的可能性的目标。

2、规避NULL字段

建表时，设置所有的字段都为非空字段。

NULL值可能会引发下列问题：

无法使用索引扫描（除非是位图索引）；

NULL值参与执行的所有的四则运算结果都是NULL，容易引起程序不期望的结果。

3、期望使用共享SQL语句

当你执行一个SQL语句(有时被称为一个游标)时，如果它和之前的执行过的语句完全相同，ORACLE就能很快获得已经被解析的语句以及最好的执行路径。ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用。

Oracle优化使用共享语句包含很好条件，但有一个前提：两条SQL语句必须完全一致（包含空格、换行位置、大小写等）。所以在编写程序时，尽可能遵循相同的大小写约定（比如关键字全部小写，表名全部大写等）。

4、多表查询时注意WHERE子句中表的顺序

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表（基础表）将被最先处理。在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时，会运用排序及合并的方式连接它们。首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行派序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。

例如，有两张表：

表 TAB1 16384条记录

表 TAB2 1条记录

方法一：选择TAB2作为基础表（最好的方法）

select count(*) from tab1，tab2 执行时间0.96秒

方法二：选择TAB2作为基础表（不佳的方法）

select count(*) from tab2，tab1 执行时间26.09秒

如果有3个以上的表连接查询，那就需要选择交叉表(intersection table)作为基础表，交叉表是指那个被其他表所引用的表。

例如： EMP表描述了LOCATION表和CATEGORY表的交集。那么，

SELECT * FROM LOCATION L, CATEGORY C, EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN
将比下列SQL更有效率。
SELECT * FROM EMP E, LOCATION L, CATEGORY C WHERE E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN AND E.EMP_NO BETWEEN 1000 AND 2000

5、嵌套查询时注意WHERE子句的顺序

ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。例如：
方法一：（低效，执行时间156.3秒）

SELECT … FROM EMP E WHERE SAL > 50000 AND JOB = ‘MANAGER’ AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);

方法二：(高效,执行时间10.6秒)
SELECT … FROM EMP E WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = ‘MANAGER’;

6、SELECT子句中避免使用'*'

你不写出来所有的列名称，Oracle只有帮你转换了，但是这个转换是需要耗费时间的（想象一下你是如何知道一个你并熟悉的表中所有的字段名吧，Oracle一样需要去查询数据字典）。

7、减少访问数据库的次数

当执行每条SQL语句时，ORACLE在内部执行了许多工作：解析SQL语句，估算索引的利用率，绑定变量，读数据块等等。由此可见，减少访问数据库的次数，就能实际上减少ORACLE的工作量。

8、多表查询时使用表的别名

当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上。这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

9、定期的索引重构

ALTER INDEX REBUILD