Everything about Hakan Factor

Yes, EVERYTHING! At least that's the aim; primed by the lack of official Oracle documentation, and in effect, not having one single piece of document to refer to.

There are, in total, 28 documents in Oracle support (as of the date this post is published) that have a reference to Hakan Factor; but not once an all encompassing definition of this attribute and its effect on Oracle's inner working is provided!

What is Hakan Factor?

Oracle provides its best explanation in Metalink 1389444.1: "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 the a single block of a table;" Although I've noticed Richard Foote believes concretely that Hakan Factor is in fact the "maximum number of possible rows that could potentially fit in a block."

Oracle states (same Metalink): "Several factors like the column type and not null constraints influence the Hakan factor;" but those "several factors" are not explicitly mentioned.

When does it come into play, and what's the prognosis?

If you'd need to exchange partitions of a table that has Bitmap Indexes defined on it, the Hakan Factor for the partitioned table AND the non-partitioned table should be the same; otherwise the exchange operation would fail. 

Oracle checks the Hakan Factor on both sides "to prevent corruptions. The check

is introduced in the fix for bug 2114490." (Ref: Bug ID 7352308)

If the Hakan Factors do not match, the prognosis is either of the error messages below:

ORA-14642: Bitmap index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

OR

ORA-14643: Hakan factor mismatch for tables in ALTER TABLE EXCHANGE PARTITION

How can you retrieve its current value?

Oracle provides the following query (Metalink 248643.1) for this purpose:

SQL> select a.object_name,b.spare1
     from dba_objects a, tab$ b
     where a.object_id=b.obj#
     and a.object_name in ('<Partioned_table>','<Non-Partitioned-Table>')
     and a.object_type='TABLE';

The query should be run under SYS user.

Can Hakan Factor change?

Yes. According to Metalink 397827.1 "The HAKAN-factor (records_per_block) is re-calculated when an ALTER TABLE is executed" which is only logical. The case that the Metalink refers to, takes place in "versions 9.2.0.7 to 10.2.0.5" when unused columns are dropped before exchange partitions, that would lead to a change in the "RECORDS_PER_BLOCK" value.

Oracle warns about the hidden impact of changing "null" constraints on the Hakan Factor as well;  A very interesting scenario is mentioned in Bug ID 6026618. I ran the bit of code on oracle 11.2.0.3 with 32K blocks:

create table hk(c char(1) not null, d char(100) not null);
create bitmap index bi on hk(c);
-- Hakan Factor is 311

alter table hk modify (d null);
-- Hakan Factor is still 311
create table hk2 as select * from hk;

select o.name, mod(t.spare1, 32768) hakan
from tab$ t, obj$ o
where t.obj# = o.obj#
and name in ('HK', 'HK2');
-- Hakan factor for "hk2" is 2971

It's obvious that changing the "not null" constraint to "null" would affect the row length of the records, and therefore RECORDS_PER_BLOCK would change too, but the change will not be obvious until an attempt is made to create a replica (CTAS) of the original table.

In Metalink 1389444.1 Oracle clarifies that "The factor will also be recalculated when a table is modified until there is the first bitmap Index is created," and in Bug ID 8407142 more detailed test scenarios (compared to the one in 6026618) can be found that manifest the issue on compressed tables.

Although if you add columns to a non-compressed table after the first Bitmap index is created, the Hakan factor still changes; at least on 11.2.0.3. So the above description only holds for compressed tables.

Is there a way to set the value of the Hakan Factor?

No, or at least not directly; you would assume that a proficient DBA has a full grasp of the row sizes, so he/she can control the number of rows in a block through (for example) manipulating free space in blocks.

But you can directly reset Hakan Factor's value. According to Bug ID 6026618 "Altering the table for instance with nominimize records_per_block resets the factor;" Although "The nominimize can only be done if all bitmapped indexes are dropped first."

Again, you do not need to drop the indexes to have the Hakan Factor recalculated; The indexes can be set to unused, but some might argue that rebuilding the index afterwards has the same cost as creating a new index. I'm not sure how dropping an index is compared to setting it to unused.

Is there a way to avoid the consequences of having different Hakan Factors?

Oracle generally provides solutions that seem to be devised by people totally uninterested in real-life scenarios, specifically scalability! 

Solution 1 (Metalink 248643.1):

"Issue the exchange command with EXCLUDING INDEXES, then rebuild partitioned tables' unusable indexes."

Solution 2 (Metalink 248643.1):

"Drop Bitmap indexes of tables, perform the exchange command with INCLUDE INDEXES for the rest of the indexes, then recreate Bitmap indexes."

Solution 3 (Metalink 1389444.1):

"Create the exchange table with Create Table As Select (CTAS) while the event 14529  is set, then EXCHANGE PARTITION will go through. The event should only be set for CTAS."

Solution 4:

The proposed action for ORA-14643 is clear enough:

"Action: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."

How to make sure that differences in Hakan Factor do not happen?

Two different situations have to be considered: 

1. When both tables involved in partition exchange do not have any Bitmap indexes:

ALTER TABLE X NOMINIMIZE RECORDS_PER_BLOCK;

This statement basically recalculates the RECORDS_PER_BLOCK, which for two identical tables should be the same; unless either your tables are located on different tablespaces with different block sizes, or they're not identical in the first place!

2. When Bitmap indexes exist: Metalink 1389444.1: "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." 

i.e. Do not ALTER TABLEs involved in the partition exchange after Bitmap Indexes are created on them!

Just for clarification, the above Metalink refers to situations that the non-partitioned table is created AFTER the changes were made in the partitioned table; so both tables look identical in terms of column names and data types.

Final thoughts:

[Considering my previous post as well,] I only wonder why Oracle has failed to provide a detailed description on the requirements of partition exchange operation? What [explicitly and specifically] constitutes "identical" tables, and why a full list of logical and physical attributes involved is not published?