EXPLAIN SQL STMT

EXPLAIN SQL STMT mk Sun, 03/28/2010 - 23:24

The intermediate code produced by the SQL compiler can be made visible using the explain statement modifier. It gives a detailed description of the actions taken to produce the answer.  The example below illustrates what you can expect when a simple query is starts with  the explain modifier. The output strongly depends on the optimizer pipeline. The details of this program are better understood when you have read the MAL  reference.

sql>select count(*) from tables;
+------+
| L41  |
+======+
|   93 |
+------+
1 tuple
clk: 1.993ms
sql>explain select count(*) from tables;
+------------------------------------------------------------------------------------------------------------------------+
| mal                                                                                                                    |
+========================================================================================================================+
| function user.s6_1():void;                                                                                             |
|     X_1:void := querylog.define("select count(*) from tables;":str, "default_pipe":str, 30:int);                       |
| barrier X_104:bit := language.dataflow();                                                                              |
|     X_38:bat[:lng] := bat.new(nil:lng);                                                                                |
|     X_4:int := sql.mvc();                                                                                              |
|     X_18:bat[:sht] := sql.bind(X_4:int, "sys":str, "_tables":str, "type":str, 0:int);                                  |
|     C_5:bat[:oid] := sql.tid(X_4:int, "sys":str, "_tables":str);                                                       |
|     C_89:bat[:oid] := algebra.thetaselect(X_18:bat[:sht], C_5:bat[:oid], 2:sht, "!=":str);                             |
|     (X_21:bat[:oid], X_22:bat[:sht]) := sql.bind(X_4:int, "sys":str, "_tables":str, "type":str, 2:int);                |
|     C_90:bat[:oid] := algebra.thetaselect(X_22:bat[:sht], nil:bat[:oid], 2:sht, "!=":str);                             |
|     X_20:bat[:sht] := sql.bind(X_4:int, "sys":str, "_tables":str, "type":str, 1:int);                                  |
|     C_92:bat[:oid] := algebra.thetaselect(X_20:bat[:sht], C_5:bat[:oid], 2:sht, "!=":str);                             |
|     C_27:bat[:oid] := sql.subdelta(C_89:bat[:oid], C_5:bat[:oid], X_21:bat[:oid], C_90:bat[:oid], C_92:bat[:oid]);     |
|     X_8:bat[:int] := sql.bind(X_4:int, "sys":str, "_tables":str, "id":str, 0:int);                                     |
|     (X_13:bat[:oid], X_14:bat[:int]) := sql.bind(X_4:int, "sys":str, "_tables":str, "id":str, 2:int);                  |
|     X_11:bat[:int] := sql.bind(X_4:int, "sys":str, "_tables":str, "id":str, 1:int);                                    |
|     X_29:bat[:int] := sql.projectdelta(C_27:bat[:oid], X_8:bat[:int], X_13:bat[:oid], X_14:bat[:int], X_11:bat[:int]); |
|     X_31:lng := aggr.count(X_29:bat[:int]);                                                                            |
|     X_37:bat[:lng] := sql.single(X_31:lng);                                                                            |
|     C_32:bat[:oid] := sql.tid(X_4:int, "tmp":str, "_tables":str);                                                      |
|     X_34:bat[:int] := sql.bind(X_4:int, "tmp":str, "_tables":str, "id":str, 0:int);                                    |
|     X_35:bat[:int] := algebra.projection(C_32:bat[:oid], X_34:bat[:int]);                                              |
|     X_36:lng := aggr.count(X_35:bat[:int]);                                                                            |
|     X_40:bat[:lng] := bat.append(X_38:bat[:lng], X_37:bat[:lng], true:bit);                                            |
|     X_42:bat[:lng] := bat.append(X_40:bat[:lng], X_36:lng, true:bit);                                                  |
|     X_43:lng := aggr.sum(X_42:bat[:lng]);                                                                              |
|     language.pass(C_5:bat[:oid]);                                                                                      |
| exit X_104:bit;                                                                                                        |
|     sql.resultSet(".L41":str, "L41":str, "bigint":str, 64:int, 0:int, 7:int, X_43:lng);                                |
| end user.s6_1;                                                                                                         |
...
+------------------------------------------------------------------------------------------------------------------------+
55 tuples
clk: 3.942 ms

 

The SQL compiler maintains a cache of compiled (or prepared) queries. Each query is looked up in this cache based on an expression pattern match where the constants may take on different values. If it doesn't exist, the query is converted into a code block and stored in the module user.s0.

The call to the cached function is included in a wrapper function main, which is the only piece of code produced if the query is used more than once. The query cache disappears when the server is brought to a halt.

     +----------------------------+
     | function user.main():void; |
     |     mdb.start();           |
     |     user.s3_2();           |
     |     mdb.stop();            |
     | end main;                  |
     +----------------------------+