使用索引常见的几个问题

1、复合索引的使用
在T（x,y）上有一个索引，如果查询中不涉及列x（如where y=5），则不使用索引，但索引跳跃式扫描除外。
JEL@JEL >create table t as select decode(mod(rownum,2),0,'M','F') gender,all_objects.* from all_objects;

Table created.

JEL@JEL >create index i_t on t(gender,object_id);

Index created.

JEL@JEL >exec dbms_stats.gather_table_stats('JEL','T',cascade=>true);

PL/SQL procedure successfully completed.

JEL@JEL >set autotrace traceonly explain
JEL@JEL >select * from t where object_id=42;

Execution Plan
----------------------------------------------------------
Plan hash value: 3072826391

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

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

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

|   0 | SELECT STATEMENT            |      |     1 |    86 |     4   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |    86 |     4   (0)| 00:00:01 |

|*  2 |   INDEX SKIP SCAN           | I_T  |     1 |       |     3   (0)| 00:00:01 |

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


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

   2 - access("OBJECT_ID"=42)
       filter("OBJECT_ID"=42)

如上使用了索引，是因为gender列只有很少的几个不同值，而且优化器了解这一点。

JEL@JEL >update t set gender=chr(mod(rownum,256));

9381 rows updated.

JEL@JEL >select * from t where object_id=42;

Execution Plan
----------------------------------------------------------
Plan hash value: 3072826391

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

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

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

|   0 | SELECT STATEMENT            |      |     1 |    86 |     4   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |    86 |     4   (0)| 00:00:01 |

|*  2 |   INDEX SKIP SCAN           | I_T  |     1 |       |     3   (0)| 00:00:01 |

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


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

   2 - access("OBJECT_ID"=42)
       filter("OBJECT_ID"=42)

JEL@JEL >exec dbms_stats.gather_table_stats('JEL','T',cascade=>true);

PL/SQL procedure successfully completed.

JEL@JEL >select * from t where object_id=42;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

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

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

   1 - filter("OBJECT_ID"=42)

注意，在执行统计信息后，查询执行的全表扫描。
当第一列的取值比较多，查询中没有用到第一列，且优化器知道信息的情况下，执行查询会进行全表扫描


2、索引字段含有null值
JEL@JEL >create table test (x int ,y int);

Table created.

JEL@JEL >create index i_test on test (x);

Index created.

JEL@JEL >insert into test values (1,1);

1 row created.

JEL@JEL >insert into test values (null,1);

1 row created.

JEL@JEL >insert into test values (null,2);

1 row created.

JEL@JEL >insert into test values (null,1);

1 row created.

JEL@JEL >insert into test values (null,2);

1 row created.

JEL@JEL >commit;

Commit complete.

JEL@JEL >select * from test;

         X          Y
---------- ----------
         1          1
                    1
                    2
                    1
                    2

JEL@JEL >select count(*) from test;

  COUNT(*)
----------
         5
JEL@JEL >select * from test where x=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    26 |    17   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    26 |    17   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("X"=1)

Note
-----
   - dynamic sampling used for this statement

JEL@JEL >select /*+index(test i_test) */* from test where x=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3945961961

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

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

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

|   0 | SELECT STATEMENT            |        |     1 |    26 |   161   (0)| 00:00:02 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST   |     1 |    26 |   161   (0)| 00:00:02 |

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

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


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

   2 - access("X"=1)

Note
-----
   - dynamic sampling used for this statement

索引字段含有null值，查询不走索引。对于索引键完全为null的行不会建立相应的索引条目，所以索引中的行数并不是表中的行数。

3、因为有函数，所以没用索引
JEL@JEL >create table t (x char(1),y char(1));

Table created.

JEL@JEL >create index i_t on t(x);

Index created.

JEL@JEL >insert into t values ('a','a');

1 row created.
JEL@JEL >exec dbms_stats.gather_table_stats('JEL','T',cascade=>true);

PL/SQL procedure successfully completed.

JEL@JEL >select x,y from t where x=lower('A');

Execution Plan
----------------------------------------------------------
Plan hash value: 2928007915

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

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

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

|   0 | SELECT STATEMENT            |      |     1 |     4 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |     4 |     2   (0)| 00:00:01 |

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

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


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

   2 - access("X"='a')

JEL@JEL >select x,y from t where upper(x)='A';

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |    17   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     4 |    17   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(UPPER("X")='A')


4、隐式转换
继续上面的例子
JEL@JEL >insert into t values (1,1);

1 row created.
JEL@JEL >delete from t where x='a';

1 row deleted.

JEL@JEL >select * from t where x=1;

X Y
- -
1 1


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |    17   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     4 |    17   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         21  consistent gets
          0  physical reads
          0  redo size
        456  bytes sent via SQL*Net to client
        380  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
如上，没有使用索引，且进行了隐式转换（ 1 - filter(TO_NUMBER("X")=1)）
JEL@JEL >select * from t where x='1';

X Y
- -
1 1


Execution Plan
----------------------------------------------------------
Plan hash value: 2928007915

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

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

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

|   0 | SELECT STATEMENT            |      |     1 |     4 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |     4 |     2   (0)| 00:00:01 |

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

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


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

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


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        460  bytes sent via SQL*Net to client
        380  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

如上，使用了索引。
结论，SQL中尽量在等号（=）右边进行转换

5、使用索引反而更慢
JEL@JEL >create table t1 (x,y,primary key(x)) as select rownum x,object_name from all_objects;

Table created.
JEL@JEL >exec dbms_stats.gather_table_stats('JEL','T1',cascade=>true);

PL/SQL procedure successfully completed.

JEL@JEL >select x,y from t1 where x<50;

Execution Plan
----------------------------------------------------------
Plan hash value: 547136816

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

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

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

|   0 | SELECT STATEMENT            |             |    49 |  1029 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1          |    49 |  1029 |     3   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | SYS_C002878 |    49 |       |     2   (0)| 00:00:01 |

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


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

   2 - access("X"<50)

JEL@JEL >select x,y from t1 where x<5000;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  5000 |   102K|    13   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  5000 |   102K|    13   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("X"<5000)

通过索引获取的行数超过一定得阈值，则不使用索引而进行全表扫描

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