[20190624]12c group by优化 .txt

最新推荐文章于 2024-07-24 04:15:11 发布

转载最新推荐文章于 2024-07-24 04:15:11 发布 · 150 阅读

[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;
--//一样无效,执行计划不再贴出!!