There are times when I think Oracle is not for the experienced hand and that I have lost my touch

Why do I keep falling into the same man traps?

Why can I not learn?

Why do I put myself through the same debugging exercise over and over and over and over to finally, after losing hair, realise that the behaviour I am observing I have previously observed, and that it is not an Oracle bug.

Have a look at the log output for execution of these simple pieces of SQL.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> delete from bigTable
  2   where productID = 198
  3     and amount > 1
  4     and rownum < 10;
9 rows deleted.
SQL>
SQL>
SQL>
SQL> select productID, sum(amount) sumAmount, count(*) countProductID
  2   from bigTable
  3     where productID = 198
  4       group by productID;

 PRODUCTID  SUMAMOUNT COUNTPRODUCTID
---------- ---------- --------------
       198 1806671.32          68915

SQL>
SQL>
SQL> select distinct sum(amount) sumAmount, count(*) countProductID
  2   from bigTable
  3     where productID = 198;

 SUMAMOUNT COUNTPRODUCTID
---------- --------------
1806655.42          68906

SQL>
SQL> commit;

Commit complete.

SQL>
SQL>
SQL> select productID, sum(amount) sumAmount, count(*) countProductID
  2   from bigTable
  3     where productID = 198
  4       group by productID;

 PRODUCTID  SUMAMOUNT COUNTPRODUCTID
---------- ---------- --------------
       198 1806655.42          68906

SQL>
SQL>
SQL> select distinct sum(amount) sumAmount, count(*) countProductID
  2   from bigTable
  3     where productID = 198;

 SUMAMOUNT COUNTPRODUCTID
---------- --------------
1806655.42          68906

SQL>
SQL>

It just does not make sense that two semantically equivalent queries return different sum(amount) and count(*) results before the commit, yet are identical and correct after the database commit.

Or perhaps it does?

You tell me why? I know the answer, I have just worked out why, again. If the underlying issue is not immediately apparent to you, …. well join the club !

The code in this blog article was developed and tested against the following Oracle version.

SQL>
SQL>
SQL>
SQL>
SQL> set linesize 132 tab off trunc off wrap off
SQL>
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL>
SQL>
SQL>

— Published by Mike, 13:31:57 22 September 2017 (GMT)