Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Summary
- =======
- Create index on list partitioned table leads to results error in 12c
- =====================================================================
- set echo on
- select banner from v$version where banner like 'Oracle Database%';
- drop table t purge;
- create table t (
- p number,
- i number
- )
- partition by list (p) (
- partition p3 values (3),
- partition p4 values (4)
- );
- insert into t ( p, i) values ( 3, 3 );
- insert into t ( p, i) values ( 4, 4 );
- insert into t ( p, i) values ( 4, 4 );
- select p from t where i = ( select min(i) from t where p = 4);
- create index i on t (i);
- select p from t where i = ( select min(i) from t where p = 4);
- SQL> set echo on
- SQL> select banner from v$version where banner like 'Oracle Database%';
- BANNER
- --------------------------------------------------------------------------------
- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
- SQL> drop table t purge;
- Table T dropped.
- SQL> create table t (
- p number,
- i number
- )
- partition by list (p) (
- partition p3 values (3),
- partition p4 values (4)
- );
- Table T created.
- SQL> insert into t ( p, i) values ( 3, 3 );
- 1 row inserted.
- SQL> insert into t ( p, i) values ( 4, 4 );
- 1 row inserted.
- SQL> insert into t ( p, i) values ( 4, 4 );
- 1 row inserted.
- SQL> select p from t where i = ( select min(i) from t where p = 4);
- P
- ----------
- 4
- 4
- -- HERE COMES THE BUG
- =====================
- SQL> create index i on t (i);
- Index I created.
- SQL> select p from t where i = ( select min(i) from t where p = 4);
- P
- ----------
- 3
- No bug in 11gR1
- ===============
- SQL> select banner from v$version where banner like 'Oracle Database%';
- BANNER
- --------------------------------------------------------------------------------
- Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
- SQL> drop table t purge;
- Table T dropped.
- SQL> create table t (
- p number,
- i number
- )
- partition by list (p) (
- partition p3 values (3),
- partition p4 values (4)
- );
- Table T created.
- SQL> insert into t ( p, i) values ( 3, 3 );
- 1 row inserted.
- SQL> insert into t ( p, i) values ( 4, 4 );
- 1 row inserted.
- SQL> insert into t ( p, i) values ( 4, 4 );
- 1 row inserted.
- SQL> select p from t where i = ( select min(i) from t where p = 4);
- P
- ----------
- 4
- 4
- SQL> create index i on t (i);
- Index I created.
- SQL> select p from t where i = ( select min(i) from t where p = 4);
- P
- ----------
- 4
- 4
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement