SET ECHO OFF
REM ***************************************************************************
REM ***************************** T R I V A D I S *****************************
REM ****************** Oracle Database 12c/18c New Features *******************
REM ************************ http://www.trivadis.com **************************
REM ***************************************************************************
REM
REM Privileges..: DBA
REM File name...: pwise_aggregations.sql
REM Author......: Christian Antognini
REM Date........: May 2018
REM Description.: This script shows examples of partition-wise operations.
REM Notes.......: -
REM Parameters..: -
REM Requirements: Oracle Database 18c Enterprise Edition
REM
REM Changes:
REM DD.MM.YYYY Author Description
REM ---------------------------------------------------------------------------
REM 
REM ***************************************************************************

SET TERMOUT ON
SET FEEDBACK OFF
SET SERVEROUTPUT OFF
SET LINESIZE 140
SET PAGESIZE 100

@connect.sql

SET ECHO ON

REM
REM Setup test environment
REM

SET TERMOUT OFF
DROP TABLE t;
SET TERMOUT ON

CREATE TABLE t (
  id NUMBER,
  d1 DATE,
  n1 NUMBER,
  n2 NUMBER,
  n3 NUMBER,
  pad VARCHAR2(4000),
  CONSTRAINT t_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (d1)
SUBPARTITION BY LIST (n1)
SUBPARTITION TEMPLATE (
  SUBPARTITION sp_1 VALUES (1),
  SUBPARTITION sp_2 VALUES (2),
  SUBPARTITION sp_3 VALUES (3),
  SUBPARTITION sp_4 VALUES (4)
)(
  PARTITION t_q1_2018 VALUES LESS THAN (to_date('2018-04-01 00:00:00','YYYY-MM-DD HH24:MI:SS')),
  PARTITION t_q2_2018 VALUES LESS THAN (to_date('2018-07-01 00:00:00','YYYY-MM-DD HH24:MI:SS')),
  PARTITION t_q3_2018 VALUES LESS THAN (to_date('2018-10-01 00:00:00','YYYY-MM-DD HH24:MI:SS')),
  PARTITION t_q4_2018 VALUES LESS THAN (to_date('2019-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))
);

INSERT INTO t
SELECT rownum AS id,
       trunc(to_date('2018-01-01','YYYY-MM-DD')+rownum/27.4) AS d1,
       1+mod(rownum,4) AS n1,
       rownum AS n2,
       rownum AS n3,
       rpad('*',100,'*') AS pad
FROM dual
CONNECT BY level <= 10000;

BEGIN
  dbms_stats.gather_table_stats(
    ownname          => user,
    tabname          => 'T'
  );
END;
/

ALTER SESSION SET parallel_degree_policy = manual;

REM
REM GROUP BY (12.2 required - only the (no_)use_partition_wise_gby hints are new)
REM

REM Serial plans without/with pwise gby

EXPLAIN PLAN FOR SELECT /*+ no_use_partition_wise_gby(@sel$1) */ n1, d1, sum(n2) FROM t GROUP BY n1, d1;

SELECT * FROM table(dbms_xplan.display(format=>'basic outline'));

PAUSE

EXPLAIN PLAN FOR SELECT /*+ use_partition_wise_gby(@sel$1) */ n1, d1, sum(n2) FROM t GROUP BY n1, d1;

SELECT * FROM table(dbms_xplan.display(format=>'basic outline'));

PAUSE

REM Parallel plans without/with pwise gby

EXPLAIN PLAN FOR SELECT /*+ parallel(2) no_use_partition_wise_gby(@sel$1) */ n1, d1, sum(n2) FROM t GROUP BY n1, d1;

SELECT * FROM table(dbms_xplan.display(format=>'basic parallel outline'));

PAUSE

EXPLAIN PLAN FOR SELECT /*+ parallel(2) use_partition_wise_gby(@sel$1) */ n1, d1, sum(n2) FROM t GROUP BY n1, d1;

SELECT * FROM table(dbms_xplan.display(format=>'basic parallel outline'));

PAUSE

REM Serial plans with pwise gby, only part of the partition key used

EXPLAIN PLAN FOR SELECT /*+ use_partition_wise_gby(@sel$1) */ n1, sum(n2) FROM t GROUP BY n1;

SELECT * FROM table(dbms_xplan.display(format=>'basic outline'));

PAUSE

EXPLAIN PLAN FOR SELECT /*+ use_partition_wise_gby(@sel$1) */ d1, sum(n2) FROM t GROUP BY d1;

SELECT * FROM table(dbms_xplan.display(format=>'basic outline'));

PAUSE

REM Parallel plans with pwise gby, only part of the partition key used

EXPLAIN PLAN FOR SELECT /*+ parallel(2) use_partition_wise_gby(@sel$1) */ n1, sum(n2) FROM t GROUP BY n1;

SELECT * FROM table(dbms_xplan.display(format=>'basic parallel outline'));

PAUSE

EXPLAIN PLAN FOR SELECT /*+ parallel(2) use_partition_wise_gby(@sel$1) */ d1, sum(n2) FROM t GROUP BY d1;

SELECT * FROM table(dbms_xplan.display(format=>'basic parallel outline'));

PAUSE

REM
REM DISTINCT (12.2 required - parallel execution and (no_)use_partition_wise_distinct are new)
REM

REM Serial plans without/with pwise distinct

EXPLAIN PLAN FOR SELECT /*+ no_use_partition_wise_distinct(@sel$1) */ DISTINCT n1, d1 FROM t;

SELECT * FROM table(dbms_xplan.display(format=>'basic outline'));

PAUSE

EXPLAIN PLAN FOR SELECT /*+ use_partition_wise_distinct(@sel$1) */ DISTINCT n1, d1 FROM t;

SELECT * FROM table(dbms_xplan.display(format=>'basic outline'));

PAUSE

REM Parallel plans without/with pwise distinct

EXPLAIN PLAN FOR SELECT /*+ parallel(2) no_use_partition_wise_distinct(@sel$1) */ DISTINCT n1, d1 FROM t;

SELECT * FROM table(dbms_xplan.display(format=>'basic parallel outline'));

PAUSE

EXPLAIN PLAN FOR SELECT /*+ parallel(2) use_partition_wise_distinct(@sel$1) */ DISTINCT n1, d1 FROM t;

SELECT * FROM table(dbms_xplan.display(format=>'basic parallel outline'));

PAUSE

REM Parallel plans with pwise distinct, only part of the partition key used

EXPLAIN PLAN FOR SELECT /*+ parallel(2) use_partition_wise_distinct(@sel$1) */ DISTINCT n1 FROM t;

SELECT * FROM table(dbms_xplan.display(format=>'basic parallel outline'));

PAUSE

EXPLAIN PLAN FOR SELECT /*+ parallel(2) use_partition_wise_distinct(@sel$1) */ DISTINCT d1 FROM t;

SELECT * FROM table(dbms_xplan.display(format=>'basic parallel outline'));

PAUSE

REM
REM Windowing functions - Example 1 (18.1 required - parallel execution and (no_)use_partition_wise_wif hints are new)
REM

REM Serial plans without/with pwise wif, no_use_partition_wise_wif hint not honored!

EXPLAIN PLAN FOR SELECT /*+ no_use_partition_wise_wif(@sel$1) */ n1, d1, avg(n2) OVER (PARTITION BY n1, d1) AS average FROM t;

SELECT * FROM table(dbms_xplan.display(format=>'basic outline'));

PAUSE

EXPLAIN PLAN FOR SELECT /*+ use_partition_wise_wif(@sel$1 1) */ n1, d1, avg(n2) OVER (PARTITION BY n1, d1) AS average FROM t;

SELECT * FROM table(dbms_xplan.display(format=>'basic outline'));

PAUSE

REM Parallel without/with pwise wif

EXPLAIN PLAN FOR SELECT /*+ parallel(2) no_use_partition_wise_wif(@sel$1) */ n1, d1, avg(n2) OVER (PARTITION BY n1, d1) AS average FROM t;

SELECT * FROM table(dbms_xplan.display(format=>'basic parallel outline'));

PAUSE

EXPLAIN PLAN FOR SELECT /*+ parallel(2) use_partition_wise_wif(@sel$1 1) */ n1, d1, avg(n2) OVER (PARTITION BY n1, d1) AS average FROM t;

SELECT * FROM table(dbms_xplan.display(format=>'basic parallel outline'));

PAUSE

REM
REM Windowing functions - Example 2 (18.1 required - parallel execution and (no_)use_partition_wise_wif hints are new)
REM

REM Serial plans without/with pwise wif, no_use_partition_wise_wif hint not honored!

EXPLAIN PLAN FOR 
SELECT /*+ no_use_partition_wise_wif(@sel$1) */ 
       n1, d1, sum(n2) OVER (PARTITION BY n1, d1 
                             ORDER BY n1, d1, id 
                             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS tot 
FROM t;

SELECT * FROM table(dbms_xplan.display(format=>'basic outline'));

PAUSE

EXPLAIN PLAN FOR 
SELECT /*+ use_partition_wise_wif(@sel$1 1) */ 
       n1, d1, sum(n2) OVER (PARTITION BY n1, d1 
                             ORDER BY n1, d1, id 
                             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS tot 
FROM t;

SELECT * FROM table(dbms_xplan.display(format=>'basic outline'));

PAUSE

EXPLAIN PLAN FOR 
SELECT /*+ parallel(2) no_use_partition_wise_wif(@sel$1) */ 
       n1, d1, sum(n2) OVER (PARTITION BY n1, d1 
                             ORDER BY n1, d1, id 
                             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS tot 
FROM t;

SELECT * FROM table(dbms_xplan.display(format=>'basic parallel outline'));

PAUSE

EXPLAIN PLAN FOR 
SELECT /*+ parallel(2) use_partition_wise_wif(@sel$1 1) */ 
       n1, d1, sum(n2) OVER (PARTITION BY n1, d1 
                             ORDER BY n1, d1, id 
                             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS tot 
FROM t;

SELECT * FROM table(dbms_xplan.display(format=>'basic parallel outline'));

PAUSE

REM
REM Clean up
REM

DROP TABLE t PURGE;
