Hakan factor ORA-14642, ORA-14643

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

Oracle provides following query to calculate Hakan factor for a table:

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';
NOTE – this query will work only as user SYS
Issue example
Create initial table TEST_TBL
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;
Check Hakan factor for TEST_TBL
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
Modify TEST_TBL to influence Hakan factor
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;
Check Hakan factor for TEST_TBL. As you can notice it has changed drastically from 540 to 66076
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
Add new exchange table TEST_TBL_EXCH as copy from TEST_TBL
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);
Check Hakan factor for TEST_TBL and TEST_TBL_EXCH
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
Add new partition to TEST_TBL
alter table test_tbl add partition p3 values less than (30);
Trying to exchange new partition TEST_TBL(P3) with table TEST_TBL_EXCH causes error related to different Hakan factors.
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
Avoiding Hakan factor
There are a few options to resolve this issue:
  • 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
General advice
  • 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 🙂
Have a fun 🙂
  Tomasz

4 thoughts on “Hakan factor ORA-14642, ORA-14643

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.