Data Blog

Partition-Wise Functions: Oracle Database 12c and 18c

A sales figure showing statistics from data for every quarter from 1999 to 2000

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.

Uncategorized

Security Trends For 2024: SBOMs

A recent article on Forbes, linked below, lists five security trends that are likely to be important in 2024. Of course, AI-related security topics top...

Keep Reading

Uncategorized

Getting started with your first…

Anyone looking at writing their first Generative AI application should read this to save time. In June 2023, Google Cloud published a useful blog post,...

Keep Reading

Uncategorized

Read-Write and Read-Only query splitting…

Optimize the usage of the standby (secondary) read-only MySQL InnoDB Cluster servers by automatically and transparently distributing read-queries to them. A great feature in MySQL...

Keep Reading

Uncategorized

Considering Cloud Diversification?

Moving an enterprise to a Public Cloud can quickly feel like moving into a locked-in relationship with the Cloud vendor. One solution to diminish this...

Keep Reading

Uncategorized

Gartner: Global cloud consumption will…

Gartner forecasts that 2024 will see a 20% global cloud consumption increase from 2023 to $679B, while 2023 has seen an 18% increase from 2022...

Keep Reading

Uncategorized

ByteDance: Use AI for tuning…

ZDNet is reporting that a ByteDance (the maker of TikTok) Linux kernel developer has proposed to implement AI to tune the performance of Linux systems...

Keep Reading

AI Governance

What is AI TRiSM?

If reading now about AI TRiSM for the first time, it might be good read on and learn what it’s about because it leads the...

Keep Reading

Cloud Blog

4 Ways To Benefit from…

One of the benefits you get when you work with cloud applications is that you often have quarterly updates that are packed with features. This...

Keep Reading

Artificial Intelligence

Data and Analytics: Cross the…

  Artificial intelligence is a manna sent from digital heaven. That’s how blessed your business can get if you immerse into the AI of things....

Keep Reading

Business Intelligence Blog

How Brand Names Survive in…

  The age of digital marketplace has made it possible for unknown and smaller companies to compete with better-known and well-established brands. Take for example...

Keep Reading

Live Chat | Emergency