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: <zugzug21_at_gmail.com>
Date: 13 Feb 2007 11:37:06 -0800
Message-ID: <1171395426.667275.242090@v45g2000cwv.googlegroups.com>


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:

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 coworkers  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 -
Received on Tue Feb 13 2007 - 13:37:06 CST

Original text of this message

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