On Feb 12, 3:29 pm, zugzu...@gmail.com wrote:
> Good idea but it didn't help.
>
> If I just try to use bill_of_lading, qty, * it gives me: "ORA-00936:
> missing expression".
>
> If I try to give the table an identifier (select bill_of_lading, qty,
> z.* from ps_rsc_ar820_tbl z...) then it just returns an identical Z
> row for each of the dups. I also tried to include the identifiers in
> the sub-query to make sure something weird wasn't going on there and
> as expected Oracle complained that I was referencing an invalid
> identifier.
>
> Thanks,
> Ryan
>

Sorry, I was too quick while trying to keep it simple. Just try
something in place of * that will uniquely identify the source row, in
fact, ROWID would be a good choice.

Then if the results show the same rowid for the duplicates, then it
may be a bug. But if the results show different rows with duplicate
bill_of_lading values, then you know the problem is in the select.
(IOW, something was not the same between query 1 and query 3)

HTH,
ed

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@opis a
> where a.ship_from_loc_key = 5
> and a.ship_dt > SYSDATE - 11
> and exists (select * from
> op_tag_history@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 -

Ed,

I re-ran the query with ROWID and for each row that appears to be a
duplicate it has the same ROWID. According to the info I found from
Anurag's suggestions this defaintely appears to be a bug.

Thanks for the help,
Ryan

>
> Sorry, I was too quick while trying to keep it simple. Just try
> something in place of * that will uniquely identify the source row, in
> fact, ROWID would be a good choice.
>
> Then if the results show the same rowid for the duplicates, then it
> may be a bug. But if the results show different rows with duplicate
> bill_of_lading values, then you know the problem is in the select.
> (IOW, something was not the same between query 1 and query 3)
>
> HTH,
> ed

On Feb 13, 2:37 pm, zugzu...@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@opis a
> > where a.ship_from_loc_key = 5
> > and a.ship_dt > SYSDATE - 11
> > and exists (select * from
> > op_tag_history@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

zugzug21@gmail.com wrote:
> Ed,
>
> I re-ran the query with ROWID and for each row that appears to be a
> duplicate it has the same ROWID. According to the info I found from
> Anurag's suggestions this defaintely appears to be a bug.
>
> Thanks for the help,
> Ryan

That is what I saw and why I wrote that it was a Cartesian.

I can still not find any evidence that there is a bug reported.
Though it may just be that "I" can't find it. Still it is hard
to believe this has not been previously reported.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Anurag Varma wrote:
> On Feb 13, 2:37 pm, zugzu...@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@opis a
>>> where a.ship_from_loc_key = 5
>>> and a.ship_dt > SYSDATE - 11
>>> and exists (select * from
>>> op_tag_history@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@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org