使用索引的误区之二：使用了 和 != 操作符，导致查询不使用索引
2024-07-21 02:06:28
供稿：网友
使用索引的误区之二：使用了 <> 和 != 操作符，导致查询不使用索引
首先，请记住这个结论：
使用了<> 和!=后，就不会使用索引
例如，下面的例子使用了<>，所以查询没有用到索引
select empno from emp where empno <>10;
plan_table_output
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| id | operation | name | rows | bytes | cost |
--------------------------------------------------------------------
| 0 | select statement | | | | |
|* 1 | table access full | emp | | | |
--------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - filter("emp"."empno"<>10)
note: rule based optimization
14 rows selected
将上面的查条件“empno <>10”转换成“empno <10 and empno>10”后，就可以使用索引了
select empno from emp where empno <10 and empno>10;
plan_table_output
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| id | operation | name | rows | bytes | cost |
--------------------------------------------------------------------
| 0 | select statement | | | | |
|* 1 | index range scan | emp_id1 | | | |
--------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - access("emp"."empno">10 and "emp"."empno"<10)
note: rule based optimization
14 rows selected
sql>
再看下面的例子：
由于使用了前导列，所以使用了索引，后面的"!="是从索引范围扫描的结果中筛选合适的记录的
select empno from emp where empno <=10 and ename != 'rich';
plan_table_output
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| id | operation | name | rows | bytes | cost |
--------------------------------------------------------------------
| 0 | select statement | | | | |
|* 1 | index range scan | emp_id1 | | | |
--------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - access("emp"."empno"<=10)
filter("emp"."empno"<=10 and "emp"."ename"<>'rich')
note: rule based optimization
15 rows selected
再做一个试验：
sql> desc dept
name type nullable default comments
------ ------------ -------- ------- --------
deptno number(2) y
dname varchar2(14) y
loc varchar2(13) y
创建一个单键索引：
sql> create index dept_id1 on dept(dname);
index created
如果使用"<>"，则查询不使用索引：
select deptno from dept where dname <> 'developer';
plan_table_output
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| id | operation | name | rows | bytes | cost |
--------------------------------------------------------------------
| 0 | select statement | | | | |
|* 1 | table access full | dept | | | |
--------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - filter("dept"."dname"<>'developer')
note: rule based optimization
14 rows selected
将条件修改为“dname <'developer' and dname>'developer'”，则可以使用索引
select deptno from dept where dname <'developer' and dname>'developer';
plan_table_output
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost |
---------------------------------------------------------------------------
| 0 | select statement | | | | |
| 1 | table access by index rowid| dept | | | |
|* 2 | index range scan | dept_id1 | | | |
---------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
2 - access("dept"."dname">'developer' and "dept"."dname"<'developer')
note: rule based optimization
15 rows selected
sql>
