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