SELECT COUNT(*) FROM Tab -> No Rows Selected

In the following SQL*Plus output…

SQL> select sum(tran_sum), count(*)
  2    from trans;

no rows selected

…something is goofed up. You should not have a no rows selected with this SQL.

When you see something like that for the first time, you can generally draw from four conclusions:

1. Someone edited the output
2. SQL*Plus bug
3. Oracle bug
4. Combination of the above

Since it was me who saw this, number one was an easy but irrelevant answer.

What about an SQL*Plus bug? OK, run this using some other ad hoc tool. Same result. Time to take a more precise look at the problem. What is trans?

SQL> select object_type from user_objects where object_name='TRANS';

OBJECT_TYPE
-------------------
TABLE

SQL> desc TRANS;
 Name          Null?    Type
 ------------- -------- -------
 TRAN_ID       NOT NULL NUMBER
 TRAN_SUM      NOT NULL NUMBER

Table trans has two not null columns (of the number datatype). Couldn’t be simpler. Oracle can’t have a bug in such a simple case, so what’s the deal?

Could it be because what we see is not what we see? Take a look at an autotrace plan output (I have trimmed the right side of the plan to make it fit nicely):

SQL> set autot traceonly explain
SQL> select sum(tran_sum), count(*)
  2    from trans;

Execution Plan
---------------------------------------------------------
Plan hash value: 3295210495

---------------------------------------------------------
| Id  | Operation                    | Name     | Rows  |
---------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV_TRANS |     1 |
---------------------------------------------------------

It turns out that our query was rewritten.

SQL> select query, rewrite_enabled, refresh_mode, refresh_method
  2   from user_mviews
  3   where mview_name='MV_TRANS';

QUERY                REWRITE_ENABLED REFRESH_MODE REFRESH_METHOD
-------------------- --------------- ------------ --------------
select sum(tran_sum) Y               COMMIT       FAST
 trans_sum, count(*)
 trans_count
   from trans

We have a materialized view with query rewrite enabled that refreshes on commit (the refresh method is fast so we have a materialized view log). By now you can guess that the materialized view itself is empty. Let’s go ahead and check that.

SQL> select * from mv_trans;

no rows selected

It’s not a surprise that our select ended up with a no rows selected. Perhaps this is not a bug. But…

SQL> show parameter query_rewrite_integrity

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
query_rewrite_integrity              string      enforced

…not with enforced query rewrite integrity.

It should be a few relatively easy steps to reproduce the problem from now.

SQL> create table trans
  2  (
  3   tran_id  number primary key,
  4   tran_sum number not null
  5  );

Table created.

SQL> create materialized view log on trans
  2   with rowid (tran_sum) including new values;

Materialized view log created.

SQL> create materialized view mv_trans
  2   refresh fast on commit with rowid
  3   enable query rewrite as
  4   select sum(tran_sum) trans_sum, count(*) trans_count
  5    from trans;

Materialized view created.

So far we should be OK with our query:

SQL> select sum(tran_sum), count(*)
  2    from trans;

SUM(TRAN_SUM)   COUNT(*)
------------- ----------
                       0

SQL> select * from mv_trans;

 TRANS_SUM TRANS_COUNT
---------- -----------
                     0

How we can delete all rows from mv_trans when DML is prohibited on non-updatable MVs? A little trick…

SQL> delete from trans;

0 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from mv_trans;

no rows selected

SQL> select sum(tran_sum), count(*)
  2    from trans;

no rows selected

…is all it takes.

Let’s proceed and track down this issue further. Something wrong is happening during the fast refresh process. We are going to find out what.

(As a side note, notice how adding the group by 1 changes the answer:)

SQL> select * from t;

no rows selected

SQL> select count(*) from t;

  COUNT(*)
----------
         0

SQL> select count(*) from t group by 1;

no rows selected

I’ll skip the entire SQL for the materialized view refresh process (it’s long and you can always see it for yourself, just turn on sql_trace before you commit). I will only mention two significant points instead.

The refresh process consists of two main statements. The first statement does an update to a materialized view based on aggregated data from the materialized view log. But since the materialized view log is empty and the aggregation is done using the group by 1 clause, it updates nothing (since that group by 1 results in nothing). It’s not an error in our case, but I don’t think it’s an optimization either. It really should have used the original aggregate statement.

The second statement is a delete from the materialized view itself:

/* MV_REFRESH (DEL) */ DELETE FROM "TEST"."MV_TRANS" "SNA$" WHERE "SNA$"."TRANS_COUNT"=0

This one deletes the last and only row from the materialized view. The purpose of that last delete should be to cleanup the no longer existing group by keys since the update of the materialized view will set their counts to zero. Again, the delete was done assuming that we do have a group by in place. But we don’t. That last delete is plain wrong.

Will an insert into the base table repopulate our materialized view again? No. That’s because that insert would result in an update to a materialized view. But there is nothing to update anymore. No amount of subsequent DML will be able to fix that situation. For those of you who are curious, an insert to a materialized view with group by will result in a merge instead (so it can account for the new keys).

It looks like the algorithm used for a refresh after a delete was designed as a generic one — it always assumes a group by clause, and it’s a poorer algorithm for it.

P.S.: All tests were performed on 10.2.0.3.