ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 索引受限的情况

索引受限的情况

原创 Linux操作系统 作者:gvora 时间:2009-01-10 23:46:58 0 删除 编辑

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 NULLIS NOT NULL

  使用IS NULLIS 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 NULLDEFAULT,对于避免可能出现的性能问题很有帮助。

 

3、使用函数

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

 

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

  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行。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15203236/viewspace-536319/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 组合索引
请登录后发表评论 登录
全部评论

注册时间:2008-12-30

  • 博文量
    62
  • 访问量
    289393