Oracle Scratchpad

October 17, 2013

Quiz Night

Filed under: constraints,Infrastructure,Oracle,Partitioning — Jonathan Lewis @ 10:58 pm BST Oct 17,2013

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  ;


1 row selected.

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).


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 is supposed to check your data is absolutely perfect in the face of all possible accidents.


  1. By “nothing else happening” you are discarding that rows could have been inserted from another session?.

    Comment by Nelson — October 17, 2013 @ 11:14 pm BST Oct 17,2013 | Reply

  2. May be nls_sort? But for collation prefix trick “FAILED” status is too short.

    Comment by Valentin Nikotin — October 18, 2013 @ 12:00 am BST Oct 18,2013 | Reply

  3. The group by gets transformed with unnesting so you are seeing the (incorrect) result of the hash join of the substring result set with the status result set?

    Please include plan.

    Comment by jgarry — October 18, 2013 @ 12:55 am BST Oct 18,2013 | Reply

  4. Any chance we could see the CREATE TABLE statement for T1?

    Comment by Charlie Callaway — October 18, 2013 @ 5:32 am BST Oct 18,2013 | Reply

  5. Another guess – virtual column with false-deterministic function.

    Comment by Valentin Nikotin — October 18, 2013 @ 8:13 am BST Oct 18,2013 | Reply

    • Valentin,


      I believe I could do this on any version of Oracle from 8.0 – so no virtual columns

      This is a heap table, not clustered, not an IOT; it has no indexes, no object-type columns, no XML types, no LOBs or longs,no views or PL/SQL involved, no triggers, no row-level security, no contexts, no profiles.

      Comment by Jonathan Lewis — October 18, 2013 @ 8:21 am BST Oct 18,2013 | Reply

  6. To try to isolate the issue, could you send output of this:

    select  /*+ full(t1) */    status, count(*) 
    from t1
    group by status;

    Arturo Gutierrez

    Comment by arturogutierrezgomez — October 18, 2013 @ 8:44 am BST Oct 18,2013 | Reply

    • Arturo,
      Worth making the suggestion – I should have mentioned that you’d see the same effect without the dump():

      STATUS                 COUNT(*)
      -------------------- ----------
      FAILED                      200
      FAILED                      200
      2 rows selected

      Comment by Jonathan Lewis — October 18, 2013 @ 1:44 pm BST Oct 18,2013 | Reply

  7. Could be partition problem, something like this:

    create table t1 (id number, status VARCHAR2(10))
     partition by range (status)
      (partition P1 values less than ('G'),
       partition P2 values less than (maxvalue));
     for i in 1..200 loop
      insert into t1 values(i,'FAILED');
     end loop;
    create table t2 as select * from t1;
    alter table t1 exchange partition p2 with table t2 without validation;
    select /*+ full(t1) */ count(*) from t1 where status = 'FAILED';
    select /*+ full(t1) */ status, substr(dump(status,16),1,40) dumped, count(*)
     from t1 group by status, substr(dump(status,16),1,40);
    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

    Comment by jelinekvlado — October 18, 2013 @ 11:24 am BST Oct 18,2013 | Reply

    • Correct – that’s virtually identical to the script I wrote.

      Comment by Jonathan Lewis — October 18, 2013 @ 1:50 pm BST Oct 18,2013 | Reply

      • Exchange partition without validation is very powerfull tool, but also very dangerous (as everything powerfull) :-).
        And partition pruning make it very hard to find problem, if you have data in wrong partitions and don’t have clue what’s going on.

        Comment by Jelinek Vladimir — October 18, 2013 @ 6:38 pm BST Oct 18,2013 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by