Need a unique gift idea?
A Pastebin account makes a great Christmas gift
SHARE
TWEET

12c results error in list partitioned table

ExaGridDba Jun 4th, 2017 (edited) 80 Never
Upgrade to PRO!
ENDING IN00days00hours00mins00secs
 
  1. Summary
  2. =======
  3. Create index on list partitioned table leads to results error in 12c
  4. =====================================================================
  5.  
  6. set echo on
  7. select banner from v$version where banner like 'Oracle Database%';
  8. drop table t purge;
  9. create table t (
  10.         p number,
  11.         i number
  12. )
  13. partition by list (p) (
  14.         partition p3 values (3),
  15.         partition p4 values (4)
  16. );
  17.  
  18. insert into t ( p, i) values ( 3, 3 );
  19. insert into t ( p, i) values ( 4, 4 );
  20. insert into t ( p, i) values ( 4, 4 );
  21.  
  22. select p from t where i = ( select min(i) from t where p = 4);
  23.  
  24. create index i on t (i);
  25.  
  26. select p from t where i = ( select min(i) from t where p = 4);
  27.  
  28.  
  29.  
  30. SQL> set echo on
  31. SQL> select banner from v$version where banner like 'Oracle Database%';
  32.  
  33. BANNER                                                                        
  34. --------------------------------------------------------------------------------
  35. Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production    
  36.  
  37. SQL> drop table t purge;
  38.  
  39. Table T dropped.
  40.  
  41. SQL> create table t (
  42.         p number,
  43.         i number
  44. )
  45. partition by list (p) (
  46.         partition p3 values (3),
  47.         partition p4 values (4)
  48. );
  49.  
  50. Table T created.
  51.  
  52. SQL> insert into t ( p, i) values ( 3, 3 );
  53.  
  54. 1 row inserted.
  55.  
  56. SQL> insert into t ( p, i) values ( 4, 4 );
  57.  
  58. 1 row inserted.
  59.  
  60. SQL> insert into t ( p, i) values ( 4, 4 );
  61.  
  62. 1 row inserted.
  63.  
  64. SQL> select p from t where i = ( select min(i) from t where p = 4);
  65.  
  66.          P
  67. ----------
  68.          4
  69.          4
  70.  
  71. -- HERE COMES THE BUG
  72. =====================
  73.  
  74. SQL> create index i on t (i);
  75.  
  76. Index I created.
  77.  
  78. SQL> select p from t where i = ( select min(i) from t where p = 4);
  79.  
  80.          P
  81. ----------
  82.          3
  83.  
  84.  
  85. No bug in 11gR1
  86. ===============
  87.  
  88.  
  89. SQL> select banner from v$version where banner like 'Oracle Database%';
  90.  
  91. BANNER                                                                        
  92. --------------------------------------------------------------------------------
  93. Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production    
  94.  
  95. SQL> drop table t purge;
  96.  
  97. Table T dropped.
  98.  
  99. SQL> create table t (
  100.         p number,
  101.         i number
  102. )
  103. partition by list (p) (
  104.         partition p3 values (3),
  105.         partition p4 values (4)
  106. );
  107.  
  108. Table T created.
  109.  
  110. SQL> insert into t ( p, i) values ( 3, 3 );
  111.  
  112. 1 row inserted.
  113.  
  114. SQL> insert into t ( p, i) values ( 4, 4 );
  115.  
  116. 1 row inserted.
  117.  
  118. SQL> insert into t ( p, i) values ( 4, 4 );
  119.  
  120. 1 row inserted.
  121.  
  122. SQL> select p from t where i = ( select min(i) from t where p = 4);
  123.  
  124.          P
  125. ----------
  126.          4
  127.          4
  128.  
  129. SQL> create index i on t (i);
  130.  
  131. Index I created.
  132.  
  133. SQL> select p from t where i = ( select min(i) from t where p = 4);
  134.  
  135.          P
  136. ----------
  137.          4
  138.          4
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