table in ANSI JOIN *but not* being accessed at all?

From: Taylor, Chris David <ChrisDavid.Taylor_at_ingrambarge.com>
Date: Tue, 31 Jan 2012 09:36:30 -0600
Message-ID: <C5533BD628A9524496D63801704AE56D6ADD161A49_at_SPOBMEXC14.adprod.directory>

I've run into a situation I can't explain and it may just be I'm unfamiliar with a VALID reason this would occur. (Though, I can't see how this would be valid) We have a query:

Select btd.trip_id, CO.CUSTOMER_ID
-- redacted the rest of the select portion
  FROM IBARDM.xxxxxx btl

       LEFT JOIN IBARDM.xxxx btd
          ON btd.trip_id = btl.trip_id
       INNER JOIN IBARDM.xxxxxx bt
          ON bt.trip_id = btl.trip_id
       INNER JOIN IBARDM.xxxxx c
          ON c.cargo_id = bt.trip_cargo_id
       INNER JOIN IBARDM.xxxxxx cm
          ON cm.cargo_master_id = c.cargo_master_id
       INNER JOIN PRDATA.xxxxxx j
          ON j.id = bt.prometheus_job_id
       INNER JOIN PRDATA.CONTRACT co
          ON co.id = j.contract_id
       INNER JOIN IBARDM.CUSTOMER cu <<---- This table not being access in the SELECT or WHERE stmt
          ON cu.customer_id = co.customer_id
       INNER JOIN PRDATA.xxxxxx ra
          ON ra.barge_trip_id = bt.trip_id
 WHERE     TRUNC (btl.release_load_dt) BETWEEN '1-DEC-2010' AND '31-JAN-2011'
       AND (   TRUNC (btd.release_discharge_dt) BETWEEN '1-JAN-2011'
                                                    AND '31-JAN-2011'
            OR TRUNC (btd.release_discharge_dt) IS NULL)
       AND cm.cargo_base_type_cd = 'LIQUID'
       AND ra.service_type = 'AF'

/

If I run this statement I get 196 total rows.

Now, if I add a CU.CUSTOMER_ID (the table not being accessed) to the WHERE, the SELECT, or the INNER JOIN, then I get 0 rows and the table gets accessed.

I have verified that no portion of the table or index on the table is being touched at all when the original SELECT runs.

So, is there a VALID reason Oracle might choose to leave out a table when it is specified in the INNER JOIN statements?

I've checked the CO.CUSTOMER_ID (PRDATA.CONTRACT) and verified that the CO.CUSTOMER_IDs returned in the 196 rows DO NOT exist in the IBARDM.CUSTOMER cu table.

I'm wondering if I have bug or if I've just forgotten some bit of Oracle reasoning here...

Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205

"Quality is never an accident; it is always the result of intelligent effort."
-- John Ruskin (English Writer 1819-1900)

CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 31 2012 - 09:36:30 CST