12c에 반복 표현식에 대한 성능 개선 기능이 추가된 것으로 보입니다.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다.
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1) AS SELECT DATE '2050-01-01' FROM XMLTABLE ('1 to 1000000');
아래 쿼리는 TO_CHAR (c1, 'MM') 표현식을 12번 반복하여 사용합니다. 11.2 버전에서 수행에 1.56초가 소요됩니다.
-- 2: 11.2
SELECT SUM (DECODE (TO_CHAR (c1, 'MM'), '01', 1)) AS c01
, SUM (DECODE (TO_CHAR (c1, 'MM'), '02', 1)) AS c02
, SUM (DECODE (TO_CHAR (c1, 'MM'), '03', 1)) AS c03
, SUM (DECODE (TO_CHAR (c1, 'MM'), '04', 1)) AS c04
, SUM (DECODE (TO_CHAR (c1, 'MM'), '05', 1)) AS c05
, SUM (DECODE (TO_CHAR (c1, 'MM'), '06', 1)) AS c06
, SUM (DECODE (TO_CHAR (c1, 'MM'), '07', 1)) AS c07
, SUM (DECODE (TO_CHAR (c1, 'MM'), '08', 1)) AS c08
, SUM (DECODE (TO_CHAR (c1, 'MM'), '09', 1)) AS c09
, SUM (DECODE (TO_CHAR (c1, 'MM'), '10', 1)) AS c10
, SUM (DECODE (TO_CHAR (c1, 'MM'), '11', 1)) AS c11
, SUM (DECODE (TO_CHAR (c1, 'MM'), '12', 1)) AS c12
FROM t1;
-------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |00:00:01.56 | 1804 |
| 1 | SORT AGGREGATE | | 1 |00:00:01.56 | 1804 |
| 2 | TABLE ACCESS FULL| T1 | 1000K|00:00:00.05 | 1804 |
-------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) SUM(DECODE(TO_CHAR(INTERNAL_FUNCTION("C1"),'MM'),'12',1))[22],
SUM(DECODE(TO_CHAR(INTERNAL_FUNCTION("C1"),'MM'),'11',1))[22],
SUM(DECODE(TO_CHAR(INTERNAL_FUNCTION("C1"),'MM'),'10',1))[22],
SUM(DECODE(TO_CHAR(INTERNAL_FUNCTION("C1"),'MM'),'09',1))[22],
SUM(DECODE(TO_CHAR(INTERNAL_FUNCTION("C1"),'MM'),'08',1))[22],
SUM(DECODE(TO_CHAR(INTERNAL_FUNCTION("C1"),'MM'),'07',1))[22],
SUM(DECODE(TO_CHAR(INTERNAL_FUNCTION("C1"),'MM'),'06',1))[22],
SUM(DECODE(TO_CHAR(INTERNAL_FUNCTION("C1"),'MM'),'05',1))[22],
SUM(DECODE(TO_CHAR(INTERNAL_FUNCTION("C1"),'MM'),'04',1))[22],
SUM(DECODE(TO_CHAR(INTERNAL_FUNCTION("C1"),'MM'),'03',1))[22],
SUM(DECODE(TO_CHAR(INTERNAL_FUNCTION("C1"),'MM'),'02',1))[22],
SUM(DECODE(TO_CHAR(INTERNAL_FUNCTION("C1"),'MM'),'01',1))[22]
2 - "C1"[DATE,7]
아래 쿼리는 TO_CHAR (c1, 'MM') 표현식을 반복 사용하도록 병합되지 않는 인라인 뷰에 표현식을 사용합니다. 11.2 버전에서 수행에 0.63초가 소요됩니다.
-- 3: 11.2
SELECT SUM (DECODE (mm, '01', 1)) AS c01
, SUM (DECODE (mm, '02', 1)) AS c02
, SUM (DECODE (mm, '03', 1)) AS c03
, SUM (DECODE (mm, '04', 1)) AS c04
, SUM (DECODE (mm, '05', 1)) AS c05
, SUM (DECODE (mm, '06', 1)) AS c06
, SUM (DECODE (mm, '07', 1)) AS c07
, SUM (DECODE (mm, '08', 1)) AS c08
, SUM (DECODE (mm, '09', 1)) AS c09
, SUM (DECODE (mm, '10', 1)) AS c10
, SUM (DECODE (mm, '11', 1)) AS c11
, SUM (DECODE (mm, '12', 1)) AS c12
FROM (SELECT /*+ NO_MERGE */ TO_CHAR (c1, 'MM') AS mm FROM t1);
--------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |00:00:00.63 | 1804 |
| 1 | SORT AGGREGATE | | 1 |00:00:00.63 | 1804 |
| 2 | VIEW | | 1000K|00:00:00.29 | 1804 |
| 3 | TABLE ACCESS FULL| T1 | 1000K|00:00:00.06 | 1804 |
--------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) SUM(DECODE("MM",'12',1))[22], SUM(DECODE("MM",'11',1))[22], SUM(DECODE("MM",'10',1))[22],
SUM(DECODE("MM",'09',1))[22], SUM(DECODE("MM",'08',1))[22], SUM(DECODE("MM",'07',1))[22],
SUM(DECODE("MM",'06',1))[22], SUM(DECODE("MM",'05',1))[22], SUM(DECODE("MM",'04',1))[22],
SUM(DECODE("MM",'03',1))[22], SUM(DECODE("MM",'02',1))[22], SUM(DECODE("MM",'01',1))[22]
2 - "MM"[VARCHAR2,2]
3 - "C1"[DATE,7]
앞서 수행한 두 쿼리를 12.2 버전에서 수행하면 0.23초로 동일한 시간이 소요되는 것을 확인할 수 있습니다.
-- 4-1: 12.2
-------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |00:00:00.23 | 1819 |
| 1 | SORT AGGREGATE | | 1 |00:00:00.23 | 1819 |
| 2 | TABLE ACCESS FULL| T1 | 1000K|00:00:00.01 | 1819 |
-------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) SUM(DECODE(TO_CHAR(INTERNAL_FUNCTION("C1"),'MM'),'12',1))[22],
SUM(DECODE(TO_CHAR(INTERNAL_FUNCTION("C1"),'MM'),'11',1))[22],
SUM(DECODE(TO_CHAR(INTERNAL_FUNCTION("C1"),'MM'),'10',1))[22],
SUM(DECODE(TO_CHAR(INTERNAL_FUNCTION("C1"),'MM'),'09',1))[22],
SUM(DECODE(TO_CHAR(INTERNAL_FUNCTION("C1"),'MM'),'08',1))[22],
SUM(DECODE(TO_CHAR(INTERNAL_FUNCTION("C1"),'MM'),'07',1))[22],
SUM(DECODE(TO_CHAR(INTERNAL_FUNCTION("C1"),'MM'),'06',1))[22],
SUM(DECODE(TO_CHAR(INTERNAL_FUNCTION("C1"),'MM'),'05',1))[22],
SUM(DECODE(TO_CHAR(INTERNAL_FUNCTION("C1"),'MM'),'04',1))[22],
SUM(DECODE(TO_CHAR(INTERNAL_FUNCTION("C1"),'MM'),'03',1))[22],
SUM(DECODE(TO_CHAR(INTERNAL_FUNCTION("C1"),'MM'),'02',1))[22],
SUM(DECODE(TO_CHAR(INTERNAL_FUNCTION("C1"),'MM'),'01',1))[22]
2 - (rowset=256) "C1"[DATE,7]
-- 4-2: 12.2
--------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |00:00:00.23 | 1819 |
| 1 | SORT AGGREGATE | | 1 |00:00:00.23 | 1819 |
| 2 | VIEW | | 1000K|00:00:00.07 | 1819 |
| 3 | TABLE ACCESS FULL| T1 | 1000K|00:00:00.01 | 1819 |
--------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) SUM(DECODE("MM",'12',1))[22], SUM(DECODE("MM",'11',1))[22], SUM(DECODE("MM",'10',1))[22],
SUM(DECODE("MM",'09',1))[22], SUM(DECODE("MM",'08',1))[22], SUM(DECODE("MM",'07',1))[22],
SUM(DECODE("MM",'06',1))[22], SUM(DECODE("MM",'05',1))[22], SUM(DECODE("MM",'04',1))[22],
SUM(DECODE("MM",'03',1))[22], SUM(DECODE("MM",'02',1))[22], SUM(DECODE("MM",'01',1))[22]
2 - (rowset=256) "MM"[VARCHAR2,2]
3 - (rowset=256) "C1"[DATE,7]