Скачать или смотреть Understanding Single-row Subqueries in Oracle: Analyzing Join Plans

Discover how to effectively utilize single-row subqueries in Oracle and understand their join plans through detailed analysis and practical examples.
---
This video is based on the question https://stackoverflow.com/q/180518/ asked by the user 'SquareCog' ( https://stackoverflow.com/u/15962/ ) and on the answer https://stackoverflow.com/a/180588/ provided by the user 'WW.' ( https://stackoverflow.com/u/14663/ ) 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, comments, revision history etc. For example, the original title of the Question was: Single-row subqueries in Oracle -- what is the join plan?

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 2.5' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 2.5' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding Single-row Subqueries in Oracle: Analyzing Join Plans

In the world of databases, particularly when working with Oracle, you might come across single-row subqueries. But what exactly does this mean, and how does it impact the way data is fetched from tables? Here, we will unravel the concept of single-row subqueries and delve deep into the join plans associated with them, particularly through an example that demonstrates the select statement's behavior when utilizing inline sub-selects.

The Problem: A Question on Join Strategy

Consider the following SQL query that features a single-row subquery:

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

This query pulls data from two tables – foo and bar – with the expectation that for each row in foo, there's a direct match in bar. A colleague mentioned that this approach is more efficient than traditional joins, raising some important questions: Is that true? How does Oracle determine the join strategy here? And why is there limited information about the join strategy in the initial explain plan?

Understanding the Join Plan

The initial explain plan you receive may not shed much light on the join strategy. For instance, the plan you got looks like this:

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

This entry indicates that it is conducting a full table access for both bar and foo. However, it doesn't elaborate on how these tables are being joined.

Getting a Detailed Plan

To gain more insight into the join operations taking place behind the scenes, you can utilize SQL*Plus along with the DBMS_XPLAN package. A helpful script called utlxpls.sql can be run to provide a deeper analysis of the execution plan.

When you execute the enhanced analysis, you might see something like:

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



| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1837 | 23881 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| BAR | 18 | 468 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| FOO | 1837 | 23881 | 3 (0)| 00:00:01 |

Predicate Information (identified by operation id):

1 - filter("BAR"."A"=:B1)
Note

dynamic sampling used for this statement

18 rows selected.

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