Using SET EXPLAIN

When the table is fragmented, the output of the SET EXPLAIN ON statement shows which table or index the database server scans to execute the query. The SET EXPLAIN output identifies the fragments with a fragment number. The fragment numbers are the same as those contained in the partn column in the sysfragments system catalog table.

The following example of SET EXPLAIN output shows a query that takes advantage of fragment elimination and scans two fragments in table t1:

QUERY:
------
SELECT * FROM t1 WHERE c1 > 12

Estimated Cost: 3
Estimated # of Rows Returned: 2

1) informix.t1: SEQUENTIAL SCAN (Serial, fragments: 1, 2)

   Filters: informix.t1.c1 > 12

If the optimizer must scan all fragments (that is, if it is unable to eliminate any fragment from consideration), the SET EXPLAIN output displays fragments: ALL. In addition, if the optimizer eliminates all the fragments from consideration (that is, none of the fragments contain the queried information), the SET EXPLAIN output displays fragments: NONE.

For information on how the database server eliminates a fragment from consideration, see Using Distribution Schemes to Eliminate Fragments.

For more information on the SET EXPLAIN ON statement, see Query Plan Report.