So today I had an interesting battle with a partitioned table and bitmap indexes (or indices if you are adamant about English). I was writing this process for PEL (partition exchange loading) where I had a table with daily partitions and wanted to add a new partition to it on the fly.

Now, before going further, this was on version 10.2.x.

So, the original table has loads of partitions or around 1000 (3 years roughly). I also noticed that all the partitions had a pctfree of 10 meaning in this case that there was 10% air in the partitions since the partitions where created using insert /*+ append */ (which will honor the pctfree setting). All partitions were read-only once created so I thought I had a perfect opportunity to compress everything compressable and bitmap index it to hell. Of course I was going to do this using the "swap-in" partition.

So, what is PEL anyway:

If you have a partitioned table with i.e. partitions named p1 – p10 and want to replace partition p10 with a new version of it, the most elegant way of doing it is using PEL meaning that you leave the original table, called “OriginalTable” in place while building a swap table on the side, called “SwapTable”. SwapTable has to look exactly the same as OriginalTable except that it is not partitioned. It will become a partition in OriginalTable once complete. Basically, you populate it with new data, build your indexes e.t.c. and exchange it for p10 in OriginalTable. No downtime, no interruption on OriginalTable (more or less). This is a dictionary operation and if you have OriginalTable set up properly with local indexes only, this takes a fraction of a second.

Enter my troubles:

I created SwapTable, I built the local indexes. Being a smart guy I decided to bring pctfree down to 0 percent to optimize storage and memory (leaving 10% free in a block uses 10% more memory for the data than is necessary, roughly). I also compressed local indexes that were not bitmaps and had leading repeating columns. Otherwise it was identical.

ALTER TABLE OriginalTable EXCHANGE PARTITION P1 WITH TABLE SwapTable WITHOUT VALIDATION INCLUDING INDEXES;

How could this be? I just truncated the swap table and inserted with insert /*+ append */? Certainly no fragmentation! Some reading brought me to disable compression on the indexes as this may influence this stuff, apparently by changing something called the Hakan factor. The Hakan factor is something like a stable Windows Vista. Rumour has it that it exists but no one has seen it yet .

So, disabling the compression allowed me to exchange the partition but I had to do it using:

ALTER TABLE OriginalTable EXCHANGE PARTITION P1 WITH TABLE SwapTable WITHOUT VALIDATION EXCLUDING INDEXES;

ALTER TABLE OriginalTable MODIFY PARTITION p1 REBUILD UNUSABLE LOCAL INDEXES;

In my case this took about 2 minutes which was OK but many will not allow that. I also ran into the error above intermittently . By changing pctfree back to 10% AND running ALTER TABLE…MINIMIZE RECORDS_PER_BLOCK (first, drop bitmap indexes), I got around that.

So bottom line:

· Make sure you do not use compression when using PEL (This may be fixed in later versions). I’m not sure if this is ok if all partitions have the same compression.

· If using bitmap indexes use ALTER TABLE..MINIMIZE RECORDS_PER_BLOCK. If you still run into the error above increase PCTFREE and move/re-create it.

· If you are still running into problems, check it the 2 tables have the same HAKAN factor. It can be found by i.e.:
select spare1

from tab$, dba_objects do

where do.object_id = tab$.obj#

and do.object_name = ‘<table>’

You have to check this for both tables and compare the column SPARE1 which is the Hakan factor. In order to be able to exchange partitions this will have to be the same for both tables. The above objects are owned by SYS so you will have to have access to them to check this. (BTW, I found this on Google and I believe this comes from Jonathan Lewis).

Apparently you can compress the data segments if you make sure that there are no bitmap indexes on it before compression. I didn’t try it.

Anyway, Hakan factor or not. I’m going to sleep.

p.s.

I just read actually that the Hakan factor is used to determine the number of rows that can fit on a single block. I also found out that Hakan Jakobsson is one of the contributors to Oracle’s Data Warehousing Guide. I don’t know if there is any relation but he better not come to Iceland.