getExplain
Runs a query plan on the given query. It performs a fully execution on the query to generate the complete query plan, fetches the plan from the server and returns it as an LVARCHAR.
This execute the SQL statement provided. It uses SET EXPLAIN ON/OFF statements and returns the pure plan, not a JSON representation like other functions.
Setup
Syntax
CREATE FUNCTION getExplain(LVARCHAR) RETURNS LVARCHAR(30000) EXTERNAL NAME 'com.informix.judrs.Explain.getExplain(java.lang.String)' LANGUAGE JAVA; GRANT EXECUTE ON FUNCTION getExplain(LVARCHAR) TO PUBLIC;
Usage
> execute function getExplain("SELECT * FROM systables a, syscolumns b where a.tabid = b.tabid");
(expression) |
--------------------
QUERY: (OPTIMIZATION TIMESTAMP: 04-15-2020 19:34:51)
------
SELECT * FROM systables a, syscolumns b where a.tabid = b.tabid
Estimated Cost: 75
Estimated # of Rows Returned: 564
1) informix.a: SEQUENTIAL SCAN
2) informix.b: INDEX PATH
(1) Index Name: informix.column
Index Keys: tabid colno
Lower Index Filter: informix.a.tabid = informix.b.tabid
NESTED LOOP JOIN
----------
Procedure: informix.getexplain
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 a
t2 b
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 1 72 7 00:00.00 10
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 4 564 4 00:00.00 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 4 564 00:00.00 75