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

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.

From: Phillip Jones [mailto:phil_at_phillip.im] Sent: Tuesday, January 31, 2012 9:48 AM
To: Taylor, Chris David
Subject: Re: table in ANSI JOIN *but not* being accessed at all?

Does this still happen if you set:

_optimizer_join_elimination_enabled = false

Thanks,

Phil
On Tue, Jan 31, 2012 at 3:36 PM, Taylor, Chris David <ChrisDavid.Taylor_at_ingrambarge.com<mailto:ChrisDavid.Taylor_at_ingrambarge.com>> wrote: 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<http://j.id> = bt.prometheus_job_id
      INNER JOIN PRDATA.CONTRACT co
         ON co.id<http://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



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