IIUG World 2019

IIUG 2020 Online World Conference Presentations will begin soon!

Monday, June 21, 2021

Informix delivers!

This will be a quicky.

There are two things that I have been asking for in Informix for a while. One is what I consider a bug fix, bout could be considered a feature. The other is a failure to complete a feature that was never released officially and finally release and document it. Both are now in the lastest release of Informix, v14.10.FC6!

A real ROWID for partitioned tables

When table partitioning (aka fragmenting) was introduced partitioned tables did not supply access to the pseudo-column ROWID that has always been a part of Informix (and most other RDBMS) tables. It was the physical address of a row in the table and could be used to quickly access a given row. However, the rows of a partitioned table existed in multiple partitions and the Nth row in each partition would all have the same ROWID it was impractical to support the feature. So you had no built-in unique identifier you could use for tables created without a unique key. Shortly after that the option to create a partitioned table WITH ROWIDS was introduced which added a real but hidden integer column to the table and creates an index on it. However, if you created the table without the WITH ROWIDS clause and the table was very large it was impractical to add that feature to an existing table.

It seems that for a long time there has actually been a physical address of a row in a partitioned table and I have written about it before. This was the undocumented pseudo-column ifx_row_id which is a 45 byte CHAR type value containing the partition number and rowid of the row as a colon separated string. Unfortunately if you used the ifx_row_id to query a row the engine would perform a table scan rather than using the value as a row address and directly accessing the data. A SET EXPLAIN will show this behavior. Well, version 14.10.FC6 has fixed that. If you run such a query now, it is lightening fast even on a massive table and the SET EXPLAIN output will show an index scan query plan for ifx_row_id. YEEHA!

Querying LVARCHAR type columns in an array fetch

This was finally fixed in the CSDK v 4.50.xC5! Since the introduction of LVARCHAR type columns if you used an array fetch to fetch a batch of many rows into your program's memory space in a single FETCH operation you would get a spurious error (-1831 Combination of FetArrSize, Deferred-PREPARE, and OPTOFC is not supported) which have nothing to do with the actual problem which was using FetArrSize > 1 with a query that returned an LVARCHAR. This issue prevented one from using my dbcopy utility to copy any table that has one or more LVARCHAR columns or writing one from writing their own mass data processing applications. This was a large problem because array fetching is several times faster than row-by-row fetching for very large data sets.


No comments:

Post a Comment