[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;
--//一樣無效,執行計劃不再貼出!!

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2648615/，如需轉載，請註明出處，否則將追究法律責任。