Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Bug? SQL not returning correct rows.

Re: Oracle Bug? SQL not returning correct rows.

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 13 Feb 2007 12:38:01 -0800
Message-ID: <1171399078.781491@bubbleator.drizzle.com>


Anurag Varma wrote:

> On Feb 13, 2:37 pm, zugzu..._at_gmail.com wrote:

>> Anurag,
>>
>> When I ran the query using the hint I got the proper result set. What
>> triggered you to think of adding this hint? I understand what the
>> HASH_SJ hint is doing but a hint should not affect the result
>> set...right?!?
>>
>> Here is the explain plans for both:
>>
>> -- Without Hint
>> Plan
>> SELECT STATEMENT CHOOSE Cost: 2,355 Bytes: 45,195 Cardinality:
>> 1,965
>> 4 HASH JOIN Cost: 2,355 Bytes: 45,195 Cardinality: 1,965
>> 2 VIEW SYS.VW_NSO_1 Cost: 2,017 Bytes: 9,095 Cardinality: 535
>> 1 REMOTE
>> 3 TABLE ACCESS FULL SYSADM.PS_RSC_AR820_TBL Cost: 315 Bytes:
>> 1,905,084 Cardinality: 317,514
>>
>> -- With Hint
>> Plan
>> SELECT STATEMENT CHOOSE Cost: 2,542 Bytes: 45,195 Cardinality:
>> 1,965
>> 4 HASH JOIN SEMI Cost: 2,542 Bytes: 45,195 Cardinality: 1,965
>> 1 TABLE ACCESS FULL SYSADM.PS_RSC_AR820_TBL Cost: 315 Bytes:
>> 1,905,084 Cardinality: 317,514
>> 3 VIEW SYS.VW_NSO_1 Cost: 2,017 Bytes: 9,095 Cardinality: 535
>> 2 REMOTE
>>
>> Do you know if this is a known problem with 9I (9.2.0.7.0)?
>>
>> Also, I wasn't using CAPS to look like I was yelling (I know...that
>> drives me nuts too). I wanted to separate my comments from the
>> spooled output and since I can't use bold, italics, or underline that
>> was the only thing I could think of.
>>
>> Thanks for taking the time to respond to my post. I've had several co-
>> workers looking into this also. The query was for a one-time report
>> and the database I was running it from was not the system I support.
>> I'll forward a message to the DBAs to have them check if they can
>> upgrade to fix this solution.
>>
>> -Ryan
>>
>>> ok .. You might be hitting a bug. It appears that
>>> instead of doing a semi - join, the IN clause is
>>> doing a regular join.
>>> What is the explain plan of Query 1 (the one with wrong
>>> results)?
>>> Try infuencing the query using following hint:
>>> select bill_of_lading, qty
>>> from ps_rsc_ar820_tbl
>>> where bill_of_lading IN (select /*+ HASH_SJ */ a.shipper_nbr
>>> from op_shipper_at_opis a
>>> where a.ship_from_loc_key = 5
>>> and a.ship_dt > SYSDATE - 11
>>> and exists (select * from
>>> op_tag_history_at_opis b
>>> where b.shipper_key =
>>> a.shipper_key
>>> and b.event_key = 85)
>>> ) ;
>>> Note the hash_sj hint I gave.
>>> You might want to patch to 9.2.0.8 if you believe you
>>> are hitting this bug. Its quite likely it might be
>>> fixed in 9.2.0.8
>>> .. and please don't use CAPS. We are just trying to help ..
>>> CAPS=shouting .. doesn't give us much joy to help you.
>>> Anurag- Hide quoted text -
>>> - Show quoted text -
> 
> Ryan,
> 
> Goto Metalink and search for
> Bug 3828598  Wrong results with unnested remote subqueries
> 
> Thats what prompted me to advise you the hint.
> Your query fit the symptoms quite well. A
> query using a IN subquery will return duplicate rows
> mostly in cases when oracle is trying to rewrite
> the query wrong (as a straight join). In cases
> when the subquery uses a distinct, this may make sense
> however, in your case its a bug.
> 
> And the bug I mention is listed as fixed in 9.2.0.8.
> 
> Note: You might not be hitting the exact bug .. but
> you could try patching to 9.2.0.8 to see if the problem
> goes away .. or contact Oracle Support.
> 
> Sorry I mistook your CAPS to shouting .. no offense taken.
> 
> Cheers,
> Anurag

Thank you for the bug number. I'll check it out.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue Feb 13 2007 - 14:38:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US