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

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

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_at_gmail.com] Sent: Tuesday, January 31, 2012 2:53 PM
To: Taylor, Chris David
Cc: Phillip Jones; oracle-l_at_freelists.org 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

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

Received on Tue Jan 31 2012 - 15:01:30 CST