Oninit
Talk to an expert →

Informix Error -500

-500 Clustered index index-name already exists in the table.

A table may be clustered on only one index at a time. This table is already clustered on the index whose name is shown. Before you can cluster on another index, you must execute ALTER INDEX index-name TO NOT CLUSTER. To see which tables are clustered on which indexes, query sysindexes and systables as follows:

SELECT tabname, idxname FROM systables T, sysindexes X WHERE T.tabid = X.tabid AND X.clustered = 'C'

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-500 is a per-table cardinality limit on clustering: a table can only be clustered on one index at a time (clustering physically reorders the table's rows to match that index's order), so attempting to cluster on a second index while the table is already clustered on a different one is rejected.

  1. ALTER INDEX ... TO CLUSTER attempted on a second index, while the table is already clustered on a different index — the direct, only cause.
  2. A schema-tuning script assuming clustering can be layered or changed simply by clustering a new index, without first un-clustering the existing one.
  3. Forgetting which index a table is currently clustered on, since clustering configuration isn't always obvious from the table's own definition at a glance.
Solutions / Resolution
  1. Un-cluster the existing index first with ALTER INDEX index-name TO NOT CLUSTER, per the official guidance, before clustering on another index.
  2. Identify which index a table is currently clustered on, per the official guidance, by querying systables/sysindexes:
    SELECT t.tabname, i.idxname FROM systables t, sysindexes i
WHERE t.tabid = i.tabid AND i.clustered = 'C';
  3. Then cluster on the desired new index:
    ALTER INDEX new_index_name TO CLUSTER;
Examples
Switching which index a table is clustered on
-- Identify the current clustered index first:
SELECT t.tabname, i.idxname FROM systables t, sysindexes i
WHERE t.tabid = i.tabid AND t.tabname = 'orders' AND i.clustered = 'C';

-- Un-cluster it:
ALTER INDEX idx_orders_old TO NOT CLUSTER;

-- Cluster on the new index:
ALTER INDEX idx_orders_new TO CLUSTER;
The disallowed direct attempt
ALTER INDEX idx_orders_new TO CLUSTER;
-- -500: orders is already clustered on idx_orders_old
Diagnostic Checks
  1. Query systables/sysindexes for the table's currently clustered index before attempting to cluster on a different one.
  2. Confirm the un-cluster step actually completed before retrying the new cluster operation.
Related Errors / Related Topics
  • -316 — "Index index-name already exists in database." Another index-related restriction, about naming collisions rather than the clustering cardinality limit.
  • -350 — "Index already exists on the column (or on the set of columns)." Another related index restriction, about duplicate column coverage rather than clustering.

Query systables/sysindexes to find the currently clustered index, un-cluster it explicitly, then cluster on the new one — there's no direct "switch" operation.