为什么索引不用于此查询？

小编典典

为什么索引不用于此查询？

sql

我有一个查询，在我认为可能不使用索引的情况下，出于好奇，我将其重现：

创建一个test_table具有1.000.000行（在中有10个不同的值，在col中有500个字节的数据some_data）的。

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 
---------------------------------------------------------------------------------

不使用索引，前提是提示不会更改。

我想在这种情况下不能使用索引，但是为什么呢？

阅读 134

2021-04-17

共1个答案

小编典典

我运行了Peter的原始内容并复制了他的结果。然后我应用了dcp的建议…

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>

之所以如此重要，是因为在常规B-TREE索引中不包含NULL值，但是GROUP
BY必须在查询中将NULL包括为分组“值”。通过告诉优化器其中没有NULL，col可以自由使用效率更高的索引（FTS使我花费了近3.55秒的时间）。这是元数据如何影响优化器的经典示例。

顺便说一下，这显然是10g或11g数据库，因为它使用HASH GROUP BY算法，而不是较旧的SORT（GROUP BY）算法。

2021-04-17