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 is supposed to check your data is absolutely perfect in the face of all possible accidents.
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 |
Correct.
Comment by Jonathan Lewis — October 17, 2013 @ 11:16 pm BST Oct 17,2013 |
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 |
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 |
Joel,
The plan, of course, would be far too helpful.
Comment by Jonathan Lewis — October 18, 2013 @ 1:50 pm BST Oct 18,2013 |
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 |
Charlie,
That would make the problem a bit too easy to solve.
Comment by Jonathan Lewis — October 18, 2013 @ 6:56 am BST Oct 18,2013 |
Thanks–even though I didn’t solve it right off the bat, this was a fun one to kick around.
Comment by Charlie Callaway — October 18, 2013 @ 4:06 pm BST Oct 18,2013 |
Another guess – virtual column with false-deterministic function.
Comment by Valentin Nikotin — October 18, 2013 @ 8:13 am BST Oct 18,2013 |
Valentin,
No.
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 |
To try to isolate the issue, could you send output of this:
Regards
Arturo Gutierrez
Comment by arturogutierrezgomez — October 18, 2013 @ 8:44 am BST Oct 18,2013 |
Arturo,
Worth making the suggestion – I should have mentioned that you’d see the same effect without the dump():
Comment by Jonathan Lewis — October 18, 2013 @ 1:44 pm BST Oct 18,2013 |
Could be partition problem, something like this:
Comment by jelinekvlado — October 18, 2013 @ 11:24 am BST Oct 18,2013 |
Correct – that’s virtually identical to the script I wrote.
Comment by Jonathan Lewis — October 18, 2013 @ 1:50 pm BST Oct 18,2013 |
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 |
was not able to identifiy who the heck is “jelinekvlado”. Now I can see it was you :)
Comment by Pavol Babel — October 21, 2013 @ 9:10 am BST Oct 21,2013