为什么用了hint还是不走index

xifenfei

看了支付宝面试:select min(id),max(id) 优化问题的帖子，做实验发现奇怪现象，如下：

SQL>  select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

SQL>  create table test as select * from dba_objects;

表已创建。

SQL> create index i_object_id on test(object_id);

索引已创建。

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TEST',cascade => true);

PL/SQL 过程已成功完成。

SQL> set autot trace exp stat
SQL> select max(object_id),min(object_id) from test;


执行计划
----------------------------------------------------------
Plan hash value: 1950795681

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     5 |   293   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 73350 |   358K|   293   (1)| 00:00:04 |
---------------------------------------------------------------------------

这个可以理解，cbo自己决定的（但是还是有点不合理）

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1049  consistent gets
          0  physical reads
          0  redo size
        502  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> select /*+ index(test i_object_id) */max(object_id),min(object_id) from test;


执行计划
----------------------------------------------------------
Plan hash value: 1950795681

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     5 |   293   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 73350 |   358K|   293   (1)| 00:00:04 |
---------------------------------------------------------------------------

这个我已经使用了hint index提示了，为什么还是全表扫描？

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1049  consistent gets
          0  physical reads
          0  redo size
        502  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> select max(object_id),min(object_id) from test where object_id>0;


执行计划
----------------------------------------------------------
Plan hash value: 1751978921

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

-----

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

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

-----

|   0 | SELECT STATEMENT      |             |     1 |     5 |    47   (3)| 00:00

:01 |

|   1 |  SORT AGGREGATE       |             |     1 |     5 |            |
    |

|*  2 |   INDEX FAST FULL SCAN| I_OBJECT_ID | 73347 |   358K|    47   (3)| 00:00

:01 |

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

我添加了一个where条件object_id，就走index了

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

   2 - filter("OBJECT_ID">0)


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        170  consistent gets
          0  physical reads
          0  redo size
        502  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

问题是：不加where条件的情况下，加不加hint，都不会使用INDEX FAST FULL SCAN

dingjun123

constraints not null

ai2ming

如2楼所说，必须加上not null的约束条件。
因为索引里面不存null值，oracle认为走索引的结果会不对，所以这时候即使加了hints也不行。

xifenfei

正确，谢谢dingjun123 ，我刚刚删除了object_id为null的记录，然后修改为not null了，然后就可以了
SQL> select max(object_id),min(object_id) from test ;


执行计划
----------------------------------------------------------
Plan hash value: 1751978921

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

-----

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

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

-----

|   0 | SELECT STATEMENT      |             |     1 |     5 |    46   (0)| 00:00

:01 |

|   1 |  SORT AGGREGATE       |             |     1 |     5 |            |
    |

|   2 |   INDEX FAST FULL SCAN| I_OBJECT_ID | 73350 |   358K|    46   (0)| 00:00

:01 |

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

-----



统计信息
----------------------------------------------------------
        208  recursive calls
          0  db block gets
        200  consistent gets
          0  physical reads
          0  redo size
        502  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

xifenfei

我想问下，在什么情况下会出现object_id为null，怎么会出现为null的记录？

dingjun123

ctas这东西会将很多约束神马的去掉

xifenfei

我知道为什么了，因为是dblink，所以没有object_id
SQL> SELECT object_name,OBJECT_TYPE FROM dba_objects WHERE object_id IS NULL;

OBJECT_NAME                                                                      OBJECT_TYPE
-------------------------------------------------------------------------------- -------------------
FJDY                                                                             DATABASE LINK
VPC_LINK                                                                         DATABASE LINK
CON_WEBTEST                                                                      DATABASE LINK

我上面有人

如果对table进行统计分析，让oracle获得足够的信息，这时会走index吗？

xifenfei

不会哦，我已经分析过了，是因为null导致的

microsoft_fly

基础很重要！