Converting fragment by expression to fragment by range

I really like the new fragment by range method of partitioning new in 11.70 and have blogged about it before, but are IBM missing a trick somewhere?

A common scenario is a table fragmented by expression on a date or datetime column, say with one partition per month, that’s come across from your 11.50 or earlier database instance. This sort of thing sounds ideal for the new fragment by range feature as it automates the adding of a new partition each month meaning that you don’t have to do it yourself or via an application, or allocate loads of partition up front wasting valuable space.

So can we easily convert a fragment by expression table to a fragment by range table? By this I mean without row movement. Well, it seems not although I welcome anyone showing me how it should be done. Take this relatively simple index-less table:

create table conversion_test (
test_id serial not null,
ttimestamp datetime year to second not null
) fragment by expression
partition prehistory (ttimestamp < '2012-10-01 00:00:00') in dbspace1,
partition oct_2012 (ttimestamp < '2012-11-01 00:00:00') in dbspace2,
partition nov_2012 (ttimestamp < '2012-12-01 00:00:00') in dbspace3
extent size 1024 next size 256 lock mode row;

Before we go any further it’s worth noting that I’ve chosen my fragmentation strategy fairly carefully to be compatible with the fragment by range syntax which doesn’t seem to accept operators other than ‘<' for the initial fragments, something the manual doesn't state.

Insert some data:

insert into conversion_test (ttimestamp) values ('2012-09-01 00:00:01');
insert into conversion_test (ttimestamp) values ('2012-09-02 00:00:01');
insert into conversion_test (ttimestamp) values ('2012-10-01 00:00:01');
insert into conversion_test (ttimestamp) values ('2012-10-02 00:00:01');
insert into conversion_test (ttimestamp) values ('2012-11-01 00:00:01');
insert into conversion_test (ttimestamp) values ('2012-11-02 00:00:01');

Let’s try and convert this to fragment by range:

alter fragment on table conversion_test init
fragment by range (ttimestamp) interval (1 units month) store in (dbspace4)
partition prehistory (ttimestamp < '2012-10-01 00:00:00') in dbspace1,
partition oct_2012 (ttimestamp < '2012-11-01 00:00:00') in dbspace2,
partition nov_2012 (ttimestamp < '2012-12-01 00:00:00') in dbspace3;

When running this I am hoping that:

• The data do not move and the partitions remain as they are.
• The only thing that changes is that if I now insert data for December 2012, a new partition is automatically created.

Does it work? Well, it runs. Unfortunately though, row movement occurred which we can see from the logical log:

fffff018 56 BEGIN 26 10 0 11/05/2012 10:46:15 92 informix
fffff050 388 BLDCL 26 0 4018 100274 512 128 12 0 conversion_test
fffff1d4 52 CHALLOC 26 0 4050 00001:0000496520 200
fffff208 56 PTEXTEND 26 0 41d4 100274 511 00001:0000496520
fffff240 388 BLDCL 26 0 4208 100275 512 128 12 0 conversion_test
fffff3c4 52 CHALLOC 26 0 4240 00001:0000497032 200
fffff3f8 56 PTEXTEND 26 0 43c4 100275 511 00001:0000497032
fffff430 388 BLDCL 26 0 43f8 100276 512 128 12 0 conversion_test
fffff5b4 52 CHALLOC 26 0 4430 00001:0000497544 200
fffff5e8 56 PTEXTEND 26 0 45b4 100276 511 00001:0000497544
...
fffff6b8 76 HINSERT 26 0 4688 100274 101 12
fffff704 76 HINSERT 26 0 46b8 100274 102 12
fffff750 76 HINSERT 26 0 4704 100275 101 12
fffff79c 76 HINSERT 26 0 4750 100275 102 12
fffff7e8 76 HINSERT 26 0 479c 100276 101 12
fffff050 76 HINSERT 26 0 47e8 100276 102 12


I don’t like that as on a big table this would be a long logged operation so I am going to cheat and show that this simple table can be switched to fragment by range by hacking around in systables (don’t do this at home and definitely nowhere near a production system).

By cloning my database and performing the change to fragment by range I can see what the entries in the sysfragments table look before and after and unload them to files.

unload to conversion_test_sysfragments.unl
select * from sysfragments where tabid=(select tabid from systables where tabname='conversion_test');

I get three rows from before and six rows from after.

I now need to fiddle the after unload file a bit from after the alter and put the values for partn and exprbin from before the alter into the three original rows as Informix changed these when it re-initialised the table and in my cheat method the part numbers will not change. I then revert to my original database which still has the table fragmented by expression and load in the new sysfragments entries:

delete from sysfragments where tabid=109;

6 row(s) loaded.

Dbschema shows the result immediately and it seems I don’t even have to bounce the engine:

{ TABLE "informix".conversion_test row size = 12 number of columns = 2 index size = 0 }

create table "informix".conversion_test
(
test_id serial not null ,
ttimestamp datetime year to second not null
)
fragment by range(ttimestamp) interval(interval( 1) month(9) to month) store in(dbspace4)
partition prehistory VALUES < datetime(2012-10-01 00:00:00) year to second in dbspace1,
partition oct_2012 VALUES < datetime(2012-11-01 00:00:00) year to second in dbspace2,
partition nov_2012 VALUES < datetime(2012-12-01 00:00:00) year to second in dbspace3
extent size 1024 next size 256 lock mode row;

And everything still appears to work in that I can select data and insert new rows, including rows for December 2012 and beyond where a new partition is automatically created.

So what does this post prove? It shows in principle that it’s possible to covert a table fragmented by expression with no indices to one with an identical initial fragmentation strategy fragmented by range, at least where the original strategy is compatible with the range partitioning. I don’t see in principle why this would not also work with indices in place, although I have not tested it. It seems like a nice feature to put into Informix at relatively low effort. One for V.next?