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
bug 5882821
Solution
apply patch 5882821 if available for your OS, and version.
Workaround:
Set _predicate_elimination_enaled to FALSE
© 2009, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.
相关文章 | Related posts:
- Script:List OBJECT DEPENDENT
- Diagnosing ORA-14097 On Alter Table Exchange Partition
- How to Re-Organize a Table Online
- Script:List SORT ACTIVITY
- How to locate SCN/Time of DROP TABLE using LogMiner
- Global statistics – An Explanation
- Script:List Buffer Cache Details
- Script: Computing Table Size
- VIEW: X$KTUXE – Transaction Entry (table)
- Script:List NLS Parameters and Timezone
最新评论