Where条件导致查询不走索引的情况

创建测试用表:test,test2,test3

SQL> create table test as select object_id, owner, object_name, created from dba_objects;

Table created
SQL >create table test2 as select * from test where owner in ('SYS','OAK');

Table created.

SQL >create index test_ind1 on test(object_id);

Index created.
SQL >create index  test2_ind1 on test2(owner);

SQL >exec dbms_stats.gather_table_stats(user,'test');

PL/SQL procedure successfully completed.

SQL >exec dbms_stats.gather_index_stats(user,'test_ind1');

PL/SQL procedure successfully completed.

SQL >exec dbms_stats.gather_table_stats(user,'test2');

PL/SQL procedure successfully completed.

SQL >exec dbms_stats.gather_index_stats(user,'test2_ind1');

PL/SQL procedure successfully completed.
SQL >exec dbms_stats.gather_table_stats(user,'test3');

PL/SQL procedure successfully completed.

SQL >exec dbms_stats.gather_index_stats(user,'test3_ind1');

PL/SQL procedure successfully completed.

SQL >select count(*) from test;

  COUNT(*)
----------
     61684

SQL >select count(*) from test where object_id is null;

  COUNT(*)
----------
        40

SQL >select count(*),owner from test2 group by owner;

  COUNT(*) OWNER
---------- ------------------------------
     23225 SYS
         2 OAK

SQL >create table test3(object_id varchar2(20), owner varchar2(30));

Table created.

SQL >insert into test3 select object_id,owner from test;

61684 rows created.

SQL >create index test3_ind1 on test3(object_id);

Index created.

情况一:
Where条件中存在不等于操作(<>,!=)
SQL >select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID  9zfw20r27fb6j, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from test2 where wner='OAK'

Plan hash value: 2486100180

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST2      |     1 |    40 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST2_IND1 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='OAK')

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID  1zpaqum6fznd8, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from test2 where owner !='SYS'

Plan hash value: 300966803

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |    35 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST2 |     1 |    40 |    35   (3)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"<>'SYS')

情况二:
Where条件中存在is null 或 is not null

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  fyqmqv95dy7yx, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from test where object_id is null

Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    90 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST |    40 |  1640 |    90   (3)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID" IS NULL)

情况三:
使用函数,而没有基于函数的索引
SQL >select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID  2apc04k69zvww, child number 0
-------------------------------------
select /* gather_plan_statistics */ * from test2 where
lower(owner)='oak'

Plan hash value: 300966803

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |    35 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST2 |   232 |  9280 |    35   (3)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(LOWER("OWNER")='oak')

情况四:比较条件类型字段类型不匹配
SQL >select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID  fjj3yqs8dcq7z, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from test3 where object_id=60

Plan hash value: 3306317399

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |    36 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST3 |     1 |    11 |    36   (9)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("OBJECT_ID")=60)

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

转载于:http://blog.itpub.net/7419833/viewspace-675169/