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

Home -> Community -> Usenet -> c.d.o.server -> Re: here is another oracle 10g's bug?

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

From: Havel Zhang <havel.zhang_at_gmail.com>
Date: Wed, 20 Jun 2007 22:58:04 -0700
Message-ID: <1182405484.544659.275120@q69g2000hsb.googlegroups.com>


On 6 21 , 1 03 , "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> 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- -
>
> - -

hi Vladimir:

    Yes, I can confirm, I have asked our Admin to patch 10.2.0.3.     Thank you.

Havel Zhang Received on Thu Jun 21 2007 - 00:58:04 CDT

Original text of this message

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