Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

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

15 April 2016 5 Comments Written by Christian Antognini

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

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

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

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

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

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…

12.1.0.2, Bug, Indexes, Partitioning, Query Optimizer
Also Extensions Created Because of SQL Plan Directives Can Invalidate Packages – Take Two
Ad: The Method R Guide to MASTERING ORACLE TRACE DATA

5 Comments

  1. Nayab Nayab
    15 April 2016    

    We have been facing these issues intermittently, what is the resolution? One if our table is almost 2tb and cant bypass the index.

    Reply
    • Christian Antognini Christian Antognini
      15 April 2016    

      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

      Reply
  2. Noons Noons
    18 April 2016    

    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…

    Reply
  3. Brian Fitzgerald Brian Fitzgerald
    30 May 2016    

    It is also possible to observe the bug in a table without subpartitions.

    http://pastebin.com/r7yAfVPF

    Reply
    • Christian Antognini Christian Antognini
      1 June 2016    

      Thank you for the information, Brian!

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

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