我有一个查询,当我认为可能是索引没有被使用,所以我好奇地转载了它：
创建一个具有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 ---------------------------------------------------------------------------------
该索引未被使用,提供的提示不会改变这一点.
我猜这个索引在这种情况下是不能使用的,但为什么呢？
解决方法
我跑了彼得的原始的东西,并转载他的结果.然后我应用了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>
原因很重要,因为NULL值不包含在正常的B-TREE索引中,而GROUP BY必须在查询中包含NULL作为分组“值”.通过告诉优化器在col中没有NULL可以自由使用更高效的索引(FTS使用了近3.55秒的时间).这是一个经典的例子,说明元数据如何影响优化器.
顺便说一下,这显然是10g或11g数据库,因为它使用HASH GROUP BY算法,而不是旧的SORT(GROUP BY)算法.
