<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.title" content="Function: explain - Virtuoso Functions Guide" />
<meta name="dc.subject" content="Function: explain - Virtuoso Functions Guide" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="functions.html" title="Chapter Contents" />
<link rel="prev" href="appendixa.html" title="Appendix" />
<link rel="next" href="functions.html#.html" title="" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>Function: explain - Virtuoso Functions Guide</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="" />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="fn_explain" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>22. Virtuoso Functions Guide - explain</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="functions.html">Chapter Contents</a> | <a class="link" href="fn_dbg_printf.html" title="dbg_printf">Prev</a> | <a class="link" href="fn_pldbg_stats.html" title="pldbg_stats">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div>
<a class="selected" href="functions.html">Virtuoso Functions Guide</a>
</div>
<br />
<div>
<a href="functions.html#admin">Administration</a>
</div>
<div>
<a href="functions.html#aggr">Aggregate Functions</a>
</div>
<div>
<a href="functions.html#array">Array Manipulation</a>
</div>
<div>
<a href="functions.html#bpel">BPEL APIs</a>
</div>
<div>
<a href="functions.html#backup">Backup</a>
</div>
<div>
<a href="functions.html#compression">Compression</a>
</div>
<div>
<a href="functions.html#cursors">Cursor</a>
</div>
<div>
<a href="functions.html#time">Date & Time Manipulation</a>
</div>
<div>
<a class="selected" href="functions.html#debug">Debug</a>
</div>
<div class="selected">
<div>
<a href="fn_cov_load.html">cov_load</a>
</div>
<div>
<a href="fn_cov_report.html">cov_report</a>
</div>
<div>
<a href="fn_cov_store.html">cov_store</a>
</div>
<div>
<a href="fn_dbg_obj_print.html">dbg_obj_print</a>
</div>
<div>
<a href="fn_dbg_printf.html">dbg_printf</a>
</div>
<div>
<a class="selected" href="fn_explain.html">explain</a>
</div>
<div>
<a href="fn_pldbg_stats.html">pldbg_stats</a>
</div>
<div>
<a href="fn_pldbg_stats_load.html">pldbg_stats_load</a>
</div>
<div>
<a href="fn_trace_off.html">trace_off</a>
</div>
<div>
<a href="fn_trace_on.html">trace_on</a>
</div>
</div>
<div>
<a href="functions.html#dict">Dictionary Manipulation</a>
</div>
<div>
<a href="functions.html#encoding">Encoding & Decoding</a>
</div>
<div>
<a href="functions.html#file">File Manipulation</a>
</div>
<div>
<a href="functions.html#ft">Free Text</a>
</div>
<div>
<a href="functions.html#hash">Hashing / Cryptographic</a>
</div>
<div>
<a href="functions.html#ldap">LDAP</a>
</div>
<div>
<a href="functions.html#localization">Locale</a>
</div>
<div>
<a href="functions.html#mail">Mail</a>
</div>
<div>
<a href="functions.html#misc">Miscellaneous</a>
</div>
<div>
<a href="functions.html#number">Number</a>
</div>
<div>
<a href="functions.html#phrz">Phrases</a>
</div>
<div>
<a href="functions.html#rdf">RDF data</a>
</div>
<div>
<a href="functions.html#rmt">Remote SQL Data Source</a>
</div>
<div>
<a href="functions.html#repl">Replication</a>
</div>
<div>
<a href="functions.html#soap">SOAP</a>
</div>
<div>
<a href="functions.html#sql">SQL</a>
</div>
<div>
<a href="functions.html#string">String</a>
</div>
<div>
<a href="functions.html#txn">Transaction</a>
</div>
<div>
<a href="functions.html#type">Type Mapping</a>
</div>
<div>
<a href="functions.html#uddi">UDDI</a>
</div>
<div>
<a href="functions.html#udt">User Defined Types & The CLR</a>
</div>
<div>
<a href="functions.html#vad">VAD</a>
</div>
<div>
<a href="functions.html#jvmpl">Virtuoso Java PL API</a>
</div>
<div>
<a href="functions.html#bif">Virtuoso Server Extension Interface (VSEI)</a>
</div>
<div>
<a href="functions.html#ws">Web & Internet</a>
</div>
<div>
<a href="functions.html#xml">XML</a>
</div>
<div>
<a href="functions.html#XPATH">XPATH & XQUERY</a>
</div>
<br />
<div>
<a href="functionidx.html">Functions Index</a>
</div>
<br />
</div>
<div id="text">
<h2>explain</h2>
<div class="refpurpose">describe SQL statement compilation</div>
<div class="funcsynopsis">
<span class="funcdef">
<span class="function">explain</span>
</span>
(<span class="paramdef">in <span class="parameter">text</span>varchar</span>,
<span class="paramdefoptional">[in <span class="optional">cursor_type</span> integer]</span>);
</div>
<div class="refsect1">
<div class="refsect1title">Description</div>
<p>
The explain function compiles a SQL statement and returns a description
of that compilation as a result set, return value or parse tree. The result set consists of one
VARCHAR column with one line of the description in each row. Any given
line may be quite long, even several hundred characters.
</p>
<p>
The output is not a complete disassembly of the query graph, but it
is detailed enough to show the join order, the sub-query structure,
and the order of evaluation of query predicates, as well as the
splitting of a distributed VDB query over different data sources.
</p>
<p>
The optional cursor type can be one of the SQL_CURSOR_<xx> constants,
or one of the special values listed below. The default is 0, for
FORWARD ONLY. The special values each have special effect, as listed.
If the statement is a SELECT and the cursor type is not FORWARD ONLY,
the auxiliary SQL statements used by the cursor implementation are
shown.
</p>
<table class="data">
<tr>
<th class="data">Cursor Type</th>
<th class="data">Effect</th>
</tr>
<tr>
<td class="data">3</td>
<td class="data">SQL_CURSOR_STATIC</td>
</tr>
<tr>
<td class="data">2</td>
<td class="data">SQL_CURSOR_DYNAMIC</td>
</tr>
<tr>
<td class="data">1</td>
<td class="data">SQL_CURSOR_KEYSET_DRIVEN</td>
</tr>
<tr>
<td class="data">0</td>
<td class="data">SQL_CURSOR_FORWARD_ONLY</td>
</tr>
<tr>
<td class="data">-1</td>
<td class="data">A result set is printed. Same effect as 0.</td>
</tr>
<tr>
<td class="data">-2</td>
<td class="data">A parse tree is returned.</td>
</tr>
<tr>
<td class="data">-5</td>
<td class="data">A result set is printed, and a diagnostic dump is delivered
to the server's standard output. This can be viewed if the
server was started with +foreground option.</td>
</tr>
<tr>
<td class="data">-7</td>
<td class="data">Cost estimates are returned, associated with any different
join orders tried during the compilation.</td>
</tr>
</table>
<br />
</div>
<div class="refsect1">
<div class="refsect1title">Parameters</div>
<div class="refsect2">
<span class="refsect2title">text – </span>
<span class="computeroutput">varchar</span> SQL statement
</div>
<div class="refsect2">
<span class="refsect2title">cursor_type – </span>
<span class="computeroutput">integer</span> cursor type
</div>
</div>
<div class="refsect1">
<div class="refsect1title">Return Types</div>
<p>
Returns types vary with the specified cursor type.
</p>
<table class="data">
<tr>
<th class="data">Cursor Type</th>
<th class="data">Return Type</th>
</tr>
<tr>
<td class="data">0, 1, 2, 3, -5, -1</td>
<td class="data">Result set.</td>
</tr>
<tr>
<td class="data">-7</td>
<td class="data">Return value.</td>
</tr>
<tr>
<td class="data">-2</td>
<td class="data">Parse tree.</td>
</tr>
</table>
<br />
</div>
<div class="refsect1">
<div class="refsect1title">Examples</div>
<a name="ex_explainsmp" />
<div class="example">
<div class="exampletitle">Simple Example Usage</div>
<p>Execute from ISQL:</p>
<div>
<pre class="programlisting">
SQL> explain('select * from sys_users');
REPORT
VARCHAR
_______________________________________________________________________________
{
from DB.DBA.SYS_USERS by SYS_USERS 17 rows
Key SYS_USERS ASC ($47 ".U_ID", $46 ".U_NAME", $45 ".U_IS_ROLE", $44 ".U_FULL_NAME", $43 ".U_E_MAIL", $42 ".U_PASSWORD", $41 ".U_GROUP", $40 ".U_LOGI
N_TIME", $39 ".U_ACCOUNT_DISABLED", $38 ".U_DAV_ENABLE", $37 ".U_SQL_ENABLE", $36 ".U_DATA", $35 ".U_METHODS", $34 ".U_DEF_PERMS", $33 ".U_HOME", $32
".U_PASSWORD_HOOK", $31 ".U_PASSWORD_HOOK_DATA", $30 ".U_GET_PASSWORD", $29 ".U_DEF_QUAL", $28 ".U_OPTS")
Current of: <$49 "<DB.DBA.SYS_USERS >" spec 5>
Select ($47 ".U_ID", $46 ".U_NAME", $45 ".U_IS_ROLE", $44 ".U_FULL_NAME", $43 ".U_E_MAIL", $42 ".U_PASSWORD", $41 ".U_GROUP", $40 ".U_LOGIN_TIME", $39
".U_ACCOUNT_DISABLED", $38 ".U_DAV_ENABLE", $37 ".U_SQL_ENABLE", $36 ".U_DATA", $35 ".U_METHODS", $34 ".U_DEF_PERMS", $33 ".U_HOME", $32 ".U_PASSWORD
_HOOK", $31 ".U_PASSWORD_HOOK_DATA", $30 ".U_GET_PASSWORD", $29 ".U_DEF_QUAL", $28 ".U_OPTS", <$49 "<DB.DBA.SYS_USERS >" spec 5>)
}
8 Rows. -- 10 msec.
</pre>
</div>
</div>
<a name="ex_explain1" />
<div class="example">
<div class="exampletitle">Example with type -1</div>
<p>Execute from ISQL:</p>
<div>
<pre class="programlisting">
SQL> explain('sparql
select *
from <http://myopenlink.net/dataspace/person/kidehen#this>
where {?s ?p ?o} limit 10',-1);
REPORT
VARCHAR
_______________________________________________________________________________
{
Precode:
0: $27 "hen#this" := Call iri_to_id_nosignal (<constant (http://myopenlink.net/dataspace/person/kidehen#this)>)
5: BReturn 0
from DB.DBA.RDF_QUAD by RDF_QUAD 0.4 rows
Key RDF_QUAD ASC ($31 "s-1-1-t0.S", $30 "s-1-1-t0.P", $29 "s-1-1-t0.O")
inlined <col=412 G = $27 "hen#this">
Current of: <$33 "<DB.DBA.RDF_QUAD s-1-1-t0>" spec 5>
After code:
0: $34 "s" := Call id_to_iri ($31 "s-1-1-t0.S")
5: $35 "p" := Call id_to_iri ($30 "s-1-1-t0.P")
10: $36 "o" := Call __rdf_sqlval_of_obj ($29 "s-1-1-t0.O")
15: BReturn 0
Select (TOP <constant (10)>) ($34 "s", $35 "p", $36 "o", <$33 "<DB.DBA.RDF_QUAD s-1-1-t0>" spec 5>)
}
18 Rows. -- 10 msec.
</pre>
</div>
</div>
<a name="ex_explain2" />
<div class="example">
<div class="exampletitle">Example with type -2</div>
<p>Execute from ISQL:</p>
<div>
<pre class="programlisting">
SQL> select dbg_obj_print(explain('sparql
select *
from <http://myopenlink.net/dataspace/person/kidehen#this>
where {?s ?p ?o} limit 10',-2));
callret
VARCHAR
_______________________________________________________________________________
0
1 Rows. -- 20 msec.
</pre>
</div>
<p>As result on the Server console will be shown this output:</p>
<div>
<pre class="programlisting">
(100 (122 0 10 0 0 0 ) ((21 (609 'id_to_iri' ((201 's-1-1-t0' 'S' ) ) ) 0 's' 0 ) (21 (609 'id_to_iri' ((201 's-1-1-t0' 'P' ) ) ) 0 'p' 0 ) (21 (609 '
__rdf_sqlval_of_obj' ((201 's-1-1-t0' 'O' ) ) ) 0 'o' 0 ) ) 0 (106 ((107 (200 'DB.DBA.RDF_QUAD' 's-1-1-t0' 0 0 0 ) 0 ) ) (9 (201 's-1-1-t0' 'G' ) (609
'iri_to_id_nosignal' ('http://myopenlink.net/dataspace/person/kidehen#this' ) ) 0 0 0 0 0 0 ) 0 0 0 0 (907 1 ) 0 ) )
</pre>
</div>
</div>
<a name="ex_explain5" />
<div class="example">
<div class="exampletitle">Example with type -5</div>
<p>Execute from ISQL:</p>
<div>
<pre class="programlisting">
SQL> explain('sparql select * from <http://myopenlink.net/dataspace/person/kidehen#this> where {?s ?p ?o} limit 10',-5);
REPORT
VARCHAR
_______________________________________________________________________________
{
Precode:
0: $27 "hen#this" := Call iri_to_id_nosignal (<constant (http://myopenlink.net/dataspace/person/kidehen#this)>)
5: BReturn 0
from DB.DBA.RDF_QUAD by RDF_QUAD 0.4 rows
Key RDF_QUAD ASC ($31 "s-1-1-t0.S", $30 "s-1-1-t0.P", $29 "s-1-1-t0.O")
inlined <col=412 G = $27 "hen#this">
Current of: <$33 "<DB.DBA.RDF_QUAD s-1-1-t0>" spec 5>
After code:
0: $34 "s" := Call id_to_iri ($31 "s-1-1-t0.S")
5: $35 "p" := Call id_to_iri ($30 "s-1-1-t0.P")
10: $36 "o" := Call __rdf_sqlval_of_obj ($29 "s-1-1-t0.O")
15: BReturn 0
Select (TOP <constant (10)>) ($34 "s", $35 "p", $36 "o", <$33 "<DB.DBA.RDF_QUAD s-1-1-t0>" spec 5>)
}
18 Rows. -- 40 msec.
</pre>
</div>
<p>As result on the Server console will be printed this output:</p>
<div>
<pre class="programlisting">
New best dt0 is:
sequence for dt0 cost 6.9 (0.0135757 msec):RDF_QUAD as t1 on RDF_QUAD ,
{
dt dt0
unit 6.9 (0.0135757 msecs) arity 0.4 reached 1
Out cols :
call id_to_iri: (609 'id_to_iri' ((201 't1' 'S' ) ) )
call id_to_iri: (609 'id_to_iri' ((201 't1' 'P' ) ) )
call __rdf_sqlval_of_obj: (609 '__rdf_sqlval_of_obj' ((201 't1' 'O' ) ) )
call iri_to_id_nosignal: (609 'iri_to_id_nosignal' ('http://myopenlink.net/dataspace/person/kidehen#this' ) )
Table DB.DBA.RDF_QUAD(s-1-1-t0 t1) by RDF_QUAD Reached 1 unit 4.6 (0.00907572 msecs) arity 0.4
col preds:
pred artm (201 't1' 'G' ) = (609 'iri_to_id_nosignal' ('http://myopenlink.net/dataspace/person/kidehen#this' ) )
out cols: O P S
call id_to_iri: (609 'id_to_iri' ((201 't1' 'S' ) ) )
call id_to_iri: (609 'id_to_iri' ((201 't1' 'P' ) ) )
call __rdf_sqlval_of_obj: (609 '__rdf_sqlval_of_obj' ((201 't1' 'O' ) ) )
}
</pre>
</div>
</div>
<a name="ex_explain7" />
<div class="example">
<div class="exampletitle">Example with type -7</div>
<p>Execute from ISQL:</p>
<div>
<pre class="programlisting">
SQL> select explain('sparql
select *
from <http://myopenlink.net/dataspace/person/kidehen#this>
where {?s ?p ?o} limit 10',-7);
callret
VARCHAR
_______________________________________________________________________________
6.957722663879395
1 Rows. -- 0 msec.
</pre>
</div>
</div>
<a name="ex_explainvsp" />
<div class="example">
<div class="exampletitle">Example with vsp printing the result from executing explain:</div>
<div>
<pre class="programlisting">
<?vsp
declare meta, data any;
exec ('explain (?)', null, null, vector ('select * from sys_users'), 0, meta, data);
foreach (any row in data) do
{
http_value (row[0], 'p');
}
?></pre>
</div>
<p>Equivalent is:</p>
<div>
<pre class="programlisting">
<?vsp
declare meta, data any;
exec ('explain (?)', null, null, vector ('select * from sys_users'), 0, meta, data);
for (declare i,l int, i := 0, l := length (data); i < l; i := i + 1)
{
http_value (data[i][0], 'p');
}
?>
?></pre>
</div>
<p>As result, after accessing this sample vsp, the following output will be printed:</p>
<div>
<pre class="programlisting">
{
from DB.DBA.SYS_USERS by SYS_USERS 17 rows
Key SYS_USERS ASC ($47 ".U_ID", $46 ".U_NAME", $45 ".U_IS_ROLE", $44 ".U_FULL_NAME", $43 ".U_E_MAIL", $42 ".U_PASSWORD", $41 ".U_GROUP", $40 ".U_LOGIN_TIME", $39 ".U_ACCOUNT_DISABLED", $38 ".U_DAV_ENABLE", $37 ".U_SQL_ENABLE", $36 ".U_DATA", $35 ".U_METHODS", $34 ".U_DEF_PERMS", $33 ".U_HOME", $32 ".U_PASSWORD_HOOK", $31 ".U_PASSWORD_HOOK_DATA", $30 ".U_GET_PASSWORD", $29 ".U_DEF_QUAL", $28 ".U_OPTS")
Current of: <$49 "<DB.DBA.SYS_USERS >" spec 5>
Select ($47 ".U_ID", $46 ".U_NAME", $45 ".U_IS_ROLE", $44 ".U_FULL_NAME", $43 ".U_E_MAIL", $42 ".U_PASSWORD", $41 ".U_GROUP", $40 ".U_LOGIN_TIME", $39 ".U_ACCOUNT_DISABLED", $38 ".U_DAV_ENABLE", $37 ".U_SQL_ENABLE", $36 ".U_DATA", $35 ".U_METHODS", $34 ".U_DEF_PERMS", $33 ".U_HOME", $32 ".U_PASSWORD_HOOK", $31 ".U_PASSWORD_HOOK_DATA", $30 ".U_GET_PASSWORD", $29 ".U_DEF_QUAL", $28 ".U_OPTS", <$49 "<DB.DBA.SYS_USERS >" spec 5>)
}
?></pre>
</div>
</div>
</div>
<div class="refsect1">
<div class="refsect1title">See Also</div>
<p>
<a href="databaseadmsrv.html#perfdiagqueryplans">Query Plans.</a>
</p>
</div>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
277
