RE: table in ANSI JOIN *but not* being accessed at all?
- From: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
- To: 'Michael Moore' <michaeljmoore@xxxxxxxxx>
- Date: Tue, 31 Jan 2012 15:01:30 -0600
Ok - let me clarify:
1. I run the statement (as is, no modifications) then I get 196 rows
2. If I modify the statement to include:
a. CU.CUSTOMER_ID in the SELECT or
b. CU.CUSTOMER_ID in the WHERE or
c. CU.CUSTOMER_ID in the FROM (INNER JOIN xxx on xxx)
Then I get 0 rows
So - in scenario 1 above the table (CU) is getting eliminated from the join
(JOIN ELIMINATION) and doesn't show up in the execution plan at all and I get
196 [invalid] rows.
Scenarios 2a-2c give me the correct results and table (CU) is correctly
accessed and shows up in the execution plan and is not eliminated (of course,
since it now must be)
It's definitely a bug with JOIN ELIMINATION and Oracle support has several bug
reports/fixes in 10g and 11g.
Really, it appears [in this case] that the bug is that the follow up to the
optimization doesn't occur which is to rejoin CO.CUSTOMER_ID to one of the
other tables but I'm not 100% positive on that. (That bug# is 6707916 in
10.2.0.4)
Hope that helps?
Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions < 11.2
Versions confirmed as being affected *
10.2.0.4<javascript:taghelp('AFFECTS_A204')>
* 11.1.0.6<javascript:taghelp('AFFECTS_B106')>
Platforms affected Generic (all / most platforms affected)
Fixed:
This issue is fixed in *
11.2 (Future Release)<javascript:taghelp('FIXED_B200')>
* 10.2.0.5 (Server Patch Set)<javascript:taghelp('FIXED_A205')>
* 11.1.0.7 (Server Patch Set)<javascript:taghelp('FIXED_B107')>
Symptoms: Related To:
*
Wrong Results<javascript:taghelp('TAGS_WRONGRES')> *
Optimizer<javascript:taghelp('TAGS_CBO')>
* _OPTIMIZER_JOIN_ELIMINATION_ENABLED
Description
Wrong results are possible with join elimination if there is
a predicate of the following form present:
T1.FK = T2.PK
T3.FK = T2.PK
When the problem occurs T2 may get removed without generating the predicate
T1.FK = T3.FK
WORKAROUND:
Set "_optimizer_join_elimination_enabled" = false;
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: Michael Moore [mailto:michaeljmoore@xxxxxxxxx]
Sent: Tuesday, January 31, 2012 2:53 PM
To: Taylor, Chris David
Cc: Phillip Jones; oracle-l@xxxxxxxxxxxxx
Subject: Re: table in ANSI JOIN *but not* being accessed at all?
I don't quite get what you are saying.
You said, "If I run this statement I get 196 total rows." The example shows
INNER JOIN IBARDM.CUSTOMER cu <<---- This table not being access in the
SELECT or WHERE stmt
ON cu.customer_id = co.customer_id
Later you say:
"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."
From your example CU.CUSTOMER_ID already IS in the INNER JOIN. I'm confused.
I've tried to duplicate the behavior you've described but with simplified
tables. No success. It's a weird problem, but I suspect there is a reason
rather than it being a bug.
Mike
--
//www.freelists.org/webpage/oracle-l
Other related posts:
