使用索引的误区之二：使用了和 != 操作符，导致查询不使用索引

最新推荐文章于 2023-09-06 11:00:00 发布

iteye_13254

最新推荐文章于 2023-09-06 11:00:00 发布

阅读量445

点赞数

使用索引的误区之二：使用了 <> 和 != 操作符，导致查询不使用索引

http://blog.csdn.net/lunar2000/archive/2004/11/24/193475.aspx

首先，请记住这个结论：<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

使用了<> 和!=后，就不会使用索引

例如，下面的例子使用了<>，所以查询没有用到索引

select empno from emp where empno <>10;

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

--------------------------------------------------------------------

--------------------------------------------------------------------

| 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 <><?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><chmetcnv w:st="on" unitname="”" sourcevalue="10" hasspace="False" negative="False" numbertype="1" tcsc="0">10”</chmetcnv>转换成“empno <10 and empno><chmetcnv w:st="on" unitname="”" sourcevalue="10" hasspace="False" negative="False" numbertype="1" tcsc="0">10”</chmetcnv>后，就可以使用索引了

select empno from emp where empno <10 and empno>10;

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

--------------------------------------------------------------------

--------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | |

--------------------------------------------------------------------

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

--------------------------------------------------------------------------------

--------------------------------------------------------------------

--------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | |

--------------------------------------------------------------------

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

--------------------------------------------------------------------------------

--------------------------------------------------------------------

--------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | |

--------------------------------------------------------------------

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

--------------------------------------------------------------------------------

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("DEPT"."DNAME">'DEVELOPER' AND "DEPT"."DNAME"<'DEVELOPER')

Note: rule based optimization

15 rows selected

SQL>

# piner发表于2005-01-20 17:15:00IP: 218.108.88.*

首先，请记住这个结论：

使用了<> 和!=后，就不会使用索引
?????

这个结论不正确，有误导倾向

# lunar发表于2005-03-01 16:38:00IP: 218.249.251.*

是有些问题， 或许应该改成，如果使用了<> 和 !=操作，如果不用提示的话，系统就不用索引，这样应该可以了吧，呵呵。

# ccq发表于2006-03-23 21:17:00IP: 172.17.35.*

select empno from emp where empno <>10;
等价于:
select empno from emp where empno <10 and empno>10;

不会吧?

是否可以调整为:
select empno from emp where empno <10
union all
select empno from emp where empno >10;

# lunar发表于2006-03-24 08:21:00IP: 218.206.69.*

对，粗心了，不好意思：）