Re: select counts versus insert/select counts

Recently we have hit similar bug with MobileAdmin.

When MobileAdmin is syncing data, it issues similar "insert as select" statements. This bug was very hard to track, because it also did not occurred on regular basis, but only from time to time.

This metalink note describes situation: Note:406966.1 - Table Prefetching causes intermittent Wrong Results in 9iR2,10gR1 and 10gR2 and associated Bug No. 5129039: WRONG RESULTS WITH INSERT AS SELECT AND TABLE PREFETCH In our case it was decided to use hidden parameter _table_lookup_prefetch_size=0.

So actually, as you can see, there are a lot of bugs in Oracle that could cause the issue you are observing. What I would suggest is to try to create a simple test case, it will allow you to find out why are you getting this issue and then test if the solution is working or not.

Best regards,
Edgar

On 10/19/07, Michael.Coll-Barth_at_verizonwireless.com <Michael.Coll-Barth_at_verizonwireless.com> wrote:
>
>
> The SQL statement was basically of this form;
>
> insert into tablex
> (
> a, b, c, d
> )
> select a, b, c, d
> from tabley y,
> tablez z
> where y.key = z.key
> ;
>
> The number of rows 'select'ed is different than the number of rows
> 'insert'ed when the code is cut and pasted into a SQL prompt.
>

-- 
Best regards,
  Edgar Chupit
 callto://edgar.chupit
--
http://www.freelists.org/webpage/oracle-l