Search

Top 60 Oracle Blogs

Recent comments

Wrong Results Involving INDEX FULL SCAN (MIN/MAX) in 12.1.0.2

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…