Consultium's Blog

If you have a large query with multiple tables, but there appear to be multiple rows when you expect only one it can be an onerous task to discover the missing join condition. Here’s a quick method I came up with to find the table that has a missing join condition that can give you a clue to what the missing predicate should be.

Example:

SELECT
ooh.order_number
,ool.line_number
,msi.segment1 item_number
FROM
oe_order_headers ooh
,oe_order_lines ool
,mtl_system_items msi
WHERE 1=1
AND ool.header_id = ooh.header_id
AND msi.inventory_item_id = ool.inventory_item_id

Taking this little sample query, add a predicate to narrow the query down to a single order, line or item. Then replace the SELECT part of the statement and get the ROWID of each of the tables in the query, giving:

SELECT
ooh.ROWID ooh
,ool.ROWID ool
,msi.ROWID msi
FROM
oe_order_headers_all ooh
,oe_order_lines_all ool
,mtl_system_items_b msi
WHERE 1=1
AND ooh.order_number = 20000722
AND ool.header_id = ooh.header_id
AND msi.inventory_item_id = ool.inventory_item_id

Running this gives the following:

OOH............... OOL............... MSI
------------------ ------------------ ------------------
AAAVeuAAYAAAXB8AAA AAAVl9AAYAAAXCzAAR AAATTdAAWAAAcTNAAI
AAAVeuAAYAAAXB8AAA AAAVl9AAYAAAXCzAAR AAATTdAAYAAAjWBAAP
AAAVeuAAYAAAXB8AAA AAAVl9AAYAAAXCzAAR AAATTdAAWAAAf5DAAP
AAAVeuAAYAAAXB8AAA AAAVl9AAYAAAXCzAAR AAATTdAAXAAAfkHAAL
AAAVeuAAYAAAXB8AAA AAAVl9AAYAAAXCzAAR AAATTdAAWAAAQMRAAJ
AAAVeuAAYAAAXB8AAA AAAVl9AAYAAAXCzAAR AAATTdAAYAAGY3YAAT
AAAVeuAAYAAAXB8AAA AAAVl9AAYAAA/SbAAW AAATTdAAWAAAcTVAAF
AAAVeuAAYAAAXB8AAA AAAVl9AAYAAA/SbAAW AAATTdAAYAAAjWAAAR
AAAVeuAAYAAAXB8AAA AAAVl9AAYAAA/SbAAW AAATTdAAWAAAf5CAAR

Looking at the ROWIDs, you can easily see that the header (OOH) is repeated, i.e. for a single header. The line (OOL) is repeated in 2 parts suggesting 2 lines, and the item (MSI) is different for every row – this suggests that this is the table that has not been joined to properly.

In fact the missing join was:

AND msi.organization_id = ool.ship_from_org_id

Giving the following in our test query:

OOH............... OOL............... MSI
------------------ ------------------ ------------------
AAAVeuAAYAAAXB8AAA AAAVl9AAYAAAXCzAAR AAATTdAAWAAAQMRAAJ
AAAVeuAAYAAAXB8AAA AAAVl9AAYAAA/SbAAW AAATTdAAWAAAQMQAAL


This works best when you’re looking at huge queries that would ordinarily take a long time to rebuild until you find the missing join. I’ve also used this to discover why a DISTINCT is being used in a query that is poorly performing, and found missing joins that allowed me to remove the DISTINCT which is resource hungry causing multiple sorts, and fixing the query at the same time.