sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 开发DBA >

学习笔记:Oracle数据库索引 index hint指定index的深入理解案例

时间:2016-05-29 22:40   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净

Oracle查询select语句通过hint指定使用索引index的案例深入理解

1.模拟Oracle数据库环境

创建一个表,含有位图index和b-tree index

SQL> create table t_xifenfei as 
  2  select object_id,object_name from dba_objects;

Table created.

SQL> create index b_tree_t_xifenfei on t_xifenfei(object_id);

Index created.

SQL> CREATE BITMAP INDEX  bitmap_t_xifenfei on t_xifenfei(object_name);

Index created.

SQL> BEGIN
  2  dbms_stats.gather_table_stats(USER,'T_XIFENFEI',cascade => true);
  3  END;
  4  / 

PL/SQL procedure successfully completed.

2.无index hint时的SQL执行计划

SQL> SET AUTOT TRACE EXPL STAT 
SQL> SELECT OBJECT_ID FROM t_xifenfei;

845708 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |   841K|  4109K|   886   (3)| 00:00:11 |
|   1 |  TABLE ACCESS FULL| T_XIFENFEI |   841K|  4109K|   886   (3)| 00:00:11 |
--------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      60525  consistent gets
          0  physical reads
          0  redo size
   15543305  bytes sent via SQL*Net to client
     620649  bytes received via SQL*Net from client
      56382  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     845708  rows processed

这里因为object_id列可能有null值,所以不会使用b_tree_t_xifenfei索引,预料之中事件

3.index hint b_tree_t_xifenfei时的SQL执行计划

SQL> SET  LINESIZE 150
SQL> SELECT /*+ INDEX(T b_tree_t_xifenfei) */object_id from t_xifenfei t;

845708 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1935372603

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |   841K|  4109K| 18940   (1)| 00:03:48 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T_XIFENFEI        |   841K|  4109K| 18940   (1)| 00:03:48 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                   |       |       |            |          |
|   3 |    BITMAP INDEX FULL SCAN    | BITMAP_T_XIFENFEI |       |       |            |          |
--------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      91537  consistent gets
          0  physical reads
          0  redo size
   42362633  bytes sent via SQL*Net to client
     620649  bytes received via SQL*Net from client
      56382  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     845708  rows processed

这里因为object_id列可能有null值,所以不会使用b_tree_t_xifenfei索引,这里的疑惑是:
就算不会使用b_tree_t_xifenfei index也不应该会使用BITMAP_T_XIFENFEI index,因为使用这个的cost会大于全表扫描

4.index hint 一个无效index

SQL> SELECT /*+ INDEX(T abc) */object_id from t_xifenfei t;

845708 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1935372603

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |   841K|  4109K| 18940   (1)| 00:03:48 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T_XIFENFEI        |   841K|  4109K| 18940   (1)| 00:03:48 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                   |       |       |            |          |
|   3 |    BITMAP INDEX FULL SCAN    | BITMAP_T_XIFENFEI |       |       |            |          |
--------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      91537  consistent gets
          0  physical reads
          0  redo size
   42362633  bytes sent via SQL*Net to client
     620649  bytes received via SQL*Net from client
      56382  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     845708  rows processed

这里使用了一个无效的index,也使用了BITMAP_T_XIFENFEI,让人更加的感觉奇怪

6.Oracle研究中心原因分析

If the INDEX hint specifies no indexes, then the optimizer considers the cost of a scan on each available index on the table and then performs the index scan with the lowest cost. The database can also choose to scan multiple indexes and merge the results, if such an access path has the lowest cost. The optimizer does not consider a full table scan.
如果我们使用hint指定了一个无效的index,优化器会扫描表中所有可以使用的index,然后选择cost最小的index或者index组合,而不会选择全表扫描。
因为我们hint指定b_tree_t_xifenfei index的时候,因为object_id可能有值为空(列没定义为not null),所以不能使用该index,从而也就是相当于一个无效的index,从而扫描该表的其他可以使用的index,导致使用了位图索引(该类型index不排除null),而不是全表扫描.
温馨提示:使用hint指定index的时候需要慎重,如果不合适或者无效,可能导致程序效率更低

 


--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:Oracle数据库索引 index hint指定index的深入理解案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/245.html

Oracle研究中心

关键词:

Oracle数据库索引

index hint指定index的深入理解案例

hint强制select语句使用指定索引