sql – 为什么没有索引用于此查询？

创建一个具有1.000.000行(col中的10个不同值,some_data中的500字节数据)的test_table.

CREATE TABLE test_table AS (
  SELECT MOD(ROWNUM,10) col,LPAD('x',500,'x') some_data
  FROM dual
  CONNECT BY ROWNUM <= 1000000
);

创建索引并收集表统计信息：

CREATE INDEX test_index ON test_table ( col );

EXEC dbms_stats.gather_table_stats( 'MY_SCHEMA','TEST_TABLE' );

尝试获取col和COUNT的不同值：

EXPLAIN PLAN FOR
  SELECT col,COUNT(*)
  FROM test_table
  GROUP BY col;

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |    10 |    30 | 15816   (1)| 00:03:10 
|   1 |  HASH GROUP BY     |            |    10 |    30 | 15816   (1)| 00:03:10 
|   2 |   TABLE ACCESS FULL| TEST_TABLE |   994K|  2914K| 15755   (1)| 00:03:10 
---------------------------------------------------------------------------------

该索引未被使用,提供的提示不会改变这一点.

我猜这个索引在这种情况下是不能使用的,但为什么呢？

解决方法

SQL> alter table test_table modify col not null;

Table altered.

SQL> EXEC dbms_stats.gather_table_stats( user,'TEST_TABLE',cascade=>true)

PL/SQL procedure successfully completed.

SQL> EXPLAIN PLAN FOR
  2    SELECT col,COUNT(*)
  3    FROM test_table
  4    GROUP BY col;

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 2099921975

------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |    10 |    30 |   574   (9)| 00:00:07 |
|   1 |  HASH GROUP BY        |            |    10 |    30 |   574   (9)| 00:00:07 |
|   2 |   INDEX FAST FULL SCAN| TEST_INDEX |  1000K|  2929K|   532   (2)| 00:00:07 |
------------------------------------------------------------------------------------

9 rows selected.

SQL>

原因很重要,因为NULL值不包含在正常的B-TREE索引中,而GROUP BY必须在查询中包含NULL作为分组“值”.通过告诉优化器在col中没有NULL可以自由使用更高效的索引(FTS使用了近3.55秒的时间).这是一个经典的例子,说明元数据如何影响优化器.

顺便说一下,这显然是10g或11g数据库,因为它使用HASH GROUP BY算法,而不是旧的SORT(GROUP BY)算法.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!