• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Redundant Join Criteria: Good or Bad Idea?

    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



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    ARCAD Software:  FREE Webinar, ITIL Best Practices with Philippe Magne, January 28
    COMMON:  Join us at the 2009 annual meeting and expo, April 26-30, Reno, Nevada
    System i Developer:  RPG & DB2 Summit in Orlando, April 15-17 for 3 days of serious training

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    Getting Started with PHP for i5/OS: List Price, $59.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket Developers' Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    iSeries Express Web Implementer's Guide: List Price, $59.00
    Getting Started with WebSphere Development Studio for iSeries: List Price, $79.95
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    WebFacing Application Design and Development Guide: List Price, $55.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    The All-Everything Machine: List Price, $29.95
    Chip Wars: List Price, $29.95

    Ricoh Launches New Color MFP i Roadmaps: Here Be Dragons

    Leave a Reply Cancel reply

Volume 9, Number 3 -- January 21, 2009
THIS ISSUE SPONSORED BY:

Help/Systems
WorksRight Software
System i Developer

Table of Contents

  • Redundant Join Criteria: Good or Bad Idea?
  • Do Your File Specifications Lie?
  • Trouble-Shooting WebSM to HMC Connectivity Problems

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • The Power11 Transistor Count Discrepancies Explained – Sort Of
  • Is Your IBM i HA/DR Actually Tested – Or Just Installed?
  • Big Blue Delivers IBM i Customer Requests In ACS Update
  • New DbToo SDK Hooks RPG And Db2 For i To External Services
  • IBM i PTF Guide, Volume 27, Number 33
  • Tool Aims To Streamline Git Integration For Old School IBM i Devs
  • IBM To Add Full System Replication And FlashCopy To PowerHA
  • Guru: Decoding Base64 ASCII
  • The Price Tweaking Continues For Power Systems
  • IBM i PTF Guide, Volume 27, Numbers 31 And 32

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle