Advertisement
ExaGridDba

12c results error in list partitioned table

Jun 4th, 2017
158
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.82 KB | None | 0 0
  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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement