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.
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.
- 0x08000: MINIMIZE RECORDS_PER_BLOCK in effect
- 0x10000: 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.
- 0x20000: 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’
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:
- Make sure that event 14529 is set in the session.
- Create the table as SELECT * from the original table.
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:
- The event must be set only during a CREATE TABLE … AS SELECT * …
- The event has no effect outside a CREATE TABLE … AS SELECT * …