[20220124]group by bug.txt

[20220124]group by bug.txt --//https://jonathanlewis.wordpress.com/2022/01/01/happy-new-year/,重复测试: 1.环境: SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production create table t as (         select date'2021-12-30' d from dual         union all         select date'2021-12-31'   from dual ); select extract(year from d), count(*) from t group by extract(year from d); alter table t add primary key(d); select extract(year from d), count(*) from t group by extract(year from d); --//测试结果. SCOTT@book> select extract(year from d), count(*) from t group by extract(year from d); EXTRACT(YEARFROMD)   COUNT(*) ------------------ ----------               2021          2 SCOTT@book> alter table t add primary key(d); Table altered. SCOTT@book> select extract(year from d), count(*) from t group by extract(year from d); EXTRACT(YEARFROMD)   COUNT(*) ------------------ ----------               2021          2 --//无法测试出来.主要问题在于志行计划id=2 走HASH GROUP BY Plan hash value: 1082526398 ------------------------------------------------------------------------------------------------------------- | Id  | Operation        | Name         | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT |              |        |       |     2 (100)|          |       |       |          | |   1 |  HASH GROUP BY   |              |      2 |    16 |     2  (50)| 00:00:01 |  1558K|  1558K|  489K (0)| |   2 |   INDEX FULL SCAN| SYS_C0012226 |      2 |    16 |     1   (0)| 00:00:01 |       |       |          | ------------------------------------------------------------------------------------------------------------- SCOTT@book> select /*+ NO_USE_HASH_AGGREGATION(@SEL$1) */ extract(year from d), count(*) from t group by extract(year from d); EXTRACT(YEARFROMD)   COUNT(*) ------------------ ----------               2021          2 Plan hash value: 1136141978 ------------------------------------------------------------------------------------------------------------- | Id  | Operation        | Name         | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT |              |        |       |     2 (100)|          |       |       |          | |   1 |  SORT GROUP BY   |              |      2 |    16 |     2  (50)| 00:00:01 |  2048 |  2048 | 2048  (0)| |   2 |   INDEX FULL SCAN| SYS_C0012226 |      2 |    16 |     1   (0)| 00:00:01 |       |       |          | ------------------------------------------------------------------------------------------------------------- Matthias Rogel supplied a follow-up demonstrating the problem with to_char(), which prompted me to create an example showing that it wasn't just about dates – which I've tidied up below (reminder, all results on this page are from 12.2.0.1): create  table t1 as select  round(rownum/10,1) n1 from    all_objects where   rownum <= 10 ; select n1 from t1 order by n1; select n1, count(*) from t1 group by n1 order by n1; column nch format A3 select to_char(n1,'99') nch, count(*) from t1 group by to_char(n1,'99') order by 1,2; select * from table(dbms_xplan.display_cursor(format =>'outline')); alter table t1 add constraint t1_pk primary key(n1); select to_char(n1,'99') nch , count(*) from t1 group by to_char(n1,'99') order by 1,2; select * from table(dbms_xplan.display_cursor(format =>'outline')); --//重复测试在11.2.0.4没有这个问题.不过这个问题在19c上再现. > @ prxx ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 19.0.0.0.0 BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.9.0.0.0 BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. create table t as (         select date'2021-12-30' d from dual         union all         select date'2021-12-31'   from dual   5  ); Table created. > select extract(year from d), count(*) from t group by extract(year from d); EXTRACT(YEARFROMD)   COUNT(*) ------------------ ----------               2021          2 > alter table t add primary key(d); Table altered. > select extract(year from d), count(*) from t group by extract(year from d); EXTRACT(YEARFROMD)   COUNT(*) ------------------ ----------               2021          1               2021          1 > @ sl all alter session set statistics_level = all; Session altered. > select extract(year from d), count(*) from t group by extract(year from d)  order by 2; EXTRACT(YEARFROMD)   COUNT(*) ------------------ ----------               2021          1               2021          1 > @ dpc '' 'projection outline' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  b0yb7wmdfq4jk, child number 0 ------------------------------------- select extract(year from d), count(*) from t group by extract(year from d)  order by 2 Plan hash value: 3804481363 --------------------------------------------------------------------------------------------------------------------------- | Id  | Operation        | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | --------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT |              |      1 |        |       |     1 (100)|          |      2 |00:00:00.01 |       2 | |   1 |  INDEX FULL SCAN | SYS_C0065438 |      1 |      2 |    16 |     1   (0)| 00:00:01 |      2 |00:00:00.01 |       2 | --------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$9BB7A81A / T@SEL$1 Outline Data -------------   /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('19.1.0')       DB_VERSION('19.1.0')       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"."D"))       END_OUTLINE_DATA   */ Column Projection Information (identified by operation id): -----------------------------------------------------------    1 - "D"[DATE,7] --//可以发现执行计划取消group by. > select /*+ NO_ELIM_GROUPBY(@"SEL$47952E7A") */ extract(year from d), count(*) from t group by extract(year from d)  ; EXTRACT(YEARFROMD)   COUNT(*) ------------------ ----------               2021          1               2021          1 --//加入提示NO_ELIM_GROUPBY无效.执行计划里面提示 Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 (U - Unused (1)) ---------------------------------------------------------------------------    1 -  SEL$9BB7A81A          U -  NO_ELIM_GROUPBY(@"SEL$47952E7A") --//应该使用SEL$9BB7A81A SCOTT@78> select /*+ NO_ELIM_GROUPBY(@SEL$9BB7A81A */ extract(year from d), count(*) from t group by extract(year from d)  ; EXTRACT(YEARFROMD)   COUNT(*) ------------------ ----------               2021          2 --//昏,提示后少1个括号也是可以生效的. Elapsed: 00:00:00.02 SCOTT@78> @ dpc '' 'outline projection' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  aur3jwvkg70cb, child number 0 ------------------------------------- select /*+ NO_ELIM_GROUPBY(@SEL$9BB7A81A */ extract(year from d), count(*) from t group by extract(year from d) Plan hash value: 47235625 ------------------------------------------------------------------------------------------------------------------------------------------------ | Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT   |      |      1 |        |       |     4 (100)|          |      1 |00:00:00.01 |       2 |       |       |          | |   1 |  HASH GROUP BY     |      |      1 |      2 |    16 |     4  (25)| 00:00:01 |      1 |00:00:00.01 |       2 |  1558K|  1558K|  482K (0)| |   2 |   TABLE ACCESS FULL| T    |      1 |      2 |    16 |     3   (0)| 00:00:01 |      2 |00:00:00.01 |       2 |       |       |          | ------------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$1 / T@SEL$1 Outline Data -------------   /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('11.2.0.4')       DB_VERSION('11.2.0.4')       ALL_ROWS       OUTLINE_LEAF(@"SEL$1")       FULL(@"SEL$1" "T"@"SEL$1")       USE_HASH_AGGREGATION(@"SEL$1")       END_OUTLINE_DATA   */ Column Projection Information (identified by operation id): -----------------------------------------------------------    1 - EXTRACT(YEAR FROM INTERNAL_FUNCTION("D"))[22], COUNT(*)[22]