代码之家 › 专栏 › 技术社区 › thecoop

如何获取count(col)…group by以使用索引?

query-optimization indexing group-by oracle

thecoop · 技术社区 · 16 年前

我有一个索引为(col1,col2,…)的表(col1,col2,…)。表中有数百万行,我想运行一个查询:

 SELECT col1, COUNT(col2) WHERE col1 NOT IN (<couple of exclusions>) GROUP BY col1

不幸的是,这将导致对表的全表扫描,这需要一分钟以上的时间。有什么方法可以让oracle使用列上的索引更快地返回结果吗?

编辑:

更具体地说,我正在运行以下查询:

SELECT owner, COUNT(object_name) FROM all_objects GROUP BY owner

上面有一个索引 SYS.OBJ$ ( SYS.I_OBJ2 )哪个索引 owner# 和 name 列;我相信我应该能够在查询中使用此索引,而不是对 系统对象$

4 回复 | 直到 16 年前

APC 16 年前

我已经有机会玩弄这个了,我之前关于“不在”的评论在这种情况下是个危险的话题。关键是是否存在空值,或者索引列是否没有强制执行空约束。

这将取决于您正在使用的数据库的版本,因为优化器在每次发布时都会变得更加智能。我使用的是11gr1,优化器在所有情况下都使用了索引,只有一种情况除外:当两列都为空并且我没有包括 NOT IN 条款:

SQL> desc big_table
 Name                                  Null?    Type
 -----------------------------------  ------    -------------------
 ID                                             NUMBER
 COL1                                           NUMBER
 COL2                                           VARCHAR2(30 CHAR)
 COL3                                           DATE
 COL4                                           NUMBER

没有不在条款…

SQL> explain plan for
  2      select col4, count(col1) from big_table
  3      group by col4
  4  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 1753714399

----------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           | 31964 |   280K|       |  7574   (2)| 00:01:31 |
|   1 |  HASH GROUP BY     |           | 31964 |   280K|    45M|  7574   (2)| 00:01:31 |
|   2 |   TABLE ACCESS FULL| BIG_TABLE |  2340K|    20M|       |  4284   (1)| 00:00:52 |
----------------------------------------------------------------------------------------

9 rows selected.


SQL>

当我拍下 不在 子句返回时,优化器选择使用索引。奇怪的。

SQL> explain plan for
  2      select col4, count(col1) from big_table
  3      where col1 not in (12, 19)
  4      group by col4
  5  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 343952376

----------------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        | 31964 |   280K|       |  5057   (3)| 00:01:01 |
|   1 |  HASH GROUP BY        |        | 31964 |   280K|    45M|  5057   (3)| 00:01:01 |
|*  2 |   INDEX FAST FULL SCAN| BIG_I2 |  2340K|    20M|       |  1767   (2)| 00:00:22 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------

   2 - filter("COL1"<>12 AND "COL1"<>19)

14 rows selected.

SQL>

重复一下,在所有其他情况下,只要其中一个索引列声明为非nill,就可以使用索引来满足查询。这在早期版本的oracle上可能不是真的,但它可能指明了前进的方向。

Shepherdess 16 年前

你需要一个提示 http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/hintsref.htm , 但是请记住,使用索引并不总是会导致更快的执行。

Marcelo Cantos 16 年前

(以防万一,你确定它是在做表扫描而不是索引扫描吗?)

试用使用 COUNT(*) 而不是 COUNT(col2) (当然,假设这适合你的问题)。另外,也许可以尝试一个索引 col1 .

MichaelN 16 年前

您正在查询Oracle的固定表,因为您还没有说明这是哪个数据库版本,所以我假设是最近的一个。固定表是否经过分析并更新了统计数据?您是否使用/*+rule*/hint使用规则基优化器尝试过查询。我经常看到,当使用规则库优化器时,针对Oracle自己的固定表的查询性能会更好。