使用索引的誤區(qū)之二：使用了 和 != 操作符，導(dǎo)致查詢不使用索引
2024-07-21 02:06:28
供稿：網(wǎng)友
使用索引的誤區(qū)之二：使用了 <> 和 != 操作符，導(dǎo)致查詢不使用索引
首先，請記住這個(gè)結(jié)論：
使用了<> 和!=后，就不會使用索引
例如，下面的例子使用了<>，所以查詢沒有用到索引
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”轉(zhuǎn)換成“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>
再看下面的例子：
由于使用了前導(dǎo)列，所以使用了索引，后面的"!="是從索引范圍掃描的結(jié)果中篩選合適的記錄的
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
再做一個(gè)試驗(yàn)：
sql> desc dept
name type nullable default comments
------ ------------ -------- ------- --------
deptno number(2) y
dname varchar2(14) y
loc varchar2(13) y
創(chuàng)建一個(gè)單鍵索引：
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>
