Single-row subqueries in Oracle -- what is the join plan?

Question

I've just discovered that Oracle lets you do the following:

SELECT foo.a, (SELECT c 
               FROM bar 
               WHERE foo.a = bar.a) 
from foo

As long as only one row in bar matches any row in foo.

The explain plan I get from PL/SQL developer is this:

SELECT STATEMENT, GOAL = ALL_ROWS      
 TABLE ACCESS FULL  BAR 
 TABLE ACCESS FULL  FOO

This doesn't actually specify how the tables are joined. A colleague asserted that this is more efficient than doing a regular join. Is that true? What is the join strategy on such a select statement, and why doesn't it show up in the explain plan?

Thanks.

Answer 1

The plan you have there does not provide much information at all.

Use SQL*Plus and use dbms_xplan to get a more detailed plan. Look for a script called utlxpls.sql.

This gives a bit more information:-

--------------------------------------------------------------------------
| 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.

I didn't create any indexes or foreign keys or collect statistics on the tables, which would change the plan and the join mechanism choosen. Oracle is actually doing a NESTED LOOPS type join here. Step 1, your inline sub-select, is performed for every row returned from FOO.

This way of performing a SELECT is not quicker. It could be the same or slower. In general try and join everything in the main WHERE clause unless it becomes horribly unreadable.

Answer 2

If you create a normal index on bar(a) the CBO should be able to use, but I'm pretty sure that it won't be able to do hash joins. These kind of queries only make sense if you're using an aggregate function and you got multiple single-row subqueries in your top SELECT. Even so, you can always rewrite the query as:

SELECT foo.a, bar1.c, pub1.d
FROM foo
JOIN (SELECT a, MIN(c) as c
      FROM bar
      GROUP BY a) bar1
  ON foo.a = bar1.a
JOIN (SELECT a, MAX(d) as d
      FROM pub
      GROUP BY a) pub1
  ON foo.a = pub1.a

This would enable the CBO to use more options, while at the same time it would enable you to easily retrieve multiple columns from the child tables without having to scan the same tables multiple times.