What partition-wise operations are new to Oracle Database 12c and 18c?

Beautifully suited for all your web-based needs

What partition-wise operations are new to Oracle Database 12c and 18c?

What partition-wise operations are new to Oracle Database 12c and 18c?

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.

What’s new?

Partition-wise processes are not new to Oracle Databases, but there have been some improvements with regards to this in both Oracle Database 12c and 18c. What are these?

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.

Example

These new partition-wise operations work the same way. An example would be helpful to show how it works in your 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.

No Comments

Post a Comment

Your email address will not be published.