Redundant Join Criteria: Good or Bad Idea?

Published: January 21, 2009

Hey, Ted:

Suppose I have four tables that I commonly join. Is there any benefit to adding redundant criteria to the join? Or to the "where" clause? That is, will redundant criteria or selection expressions improve performance?

--Philip

Philip's four tables are keyed as follows:

SITE     ITEM      PROCESS   STRUCTURE
------   ------    --------  ---------
SiteID   SiteID    SiteID    SiteID
         ItemID    ItemID    ItemID
         Revision  Revision  Revision
                             StructID

Here's a join without redundant join criteria:

select whatever
from site as s
join item as i 
  on s.siteid = i.siteid
join process as p
  on i.siteid = p.siteid
 and i.itemid = p.itemid
 and i.rev    = p.rev
join structure as st
  on p.siteid = st.siteid
 and p.itemid = st.itemid
 and p.rev    = st.rev

Site joins to item, item joins to process, process joins to structure.

Here's the same join with redundant join criteria (in red).

select whatever
from site as s
join item as i 
  on s.siteid = i.siteid
join process as p
  on s.siteid = p.siteid
 and i.siteid = p.siteid
 and i.itemid = p.itemid
 and i.rev    = p.rev
join structure as st
  on s.siteid = st.siteid
 and i.siteid = st.siteid
 and i.itemid = st.itemid
 and i.rev    = st.rev
 and p.siteid = st.siteid
 and p.itemid = st.itemid
 and p.rev    = st.rev

The same joins are defined as before, but there are additional joins from site to process, from site to structure, and from item to structure.

Here's a similar query, with redundant record selection expressions (in red).

select whatever
  from item as i 
  join process as p
    on i.siteid = p.siteid
   and i.itemid = p.itemid
   and i.rev    = p.rev
  join structure as st
    on p.siteid = st.siteid
   and p.itemid = st.itemid
   and p.rev    = st.rev
 where i.itemid = 'ABC123'
   and p.itemid = 'ABC123'
   and st.itemid = 'ABC123'

Whereas selecting the itemID from the item table is sufficient for selecting the desired data set, the query also selects the same item from the process and structure tables.

I didn't know the answer to Philip's questions, so I directed them to Dan Cruikshank of IBM, who was kind enough to respond. According to him, both the Classic Query Engine (CQE) and the SQL Query Engine (SQL) generate selection predicates over join columns, so the answer to the second question, regarding redundant record selection expressions, is no. The optimizers will generate the where predicates in red.

The answer to the first question depends on the optimizer. The SQE optimizer understands transitive closure. That is, it knows that if A equals B and B equals C, then A equals C. The SQE will add join predicates as part of the greedy join process for inner joins. The SQE optimizer does add the join predicates shown in red above. I infer that redundant join criteria may help with queries that the CQE processes.

--Ted