1. 首页
  2. IT资讯

[20200120]12c Group by Elimination bug.txt

[20200120]12c Group by Elimination bug.txt

–//前一阵子做的测试:http://blog.itpub.net/267265/viewspace-2648615/=>[20190624]12c group by优化.txt
–//实际上没啥意思,就是主键group by可以不用执行SORT GROUP BY NOSORT,但是我当时的测试很奇怪,如果主键
–//2个字段的复合索引,这个功能就失效了。昨天看https://jonathanlewis.wordpress.com/2020/01/17/group-by-elimination/
–//发现这个还有bug存在,自己也重复测试看看。

1.环境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
—————————— ————– ——————————————————————————– ———-
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production              0

SCOTT@test01p> select sysdate from dual;
SYSDATE
——————-
2020-01-20 08:37:32

SCOTT@test01p> create table t as select sysdate+rownum c from dual connect by level<=100;
Table created.

–//分析略。

2.测试:
SCOTT@test01p> select trunc(c,'yyyy') ,count(*) from t group by trunc(c,'yyyy') order by trunc(c,'yyyy');
TRUNC(C,'YYYY')       COUNT(*)
——————- ———-
2020-01-01 00:00:00        100

Plan hash value: 81261667
——————————————————————————————————–
| Id  | Operation           | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
——————————————————————————————————–
|   0 | SELECT STATEMENT    |      |        |       |     5 (100)|          |       |       |          |
|   1 |  SORT ORDER BY      |      |    100 |   800 |     5  (40)| 00:00:01 |  2048 |  2048 | 2048  (0)|
|   2 |   HASH GROUP BY     |      |    100 |   800 |     5  (40)| 00:00:01 |  1394K|  1394K|  495K (0)|
|   3 |    TABLE ACCESS FULL| T    |    100 |   800 |     3   (0)| 00:00:01 |       |       |          |
——————————————————————————————————–
–//ok正确!!

SCOTT@test01p> select to_char(c,'yyyy') ,count(*) from t group by to_char(c,'yyyy') order by to_char(c,'yyyy');
TO_C   COUNT(*)
—- ———-
2020        100
–//ok正确!!执行计划略。

3.增加索引唯一测试:
SCOTT@test01p> create unique index pk_t on t (c);
Index created.

SCOTT@test01p> select trunc(c,'yyyy') ,count(*) from t group by trunc(c,'yyyy') order by trunc(c,'yyyy');
TRUNC(C,'YYYY')       COUNT(*)
——————- ———-
2020-01-01 00:00:00        100

SCOTT@test01p> select to_char(c,'yyyy') ,count(*) from t group by to_char(c,'yyyy') order by to_char(c,'yyyy');
TO_C   COUNT(*)
—- ———-
2020        100
–//ok正确!!执行计划略。

4.继续测试增加c字段not null:
SCOTT@test01p> alter table t modify(c not  null);
Table altered.

SCOTT@test01p> select trunc(c,'yyyy') ,count(*) from t group by trunc(c,'yyyy') order by trunc(c,'yyyy');
TRUNC(C,'YYYY')       COUNT(*)
——————- ———-
2020-01-01 00:00:00        100
–//使用trunc函数依旧正确。

SCOTT@test01p> select to_char(c,'yyyy') ,count(*) from t group by to_char(c,'yyyy') order by to_char(c,'yyyy');
TO_C   COUNT(*)
—- ———-
2020          1
2020          1
2020          1

2020          1
2020          1
2020          1
100 rows selected.

–//输出发生错误。应该仅仅1行才正确,也就是这时不应该使用Group by Elimination。
–//查看执行计划:
SCOTT@test01p> @ dpc '' outline
PLAN_TABLE_OUTPUT
————————————-
SQL_ID  5rqwk9s50uxg7, child number 0
————————————-
select to_char(c,'yyyy') ,count(*) from t group by to_char(c,'yyyy')
order by to_char(c,'yyyy')

Plan hash value: 3513526269

—————————————————————————————————–
| Id  | Operation        | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
—————————————————————————————————–
|   0 | SELECT STATEMENT |      |        |       |     2 (100)|          |       |       |          |
|   1 |  SORT ORDER BY   |      |    100 |   800 |     2  (50)| 00:00:01 |  9216 |  9216 | 8192  (0)|
|   2 |   INDEX FULL SCAN| PK_T |    100 |   800 |     1   (0)| 00:00:01 |       |       |          |
—————————————————————————————————–

Query Block Name / Object Alias (identified by operation id):
————————————————————-

   1 – SEL$9BB7A81A
   2 – SEL$9BB7A81A / T@SEL$1

Outline Data
————-

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$9BB7A81A")
      ELIM_GROUPBY(@"SEL$47952E7A")
      OUTLINE(@"SEL$47952E7A")
      ELIM_GROUPBY(@"SEL$1")
      ~~~~~~~~~~~~~~~~~~~~~~
      OUTLINE(@"SEL$1")
      INDEX(@"SEL$9BB7A81A" "T"@"SEL$1" ("T"."C"))
      END_OUTLINE_DATA
  */

–//注意看下划线内容ELIM_GROUPBY.

SCOTT@test01p> @ sqlhint ELIM_GROUPBY
NAME            SQL_FEATURE          CLASS        INVERSE         TARGET_LEVEL PROPERTY VERSION  VERSION_OUTLINE CON_ID
————— ——————– ———— ————— ———— ——– ——– ————— ——
ELIM_GROUPBY    QKSFM_TRANSFORMATION ELIM_GROUPBY NO_ELIM_GROUPBY            2       16 12.1.0.2 12.1.0.2             0
NO_ELIM_GROUPBY QKSFM_TRANSFORMATION ELIM_GROUPBY ELIM_GROUPBY               2       16 12.1.0.2 12.1.0.2             0

–//加入提示no_ELIM_GROUPBY.
SCOTT@test01p> select /*+ no_ELIM_GROUPBY(@"SEL$1") */ to_char(c,'yyyy') ,count(*) from t group by to_char(c,'yyyy') order by to_char(c,'yyyy');
TO_C   COUNT(*)
—- ———-
2020        100

–//设置:_optimizer_aggr_groupby_elim=false也可以达到效果。
SYS@test> @ hide optimizer_aggr_groupby_elim
NAME                         DESCRIPTION                          DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
—————————- ———————————— ————- ————- ———— —– ———
_optimizer_aggr_groupby_elim group-by and aggregation elimination TRUE          TRUE          TRUE         TRUE  IMMEDIATE
_optimizer_aggr_groupby_elim group-by and aggregation elimination TRUE          TRUE          TRUE         TRUE  IMMEDIATE
_optimizer_aggr_groupby_elim group-by and aggregation elimination TRUE          TRUE          TRUE         TRUE  IMMEDIATE
–//注:输出3行,估计与使用pdb有关。

SCOTT@test01p> select /*+ opt_param('_optimizer_aggr_groupby_elim','false') */ to_char(c,'yyyy') ,count(*) from t group by to_char(c,'yyyy') order by to_char(c,'yyyy');
TO_C   COUNT(*)
—- ———-
2020        100

–//链接https://jonathanlewis.wordpress.com/2020/01/17/group-by-elimination/注解处许多讨论,不再展开。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2674061/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/183094.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code