Re: select and insert-select statements have different results

Hi Sybrand,

The database runs on a virtual machine (vmware) and I'm the only user on the machine/database.

Are there any issues known with running 10g on a virtual machine?

Is it possible that things may be corrupt or so?

Thanks,
Martijn

sybrandb_at_yahoo.com wrote:
> Blaenzo wrote:
> > Hi,
> > I'm completely stuck on the following:
> >
> > I run a select query to fill an EMTPY table with (summed) data.
> >
> > Then, however, the total sum of all records doesn't match the
> > total sum of the direct query on the original table.
> >
> > Both tables have the same layout.
> >
> > How can the total sum of SUMMED/GOUPED BY data be different from the
> > total sum of the original data with the EXACT same select statement??
> >
> > I use Oracle 10g.
> > These are my queries:
> >
> > //empty destination table
> > TRUNCATE TABLE AGGPNLVECTOR
> >
> > //fill table with aggregated numbers
> > INSERT INTO AGGPNLVECTOR
> > SELECT 'AGG1680', MRTKCOBDATE, PNLVECTORTYPEID, SCENARIOID,
> > ELEMENTNUMBER ,SUM(NEWVALUE),ERRORSTATUS, RISKFACTORGROUPID,
> > NEWIGNOREREPORTSTATUS, SUM(ALTVALUE)
> >
> > FROM PNLVECTOR
> > WHERE MRTKCOBDATE='1-MAY-06'
> > AND SCENARIOID=0
> > AND ERRORSTATUS='V'
> > AND NEWIGNOREREPORTSTATUS='R'
> > AND (
> > PNLVECTOR.DESKLONGID='DSK1116'
> > OR PNLVECTOR.DESKLONGID='DSK1120'
> > OR PNLVECTOR.DESKLONGID='DSK1644'
> > )
> >
> > GROUP BY MRTKCOBDATE, PNLVECTORTYPEID, SCENARIOID,
> > ELEMENTNUMBER ,ERRORSTATUS, RISKFACTORGROUPID,
> > NEWIGNOREREPORTSTATUS
> >
> >
> > //total sum no.1
> > SELECT SUM(NEWVALUE)
> > FROM PNLVECTOR
> > WHERE MRTKCOBDATE='1-MAY-06'
> > AND SCENARIOID=0
> > AND ERRORSTATUS='V'
> > AND NEWIGNOREREPORTSTATUS='R'
> > AND (
> > PNLVECTOR.DESKLONGID='DSK1116'
> > OR PNLVECTOR.DESKLONGID='DSK1120'
> > OR PNLVECTOR.DESKLONGID='DSK1644'
> > )
> >
> > //total sum no. 2
> > SELECT SUM(NEW VALUE)
> > FROM AGGPNLVECTOR
> >
> >
> > Many thanks!
> > Martijn
>
> Because some other session changed the table?
> In Oracle SELECTs aren't repeatable unless you set the isolation_level
> of your session to serializable.
> The performance drawbacks of such an approach are evident.
> You could of course LOCK the table in exclusive mode.
>
> --
> Sybrand Bakker
> Senior Oracle DBA
Received on Fri Jun 02 2006 - 04:41:05 CDT