|
|
3.2.1 EXPLAIN Statement
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 prepended by the explain modifier. Although the details of this program are better understood when you have read the MonetDB Version 5 Reference Manual, the global structure is easy to explain.
sql>select count(*) from tables;
+--------+
| count_ |
+========+
| 27 |
+--------+
sql>explain select count(*) from tables;
+------------------------------------------------------------------------------+
| function user.s0_1():void; |
| barrier _66 := language.dataflow(); |
| _1:bat[:oid,:int] := sql.bind("tmp","_tables","id",0); |
| _6:bat[:oid,:oid] := sql.bind_dbat("tmp","_tables",1); |
| _8 := bat.reverse(_6); |
| _6:bat[:oid,:oid] := nil; |
| _9 := algebra.kdifference(_1,_8); |
| _8 := nil; |
| _11 := algebra.markT(_9,0@0); |
| _9 := nil; |
| _12 := bat.reverse(_11); |
| _11 := nil; |
| _13:bat[:oid,:sht] := sql.bind("tmp","_tables","type",0); |
| _15 := algebra.join(_12,_13); |
| _13:bat[:oid,:sht] := nil; |
| _16:bat[:oid,:int] := sql.bind("sys","_tables","id",0); |
| _18:bat[:oid,:int] := sql.bind("sys","_tables","id",1); |
| _19 := algebra.kunion(_16,_18); |
| _16:bat[:oid,:int] := nil; |
| _18:bat[:oid,:int] := nil; |
| _20:bat[:oid,:int] := sql.bind("sys","_tables","id",2); |
| _22 := algebra.kdifference(_19,_20); |
| _19 := nil; |
| _23 := algebra.kunion(_22,_20); |
| _22 := nil; |
| _20:bat[:oid,:int] := nil; |
| _24:bat[:oid,:oid] := sql.bind_dbat("sys","_tables",1); |
| _25 := bat.reverse(_24); |
| _24:bat[:oid,:oid] := nil; |
| _26 := algebra.kdifference(_23,_25); |
| _25 := nil; |
| _27 := algebra.markT(_26,0@0); |
| _26 := nil; |
| _28 := bat.reverse(_27); |
| _27 := nil; |
| _29:bat[:oid,:sht] := sql.bind("sys","_tables","type",0); |
| _30:bat[:oid,:sht] := sql.bind("sys","_tables","type",1); |
| _31 := algebra.kunion(_29,_30); |
| _29:bat[:oid,:sht] := nil; |
| _30:bat[:oid,:sht] := nil; |
| _32:bat[:oid,:sht] := sql.bind("sys","_tables","type",2); |
| _33 := algebra.kdifference(_31,_32); |
| _31 := nil; |
| _34 := algebra.kunion(_33,_32); |
| _33 := nil; |
| _32:bat[:oid,:sht] := nil; |
| _35 := algebra.join(_28,_34); |
| _34 := nil; |
| exit _66; |
| bat.append(_35,_15,true); |
| _15 := nil; |
| _39 := algebra.thetauselect(_35,2,"<"); |
| _35 := nil; |
| _41 := algebra.markT(_39,0@0); |
| _39 := nil; |
| _42 := bat.reverse(_41); |
| _41 := nil; |
| _43 := algebra.join(_12,_1); |
| _12 := nil; |
| _1:bat[:oid,:int] := nil; |
| _44 := algebra.join(_28,_23); |
| _28 := nil; |
| _23 := nil; |
| bat.append(_44,_43,true); |
| _43 := nil; |
| _46 := algebra.join(_42,_44); |
| _42 := nil; |
| _44 := nil; |
| _47 := algebra.markT(_46,0@0); |
| _46 := nil; |
| _48 := bat.reverse(_47); |
| _47 := nil; |
| _49 := aggr.count(_48); |
| _48 := nil; |
| sql.exportValue(1,"sys.tables","count_","wrd",64,0,6,_49,""); |
| end s0_1; |
+------------------------------------------------------------------------------+
sql>
The SQL compiler maintains a cache of compiled 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_1(); |
| mdb.stop(); |
| end main; |
+----------------------------+