Home > Oracle, SQL > Common errors seen when using OUTER JOIN: Join to a Constant

Common errors seen when using OUTER JOIN: Join to a Constant


Tom Kyte published an interesting OUTER JOIN example in the March/April 2011 issue of Oracle Magazine in which a three-way join (including one outer join) was equivalent to querying a single table. Here are the commands to set up the example:

--Tables

create table t1 (id number(18) not null, data varchar2(1000));
create table t2 (s_id number(18) not null, id number(18) not null, data varchar2(1000));
create table t3 (s_id number(18), related_s_id number(18), data varchar2(1000));

-- Primary key constraints

alter table t1 add constraint t1_pk1 primary key (id);
alter table t2 add constraint t2_pk1 primary key (s_id);
alter table t3 add constraint t3_order_pk1 primary key (s_id);

-- Foreign key constraints

alter table t2 add constraint t2_oso_fk1 foreign key (id) references t1 (id);
alter table t3 add constraint t3_ols_s_fk1 foreign key (s_id) references t2 (s_id);

-- Indexes

create index t3_ols_rs_1 on t3 (related_s_id);

-- Sample data

insert into t1 (id) values (1);
insert into t2 (s_id, id) values (1,1);
insert into t2 (s_id, id) values (2,1);
insert into t3 (s_id, related_s_id) values (1,1);
commit;

-- Optimizer statistics

exec dbms_stats.gather_table_stats('&&user', 'T1');
exec dbms_stats.gather_table_stats('&&user', 'T2');
exec dbms_stats.gather_table_stats('&&user', 'T3');

Here is the query and the query plan from an 11g Release 2 database:

SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 5 15:47:17 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> variable v0 varchar2(32);
SQL> exec :v0 := '1';

PL/SQL procedure successfully completed.

SQL> select count(*)
  2  from t1, t2, t3
  3  where t2.id = t1.id
  4  and t2.s_id = t3.s_id (+)
  5  and t3.related_s_id = to_number(:v0);

  COUNT(*)
----------
         1

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'typical iostats last +peeked_binds'));

PLAN_TABLE_OUTPUT

SQL_ID  04s5t0n3va8y1, child number 0
-------------------------------------
select count(*) from t1, t2, t3 where t2.id = t1.id and t2.s_id =
t3.s_id (+) and t3.related_s_id = to_number(:v0)

Plan hash value: 3544670422

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |      1 |        |       |     1 (100)|          |      1 |00:00:00.01 |       1 |
|   1 |  SORT AGGREGATE   |             |      1 |      1 |     6 |            |          |      1 |00:00:00.01 |       1 |
|*  2 |   INDEX RANGE SCAN| T3_OLS_RS_1 |      1 |      1 |     6 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - (VARCHAR2(30), CSID=178): '1'

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T3"."RELATED_S_ID"=TO_NUMBER(:V0))

The reason why there is only one table mentioned in the query plan is that the optimizer first converted the outer join into a join (and then eliminated two tables from the query using the join elimination technique.) Tom explains:

Whenever you see a construct such as

AND T2.s_id = T3.s_id (+) AND T3.related_s_id = TO_NUMBER(:v0);

you should know automatically that it can (and should!) be coded as follows:

AND T2.s_id = T3.s_id AND T3.related_s_id = TO_NUMBER(:v0);

The reason is quite simple: if the outer join from T2 to T3 were necessary and you in fact made up a row in T3 to join to T2 (that is what an outer join does if there is no matching data in T3 for T2), T3.related_s_id would be NULL, as would all the attributes of T3 for that record. And because NULL is never equal to anything, you know that the last bit of the predicate could never be true. Therefore, the outer join is not needed and the only thing it does by being there is make the coder look bad!

Fortunately, however, the cost-based optimizer is generally smart enough to figure that out itself and will have already removed the outer join.

However, it is possible that the developer made the common “Join to a Constant” error described in the article on outer joins by Kevin Meade, the other common errors being the “Half Baked Multi-Column Join” and the “Incomplete Join Trail.” The SQL Reference manual warns that “if A and B are joined by multiple join conditions, then you must use the (+) operator in all of these conditions. If you do not, then Oracle Database will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join.”

It is possible that the developer actually meant to write:

select count(*)
from t1, t2, t3
where t2.id = t1.id
and t2.s_id = t3.s_id (+)
and t3.related_s_id (+) = to_number(:v0);

Here is the resulting query plan; this time Oracle eliminated only one of the two joins:

SQL> select count(*)
  2  from t1, t2, t3
  3  where t2.id = t1.id
  4  and t2.s_id = t3.s_id (+)
  5  and t3.related_s_id (+) = to_number(:v0);

  COUNT(*)
----------
         2

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'typical iostats last +peeked_binds'));

PLAN_TABLE_OUTPUT

SQL_ID  fsgrfn69wcn2h, child number 0
-------------------------------------
select count(*) from t1, t2, t3 where t2.id = t1.id and t2.s_id =
t3.s_id (+) and t3.related_s_id (+) = to_number(:v0)

Plan hash value: 2437859219

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       4 |
|   1 |  SORT AGGREGATE               |              |      1 |      1 |     9 |            |          |      1 |00:00:00.01 |       4 |
|   2 |   NESTED LOOPS OUTER          |              |      1 |      2 |    18 |     2   (0)| 00:00:01 |      2 |00:00:00.01 |       4 |
|   3 |    INDEX FULL SCAN            | T2_PK1       |      1 |      2 |     6 |     1   (0)| 00:00:01 |      2 |00:00:00.01 |       1 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| T3           |      2 |      1 |     6 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|*  5 |     INDEX UNIQUE SCAN         | T3_ORDER_PK1 |      2 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - (VARCHAR2(30), CSID=178): '1'

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("T3"."RELATED_S_ID"=TO_NUMBER(:V0))
   5 - access("T2"."S_ID"="T3"."S_ID")

The developer used the traditional Oracle outer join syntax which is less powerful that the ANSI syntax. Here is an ANSI version of the above query:

SQL> select count(*)
  2  from t1
  3  join t2 on t2.id = t1.id
  4  left outer join t3 on t2.s_id = t3.s_id and t3.related_s_id = to_number(:v0);

  COUNT(*)
----------
         2

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'typical iostats last +peeked_binds'));

PLAN_TABLE_OUTPUT

SQL_ID  3un42jjr4hj6s, child number 0
-------------------------------------
select count(*) from t1 join t2 on t2.id = t1.id left outer join t3 on
t2.s_id = t3.s_id and t3.related_s_id = to_number(:v0)

Plan hash value: 2437859219

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       4 |
|   1 |  SORT AGGREGATE               |              |      1 |      1 |     9 |            |          |      1 |00:00:00.01 |       4 |
|   2 |   NESTED LOOPS OUTER          |              |      1 |      2 |    18 |     2   (0)| 00:00:01 |      2 |00:00:00.01 |       4 |
|   3 |    INDEX FULL SCAN            | T2_PK1       |      1 |      2 |     6 |     1   (0)| 00:00:01 |      2 |00:00:00.01 |       1 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| T3           |      2 |      1 |     6 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|*  5 |     INDEX UNIQUE SCAN         | T3_ORDER_PK1 |      2 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - (VARCHAR2(30), CSID=178): '1'

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("T3"."RELATED_S_ID"=TO_NUMBER(:V0))
   5 - access("T2"."S_ID"="T3"."S_ID")

However, the “Join Against a Constant” error can easily be made even when using ANSI outer join syntax. A common error is to inappropriately move a predicate from the ON clause to the WHERE clause. The following query illustrates the mistake:

SQL> select count(*)
  2  from t1
  3  join t2 on t2.id = t1.id
  4  left outer join t3 on t2.s_id = t3.s_id
  5  where t3.related_s_id = to_number(:v0);

  COUNT(*)
----------
         1

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'typical iostats last +peeked_binds'));

PLAN_TABLE_OUTPUT

SQL_ID  3h7w9c69mnnnm, child number 0
-------------------------------------
select count(*) from t1 join t2 on t2.id = t1.id left outer join t3 on
t2.s_id = t3.s_id where t3.related_s_id = to_number(:v0)

Plan hash value: 3544670422

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |      1 |        |       |     1 (100)|          |      1 |00:00:00.01 |       1 |
|   1 |  SORT AGGREGATE   |             |      1 |      1 |     6 |            |          |      1 |00:00:00.01 |       1 |
|*  2 |   INDEX RANGE SCAN| T3_OLS_RS_1 |      1 |      1 |     6 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - (VARCHAR2(30), CSID=178): '1'

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T3"."RELATED_S_ID"=TO_NUMBER(:V0))

Refer to the article on ANSI outer join syntax by Jonathan Gennick for an explanation of which predicates properly belong in the ON clause and which properly belong in the WHERE clause.

A SQL Minute: Injudicious Use of Views (SQL AntiPatterns)

A SQL Minute: Using a Table To Provide Query Parameters (Query AntiPatterns)

A SQL Minute: The Rest Is History

SQL Ridicu-List: Let He Who Is Without Sin Cast The First Stone

Categories: Oracle, SQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: