时间戳

首页 > 解决方案 > Oracle 和 group by 的奇怪行为

sql - Oracle 和 group by 的奇怪行为

问题描述

在分析 Oracle 中 SQL 查询的性能时，我注意到一个奇怪的行为。我注意到 Oracle 的计划行为会根据查询中使用的值而变化。

例如这里是我的表结构：


  CREATE TABLE "USAGE" 
   (    "ID" NUMBER(11,0) NOT NULL ENABLE, 
    "CREATED_DATE" TIMESTAMP (6), 
    "MODIFIED_DATE" TIMESTAMP (6),
    "PERIOD" TIMESTAMP (6) NOT NULL ENABLE, 
    "DOWNLOAD" NUMBER(19,0),
     PRIMARY KEY ("ID")
   );

  CREATE INDEX "USAGE_A0ACFA46" ON "USAGE" ("PERIOD");


  CREATE UNIQUE INDEX "USAG_PERIOD_772992E2_UNIQ" ON "USAGE" ("PERIOD");

当我获取以下查询的计划时，我看到该表被 INDEX RANGE SCAN 访问，这是预期的：


explain plan for
select usg.period, sum(usg.download)
 from usage usg
 where usg.period>=TIMESTAMP '2018-11-30 00:00:00'
    group by usg.period;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                           |     1 |    18 |     3   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT        |                           |     1 |    18 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| USAGE                     |     1 |    18 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | USAG_PERIOD_E67F63D3_UNIQ |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

但是，当我只更改值时，我注意到表是由 TABLE ACCESS FULL 访问的，这对我来说很奇怪：


select usg.period, sum(usg.download)
 from usage usg
 where usg.period>=TIMESTAMP '2017-11-30 00:00:00'
    group by usg.period;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |   133 |  2394 |    69   (2)| 00:00:01 |
|   1 |  HASH GROUP BY     |       |   133 |  2394 |    69   (2)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| USAGE |  9505 |   167K|    68   (0)| 00:00:01 |
----------------------------------------------------------------------------

我的问题是，为什么会这样？无论值是什么，我都希望 Oracle 使用 INDEX RANGE SCAN。

我的数据库是 Oracle 11g

标签： sqloracleoracle11ggroup-byquery-performance

解决方案

优化器可能会根据数据量决定是否使用单个索引，对于大量数据，full-scan首选而不是the index range scan.

您的第二种情况似乎扫描更大的数据集，因为间隔更长。

例如，尝试将您的扫描限制在一个月内

Q1：

select usg.period, sum(usg.download)
  from usage usg
 where usg.period between timestamp'2017-11-01 00:00:00' and timestamp'2017-11-30 00:00:00'
 group by usg.period;

和

Q2：

select usg.period, sum(usg.download)
 from usage usg
 where usg.period between timestamp'2018-11-01 00:00:00' and timestamp'2018-11-30 00:00:00'
    group by usg.period;

对于查询 Q1 和 Q2，您很可能会看到索引范围扫描，其成本值接近，具体取决于表的同质填充数据。索引主要适用于少量行。

推荐阅读