Two misconceptions about using indexes: using the and!= operators, causing queries to not use indexes

Two myths about indexing using indexes: Using the <> and!= operators, causing queries to not use indexes
First of all, please remember this conclusion:

The index is not used after <> and!= are used



For example, the following example uses &LT;&GT, so the query does not use the index

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



Rows selected





After converting the above "empno <>10" to "Empno <10 and Empno>10", you can use the index

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



Rows selected



Sql>



Look at the following example:

Because the leading column is used, the index is used, followed by "!=" to filter the appropriate records from the result of the index range scan

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



Rows selected





One more experiment:

Sql> DESC Dept

Name Type Nullable Default Comments

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

DEPTNO Number (2) Y

Dname VARCHAR2 (Y)

LOC VARCHAR2 (Y)



To create a single key index:

Sql> CREATE INDEX DEPT_ID1 on dept (Dname);



Index created



If you use "<>", the query does not use an index:

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



Rows selected



Modify the condition to "Dname < ' DEVELOPER ' and dname> ' DEVELOPER '", you can use the index

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



Rows selected



Sql>