Written by One of my customers that recently upgraded to 12c hit a bug (22913528) that I think is good to be aware of. Note that as the title of this post states, the problem only occur in 12.1.0.2. At least, I wasn’t able to reproduce it in any other version.
To reproduce it you simply need a composite partitioned table with a non-partitioned or global-partitioned index. In other words, if all your indexes are local, you shouldn’t be impacted by the bug.
The SQL statements I use to prepare the schema to reproduce it are the following:
CREATE TABLE t (p DATE, s number, i DATE)
PARTITION BY RANGE (p) INTERVAL(numtoyminterval(1,'MONTH'))
SUBPARTITION BY LIST (s) SUBPARTITION TEMPLATE (
SUBPARTITION s1 VALUES (1),
SUBPARTITION s2 VALUES (2),
SUBPARTITION s3 VALUES (3),
SUBPARTITION s4 VALUES (4),
SUBPARTITION s5 VALUES (5),
SUBPARTITION s6 VALUES (6),
SUBPARTITION s7 VALUES (7)
)(
PARTITION p_others VALUES LESS THAN (to_date('2016-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
);
INSERT INTO t
SELECT to_date('2016-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')-rownum,
mod(rownum,7)+1,
trunc(sysdate+mod(rownum,7))
FROM dual
CONNECT BY level <= 365;
COMMIT;
CREATE INDEX i ON t (i);
REM CREATE INDEX i ON t (i) GLOBAL PARTITION BY HASH (i) PARTITIONS 4;
REM CREATE INDEX i ON t (i) LOCAL;
execute dbms_stats.gather_table_stats(user, 'T') |
Note that the minimum value for each subpartition key is the following:
SQL> SELECT s, min(i)
2 FROM t
3 GROUP BY s;
S MIN(I)
---------- ---------
1 15-APR-16
2 16-APR-16
3 17-APR-16
4 18-APR-16
5 19-APR-16
6 20-APR-16
7 21-APR-16 |
The query to reproduce the bug is the following (the right answer is “17-APR-16”):
SQL> SELECT min(i) FROM t WHERE s = 3; MIN(I) --------- 15-APR-16 |
The execution plan shows that the query optimizer “loses” the partition pruning information:
SQL> EXPLAIN PLAN FOR SELECT min(i) FROM t WHERE s = 3; SQL> SELECT * FROM table(dbms_xplan.display(format=>'basic +partition')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------- Plan hash value: 1554646154 ------------------------------------------- | Id | Operation | Name | ------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | INDEX FULL SCAN (MIN/MAX)| I | ------------------------------------------- |
It goes without saying that when the index isn’t used the result is correct:
SQL> SELECT /*+ no_index(t) */ min(i) FROM t WHERE s = 3; MIN(I) --------- 17-APR-16 SQL> EXPLAIN PLAN FOR SELECT /*+ no_index(t) */ min(i) FROM t WHERE s = 3; SQL> SELECT * FROM table(dbms_xplan.display(format=>'basic +partition')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------- Plan hash value: 1728811753 -------------------------------------------------------- | Id | Operation | Name | Pstart| Pstop | -------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 1 | SORT AGGREGATE | | | | | 2 | PARTITION RANGE ALL | | 1 |1048575| | 3 | PARTITION LIST SINGLE| | 3 | 3 | | 4 | TABLE ACCESS FULL | T | KEY | KEY | -------------------------------------------------------- |
If you are on 12.1.0.2 (or you are thinking about upgrading to it), because of this bug I strongly advise you to check whether you have non local indexes…
We have been facing these issues intermittently, what is the resolution? One if our table is almost 2tb and cant bypass the index.
Hi Nayab
I’m not aware of a way to using the INDEX FULL SCAN (MIN/MAX) operation and not getting wrong results.
Best,
Chris
Thanks for this, Christian.
Let’s hope a fix for this problem gets included in a PSU soon.
It does not reflect well on Oracle’s continued efforts to increase confidence in its constantly buggy software.
Reminded me of the good old days of 10.0 when wrong results were more common than correct ones…
It is also possible to observe the bug in a table without subpartitions.
http://pastebin.com/r7yAfVPF
Thank you for the information, Brian!