table in ANSI JOIN but not being accessed at all?

From: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 31 Jan 2012 09:36:30 -0600

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.



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

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

Other related posts:

table in ANSI JOIN but not being accessed at all?