为什么这个查询没有使用索引?
我不得不在当我认为它可能是未使用的索引的查询,所以再现出来的好奇心:为什么这个查询没有使用索引?
创建test_table
与1.000.000行(在col
10倍不同的值,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
---------------------------------------------------------------------------------
不使用索引,提供线索呢不改变这一点。
我猜,索引不能在这种情况下使用,但为什么?
我跑了彼得的原始的东西,他的复制效果。然后我申请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树索引收录,但GROUP BY必须包括NULL作为分组“值”您的查询。通过告诉优化器在col
中没有NULL,可以*使用效率更高的索引(我得到的时间与FTS差不多3.55秒)。这是元数据如何影响优化器的典型示例。
顺便说一下,这显然是10g或11g数据库,因为它使用HASH GROUP BY算法,而不是旧的SORT(GROUP BY)算法。
更新: 尝试使col列NOT NULL。这是它不使用索引的原因。当它不是空的,这是计划。
SELECT STATEMENT, GOAL = ALL_ROWS 69 10 30
HASH GROUP BY 69 10 30
INDEX FAST FULL SCAN SANDBOX TEST_INDEX 56 98072 294216
如果优化器确定不使用索引更有效(可能是由于重写查询),那么它不会。优化器提示就是这样,即提示告诉Oracle您将使用类似的索引。你可以将它们视为建议。但是,如果优化器确定最好不要使用索引(再次,例如查询重写的结果),那么它不会。
参考此链接:http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm “指定这些提示之一会使优化选择只有在访问路径是提供基于索引或群集的存在和对SQL的语法结构指定的访问路径语句。如果提示指定了不可用的访问路径,则优化器会忽略它。“
由于您正在运行count(*)操作,因此优化程序已确定只扫描整个表和散列而不是使用索引会更有效。
下面是关于提示另一个方便的链接: http://www.dba-oracle.com/t_hint_ignored.htm
查询中没有输出表中的数据,只是'col'和'count(*)'。整个结果可以通过扫描索引产生。扫描索引而不是表格会更快(即使它必须以任何方式执行完整扫描)似乎并不合逻辑?该指数较小.. – 2010-02-02 16:46:25
@dcp:我知道提示如何工作,我很好奇为什么索引不是用在第一位。 – 2010-02-02 16:48:36
@Sam:是的,这就是我的想法...... – 2010-02-02 16:49:58
您忘记这个非常重要的信息:如果列可以为空COL不为空
,该指数不能被使用,因为有可能是没有索引的列。
SQL> ALTER TABLE test_table MODIFY (col NOT NULL);
Table altered
SQL> EXPLAIN PLAN FOR
2 SELECT col, COUNT(*) FROM test_table GROUP BY col;
Explained
SQL> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1077170955
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 30 | 1954 (1)| 00:00:2
| 1 | SORT GROUP BY NOSORT| | 10 | 30 | 1954 (1)| 00:00:2
| 2 | INDEX FULL SCAN | TEST_INDEX | 976K| 2861K| 1954 (1)| 00:00:2
--------------------------------------------------------------------------------
位图索引也将这样做
Execution Plan ---------------------------------------------------------- Plan hash value: 2200191467 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 30 | 15983 (2)| 00:03:12 | | 1 | HASH GROUP BY | | 10 | 30 | 15983 (2)| 00:03:12 | | 2 | TABLE ACCESS FULL| TEST_TABLE | 1013K| 2968K| 15825 (1)| 00:03:10 | --------------------------------------------------------------------------------- SQL> create bitmap index test_index on test_table(col); Index created. SQL> EXEC dbms_stats.gather_table_stats('MY_SCHEMA', 'TEST_TABLE'); PL/SQL procedure successfully completed. SQL> SELECT col, COUNT(*) 2 FROM test_table 3 GROUP BY col 4/ Execution Plan ---------------------------------------------------------- Plan hash value: 238193838 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 30 | 286 (0)| 00:00:04 | | 1 | SORT GROUP BY NOSORT | | 10 | 30 | 286 (0)| 00:00:04 | | 2 | BITMAP CONVERSION COUNT| | 1010K| 2961K| 286 (0)| 00:00:04 | | 3 | BITMAP INDEX FULL SCAN| TEST_INDEX | | | | | ---------------------------------------------------------------------------------------
位图索引仅适用于有限范围的表格。如果我们的表受到大量插入,更新或删除操作的影响,那么维护索引的成本可能太高。 – APC 2010-02-04 07:50:12
使用该指数将无法防止全面扫描,所以它确实提供了没有任何好处。 – recursive 2010-02-02 16:46:18
@recursive:看起来确实如此,但是为什么在全表扫描中首选的索引不是全面扫描? – 2010-02-02 16:51:02
如果你在这里找不到解决方案,你可以试试asktom.oracle.com(这对我来说非常有帮助)。 – 2010-02-02 17:01:06