daily pastebin goal
1%
SHARE
TWEET

Incorrect results 12c global index

ExaGridDba May 30th, 2016 (edited) 133 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Incorrect results from Oracle 12c.
  2. ==================================
  3.  
  4. This is a simpler case than found in
  5. https://antognini.ch/2016/04/wrong-results-involving-index-full-scan-minmax-in-12-1-0-2/
  6.  
  7. [oracle@stormking cdb12102 partition]$ sqlplus /nolog @ conn.u.sql
  8.  
  9. SQL*Plus: Release 12.1.0.2.0 Production on Mon May 30 13:22:18 2016
  10.  
  11. Copyright (c) 1982, 2014, Oracle.  All rights reserved.
  12.  
  13. Connected.
  14. SQL> host cat results2.sql
  15. drop table t ;
  16. create table t (
  17.         p number,
  18.         i date
  19. )
  20. partition by list (p) (
  21.         partition p1 values (1),
  22.         partition p2 values (2),
  23.         partition p3 values (3),
  24.         partition p4 values (4),
  25.         partition p5 values (5),
  26.         partition p6 values (6),
  27.         partition p7 values (7)
  28. );
  29.  
  30. insert into t ( p, i)
  31. select mod(rownum,7)+1,
  32. trunc(sysdate+mod(rownum,7))
  33. from dual
  34. connect by level <= 365;
  35.  
  36. select p, min(i)
  37. from t
  38. group by p;
  39.  
  40. select min(i) right from t where p = 3;
  41.  
  42. create index i on t (i);
  43.  
  44. select min(i) wrong from t where p = 3;
  45.  
  46. alter session set "_fix_control" = '16346018:off';
  47. select min(i) from t where p = 3;
  48.  
  49. SQL> @ results2.sql
  50.  
  51. Table dropped.
  52.  
  53.  
  54. Table created.
  55.  
  56.  
  57. 365 rows created.
  58.  
  59.  
  60.          P MIN(I)
  61. ---------- ----------
  62.          1 2016-05-30
  63.          2 2016-05-31
  64.          3 2016-06-01
  65.          4 2016-06-02
  66.          5 2016-06-03
  67.          6 2016-06-04
  68.          7 2016-06-05
  69.  
  70. 7 rows selected.
  71.  
  72.  
  73. RIGHT
  74. ----------
  75. 2016-06-01
  76.  
  77.  
  78. Index created.
  79.  
  80.  
  81. WRONG
  82. ----------
  83. 2016-05-30
  84.  
  85.  
  86. Session altered.
  87.  
  88.  
  89. MIN(I)
  90. ----------
  91. 2016-06-01
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top