oracle不走索引-猿码集

1. 引言

在关系型数据库中，对于大表、复杂查询，优化查询速度常常需要使用索引。索引是一种排序的数据结构，可以提高查询效率。而在Oracle数据库中，即便建立了索引，查询仍有可能不走索引。本文将探讨Oracle中不走索引的情况，以及如何避免这种情况。

2. 何时会不走索引

2.1 非等值条件下的查询

当查询条件中包含不等值比较符（如>、<、>=、<=、<>, !=等），Oracle会基于cost选择最优执行计划，有可能不走索引。例如，以下查询语句：

SELECT * FROM employees WHERE salary > 5000;

即便在salary字段上建立了索引，Oracle有可能会选择全表扫描来处理这个查询。

2.2 NULL值查询

当查询条件中包含NULL值条件时，索引并不会被使用。例如，以下查询语句：

SELECT * FROM employees WHERE commission_pct IS NULL;

即便在commission_pct字段上建立了索引，Oracle也不会使用该索引。

2.3 多列索引的顺序问题

在Oracle数据库中，可以建立多列索引。对于多列索引，查询条件不符合索引第一列时，索引可能不会被使用。例如，以下查询语句：

SELECT * FROM employees WHERE department_id=10 AND salary > 5000;

如果在(department_id, salary)上建立了索引，并且department_id是索引的第二列，那么Oracle可能不会使用该索引。

3. 如何避免不走索引

3.1 正确使用索引

在建立索引时，需要根据业务需求正确选择索引的列和类型。

当查询包含不等值条件时，可以考虑建立位图索引。位图索引适用于相对静态的表，其中列的值有相对较少的修改，查询包含大量等于操作和少量不等于操作的场景。例如，以下查询语句：

SELECT * FROM employees WHERE salary > 5000;

可以建立位图索引：

CREATE BITMAP INDEX employees_salary_index ON employees(salary);

3.2 优化SQL语句

在编写SQL查询时，可以通过多种方式优化语句，从而避免不走索引的情况：

3.2.1 避免使用变量或函数包装列名

当SQL语句中的列名被包装在变量或函数中时，会导致Oracle不走索引。例如，以下查询语句：

DECLARE
  salary employees.salary%TYPE;
BEGIN
  SELECT AVG(salary) INTO salary FROM employees;
END;

这种SQL语句的执行计划中不会使用employees表上的任何索引。

相应地，可以将列名直接写在SQL语句中，避免使用变量或函数包装列名。

3.2.2 避免使用NOT关键字

当SQL语句中包含NOT关键字时，索引不一定会被使用。例如，以下查询语句：

SELECT * FROM employees WHERE salary < 5000 AND commission_pct IS NOT NULL;

可以通过改写为等价的SQL语句来避免NOT关键字：

SELECT * FROM employees WHERE salary < 5000 AND commission_pct > 0;

3.2.3 避免使用OR关键字

当SQL语句中包含OR关键字时，索引也不一定会被使用。例如，以下查询语句：

SELECT * FROM employees WHERE salary < 5000 OR commission_pct IS NOT NULL;

可以通过将OR改写为UNION ALL来避免该问题：

SELECT * FROM employees WHERE salary < 5000
UNION ALL
SELECT * FROM employees WHERE commission_pct IS NOT NULL;

3.2.4 避免对查询结果进行排序

当SQL语句中包含ORDER BY关键字时，索引也不一定会被使用。例如，以下查询语句：

SELECT * FROM employees WHERE department_id=10 ORDER BY salary DESC;

可以通过将排序操作移至应用程序层来避免该问题：

SELECT * FROM employees WHERE department_id=10;

在应用程序中对查询结果进行排序。

3.2.5 避免扫描超过20%的数据块

当SQL语句需要扫描超过20%的数据块时，索引也不一定会被使用。可以通过调整排序方式、添加WHERE子句等方式来减小扫描数据块的数量。

4. 总结

Oracle不走索引的情况有多种，包括非等值条件下的查询、NULL值查询、多列索引的顺序问题等。为避免这种情况，需要在建立索引时根据业务需求选择正确的列和类型，并优化SQL语句，避免使用变量或函数包装列名、NOT、OR或ORDER BY关键字，以及避免扫描超过20%的数据块。

oracle不走索引