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);



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?

  • This was perfectly happy on Stack Overflow; why did it need moving to DBAs? It is very, very, very irksome! Jul 17 '12 at 7:40
  • There are certain SO individuals who are prejudiced towards me!.. If you noticed, the same users have been closing my questions, I know who they are!.. Did the +50 bounty I awarded you disappear?
    – Frank R.
    Jul 17 '12 at 7:48
  • Yes, the bonus went too. It is just frustrating. I think they're tryingto drum up business for DBAs, but the trouble is, it is another site to have to visit and I don't have the time or energy to spend on yet another site. Jul 17 '12 at 8:03
  • @JonathanLeffler - Only one of the people that voted to close on SO even has an account here and that is inactive so doubt they care about "drumming up business" for the site. Jul 17 '12 at 11:24
  • @MartinSmith: So if the 50 points bounty I awarded Jonathan Leffler for his answer, when this question was in SO, was taken away from him, the 50 points should be returned to me, or credited to Jonathan in SO!.. Fair is Fair!
    – Frank R.
    Jul 17 '12 at 15:12

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.

  • +50 because it answers my question, specific to Informix 11.70. However, this may not hold true for other DB's. In my particular app, I have to unload/load the data because although each customers transactions need to be grouped together, active transactions need to be ordered by last_payment_date ASCENDING and inactive transactions by last_payment_date DESCENDING.
    – Frank Computer
    Jul 15 '12 at 23:20

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.