Informix query plan

Question

How can I get the execution plan for a query in Informix?

I need something similar to what I get in Oracle with explain plan or in SQL Server with set showplan_all on.

I'm connecting from a .NET application (using IBM.Data.Informix), if that's important.

Thanks!

Answer 1

In your SQL, you can either:

• execute SET EXPLAIN ON; This will remain in effect until you disconnect or execute the equivalent OFF statement. Works at least as far back as v7, may even work in v5.
• add an optimiser directive (v9 onwards), ie SELECT {+EXPLAIN} foo, bar FROM .... You can also use SELECT --+EXPLAIN \n foo bar ... depending on the exact syntax of your SQL (you can't close a -- comment, it runs to the end of the line). This variant is only in effect for that SELECT statement.

Where the explain file is written depends largely on the OS of the database server. It could be your user's home directory, or a directory within $INFORMIXDIR.