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

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

Leave a Reply

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