Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted September 14, 2015

Oracle's Hakan Factor

By David Fitzjarrell

An interesting problem recently submitted to Oracle Support involves a possibly little-known aspect of a table called the Hakan Factor. The problem description, in brief, states that the Hakan Factor isn't preserved for a partitioned table when a single partition is compressed. Why is this a problem? The Hakan Factor is used by bitmap indexes to map rows to the index entries and it's currently one less than the number of rows contained in a database block, due to adjustments to the calculation made necessary by unwanted behavior in Oracle releases 9.2.0.8 and earlier. To provide a 'real-world' example consder a parking garage. Each level of the garage can hold X number of vehicles and if the garage is extended upwards, by adding more levels, this doesn't affect the vehicle-to-level mapping since each new level matches the previous levels. Now consider this same parking garage being modified to increase the number of vehicles each level can contain, keeping the number of levels the same. The original garage mapping no longer applies so the garage map is invalid. Something similar happens when the Hakan Factor changes for a partition and no longer matches the table you wanted to use for partition exchange; the partition mapping doesn't match the table mapping and the bitmap indexes can't find the new data and an error is thrown. Since the Hakan Factor is critical for bitmap indexes let's look at this factor in a bit more depth.

The Hakan Factor is set by using 'alter table ... minimize records_per_block;' or by creating a table using the familiar 'create table ... as select ... from ...;' mechanism. The latter sets the Hakan Factor of the new table to that of the source table. When the Hakan Factor becomes a problem is when dealing with a table having bitmap indexes on it, notably a partitioned table where partition exchange is being executed. If the Hakan Factor of the partition and the table it's being exchanged for don't match and there are bitmap indexes present the exchange will fail with the following error:


ORA-14642: Bitmap index mismatch

This can happen for a number of reasons, including adding columns to a partition or table definition, minimizing the rows_per_block (mentioned above) and, according to the Bug report by compressing a partition. [The Bug number is 18115378.] The bug has not been reported as fixed; the workaround is to simply execute 'alter table ... minimize records_per_block;'. Of course if bitmap indexes are not in place the error won't be thrown so another way around this problem is to drop the bitmap indexes on the partitioned table, perform the exchange then recreate the bitmap indexes (a procedure recommended by Oraclec support). The bug report concerns the building of bitmap indexes after a partition has been compressed; the error thrown is:


ORA-28604: table too fragmented to build bitmap index (4625956,272,272)

...even though it isn't. The problem stems from differing Hakan Factors for the compressed and uncompressed partitions, a problem Oracle should be able to build a bitmap index around.

How can you know the current Hakan Factor for a given table? Jonathan Lewis has constructed an interesting procedure to generate the Hakan factor, shown below:


create or replace procedure show_hakan(
    i_table     in  varchar2,
    i_owner     in  varchar2 default user
) as
    m_obj       number(8,0);
    m_flags     varchar2(12);
    m_hakan     number(8,0);
begin
 
    /* created by show_hakan.sql    */
    select
        obj#,
/*
        case
            when    (spare1 > 5 * power(2,15))
                then    (spare1 - 5 * power(2,15))
            when    (spare1 > power(2,17))
                then    (spare1 - power(2,17))
            when    (spare1 > power(2,15))
                then    (spare1 - power(2,15))
                else    spare1
        end                 hakan
*/
    to_char(
        bitand(
            spare1, to_number('ffff8000','xxxxxxxx')
        ),
        'xxxxxxxx'
    )                   flags,
    bitand(spare1, 32767)           hakan   -- 0x7fff
    into
        m_obj,
        m_flags,
        m_hakan
    from
        tab$
    where   obj# in (
            select  object_id
            from    dba_objects
            where   object_name = upper(i_table)
            and object_type = 'TABLE'
            and owner = upper(i_owner)
        )
    ;
 
    dbms_output.put_line(
        'Hakan factor - 1 (see bug history) for object ' ||
        m_obj   || ' (' ||
        i_owner || '.' ||
        i_table || ') is ' ||
        m_hakan || ' with flags ' ||
        m_flags
    );
end;
/
 
drop public synonym show_hakan;
create public synonym show_hakan for show_hakan;
grant execute on show_hakan to public;

The procedure needs to be created by SYS. Once it's created, using it is fairly simple as the following example illustrates:


SQL>> 
SQL>> create table df1 (
  2  	     snord   number,
  3  	     snerd   varchar2(20),
  4  	     snard   date);

Table created.

SQL>> 
SQL>> begin
  2  	     for i in 1..10000 loop
  3  		     insert into df1
  4  		     values(i, 'Hakan test '||i, sysdate+mod(i, 19));
  5  	     end loop;
  6  
  7  	     commit;
  8  end;
  9  /

PL/SQL> procedure successfully completed.

SQL>> 
SQL>> exec show_hakan('df1');
Hakan factor - 1 (see bug history) for object 95173 (BING.df1) is 736 with flags         0                                          

PL/SQL> procedure successfully completed.

SQL>> 

The table MUST be populated to set the Hakan factor, which is done automatically when the table is loaded with data. This example was run on Oracle 12.1.0.2 but it will run on 11.2 without issues. [Jonathan Lewis tested this on 9.2.0.8 with an earlier version of the procedure and it calculated the Hakan Factor correctly, however the code posted above will need to be modified (possibly by trial-and-error) to produce results.] If a new table is created using 'create table .. as select .. ' the Hakan Factor is replicated to the new table, as mentioned above:


SQL>> exec show_hakan('EMP');
Hakan factor - 1 (see bug history) for object 95181 (BING.EMP) is 736 with flags         0

PL/SQL> procedure successfully completed.

SQL>>
SQL>> create table pme as select * from emp;

Table created.

SQL>>
SQL>> exec show_hakan('PME');
Hakan factor - 1 (see bug history) for object 95187 (BING.PME) is 736 with flags         0

PL/SQL> procedure successfully completed.

SQL>>

Altering the table to minimize records_per_block changes the Hakan Factor:


SQL> alter table df1 minimize records_per_block;

Table altered.

SQL> 
SQL> exec show_hakan('df1');
Hakan factor - 1 (see bug history) for object 95173 (BING.df1) is 232 with flags      8000                                          

PL/SQL procedure successfully completed.

SQL> 

Compressing a table can also alter the Hakan Factor according to the bug report, so that should be tested:


SQL> alter table pme compress for oltp;

Table altered.

SQL>
SQL> exec show_hakan('PME');
Hakan factor - 1 (see bug history) for object 95194 (BING.PME) is 32759 with flags     20000

PL/SQL procedure successfully completed.

SQL>

This shouldn't be a surprise since compression alters how rows are stored in a block and, in Hybrid Columnar Compression, alters how the blocks are configured. I can see why Oracle is considering this a bug; compressed partitions are marked as such and creating a bitmap index on a partitioned table with 'mixed' partitions shouldn't throw an error. Advanced Compression options can leave a compressed table/partition in a state where some blocks are compressed and others are not, but the Hakan Factor is the maximum number of rows a block can have for a given partition and since there should always be fully compressed blocks in a partition using Advanced Compression, Oracle should be able to build a bitmap index on a table containing both compresed and uncompressed partitions. Generating the Hakan Factor for a table using OLTP compression before and after a large update produces the following results:


SQL> begin
  2          for i in 1..19 loop
  3                  insert into pme select * from pme;
  4          end loop;
  5
  6          commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> alter table pme compress for oltp;

Table altered.

SQL>
SQL> exec show_hakan('PME');
Hakan factor - 1 (see bug history) for object 95208 (BING.PME) is 32759 with flags     20000

PL/SQL procedure successfully completed.

SQL>
SQL> update pme set job='CLORK' where job = 'CLERK';

2097152 rows updated.

SQL> commit;

Commit complete.

SQL>
SQL exec show_hakan('PME');
Hakan factor - 1 (see bug history) for object 95208 (BING.PME) is 32759 with flags     20000

PL/SQL procedure successfully completed.

SQL>

The Hakan Factor for the compressed table didn't change even after a large update occurred that left some blocks uncompressed. Since Oracle knows the partition is compressed it shoudn't complain about 'fragmentation'; it should consider the compression and continue to build the bitmap index.

In Oracle 9.2.0.8 and earlier releases the Hakan Factor was equal to the number of rows a block would contain; unfortunately it would decrease by 1 each time it was regenerated using 'alter table ... minimize records_per_block;' so it was possible to reduce the Hakan Factor to 1 and end up with an extremely large table containing a great deal of wasted space. Also in 9.2.0.8 and earlier releases direct path inserts behaved differently with respect to the Hakan Factor than garden-variety inserts so large direct-path loads during batch jobs could also result in an overly large table with a lot of wasted space. An attempt was made in Version 10g to make the normal inserts and direct-path inserts behave consistently; the change resulted in the Hakan Factor being one less than the actual number of rows in a block and since the Hakan Factor cannot be 0 the wasted space issue was 'resolved', at the expense of an 'incorrect' Hakan Factor.

The Hakan Factor is (or, hopefully, was) a little-known nugget of table knowledge, critical for the implementation and performance of bitmap indexes. Hopefully understanding what it is and what it affects will make the DBA's job easier when such errors, as reported here, are encountered.

See all articles by David Fitzjarrell



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM