1、使用不等于操作符（<>、!=）

　　索引只能用于查找表中已有的数据。每当在WHERE子句中使用不等于操作符时，都将无法使用所引用的列的索引。

示例：用不等于条件进行查询

Select cust_id,cust_name from customers where cust_rating<>’aa’;

把上面的语句改成如下使用OR条件进行查询，这样，在基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。

Select cust_id,cust_name from customers where cust_rating<’aa’ or cust_rating>’aa’;

注：为了优化性能，一定要确保被检索的结果集少于表容量的5%。

 

示例：对索引empt_id2(sal)的测试

SQL> explain plan for select * from empt where sal<>1100;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

 

Plan hash value: 1497070856

 

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |    13 |  1131 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMPT |    13 |  1131 |     3   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

PLAN_TABLE_OUTPUT

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

 

 

   1 - filter("SAL"<>1100)

 

Note

-----

   - dynamic sampling used for this statement

 

已选择17行。

 

SQL> explain plan for select * from empt where sal<1100 and sal>1100;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

 

Plan hash value: 1878647556

 

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

 

---------

 

| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| T

 

ime     |

 

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

 

---------

 

 

PLAN_TABLE_OUTPUT

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

 

|   0 | SELECT STATEMENT             |          |     1 |    87 |     0   (0)|

        |

 

|*  1 |  FILTER                      |          |       |       |            |

        |

 

|   2 |   TABLE ACCESS BY INDEX ROWID| EMPT     |     1 |    87 |     0   (0)| 0

 

0:00:01 |

 

|*  3 |    INDEX RANGE SCAN          | EMPT_ID2 |     1 |       |     0   (0)| 0

 

0:00:01 |

 

PLAN_TABLE_OUTPUT

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

 

 

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

 

---------

 

 

Predicate Information (identified by operation id):

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

 

   1 - filter(NULL IS NOT NULL)

   3 - access("SAL">1100 AND "SAL"<1100)

 

 

PLAN_TABLE_OUTPUT

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

 

Note

-----

   - dynamic sampling used for this statement

 

已选择20行。

 

2、使用IS NULL或IS NOT NULL

　　使用IS NULL或IS NOT NULL同样会限制索引的使用，因为NULL值并没有被定义。数据库中没有值等于NULL值，甚至NULL也不等于NULL。

 

SQL> alter table empt modify empno NUMBER(4,0) primary key;

 

表已更改。

 

SQL> alter table empt modify (sal not null);

 

表已更改。

 

技巧：NULL值通常会限制索引。在创建表时对某一列指定NOT NULL或DEFAULT，对于避免可能出现的性能问题很有帮助。

 

３、使用函数

　　如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。

 

４、比较不匹配的数据类型

　　Oracle并不会对那些不匹配的数据类型报错，它可以隐式地把VARCHAR2列的数据类型转换成要被比较的数值型数据类型。

 

示例：

SQL> create table bank

  2  (account_number varchar2(10),

  3  bank_name varchar2(10));

 

表已创建。

 

SQL> create index bank_id on bank(account_number);

 

索引已创建。

 

SQL> select * from bank;

 

ACCOUNT_NU BANK_NAME

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

1          aa

2          bb

3          cc

4          dd

5          ee

 

SQL> explain plan for select * from bank where account_number=1;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

 

Plan hash value: 1547002607

 

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |     1 |    14 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| BANK |     1 |    14 |     3   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

PLAN_TABLE_OUTPUT

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

 

 

   1 - filter(TO_NUMBER("ACCOUNT_NUMBER")=1)

 

Note

-----

   - dynamic sampling used for this statement

 

已选择17行。

Oracle可以自动把WHERE自己变成TO_NUMBER("ACCOUNT_NUMBER")=1，这样就限制了索引的使用。

SQL> explain plan for select * from bank where account_number='1';

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

 

Plan hash value: 1106549972

 

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

 

-------

 

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Tim

 

e     |

 

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

 

-------

 

 

PLAN_TABLE_OUTPUT

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

 

|   0 | SELECT STATEMENT            |         |     1 |    14 |     2   (0)| 00:

 

00:01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID| BANK    |     1 |    14 |     2   (0)| 00:

 

00:01 |

 

|*  2 |   INDEX RANGE SCAN          | BANK_ID |     1 |       |     1   (0)| 00:

 

00:01 |

 

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

 

-------

 

PLAN_TABLE_OUTPUT

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

 

 

 

Predicate Information (identified by operation id):

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

 

   2 - access("ACCOUNT_NUMBER"='1')

 

Note

-----

   - dynamic sampling used for this statement

 

已选择18行。