Hakan factor
The Hakan factor is used to unique map rows in a Bitmap Index to the rows in the base table. This factor is related to the max number of rows that can be stored in a single block of a table. Several factors like the column type and not null constraints influence the Hakan factor. The factor will also be recalculated when a table is modified until there is the first bitmap Index is created. Than the Hakan factor has to be protected for the existing bitmap indexes.
If a new table created to exchange data with the partitioned table, with a table layout, that include columns added after the bitmap index creation on the partitioned table, will most likely result in a different Hakan factor.
Errors
If the Hakan Factor do not match during exchange partitions, the prognosis is either of the error messages below:
- ORA-14642: “Bitmap index mismatch for tables in ALTER TABLE EXCHANGE PARTITION”
- ORA-14643: “Hakan factor mismatch for tables in ALTER TABLE EXCHANGE PARTITION”
The error can appear only if you exchange a partition with bitmap indexes
Calculate Hakan factor
select a.object_name,b.spare1 from dba_objects a, tab$ b where a.object_id=b.obj# and a.object_name = <TABLE_NAME> and a.object_type='TABLE';
create table test_tbl ( id1 number not null, id2 char(10) not null, id3 number ) partition by range(id1) ( partition p1 values less than (10), partition p2 values less than (20) ); create bitmap index test_tbl_idx1 on test_tbl(id1) local;
select a.object_name,b.spare1 from dba_objects a, tab$ b where a.object_id=b.obj# and a.object_name = 'TEST_TBL' and a.object_type='TABLE'; OBJECT_NAME SPARE1 -------------- ---------- TEST_TBL 540
alter table test_tbl drop column id3; alter table test_tbl add(id3 number not null); create bitmap index test_tbl_idx2 on test_tbl(id3) local;
select a.object_name,b.spare1 from dba_objects a, tab$ b where a.object_id=b.obj# and a.object_name = 'TEST_TBL' and a.object_type='TABLE'; OBJECT_NAME SPARE1 -------------- ---------- TEST_TBL 66076
create table test_tbl_exch as select * from test_tbl where 1=0; create bitmap index test_tbl_exch_idx1 on test_tbl_exch(id1); create bitmap index test_tbl_exch_idx2 on test_tbl_exch(id3);
select a.object_name,b.spare1 from dba_objects a, tab$ b where a.object_id=b.obj# and a.object_name in ('TEST_TBL', 'TEST_TBL_EXCH') and a.object_type='TABLE'; OBJECT_NAME SPARE1 -------------- ---------- TEST_TBL 66076 TEST_TBL_EXCH 476
alter table test_tbl add partition p3 values less than (30);
alter table test_tbl exchange partition p3 with table test_tbl_exch including indexes without validation; SQL Error: ORA-14642: Bitmap index mismatch for tables in ALTER TABLE EXCHANGE PARTITION 14642. 00000 - "Bitmap index mismatch for tables in ALTER TABLE EXCHANGE PARTITION" *Cause: The two tables in the EXCHANGE have usable bitmap indexes, and the INCLUDING INDEXES option has been specified and the tables have different hakan factors. *Action: Perform the exchange with the EXCLUDING INDEXES option or alter the bitmap indexes to be unusable
- exchange partitions with EXCLUDING INDEXES and after exchange rebuild UNUSABLE indexes
alter table test_tbl exchange partition p3 with table test_tbl_exch excluding indexes without validation; alter index test_tbl_idx1 rebuild partition(p3); alter index test_tbl_idx2 rebuild partition(p3);
- create exchange table as CREATE TABLE AS SELECT with event is set to 14529 then perform exchange
alter session set events '14529 trace name context forever, level 2'; create table test_tbl_exch1 as select * from test_tbl where 1=0; alter session set events '14529 trace name context off'; create bitmap index test_tbl_exch1_idx1 on test_tbl_exch1(id1); create bitmap index test_tbl_exch1_idx2 on test_tbl_exch1(id3); alter table test_tbl exchange partition p3 with table test_tbl_exch1 excluding indexes without validation;
- if records_per_block has been minimized for one of the tables, but not the other, either perform alter table with the NOMINIMIZE RECORDS_PER_BLOCK option for both tables, or perform alter table with the MINIMIZE RECORDS_PER_BLOCK for both tables. If the Hakan factors do not match perform alter table with the NOMINIMIZE RECORDS_PER_BLOCK option for both tables
- drop BITMAP indexes, perform exchange with INCLUDING INDEXES for the rest of indexes, then recreate BITMAP indexes
- if you need to modify layout of your table using ALTER TABLE that can influence your Hakan factor do it without bitmap indexes.
- Monitor your Hakan factor 🙂
Thanks for this, I haven’t come across this particular problem yet, but it’s useful to know that it exists.
Explained this rather complex thing very nicely. Thanks
Explained this rather complex thing very nicely. Thanks
Agreed 🙂 🙂
Thanks for this writeup. I ran across this today.
I had an existing partitioned table with bitmap indexes. The table was compressed. Four columns in the partitioned table were removed a while back. All four columns each had their own bitmap indexes. All bitmap indexes were disabled prior to dropping the columns and then rebuilt after the columns were added.
After all that, an exchange table was created from the DDL found in our data model. We did not use CREATE TABLE AS.
The partition exchange failed. I think it was because the two tables didn’t start out with the exact same DDL.
After spending quite a bit of time comparing the two tables within the database and researching the error online, the solution was to drop the partitioned table and recreate it from the DDL in the data model. The data in the partitioned table was backed up and then restored into the partitioned table. After that, the partition exchange worked the first time.