The beauty of working with Oracle Database is that the features do not stop coming and you have access to new features and functionalities with each update of the world’s most famous database.
A lot has been said about partition-wise joins, an operation that would join two tables that are located in the same column using a similar partitioning scheme. It is considered to be the hallmark of a shared nothing system. But there are other partition-wise operations out there.
With the release of Oracle Database 12c and 18c, there are additions and enhancements for partition-wise operations.
New to Oracle Databases?
Partition-wise processes are not new to Oracle Databases, but there have been some improvements to both Oracle Database 12c and 18c. What are these updates?
Partition-Wise GROUP BY
There are two new GROUP BY enhancements that you can use with version 12.2:
- USE_PARTITION_WISE_GBY
- NO_USE_PARTITION_WISE_GBY
You can apply these to either enable or disable this particular feature in the event that query optimizer will not choose it.
Partition-wise group by enhancement places the hash group after the partition range and list operations in serial mode. In parallel executions, you get fewer processes when the group by operation is invoked.
Partition-wise DISTINCT
Under this category, you have the most important enhancement, which allows you to run a DISTINCT in a parallel partition-wise operation.
You also have these operations:
- USE_PARTITION_WISE_DISTINCT
- NO_USE_PARTITION_WISE_DISTINCT
Like the Group By operations, these operations enable the partition-wise distinct feature in the event that query optimizer will not select it; and vice versa. It also affects serial and parallel executions in the same way.
Partition-Wise Windowing Functions
There are important enhancements that were included in Oracle Database 18c (version 18.1), such as allowing you to run a windowing function as a partition-wise operation running in parallel.
There are two hints that were introduced in version 18.1:
- USE_PARTITION_WISE_WIF
- NO_USE_PARTITION_WISE_WIF
This tells your database to enable partition-wise WIF if it is not chosen by the query optimizer; and vice versa.
There are talks that this might not be available for serial executions, and works only with parallel executions. As with the earlier partition-wise operations, including this partition-wise operation will reduce the number of processes used. It will also alter data distribution.
For Example…
These new partition-wise operations work the same way. An example would be helpful to show how it works in your Oracle Database.
In serial operation plans, it would look like this:
————————————-
| Id | Operation | Name |
————————————-
| 0 | SELECT STATEMENT | |
| 1 | HASH UNIQUE | |
| 2 | PARTITION RANGE ALL| |
| 3 | PARTITION LIST ALL| |
| 4 | TABLE ACCESS FULL| X |
————————————-
With partition-wise distinct, however, it would look like this:
————————————-
| Id | Operation | Name |
————————————-
| 0 | SELECT STATEMENT | |
| 1 | PARTITION RANGE ALL | |
| 2 | PARTITION LIST ALL | |
| 3 | HASH UNIQUE | |
| 4 | TABLE ACCESS FULL| X |
————————————-
Take note how the Hash Unique operation is placed after the partition operations. The differences are more pronounced with parallel executions where you have fewer processes and reduced data distribution.
Photo courtesy of Oracle.
