Quiz Night

The following output is the result of two immediately consecutive SQL statements (with “set echo on”), and nothing else happening to the database.

SQL> select  /*+ full(t1) */
  2          count(*)
  3  from    t1
  4  where   status = 'FAILED'
  5  ;

  COUNT(*)
----------
       200

1 row selected.

SQL>
SQL> select  /*+ full(t1) */
  2          status, substr(dump(status,16),1,40) dumped, count(*)
  3  from    t1
  4  group by
  5          status, substr(dump(status,16),1,40)
  6  ;

STATUS               DUMPED                                          COUNT(*)
-------------------- --------------------------------------------- ----------
FAILED               Typ=1 Len=6: 46,41,49,4c,45,44                       200
FAILED               Typ=1 Len=6: 46,41,49,4c,45,44                       200

2 rows selected.

I’ve used the dump() command in the second query to show you that ALL the rows in this table have exactly the same value for the status column.
But either (a) half of them aren’t being counted in the first query or (b) all of them are being counted twice in the second query.

The /*+ full(t1) */ hint is there to let you know that I’ve used the same execution plan in both cases, so I haven’t managed to engineer the problem by carefully corrupting an index.

What have I done to my database ? (12c, by the way).

Answer

Someone supplied the answer before I did, so I won’t repeat it.
The posting was prompted by an email I got about the previous posting, suggesting that the apparently redundant predicate might have been generated to avoid exactly this kind of reporting error, so I felt the need to run up a quick check to show that there are plenty of cases where such a predicate doesn’t appear and the answers can look bizarre.

Here’s the execution plan that Joel Garry asked for, by the way. It demonstrates a little feature that has been around for a very long time (possibly since the introduction of partitioning): partition-wise aggregation. Many people know about the benefits of partition-wise joins, but partition-wise aggregation is a detail that doesn’t get mentioned. and perhaps doesn’t get noticed; it’s just one of those clever little touches: the optimizer saw that it could produce the required result by collecting and aggregating the data from each partition in turn, rather than collecting all the data across every partition and then doing a single large aggregation.

(Of course in this case the fact that the same value appeared twice after aggregation gave us the clue that we have bad data in one of the partitions)

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |     4 (100)|          |       |       |
|   1 |  PARTITION LIST ALL |      |     2 |    16 |     4   (0)| 00:00:01 |     1 |     2 |
|   2 |   HASH GROUP BY     |      |     2 |    16 |     4   (0)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS FULL| T1   |  1000 |  8000 |     4   (0)| 00:00:01 |     1 |     2 |
--------------------------------------------------------------------------------------------

The moral of this story

If you’re going to say “we don’t need constraints, the code ensures that the data is correct”, or if you’re going to use the “trust me” (without validation) option make sure that the code that supposed to be checks your data is absolutely perfect.