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.1Information 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 oneTest 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