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: Anurag Varma <avoracle_at_gmail.com>
Date: 13 Feb 2007 11:47:44 -0800
Message-ID: <1171396064.835971.140900@k78g2000cwa.googlegroups.com>


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 Received on Tue Feb 13 2007 - 13:47:44 CST

Original text of this message

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