[20190624]12c group by优化 .txt

[20190624]12c group by优化 .txt --//其实不是什么优化,12cR2,如果group by的字段是主键的化(实际上唯一索引,非空也可以),取消group by的执行.通过例子说明: 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> create table tx as select rownum id1 , rownum id2,'test' name from dual connect by level<=200; Table created. SCOTT@test01p> create unique index pk_tx on tx (id1); Index created. SCOTT@test01p> alter table scott.tx modify(id1 not  null); Table altered. 2.测试: select id1,count(*) from tx group by id1; SCOTT@test01p> @ dpc '' outline PLAN_TABLE_OUTPUT -------------------------------------- SQL_ID  azhmyrwr2hxcy, child number 0 ------------------------------------- select id1,count(*) from tx group by id1 Plan hash value: 1588489161 --------------------------------------------------------------------------- | Id  | Operation        | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT |       |        |       |     1 (100)|          | |   1 |  INDEX FULL SCAN | PK_TX |    200 |   800 |     1   (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$9BB7A81A / TX@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" "TX"@"SEL$1" ("TX"."ID1"))       END_OUTLINE_DATA   */ --//注意看下划线有提示ELIM_GROUPBY(@"SEL$1"). SCOTT@test01p> alter session set optimizer_features_enable='12.1.0.1'; Session altered. SCOTT@test01p> Select id1,count(*) from tx group by id1;    ... SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  f5pz65p7nunwy, child number 0 ------------------------------------- Select id1,count(*) from tx group by id1 Plan hash value: 1908635457 ------------------------------------------------------------------------------- | Id  | Operation            | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | ------------------------------------------------------------------------------- |   0 | SELECT STATEMENT     |       |        |       |     1 (100)|          | |   1 |  SORT GROUP BY NOSORT|       |    200 |   800 |     1   (0)| 00:00:01 | |   2 |   INDEX FULL SCAN    | PK_TX |    200 |   800 |     1   (0)| 00:00:01 | ------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$1 / TX@SEL$1 --//如果设置optimizer_features_enable='12.1.0.1',多执行一步SORT GROUP BY NOSORT. 3.继续测试: --//但是如果唯一索引多个字段呢? SCOTT@test01p> alter table scott.tx modify(id2 not  null); Table altered. SCOTT@test01p> drop index pk_tx ; Index dropped. SCOTT@test01p> create unique index pk_tx on tx (id1,id2); Index created. SCOTT@test01p> show parameter optimizer_features_enable NAME                      TYPE   VALUE ------------------------- ------ -------- optimizer_features_enable string 12.2.0.1 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT -------------------------------------- SQL_ID  54tyrx33kf847, child number 0 ------------------------------------- SElect id1,id2,count(*) from tx group by id1,id2 Plan hash value: 1908635457 ------------------------------------------------------------------------------- | Id  | Operation            | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | ------------------------------------------------------------------------------- |   0 | SELECT STATEMENT     |       |        |       |     1 (100)|          | |   1 |  SORT GROUP BY NOSORT|       |    200 |  1600 |     1   (0)| 00:00:01 | |   2 |   INDEX FULL SCAN    | PK_TX |    200 |  1600 |     1   (0)| 00:00:01 | ------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$1 / TX@SEL$1 --//如果唯一索引多个字段无效. --//修改为主键看看. SCOTT@test01p> drop index pk_tx; Index dropped. create unique index pk_tx on tx (id1, id2); alter table scott.tx add constraint pk_tx primary key (id1, id2); sElect id1,id2,count(*) from tx group by id1,id2 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  7jn508pxfd2sk, child number 0 ------------------------------------- sElect id1,id2,count(*) from tx group by id1,id2 Plan hash value: 1908635457 ------------------------------------------------------------------------------- | Id  | Operation            | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | ------------------------------------------------------------------------------- |   0 | SELECT STATEMENT     |       |        |       |     1 (100)|          | |   1 |  SORT GROUP BY NOSORT|       |    200 |  1600 |     1   (0)| 00:00:01 | |   2 |   INDEX FULL SCAN    | PK_TX |    200 |  1600 |     1   (0)| 00:00:01 | ------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$1 / TX@SEL$1 --//一样无效!!感觉有时候oracle优化器查询转换之类做的怪怪的.加入提示看看. sElect /*+ ELIM_GROUPBY(@"SEL$1") */ id1,id2,count(*) from tx group by id1,id2; --//一样无效,执行计划不再贴出!!