Partition-wise operations are not something new. I do not remember when they were introduced, but at that time the release number was still a single digit. Anyway, the aim of this post is not to describe the basics, but only to describe what is new in that area in 12c and 18c.
The new features can be grouped in three categories:
- Partition-wise GROUP BY enhancements available as of version 12.2
- Partition-wise DISTINCT enhancements available as of version 12.2
- Partition-wise windowing functions enhancements available as of version 18.1
Before looking at the new features, here are the SQL statements I executed to create a partitioned table that I use through the examples. You can download the script here.
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;
/
Partition-wise GROUP BY (12.2)
The enhancements in this section are minimal. In fact, there are only two new hints: USE_PARTITION_WISE_GBY and NO_USE_PARTITION_WISE_GBY. You can use the former to enable the feature in case the query optimizer does not select it, and with the latter, you can do the opposite.
The following examples show, for the query SELECT n1, d1, sum(n2) FROM t GROUP BY n1, d1, serial/parallel execution plans with/without the partition-wise optimization. In the serial execution plans notice the placement of the HASH GROUP BY operation. In the parallel execution plans, notice the reduction of involved processes and data distribution.
serial
------------------------------------- | Id | Operation | Name | ------------------------------------- | 0 | SELECT STATEMENT | | | 1 | HASH GROUP BY | | | 2 | PARTITION RANGE ALL| | | 3 | PARTITION LIST ALL| | | 4 | TABLE ACCESS FULL| T | -------------------------------------
serial+pwise
------------------------------------- | Id | Operation | Name | ------------------------------------- | 0 | SELECT STATEMENT | | | 1 | PARTITION RANGE ALL | | | 2 | PARTITION LIST ALL | | | 3 | HASH GROUP BY | | | 4 | TABLE ACCESS FULL| T | -------------------------------------
parallel
-------------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) | | 3 | HASH GROUP BY | | Q1,01 | PCWP | | | 4 | PX RECEIVE | | Q1,01 | PCWP | | | 5 | PX SEND HASH | :TQ10000 | Q1,00 | P->P | HASH | | 6 | HASH GROUP BY | | Q1,00 | PCWP | | | 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | | 8 | TABLE ACCESS FULL| T | Q1,00 | PCWP | | --------------------------------------------------------------------------
parallel+pwise
------------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) | | 3 | PX PARTITION LIST ALL| | Q1,00 | PCWC | | | 4 | HASH GROUP BY | | Q1,00 | PCWP | | | 5 | TABLE ACCESS FULL | T | Q1,00 | PCWP | | -------------------------------------------------------------------------
Partition-wise DISTINCT (12.2)
The key enhancement of this section is the ability to execute a DISTINCT as a parallel partition-wise operation. In addition, there are two new hints: USE_PARTITION_WISE_DISTINCT and NO_USE_PARTITION_WISE_DISTINCT. You can use the former to enable the feature in case the query optimizer does not select it, and with the latter, you can do the opposite.
The following examples show, for the query SELECT DISTINCT n1, d1 FROM t, serial/parallel execution plans with/without the partition-wise optimization. In the serial execution plans notice the placement of the HASH UNIQUE operation. In the parallel execution plans, notice the reduction of involved processes and data distribution.
serial
------------------------------------- | Id | Operation | Name | ------------------------------------- | 0 | SELECT STATEMENT | | | 1 | HASH UNIQUE | | | 2 | PARTITION RANGE ALL| | | 3 | PARTITION LIST ALL| | | 4 | TABLE ACCESS FULL| T | -------------------------------------
serial+pwise
------------------------------------- | Id | Operation | Name | ------------------------------------- | 0 | SELECT STATEMENT | | | 1 | PARTITION RANGE ALL | | | 2 | PARTITION LIST ALL | | | 3 | HASH UNIQUE | | | 4 | TABLE ACCESS FULL| T | -------------------------------------
parallel
-------------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) | | 3 | HASH UNIQUE | | Q1,01 | PCWP | | | 4 | PX RECEIVE | | Q1,01 | PCWP | | | 5 | PX SEND HASH | :TQ10000 | Q1,00 | P->P | HASH | | 6 | HASH UNIQUE | | Q1,00 | PCWP | | | 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | | 8 | TABLE ACCESS FULL| T | Q1,00 | PCWP | | --------------------------------------------------------------------------
parallel+pwise
------------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) | | 3 | PX PARTITION LIST ALL| | Q1,00 | PCWC | | | 4 | HASH UNIQUE | | Q1,00 | PCWP | | | 5 | TABLE ACCESS FULL | T | Q1,00 | PCWP | | -------------------------------------------------------------------------
Partition-wise Windowing Functions (18.1)
The key enhancement of this section is the ability to execute a windowing function as a parallel partition-wise operation. In addition, there are two new hints: USE_PARTITION_WISE_WIF and NO_USE_PARTITION_WISE_WIF. You can use the former to enable the feature in case the query optimizer does not select it, and with the latter, you can do the opposite.
The following examples show, for the query SELECT n1, d1, avg(n2) OVER (PARTITION BY n1, d1) AS average FROM t, serial/parallel execution plans with/without the partition-wise optimization. Notice that the serial execution plan without the optimization is missing because either I did not correctly understand how to use the NO_USE_PARTITION_WISE_WIF hint (as it too often happens, no documentation about it is provided) or it does not work (bug?) for the serial execution plan. In the parallel execution plans, notice the reduction of involved processes and data distribution.
serial+pwise
------------------------------------- | Id | Operation | Name | ------------------------------------- | 0 | SELECT STATEMENT | | | 1 | PARTITION RANGE ALL | | | 2 | PARTITION LIST ALL | | | 3 | WINDOW SORT | | | 4 | TABLE ACCESS FULL| T | -------------------------------------
parallel
------------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) | | 3 | WINDOW SORT | | Q1,01 | PCWP | | | 4 | PX RECEIVE | | Q1,01 | PCWP | | | 5 | PX SEND HASH | :TQ10000 | Q1,00 | P->P | HASH | | 6 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | | 7 | TABLE ACCESS FULL| T | Q1,00 | PCWP | | -------------------------------------------------------------------------
parallel+pwise
------------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) | | 3 | PX PARTITION LIST ALL| | Q1,00 | PCWC | | | 4 | WINDOW SORT | | Q1,00 | PCWP | | | 5 | TABLE ACCESS FULL | T | Q1,00 | PCWP | | -------------------------------------------------------------------------
All in all, those are good features that can not only make some operation faster, but also reduce the number of involved processes in case the database engine uses parallel execution.
Very interesting stuff, Christian.
Thanks for the heads-up on this, very useful.
great post. looking forward to that new edition of TOP! also, love the way you formatted the output of each example. that makes it a lot easier to see the differences imo.
Do you know if USE_PARTITION_WISE_WIF work for MATCH_RECOGNIZE clause ?
In 12.2 optimiser seems to be doing partition wise match recognize if degree of paralelism is half the number of hash partition, but I did not find any hint to force it.
Very usefull post.
Looking forword at TOP 3 :-D