If you don’t want to read the story, the summary for this article is:
If you create bitmap join indexes on a partitioned table and you use partition exchanges to load data into the table then make sure you create the bitmap join indexes on the loading tables in exactly the same order as you created them on the partitioned table or the exchange will fail with the (truthful not quite complete) error: ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION.
My story starts with this OTN posting from John Hall where he found after a year of successful batch loading one of his partition exchanges was raising error 14098. After an exchange of ideas, user rp0428 came up with a query against sys.jijoin$ (one of the tables behind bitmap join indexes) that allowed John Hall to see that the indexes on the exchange table had been created in a different order from that of the partitioned table. I did a quick test to see if this might be relevant (it shouldn’t be, it isn’t with “normal” indexes or function-based indexes, or virtual columns) and didn’t manage to reproduce the problem with two dimension tables and two bitmap join indexes.
Fortunately John didn’t take my word for it and tested the idea on a clone of the production system – and found that the order of creation did matter. His system, however, had 9 dimension tables and 33 bitmap join indexes – which shouldn’t have made any difference in principle, but maybe it was something to do with having several indexes on the same table, maybe it was something to do with have far more tables or far more indexes than I had. So I built a larger test case with 6 dimension tables and six indexes per table – and reproduced the problem.
Then I started cutting back to see where the problem appeared, and found that all it took was one dimension with two indexes, or two dimensions with one index each – whatever I had done in my “quick test” I had clearly done it too quickly and done something wrong. (Unfortunately I had overwritten most of the code from the original quick test while building the larger test, so I couldn’t go back and see where the error was.)
Here, then, is the minimal test case that I finally ran to demonstrate that switching the order of index creation on the exchange table causes the exchange to fail:
drop table pt_range purge; drop table t1 purge; drop table dim_1 purge; drop table dim_2 purge; prompt ================= prompt Partitioned table prompt ================= create table pt_range ( id, grp1, grp2, padding ) nologging partition by range(id) ( partition p2001 values less than (2001), partition p4001 values less than (4001), partition p6001 values less than (6001), partition p8001 values less than (8001) ) as select rownum id, trunc(rownum/100) grp1, trunc(rownum/100) grp2, rpad('x',100) padding from all_objects where rownum <= 8000 ; prompt ================================================ prompt Exchange table - loaded to match partition p8001 prompt ================================================ alter table pt_range add constraint pt_pk primary key (id) using index local; create table t1 ( id, grp1, grp2, padding ) as select rownum + 6000 id, trunc(rownum/100) grp1, trunc(rownum/100) grp2, rpad('x',100) padding from all_objects where rownum <= 2000 ; alter table t1 add constraint t1_pk primary key (id); execute dbms_stats.gather_table_stats(user,'pt_range') execute dbms_stats.gather_table_stats(user,'t1') prompt ================ prompt dimension tables prompt ================ create table dim_1 as select distinct grp1, cast('A'||grp1 as varchar2(3)) agrp1, cast('B'||grp1 as varchar2(3)) bgrp1 from t1 ; create table dim_2 as select * from dim_1; prompt =============================== prompt Primary keys required for BMJIs prompt =============================== alter table dim_1 add constraint d1_pk primary key (grp1); alter table dim_2 add constraint d2_pk primary key (grp1); execute dbms_stats.gather_table_stats(user,'dim_1') execute dbms_stats.gather_table_stats(user,'dim_2') prompt ============================ prompt Creating bitmap join indexes prompt ============================ create bitmap index pt_1a on pt_range(d1.agrp1) from pt_range pt, dim_1 d1 where d1.grp1 = pt.grp1 local ; create bitmap index pt_2a on pt_range(d2.agrp1) from pt_range pt, dim_2 d2 where d2.grp1 = pt.grp2 local ; prompt ==================================================== prompt Pick your index creation order on the exchange table prompt ==================================================== create bitmap index t1_1a on t1(d1.agrp1) from t1, dim_1 d1 where d1.grp1 = t1.grp1 ; create bitmap index t1_2a on t1(d2.agrp1) from t1, dim_2 d2 where d2.grp1 = t1.grp2 ; -- create bitmap index t1_1a on t1(d1.agrp1) from t1, dim_1 d1 where d1.grp1 = t1.grp1 ; prompt ================== prompt Exchanging (maybe) prompt ================== alter table pt_range exchange partition p8001 with table t1 including indexes without validation ;
I’ve got the same create statement twice for one of the bitmap join indexes – as it stands the indexes will be created in the right order and the exchange will work; if you comment out the first t1_1a create and uncomment the second the exchange will fail. (If you comment out the ‘including indexes’ then the exchange will succeed irrespective of the order of index creation, but that rather defeats the point of being able to exchange partitions.)
I’ve reproduced the problem in 126.96.36.199, 188.8.131.52 and 10.2.0.5
Footnote: running an extended trace didn’t help me work out how Oracle is detecting the mismatch, presumably it’s something that gets into the dictionary cache in a general “load the index definition” step; but it did show me that (in the “without validation” case) the code seems to check the correctness of the exchange table’s primary key data BEFORE checking whether the indexes match properly.