Re: here is another oracle 10g's bug?

On Jun 21, 6:11 am, Havel Zhang <havel.zh..._at_gmail.com> wrote:
> On 6 21 , 12 48 , "Vladimir M. Zakharychev"
>
>
>
> <vladimir.zakharyc..._at_gmail.com> wrote:
> > On Jun 20, 3:20 pm, Havel Zhang <havel.zh..._at_gmail.com> wrote:
>
> > > On 6 20 , 6 10 , "Vladimir M. Zakharychev"
>
> > > The explain on 10g as follows:
> > > ---------------------------------------------------------------------------­---------------------------------------------------------------------------­---------------------------
> > > | Id | Operation |
> > > Name | Rows | Bytes |
> > > TempSpc| Cost (%CPU)|
> > > ---------------------------------------------------------------------------­---------------------------------------------------------------------------­---------------------------
> > > | 0 | INSERT STATEMENT
> > > | | 72276
> > > | 13M| | 826K (1)|
> > > | 1 | TABLE ACCESS BY INDEX ROWID|
> > > STORE_PRODUCT | 1 | 74 |
> > > | 3 (0)|
> > > | 2 | NESTED LOOPS
> > > | | 72276
> > > | 13M| | 826K (1)|
> > > | 3 | HASH JOIN
> > > | | 72276 |
> > > 8681K| 7136K| 609K (2)|
> > > | 4 | TABLE ACCESS FULL |
> > > DAY_DEBIT_NE_MARGIN_GROCERY | 72276 | 6281K| | 236 (1)|
> > > | 5 | TABLE ACCESS FULL |...
>
> > Well, the plan above says that the insert will process exactly 72276
> > rows, which is what you expect. The plan for 9i doesn't seem to be
> > right. Are you sure you didn't exchange 10g and 9i plans?
>
> > And if you just select, not insert as select, then both releases
> > return correct results with and without the index? If so, then it's
> > definitely a bug: a query should always return the same result set
> > given the same data and predicates, regardless if it's used as data
> > source for an insert operation or not.
>
> > Regards,
> > Vladimir M. Zakharychev
> > N-Networks, makers of Dynamic PSP(tm)
> > http://www.dynamicpsp.com- -
>
> > - -
>
> hi Vladimir
> I'm sure I haven't exchange two plans. and i tried again. I think
> its Oracle's Bug.
> Thank you for your help:)
>
> Havel Zhang

So you say that the query in 10g returns correct result with and without index, but wrong number of rows is inserted when it's used as the data source for insert statement in presence of an index. And that this is reproducible. Can you confirm? If so, definitely sounds like a case for Oracle Support.

Also, I would recommend to patch your 10g to the latest patchset, 10.2.0.3, and see if the issue persists. Or, if you can't patch your production db right away, at least load the same data into a 10.2.0.3 database as you did for 9i and test there. This may be a known defect that's already fixed.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Thu Jun 21 2007 - 00:03:36 CDT