使用索引的誤區之二:使用了 和 != 操作符,導致查詢不使用索引

來源:互聯網
上載者:User
索引 使用索引的誤區之二:使用了 <> 和 != 操作符,導致查詢不使用索引
首先,請記住這個結論:

使用了<> 和!=後,就不會使用索引

 

例如,下面的例子使用了<>,所以查詢沒有用到索引

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>

 

 


相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。