[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個欄位的複合索引，這個功能就失效了。昨天看
--//發現這個還有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

--//連結註解處許多討論，不再展開。