SAP IQ Index Migration When Migrating to SAP IQ 16.x

This topic covers the scenario where you are migrating an SAP IQ system from an older version (Sybase IQ 12.x or SAP IQ 15.x) to SAP IQ 16.x.  This also applies only to those scenarios where the database is migrated in place rather than being a fresh build.

After migrating to IQ 16.x from any prior version, you must consider whether or not to manually rebuild some indexes.  If you choose not to rebuild anything, SAP IQ will do it automatically.  While this is perfectly normal and operating as designed, you may not be prepared for the result of that.  After migrating to IQ 16.x, two index types must be rebuilt into current structures to support the other changes to the product.  Both the FP, or Flat Projection, indexes and the HG, or High Group, indexes must be updated to the new structures.

For the FP, this means converting the optimized 1-byte, 2-byte, and 3-byte variants to the new n-bit structure.  In prior versions, the FP index was optimized on byte boundaries.  In IQ 16, this was redesigned to be aligned along bit boundaries.  This change allowed IQ to offer a much higher compression level as well as having more built-in statistics and query optimizations.

With the HG index, a rebuild must be done so that you can take advantage of the new tiered HG feature that aids in data loading speed.  Portions of the HG index must be traversed and manipulated into a format that can support the tiered HG (THG) feature.  It does not matter whether you are using the new feature, the index must still be put into a format to support that feature.

When a table is opened for read-write and a data change begins (any data change), the default indexes (FP) and all HG indexes (HG, primary key, foreign key, check constraints, etc.) are checked to make sure they are in the new format.  If they are not, then SAP IQ will convert them.  For the FP index, this means simply converting from the byte-oriented structure to a bit-oriented structure.  For the HG index, IQ runs through the b-tree and g-array pages to re-construct the fields and bitmaps to support the THG. This will only happen for the first read-write DML data change and should not happen again.  The foundational structure must be modified to support the new feature and we choose to do that the first time that the index is touched for a read-write operation.

Now that we understand what happens and why, we must decide when it happens.  As mentioned previously, if you do nothing IQ will make the changes upon the first read-write operation on the table.  This can have a devastating impact to the first load that may not be expected.

Take this sample from a single table load.  This is a very large table with tens of billions of rows.  Each load is 5-20 million rows.  As you can see, though, that first load where SAP IQ was forced to rebuild the structures automatically, the load took nearly 8 hours (over 28,000 seconds) while all subsequent loads took 30-60 seconds to complete depending on input file size.

This graph makes it quite clear on what the impact will be to rebuild the structures.  The question is not whether you can change how long the process takes, it must be done.  The question is what kind of impact this process will have on data loading.

If you let the server automatically make these structure changes, they will happen during the first read-write operation on the table that changes data.  For small tables, under a billion rows, the impact will be less visible, likely seconds to minutes.  With much larger tables, billions to hundreds of billions, the impact can be significant as we see from the graph above.

As a best practice, consider manually updating tables prior to any data loads.  Choose tables that are relatively large, perhaps over 1 billion rows, and tables that are frequently used.

SAP IQ has compatibility options in IQ 16 that can revert some behavior back to IQ 15-like behavior.  The common misconception is that IQ 16 can maintain the IQ 15 index structures when changing data.  This is not the case and is documented as part of the FP_NBIT_IQ15_COMPATIBILITY option in the SAP IQ Manuals (this came from the IQ 16.1 documents).

The FP_NBIT_IQ15_COMPATIBILITY option provides tokenized FP support similar to that available in SAP IQ 15. All newly created and modified tokenized FP indexes in 16.1 will be NBit. The only 15 style FP(1),FP(2), and FP(3) byte FP indexes available in 16.1 are those from an upgraded database that have had only Read-Only activity.

This helps us drive the best practice recommendation.  Any tables that are used for read-only activity, archive tables for instance, will retain the IQ 15 type FP structures.  The optimizer will continue to use the originally formatted objects, but they will not have the ability to use any of the IQ 16 enhancements (compression, performance, tiered HG indexes, etc.).

The best practice recommendation would be the following:

• For tables that are over 1 billion rows and have data changed, rebuild the default column and HG indexes using sp_iqrebuildindex.
• Ignore tables that have fewer than 1 billion rows and simply let SAP IQ update the structures as part of the first load.
• Ignore tables that that are only used for read-only activity as the existing structures can still be used, albeit without any option for new options and enhancements.

The best practice recommendation is just that, a recommendation.  Many customers have chosen to rebuild all columns and HG indexes before starting data loading.  Others have chosen to let IQ handle it automatically.  Choose the right method, or the proper hybrid, that meets your business and system requirements.  Understanding what happens and why it must be done is the most important part of this exercise as it allows you to make an informed plan on what to do and when to do it.