ITPub博客

首页 > 数据库 > Oracle > 使用索引常见的几个问题

使用索引常见的几个问题

原创 Oracle 作者:jelephant 时间:2015-12-05 12:43:58 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-12-07

  • 博文量
    143
  • 访问量
    626777