RE: table in ANSI JOIN *but not* being accessed at all?
- From: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
- To: 'Phillip Jones' <phil@xxxxxxxxxx>
- Date: Tue, 31 Jan 2012 10:02:47 -0600
Ewwwww. No it does not.
"Zed, we [might] have a bug."
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@xxxxxxxxxx]
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@xxxxxxxxxxxxxxx<mailto:ChrisDavid.Taylor@xxxxxxxxxxxxxxx>>
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
