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

范金小草

我有一个查询，在我认为可能不使用索引的情况下，出于好奇，我将其重现：
创建一个test_table具有1.000.000行（在中有10个不同的值，在col中有500个字节的数据some_data）的。
3 N- `8 R7 P4 S  a4 RCREATE TABLE test_table AS (
2 h0 u& M$ A) T' `. T# |& L  SELECT MOD(ROWNUM,10) col, LPAD('x', 500, 'x') some_data
. F. l( }0 Y* ]: B9 ?5 W  FROM dual
  h$ o9 M# H" c3 q$ Z  p  CONNECT BY ROWNUM 创建一个索引并收集表统计信息：
CREATE INDEX test_index ON test_table ( col );
EXEC dbms_stats.gather_table_stats( 'MY_SCHEMA', 'TEST_TABLE' );
' [4 E8 \; [! }. b+ ]. @' ~尝试获取col和的不同值COUNT：
  c( o  [( ~* LEXPLAIN PLAN FOR
' j) e" `  R/ G. E5 s  SELECT col, COUNT(*)
  FROM test_table
  {5 B' g; j5 w, i  GROUP BY col;
---------------------------------------------------------------------------------
1 H- `/ @& M; _4 Y% P: k5 y' j| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time
---------------------------------------------------------------------------------
+ k. w2 B" B" s& I|   0 | SELECT STATEMENT   |            |    10 |    30 | 15816   (1)| 00:03:10
|   1 |  HASH GROUP BY     |            |    10 |    30 | 15816   (1)| 00:03:10
7 I7 l0 B5 G# m  V4 s, I|   2 |   TABLE ACCESS FULL| TEST_TABLE |   994K|  2914K| 15755   (1)| 00:03:10
1 Y+ \' F6 x( m1 u  H3 `8 w% V---------------------------------------------------------------------------------
" z( \4 m# E2 R) o不使用索引，前提是提示不会更改。
我想在这种情况下不能使用索引，但是为什么呢？
' l9 v$ x% }# q6 |2 w. q9 r               
' @+ f# N6 h3 v; a: V; Q" p解决方案:
               

8 G# m2 _+ C1 N" Q) N* S
5 g& _7 Z7 Z# t+ y' \                我运行了Peter的原始内容并复制了他的结果。然后我应用了dcp的建议…
SQL> alter table test_table modify col not null;
0 P( k* \9 x4 N( H/ |" V& Q5 @Table altered.
SQL> EXEC dbms_stats.gather_table_stats( user, 'TEST_TABLE' , cascade=>true)
PL/SQL procedure successfully completed.
! a' n7 B- q: k) s/ u3 I$ MSQL> EXPLAIN PLAN FOR
  2    SELECT col, COUNT(*)
) z7 D" p: {; @4 s" D2 g0 o7 G, x! c  3    FROM test_table
8 d' O5 S+ `: p+ }+ N  4    GROUP BY col;
& a3 M0 w* Q  Z5 BExplained.
' L; S% y5 ]  ]/ d$ ySQL> select * from table(dbms_xplan.display)
  2  /
PLAN_TABLE_OUTPUT
" K  u& M& C. ^" o: j; ~/ b' n# N- X6 e------------------------------------------------------------------------------------
" S2 I: R, v8 }# YPlan hash value: 2099921975
; s# B- P' g3 o+ s) s: h  E9 O& S6 q3 t------------------------------------------------------------------------------------
1 y0 P2 F: ?1 I' P| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |    10 |    30 |   574   (9)| 00:00:07 |
7 `8 |& D1 U2 K/ X5 d6 |" X|   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 |
) k2 H/ \4 Y/ f. ]: N) Q) I$ L3 s$ Q------------------------------------------------------------------------------------
' o  S7 I2 @% q9 rows selected.
SQL>
6 z, {$ b" q% G之所以如此重要，是因为在常规B-TREE索引中不包含NULL值，但是GROUP
BY必须在查询中将NULL包括为分组“值”。通过告诉优化器其中没有NULL，col可以自由使用效率更高的索引（FTS使我花费了近3.55秒的时间）。这是元数据如何影响优化器的经典示例。
4 h7 N/ D, H9 ^* X顺便说一下，这显然是10g或11g数据库，因为它使用HASH GROUP BY算法，而不是较旧的SORT（GROUP BY）算法。