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

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 Received on Wed Jun 20 2007 - 11:48:15 CDT