代码之家 › 专栏 › 技术社区 › Peter Lang

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

indexing oracle performance sql

Peter Lang · 技术社区 · 15 年前

我有一个查询,在我认为可以的时候没有使用索引,所以出于好奇,我复制了它:

创建 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' );

尝试获取的不同值 科尔 以及 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 
---------------------------------------------------------------------------------

不使用索引,前提是提示不会改变这一点。

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

4 回复 | 直到 13 年前

APC 15 年前

我运行了彼得的原始资料并复制了他的结果。然后我应用了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树索引中,但是group by必须将空值作为分组“value”包含在查询中。通过告诉优化器 col 使用效率更高的索引是免费的(我在使用FTS时花费了将近3.55秒)。这是元数据如何影响优化器的经典示例。

顺便说一句,这显然是一个10g或11g数据库,因为它使用了哈希分组算法,而不是旧的排序(分组)算法。

dcp 15 年前

更新: 尝试使col列不为空。这就是它不使用索引的原因。当它不是空的时候,这是计划。

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

Vincent Malgrat 15 年前

你忘了这些非常重要的信息: 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
--------------------------------------------------------------------------------

iggy 13 年前

位图索引也可以

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