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)

3 Responses to “There are times when I think Oracle is not for the experienced hand and that I have lost my touch”

  1. This is a bug isn’t it? Your blog is a double bluff?

  2. That is nasty! If there is documentation that explains why it isn’t a bug, whoever wrote it must have an evil sense of humour.

  3. Nasty is the appropriate word Bruce.


    I replicated the problem on a test VM with

    alter session set query_rewrite_integrity = stale_tolerated;
    
    
    
    
    create materialized view bigTable_mv
     refresh complete on commit
      enable query rewrite
       as
         select productID, sum(amount) sumAmount, count(*) countProductID
           from bigTable
             where productID = 198
                group by productID;
    

        


    then executing the two pieces of SQL above as the test case.


    The two underlying issues are a) QUERY REWRITE (so not querying the table as referenced in the SQL, but Oracle internally/transparently rewrites the SQL to execute against the materialised view) and b) in doing to, the sad thing cannot even recognise that the most simple case where DISTINCT and GROUP BY are semantically equivalent/syntactically different.


    On the production box, guessing the pfile has stale_tolerated as the instance default, or there is a logon trigger setting the session default, or something (I didn’t have the privs to check, or whether it has a bearing and the issue is solely down to the query rewrite).


    Needless to say, materialised views often cause “the numbers just don’t add up” grief, coupled with the fact you don’t know things are transparently rewritten to use the materialized view. The blog post above is just another another example of where I have fallen into the same damn man trap, yet again.


Leave a Reply