Oracle HAKAN Factor During Partition Exchange

Amongst the best performance functionalities in the Oracle Enterprise server is good old table partitioning, introduced way back in release 8.0.

If you are an experienced Oracle DBA, you've probably used partitioning at one point or another. As you added/dropped/exchanged/split/merged/rebuilt partitions in your data warehouse, you were quite confident that nothing would surprise you…

Well, what does the following message tell you?

ORA-14642
Bitmap index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
Error 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.

 

H a k a n factor??? You may be rolling your eyes, as some of my distinguished DBAs were doing when they first stumbled upon this.  They checked to determine why the exchange between a partition and a table was failing despite a complete match in structure – columns, indexes, constraints were the same for both segments.

They scratched their heads, and the only difference they found between the segments involved in the exchange is the way in which they were created:

  • The partitioned table involved in the exchange was changed after creation time; one of the NOT NULL columns was changed to NULL.
  • The table used for the exchange was created based on the latest structure of the partitioned table using a DDL command.

As it turned out, these two segments had different Hakan factors because of changes to the NOT NULL columns after creation time. 

So why was the mismatch in the Hakan factor failing at the exchange?

In a nutshell, the Hakan factor is an estimation of the number of records that can fit into a single block; it is used by bitmap index compression algorithms.

When exchanging a partition with usable bitmap indexes, the Hakan factor of both segments should match.

In our case, the Hakan factor of the original, partitioned table was changed after modifying the null attribute of a column, while the new table created for the exchange partition was created from scratch with a different initial Hakan factor.

How can you check the Hakan factor prior to the exchange?

The Hakan factor for a table can be located in tab$.spare1 using the following query:

select spare1 AS HAKAN_FACTOR,do.object_name AS TABLE_NAME
from SYS.tab$, dba_objects do
where do.object_id = SYS.tab$.obj#
and do.owner=<your schema>

 

How to work around the problem?

  1. Oracle's primary suggestion is either to use the ‘Excluding Indexes’ option of the exchange command, or to make the bitmap indexes unusable prior to the exchange. This requires extra maintenance to the indexes after the exchange, and is not always feasible in production environments.
  2. If you must use the include indexes, and your bitmap indexes are usable, there is a workaround suggested by Oracle support:
    1. Turn on event 14529. This will enable inheritance of the Hakan factor when creating table as select.
    2. Create the table for the exchange using ‘create table as select’.
    3. Perform the exchange.
    4. Turn the trace event off.

 

References:

[1] Oracle Support Document 194372.1 (OERR:  ORA-14642  Bitmap index mismatch for tables in ALTER TABLE EXCHANGE PARTI) can be found at: https://support.oracle.com/epmos/faces/ui/km/DocumentDisplay.jspx?id=194372.1

[2] Oracle® Database Error Messages 11g Release 2 (11.2): http://docs.oracle.com/cd/E11882_01/server.112/e17766/toc.htm

 

This article has been written by Sharon Dashet

Expert Oracle DBA

 

Labels: Databases
Leave a Comment

We encourage you to share your comments on this post. Comments are moderated and will be reviewed
and posted as promptly as possible during regular business hours

To ensure your comment is published, be sure to follow the Community Guidelines.

Be sure to enter a unique name. You can't reuse a name that's already in use.
Be sure to enter a unique email address. You can't reuse an email address that's already in use.
Type the characters you see in the picture above.Type the words you hear.
Search
Showing results for 
Search instead for 
Do you mean 
About the Author


Follow Us
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation