Does a cluster index provide more benefits than pre-sorting the load file and creating non-cluster index?

Question

Informix 11.70.TC4DE:

CREATE TABLE cluster_tbl
    (
fk_id INT,
data CHAR(2048)
    );

LOAD FROM "presorted.ld" INSERT INTO cluster_tbl;

CREATE UNIQUE CLUSTER INDEX cl_idx ON cluster_tbl(fk_id);

{###}

CREATE TABLE noncluster_tbl
    (
fk_id INT,
data CHAR(2048)
    );

LOAD FROM "presorted.ld" INSERT INTO noncluster_tbl;

CREATE UNIQUE INDEX ncl_idx ON noncluster_tbl(fk_id);

{###}

UPDATE STATISTICS;

It's my understanding that a cluster index is essentially the same as pre-sorting the load file, inserting it into the table and creating the index for the foreign key column.

I created two tables, each loaded with 2 Million rows of identically data, pre-sorted by the foreign key. However, one table has a cluster index on fk_id, the other table with a non-clustered index on fk_id.

Is there any difference in the tree structure or additional overhead in maintaining or accessing data from a table with a cluster index, versus the same table/data with a non-cluster index?

Answer 1

In an Informix database, there is not a lot of difference between the table and index created by loading an empty table in pre-sorted order and creating a clustered index on the table in the same order. Thus, the net result is basically the same. Further, there are no long-term implications for either method; Informix does not maintain the clustered index in order after the initial clustering operation. The performance benefit from a clustered index with Informix is a second-order effect; there is a small benefit when you are selecting the data in the clustered order, but it is not a huge benefit.

However, there are considerable practical advantages to using CREATE [UNIQUE] CLUSTER INDEX <idxname> ON <tablename>(<columnlist>) or using ALTER INDEX <idxname> TO CLUSTER. These include the fact that you don't have to drop the table (and thereby destroy views selecting from the table, or lose permissions granted on the table — though I suppose you could do a full table delete, or even truncate the table, instead of dropping the table), and you don't have to explicitly store the data in a file, and you save the cost of transferring the data from the DBMS to the application for saving the data and the cost of transferring the data from the application back to the DBMS when you recreate (reload) the table.

So, unless there are reasons for wanting a copy of the data outside the database, there's no point in using the UNLOAD and LOAD mechanism; you are better off using a clustered index because it is easier to manage the operation that way.

Answer 2

The order you insert data into a table does not guarantee the order of the data on this disk.

Even if the order happens to be as you wish, it's not structured in a way that the optimiser will know that it's searchable by that order. (It will be treated as un-ordered.)

When you have a non-clustered index, and you need to use fields not covered by the index itself, the index only contains a pointer to the data. This is similar to introducing an extra join; a quick one, but a definite extra step in loading that data.

In short, it should nearly always be advantageous to have a clustered index (excepting circumstances like repeatedly inserting data at random positions in the table).

Which is contrary to your experience...

So, what are the queries that you are running? It may be that something counter intuitive is happening, but it may still be explainable.

• You've given us the schema, thanks.
• Could you also include the test queries you are using?
• And could you also include the methodology and results for measuring performance?
• And, finally, if possible, the execution plans for each query?