Hi Tom,
We have found the following problem in Oracle 19c. We have a table subpartitioned by list that has more than 2 values as partitioning keys:
create table T19c
(
Filed1 varchar2(50),
Filed2 varchar2(50),
Filed3 varchar2(50),
Filed4 varchar2(50)
)
partition by list(Filed1)
subpartition by list(Filed2, Filed3, Filed4)
subpartition template
(
SUBPARTITION S_A_A_A VALUES ('A1','A1','A1')
,SUBPARTITION S_A_A_B values('A1','A1','B1')
,SUBPARTITION S_A_A_C values('A1','A1','C1')
,SUBPARTITION S_A_A_D values('A1','A1','D1')
)
(
PARTITION P_TST1 VALUES ('TST1')
);
Let's say we have inserted the following values in the table:
INSERT INTO T19c VALUES ('TST1', 'A1', 'A1', 'A1');
INSERT INTO T19c VALUES ('TST1', 'A1', 'A1', 'B1');
INSERT INTO T19c VALUES ('TST1', 'A1', 'A1', 'C1');
INSERT INTO T19c VALUES ('TST1', 'A1', 'A1', 'D1');
The problem is that when you run the below query, it returns wrong results:
select * from T19c where Filed4 <> 'A1';
Instead of returning the rows that have values for vchcol4 <> 'A', it returns "no data found". We have noticed that this problem does not persist if the subpartitions are created with 2 values as partitioning keys.
Is there any way to fix this without having to change the subpartitioning logic?
Thanks,
Alex
That's... not right!
Contact support to get a fix for this.
In the meantime I think you'll have to change the logic - I can't see another workaround at this stage.