Wrong Results from a RANGE-LIST PARTITIONED TABLE- Bug 5882821 (Doc ID 1051265.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Server - 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(joetest joetest_ix) */ part_key, subpart_key, data_id from joetest
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(joetest joetest_ix) */ part_key, subpart_key,data_id
from joetest
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 joetest
(
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 joetest values (1, 'M', 'A');
insert into joetest values (1, 'N', 'A');
insert into joetest values (1, 'O', 'A');
insert into joetest values (1, 'P', 'A');
insert into joetest values (1, 'M', 'B');
insert into joetest values (1, 'N', 'B');
insert into joetest values (1, 'O', 'B');
insert into joetest values (1, 'P', 'B');
insert into joetest values (1, 'M', 'C');
insert into joetest values (1, 'N', 'C');
insert into joetest values (1, 'O', 'C');
insert into joetest values (1, 'P', 'C');
insert into joetest values (1, 'M', 'D');
insert into joetest values (1, 'N', 'D');
insert into joetest values (1, 'O', 'D');
insert into joetest values (1, 'P', 'D');
insert into joetest values (2, 'M', 'A');
insert into joetest values (2, 'N', 'A');
insert into joetest values (2, 'O', 'A');
insert into joetest values (2, 'P', 'A');
insert into joetest values (2, 'M', 'B');
insert into joetest values (2, 'N', 'B');
insert into joetest values (2, 'O', 'B');
insert into joetest values (2, 'P', 'B');
insert into joetest values (2, 'M', 'C');
insert into joetest values (2, 'N', 'C');
insert into joetest values (2, 'O', 'C');
insert into joetest values (2, 'P', 'C');
insert into joetest values (2, 'M', 'D');
insert into joetest values (2, 'N', 'D');
insert into joetest values (2, 'O', 'D');
insert into joetest values (2, 'P', 'D');

commit;

create index joetest_ix on joetest (data_id) local;

exec dbms_stats.gather_table_stats('scott','JOETEST');
SQL> select /*+ INDEX(joetest joetest_ix) */ part_key, subpart_key, data_id
from joetest
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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms