Wrong Results from a RANGE-LIST PARTITIONED TABLE- Bug 5882821
(Doc ID 1051265.1)
Last updated on MARCH 27, 2019
Applies to:
Oracle Database - Enterprise Edition - Version 10.2.0.2 to 11.1.0.6 [Release 10.2 to 11.1]Information in this document applies to any platform.
Symptoms
A query against a range-list partitioned table is returning incorrect results when an index is used.
Wrong Results Nature
SQL> select /*+ INDEX(TEST1 TEST1_ix) */ part_key, subpart_key, data_id from TEST1
where (part_key=1 and subpart_key='M' and data_id='A')
or (part_key=1 and subpart_key='N' and data_id='B');
PART_KEY SUBPAR DATA_I
---------- ------ ------
1 M A
1 M B
1 N A
1 N B
SQL> select /*+ NO_INDEX(TEST1 TEST1_ix) */ part_key, subpart_key,data_id
from TEST1
where (part_key=1 and subpart_key='M' and data_id='A')
or (part_key=1 and subpart_key='N' and data_id='B');
PART_KEY SUBPAR DATA_I
---------- ------ ------
1 M A
1 N B
the second result is the correct one
where (part_key=1 and subpart_key='M' and data_id='A')
or (part_key=1 and subpart_key='N' and data_id='B');
PART_KEY SUBPAR DATA_I
---------- ------ ------
1 M A
1 M B
1 N A
1 N B
SQL> select /*+ NO_INDEX(TEST1 TEST1_ix) */ part_key, subpart_key,data_id
from TEST1
where (part_key=1 and subpart_key='M' and data_id='A')
or (part_key=1 and subpart_key='N' and data_id='B');
PART_KEY SUBPAR DATA_I
---------- ------ ------
1 M A
1 N B
the second result is the correct one
Test Case
create table TEST1
(
part_key number not null,
subpart_key varchar2(6) not null,
data_id varchar2(6) not null
)
partition by range (part_key)
subpartition by list (subpart_key)
(
partition test_p1 values less than (2)
(
subpartition test_p1_s1 values ('M'),
subpartition test_p1_s2 values ('N'),
subpartition test_p1_s3 values ('O'),
subpartition test_p1_s4 values ('P')
),
partition test_p2 values less than (maxvalue)
(
subpartition test_p2_s1 values ('M'),
subpartition test_p2_s2 values ('N'),
subpartition test_p2_s3 values ('O'),
subpartition test_p2_s4 values ('P')
)
);
insert into TEST1 values (1, 'M', 'A');
insert into TEST1 values (1, 'N', 'A');
insert into TEST1 values (1, 'O', 'A');
insert into TEST1 values (1, 'P', 'A');
insert into TEST1 values (1, 'M', 'B');
insert into TEST1 values (1, 'N', 'B');
insert into TEST1 values (1, 'O', 'B');
insert into TEST1 values (1, 'P', 'B');
insert into TEST1 values (1, 'M', 'C');
insert into TEST1 values (1, 'N', 'C');
insert into TEST1 values (1, 'O', 'C');
insert into TEST1 values (1, 'P', 'C');
insert into TEST1 values (1, 'M', 'D');
insert into TEST1 values (1, 'N', 'D');
insert into TEST1 values (1, 'O', 'D');
insert into TEST1 values (1, 'P', 'D');
insert into TEST1 values (2, 'M', 'A');
insert into TEST1 values (2, 'N', 'A');
insert into TEST1 values (2, 'O', 'A');
insert into TEST1 values (2, 'P', 'A');
insert into TEST1 values (2, 'M', 'B');
insert into TEST1 values (2, 'N', 'B');
insert into TEST1 values (2, 'O', 'B');
insert into TEST1 values (2, 'P', 'B');
insert into TEST1 values (2, 'M', 'C');
insert into TEST1 values (2, 'N', 'C');
insert into TEST1 values (2, 'O', 'C');
insert into TEST1 values (2, 'P', 'C');
insert into TEST1 values (2, 'M', 'D');
insert into TEST1 values (2, 'N', 'D');
insert into TEST1 values (2, 'O', 'D');
insert into TEST1 values (2, 'P', 'D');
commit;
create index TEST1_ix on TEST1 (data_id) local;
exec dbms_stats.gather_table_stats('SCHEMANAME','TEST1');
SQL> select /*+ INDEX(TEST1 TEST1_ix) */ part_key, subpart_key, data_id
from TEST1
where (part_key=1 and subpart_key='M' and data_id='A')
or (part_key=1 and subpart_key='N' and data_id='B');
PART_KEY SUBPAR DATA_I
---------- ------ ------
1 M A
1 M B
1 N A
1 N B
(
part_key number not null,
subpart_key varchar2(6) not null,
data_id varchar2(6) not null
)
partition by range (part_key)
subpartition by list (subpart_key)
(
partition test_p1 values less than (2)
(
subpartition test_p1_s1 values ('M'),
subpartition test_p1_s2 values ('N'),
subpartition test_p1_s3 values ('O'),
subpartition test_p1_s4 values ('P')
),
partition test_p2 values less than (maxvalue)
(
subpartition test_p2_s1 values ('M'),
subpartition test_p2_s2 values ('N'),
subpartition test_p2_s3 values ('O'),
subpartition test_p2_s4 values ('P')
)
);
insert into TEST1 values (1, 'M', 'A');
insert into TEST1 values (1, 'N', 'A');
insert into TEST1 values (1, 'O', 'A');
insert into TEST1 values (1, 'P', 'A');
insert into TEST1 values (1, 'M', 'B');
insert into TEST1 values (1, 'N', 'B');
insert into TEST1 values (1, 'O', 'B');
insert into TEST1 values (1, 'P', 'B');
insert into TEST1 values (1, 'M', 'C');
insert into TEST1 values (1, 'N', 'C');
insert into TEST1 values (1, 'O', 'C');
insert into TEST1 values (1, 'P', 'C');
insert into TEST1 values (1, 'M', 'D');
insert into TEST1 values (1, 'N', 'D');
insert into TEST1 values (1, 'O', 'D');
insert into TEST1 values (1, 'P', 'D');
insert into TEST1 values (2, 'M', 'A');
insert into TEST1 values (2, 'N', 'A');
insert into TEST1 values (2, 'O', 'A');
insert into TEST1 values (2, 'P', 'A');
insert into TEST1 values (2, 'M', 'B');
insert into TEST1 values (2, 'N', 'B');
insert into TEST1 values (2, 'O', 'B');
insert into TEST1 values (2, 'P', 'B');
insert into TEST1 values (2, 'M', 'C');
insert into TEST1 values (2, 'N', 'C');
insert into TEST1 values (2, 'O', 'C');
insert into TEST1 values (2, 'P', 'C');
insert into TEST1 values (2, 'M', 'D');
insert into TEST1 values (2, 'N', 'D');
insert into TEST1 values (2, 'O', 'D');
insert into TEST1 values (2, 'P', 'D');
commit;
create index TEST1_ix on TEST1 (data_id) local;
exec dbms_stats.gather_table_stats('SCHEMANAME','TEST1');
SQL> select /*+ INDEX(TEST1 TEST1_ix) */ part_key, subpart_key, data_id
from TEST1
where (part_key=1 and subpart_key='M' and data_id='A')
or (part_key=1 and subpart_key='N' and data_id='B');
PART_KEY SUBPAR DATA_I
---------- ------ ------
1 M A
1 M B
1 N A
1 N B
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Cause |
Solution |
References |