Скачать или смотреть Understanding the Oracle Hash Join and the Efficiency of Index Scans Over Partition Access

Explore how to optimize Oracle hash joins by utilizing index range scans over full partition access and understand the rationale behind the optimizer’s decisions.
---
This video is based on the question https://stackoverflow.com/q/69744115/ asked by the user 'Alex Bartsmon' ( https://stackoverflow.com/u/13605214/ ) and on the answer https://stackoverflow.com/a/69748949/ provided by the user 'Jon Heller' ( https://stackoverflow.com/u/409172/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Oracle Hash Join - Probe Table: Index over Partition?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Oracle Hash Join: Understanding Probe Tables and Index Scans

When dealing with large databases, particularly those that involve partitioning, understanding the optimizer's decisions can be a crucial aspect of performance tuning. A common question that arises is: Why might an index range scan with a partition column be more preferable to the optimizer than a full partition access when joining tables?

In this guide, we’ll delve into the underlying mechanics of Oracle hash joins and how partitioning, combined with the right indexing strategy, can impact the performance of your database queries.

The Scenario

Let’s set the stage: We have two tables, P (Parent) and C (Child), each with multiple partitions and subpartitions. Specifically, P has 10 partitions on the cat column and 316 subpartitions on the effective_date column. You might wonder why the optimizer would choose to perform an index range scan on the partition column rather than perform a full access scan on a partition, which seems like it would involve reading the same amount of blocks.

Dissecting the Problem

The seemingly counterintuitive choice made by the optimizer may stem from a mix of partitioning overhead and the actual data distribution in the tables. In databases with a myriad of subpartitions, some key points affect the optimizer's decision:

Wasted Space:

Often, a table may have thousands of subpartitions, yet contain very few rows. This can lead to a situation where there is significant empty space within those segments, ultimately inflating the cost of a full partition access.

Index Efficiency:

With an index in place (like IX_P_CAT on the cat column of table P), the optimizer can selectively fetch rows using the index, which is often cheaper than scanning large, sparsely populated partitions.

Understanding the Optimizer’s Choice

When the optimizer decides to use an index over a partition scan, consider the following points:

Cost of Full Scans: Full partition scans can become costly when segments have a lot of empty space. Even though it may seem that a full scan should read the same number of data blocks, the high overhead associated with reading empty segments can significantly affect performance.

Joining Large Data Sets: Since the tables involved may have a significant amount of data (275G rows in the scenario provided), an adaptive plan allows the optimizer to re-evaluate and choose options that may save time and resources.

The Explain Plans

Two explain plans provided show the stark differences in execution paths taken:

Index Used: The optimizer proposes using the index to build the hash table, allowing for a more efficient lookup of rows using ROWID instead of scanning empty segments.

Without Index: When explicitly advising the optimizer not to use the index, the performance slightly improves; however, the index still may provide a more efficient pathway by minimizing unnecessary reads from segments.

Suggested Solutions

To mitigate performance issues and to skew the optimizer's preference favorably, consider the following approaches:

Reduce Subpartitioning: Evaluate if the number of subpartitions can be reduced to cut down on space and complexity.

Adjust Segment Allocation Settings: Fine-tuning how Oracle allocates space in segments can lead to reduced overhead.

Shrink the Table: Utilize Oracle's built-in methods to enable row movement and shrink the space of the segments used:

[[See Video to Reveal this Text or Code Snippet]]

This ensures that the table takes only the amount of space necessary to store actual data, leading to improved access times.

Conclusion

In this competitive landscape of database management, understanding how to leverage indexing over pa