Generating a SQL explain and retrieving it for every query is difficult for me on the work server. Is there a way I can have java code that executes a query as well as gives a SQL explain plan so that I can get all the data at once?
Note:-
I'm using Informix database.
From Informix 12.10.XC2 forward you also have the possibility of using 2 new functions to retrieve the query plan:
Unlike EXPLAIN, these functions never execute the query so they do not provide a Query Statistics section in the explain output ( EXPLAIN_STAT configuration parameter ).
These functions are not documented, but are mentioned in How to acquire the Optimizer Explain File for a SQL statement into an Application., now found via the Internet Archive Wayback Machine.
Using the examples provided in the link previously mentioned, in a local virtual machine with Informix installed I get the following:
execute function ifx_explain( 'select * from systables' );
(expression)
QUERY: (OPTIMIZATION TIMESTAMP: 07-05-2019 16:57:42)
------
select * from systables
Estimated Cost: 11
Estimated # of Rows Returned: 87
1) informix.systables: SEQUENTIAL SCAN
--
-- bson_explain returns a BSON column, so cast it to a JSON, for human readability
--
SELECT bson_explain( 'select * from systables where tabid=99' )::JSON from sysmaster:sysdual
(expression) {"version":1,"explain":"
QUERY: (OPTIMIZATION TIMESTAMP: 07-05-2019 17:02:00)
------
select * from systables where tabid=99
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) informix.systables: INDEX PATH
(1) Index Name: informix.tabid
Index Keys: tabid
Lower Index Filter: informix.systables.tabid = 99
"}