[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/注解处许多讨论，不再展开。