Use the indexed misunderstanding: use <> and! = Operator, resulting in an index without using the index
First, remember this conclusion:
After using <> and! =, You will not use the index.
For example, the following example uses <>, so the query is not used to 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
14 rows selected
Put the above check conditions "Empno <>
10
"
Convert into "Empno <10 and Empno"
10
"
Once, 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
14 rows selected
SQL>
Look at the example below:
Due to the preamble, the index is used, and "! =" Is used in the result of the index range scanning to filter the appropriate record
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
Be a trial:
SQL> DESC DEPT
Name Type Nullable Default Comments
------ --------------------------------
Deptno Number (2) Y
DNAME VARCHAR2 (14) Y
Loc varchar2 (13) y
Create a single key index:
SQL> CREATE INDEX DEPT_ID1 On DEPT (DNAME);
Index created
If "<>" is used, the query does not use the 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
14 rows selected
Modify the conditions to "DNAME <'Developer' and DName> 'Developer'", you can use an 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
15 rows selected
SQL>
