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:
rem rem Script: bitmap_join_pt_bug.sql rem Dated: Feb 2016 rem Author: J.P.Lewis rem rem Last tested rem 19.3.0.0 rem 18.1.0.0 (LiveSQL) rem 12.2.0.1 rem 12.1.0.2 rem 11.2.0.4 rem 10.2.0.5 rem 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 -- > comment to avoid wordpress format issue. ; 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 -- > comment to avoid wordpress format issue. ; 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 12.2.0.1, 12.1.0.2, 11.2.0.4 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.
Update (May 2018)
This problem still exists in 18.1 (tested on LiveSQL). Of course you might be thinking at this point that you would avoid the problem by using the command “create table t1 for exchange with table pt_range;” (a possibility introduced in 12.2) but that command only creates the correct column structure for the table and doesn’t create any indexes or, even, constraints.
Funnily enough, though, the command fails anyway with Oracle error: “ORA-14424: CREATE TABLE FOR EXCHANGE cannot be used with certain types of tables.” According to the oraus.msg file “certain types of tables” includes: “cluster, temporary, index-organized table (IOT), external tables”. I haven’t yet found anything that says tables with bitmap join indexes will be excluded.
Update (May 2020)
The behaviour is unchanged in 19.3
Logically, it shouldn’t matter whether you create index 1a before 2a or 2a before 1a. So this is a bug where there is some physical (ordering) dependency ?
Comment by Hemant K Chitale — February 2, 2016 @ 8:52 am GMT Feb 2,2016 |
Hemant,
You’re right that logically it shouldn’t matter but I don’t have any good idea of what might cause the problem. There are some global temporary tables used to maintain bitmap join indexes, and these have names generated by a sequence – so perhaps your ordering thought is correct in that the code may (accidentally) look at the GTTs in order and find as a consequence that their associated dimension tables don’t seem to match.
Comment by Jonathan Lewis — February 2, 2016 @ 9:51 am GMT Feb 2,2016 |