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.

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

Artificial Intelligence

The Phenomenon That Is Artificial…

  Artificial intelligence is when a machine does cognitive functions that are more associated with humans, such as thinking, learning, problem solving, and reasoning. As...

Keep Reading

Cloud

Digital Transformation in Banking: Shift…

  The start of a new decade is already shaping up to be exciting for financial services, especially for banks as they continue to compete...

Keep Reading

Business Intelligence Blog

Data Science and Its Economic…

It would seem that artificial intelligence is the focus of businesses that are looking to future proof their organizations and stay competitive. And why not?...

Keep Reading

Data Blog

Ensure Efficient Data Science and…

In an ever-expanding landscape called Internet of Things and the exploding development of artificial intelligence, we are bombarded with complex methods of integrating data science...

Keep Reading

Business Intelligence Blog

Cybersecurity: The Top 5 Expectations…

  If you think about it, cybersecurity is closely tied to human rights, privacy, freedom, and even basic safety. As a whole, we have become...

Keep Reading

Business Intelligence Blog

Tech Security Should Be Easy…

  IT professionals know that tech security is important in everything that they do. And that there are a lot of products, tools, innovations, and...

Keep Reading

Business Intelligence Blog

The Top 6 Success Stories…

Nearly nine out of 10 digital transformation initiatives fail. Digital transformation projects fail for a variety of reasons. It can be because of a lack...

Keep Reading

Live Chat | Emergency