Bitmap Indexes and the Håkan Factor

ORA-14642: Bitmap index mismatch

You created a new table with the same column structure as the existing one, you have filled it with data, and you have created indexes to match the local ones on the existing table, and then attempted ALTER TABLE … EXCHANGE PARTITION … WITH TABLE … INCLUDING INDEXES, but failed with that error. So, what is mismatching?

The likely answer is that the Håkan factor is different between the two tables. Håkan factor?? What is that, and what has that to do with bitmap indexes?

In Oracle Database, the term “Håkan factor” (sometimes “Hakan factor”) is used to denote the maximum  number of rows in a data block of a table. This is relevant for bitmap indexes, because they represent each data block by a number of bits, one for each row in the block. So, the Håkan factor is the number of bits allocated for each data block. This number is the same for all bitmap indexes on a table, allowing bit operations (e.g. AND/OR) between indexes.

For partition exchange to work with bitmap indexes, the Håkan factor of the two tables must be the same.

When the first bitmap index is created on a table, Oracle calculates how many rows could possibly be in a data block. This is done by finding the minimum size of a row, determined by things like the data types and NULL/NOT NULL settings of the columns. Even if you later e.g. add columns to the table, the number will not change.

ALTER TABLE MINIMIZE RECORDS_PER_BLOCK

There is a different way of calculating the Håkan factor. The ALTER TABLE … MINIMIZE RECORDS_PER_BLOCK command finds the actual maximum number of rows in a data block in the table, and sets the Håkan factor accordingly. If done this way, the Håkan factor becomes a limit that will be enforced for future INSERT statements. Note that this command can be used only when no bitmap index exists on the table.

The purpose of doing this is to make bitmap indexes take up fewer bits. If the Håkan factor is too large, a number of zero bits will at the end of each block. As zero bits are compressed when the bitmap index is stored, these do not matter much for the disk space usage. But there may be an impact on the memory usage when doing bitmap operations during bitmap index access. I do not know about the actual code, so I do not know this for sure.

SYS.TAB$.SPARE1

The Håkan factor is not found in any standard data dictionary views, such as DBA_TABLES. You can find it in the SPARE1 column (a NUMBER) of SYS.TAB$. This is what I have been able to deduce about its contents by experimenting on a 10g database. It consists of a number of bit fields:

  • Lower bits (at least 12, perhaps as many as 15): Håkan factor. The largest number I have seen was just above 2k, on a database with 32k block size. I have never seen any table where the next 3 bits of this column were non-zero. Since 32k is the largest possible block size, and since a row must be at least 1 byte long, there is no way the number can exceed 15 bits. On the other hand, I have come across another limit of 8k, so perhaps 2 bits have a different meaning.
  • 0×08000: MINIMIZE RECORDS_PER_BLOCK in effect
  • 0×10000: Seems to mean that the Håkan factor has been fixed higher than the value calculated from non-null columns. This could be e.g. if set by event 14529 (see below) or if non-null columns have been added after a bitmap index has been created.
  • 0×20000: Table compression is enabled

The SYS.TAB$ table uses the object ID as key, the OBJ# column. It matches the column found in the xxx_OBJECTS data dictionary views. For example, you can use a query like the following for getting the Håkan factor and the above bits:

select object_name,to_char(spare1,’0xxxxx’) spare1_hex,bitand(spare1,32767) hakan_factor
from user_objects o,sys.tab$ t
where object_name =’MYTABLE’ and object_type=’TABLE’
and o.object_id=f.obj#;

The Solution: Event 14529

So, how do you make sure that the table you want to exchange for a partition has the right Håkan factor? There is no way you can set the Håkan factor manually, but there is a trick: You can copy the Håkan factor from the original table to the new one when creating it. To do so:

  1. Make sure that event 14529 is set in the session.
  2. Create the table as SELECT * from the original table.

For example:

ALTER SESSION SET EVENTS ’14529 TRACE NAME CONTEXT FOREVER, LEVEL 1′;
CREATE TABLE TMP AS SELECT * FROM PART_TBL WHERE 0=1;
ALTER SESSION SET EVENTS ’14529 TRACE NAME CONTEXT OFF’;

This construct ensures that the Håkan factors of the two tables are equal. Then go on filling the table, creating indexes, etc. Now the partition exchange will work.

Oracle states that:

  1. The event must be set only during a CREATE TABLE … AS SELECT * …
  2. The event has no effect outside a CREATE TABLE … AS SELECT * …

5 thoughts on “Bitmap Indexes and the Håkan Factor

  1. Karsten,

    I’d be interested to know where your limit of 8K arose – is this a row directory limit on a 32KB block size ?

    The case for allowing 14 bits for the Hakan factor may have been foresight (or good luck) because the maximum number of rows you can get in a compression unit on Exadata is 32,759. I would like to say that I’ve tested this, and I’m sure it was on my todo list – but I can’t be sure whether or not I actually did, but if I did I forgot to take a note of the result.

    • Hi Jonathan

      My vague reference to an “8k limit” comes from a problem with GTT’s on Oracle 9. We had an obscure problem where we deleted all rows in a GTT (this was in a transaction, so we could not truncate), and inserted new rows, repeatedly. From time to time, this failed. Oracle Support delved into this, and told us that there was a limit on the number of rows in a physical block, which was 8k, and since even a deleted row takes up 2 bytes, this problem should only occur if the block size of the database was >16k, and ours was 32k. They did not tell us exactly why there was a limit, and I do not know whether this is a general limit, or it is only on GTT’s. Neither do I not know if this limit exists in later Oracle versions.

      • Karsten,

        Thanks for that – I’ve gone back to look at some of my notes and have a reference to something that might have been raised by your SR. The 8K is because a “deleted” row takes 4 bytes, which would allow 8K rows in a 32KB block – but there’s a further limitation that the “lock rows” counter on an ITL has a limit of 4095 (according to the MOS notes – but 4094 according to a test I did a little while ago.)

        Your memory probably came from a time when Oracle didn’t have any code to stop the overflow on the lock count and allowed you to hit the row directory limit and then crash.

        There’s a blog note that references the MOS notes at: http://jonathanlewis.wordpress.com/2009/05/21/row-directory/

  2. Partition Exchange – Oracle Odds and Ends

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s