<!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.subject" content="SQL" />
<meta name="dc.subject" content="SQL Reference" />
<meta name="dc.subject" content="Select" />
<meta name="dc.subject" content="Update" />
<meta name="dc.subject" content="delete" />
<meta name="dc.subject" content="Select Statement" />
<meta name="dc.subject" content="SQL Syntax" />
<meta name="dc.subject" content="Syntax" />
<meta name="dc.title" content="8. SQL Reference" />
<meta name="dc.subject" content="8. SQL Reference" />
<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="sqlreference.html" title="Chapter Contents" />
<link rel="prev" href="aggregates.html" title="Standard and User-Defined Aggregate Functions" />
<link rel="next" href="sqlinverse.html" title="SQL Inverse Functions" />
<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>8. SQL Reference</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="SQL; SQL Reference; Select; Update; delete; Select Statement; SQL Syntax; Syntax; " />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="sqloptimizer" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>8. SQL Reference</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="sqlreference.html">Chapter Contents</a> | <a class="link" href="aggregates.html" title="Standard and User-Defined Aggregate Functions">Prev</a> | <a class="link" href="sqlinverse.html" title="SQL Inverse Functions">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 class="selected">
<a href="sqlreference.html">SQL Reference</a>
</div>
<br />
<div>
<a href="sqlrefDATATYPES.html">Datatypes</a>
</div>
<div>
<a href="udt.html">User Defined Types</a>
</div>
<div>
<a href="sqlrefxmldatatype.html">XML Column Type</a>
</div>
<div>
<a href="catidentifiers.html">Identifier Case & Quoting</a>
</div>
<div>
<a href="wideidentifiers.html">Wide Character Identifiers</a>
</div>
<div>
<a href="QUALIFIEDNAMES.html">Qualified Names</a>
</div>
<div>
<a href="litsbraceescs.html">Literals, Brace Escapes</a>
</div>
<div>
<a href="CREATETABLE.html">CREATE TABLE Statement</a>
</div>
<div>
<a href="DROPTABLE.html">DROP TABLE Statement</a>
</div>
<div>
<a href="CREATEINDEX.html">CREATE INDEX Statement</a>
</div>
<div>
<a href="DROPINDEX.html">DROP INDEX Statement</a>
</div>
<div>
<a href="ALTERTABLE.html">ALTER TABLE Statement</a>
</div>
<div>
<a href="CREATEVIEW.html">CREATE VIEW Statement</a>
</div>
<div>
<a href="CREATEXMLSCHEMA.html">CREATE XML SCHEMA Statement</a>
</div>
<div>
<a href="DROPXMLSCHEMA.html">DROP XML SCHEMA Statement</a>
</div>
<div>
<a href="sequenceobjects.html">Sequence Objects</a>
</div>
<div>
<a href="insertSTMT.html">INSERT Statement</a>
</div>
<div>
<a href="updatestmt.html">UPDATE Statement</a>
</div>
<div>
<a href="SELECTSTMT.html">SELECT Statement</a>
</div>
<div>
<a href="COMMIT_ROLLBACK.html">COMMIT WORK, ROLLBACK WORK Statement</a>
</div>
<div>
<a href="CHECKPOINT.html">CHECKPOINT, SHUTDOWN Statement</a>
</div>
<div>
<a href="spasviewsandtables.html">Stored Procedures as Views & Derived Tables</a>
</div>
<div>
<a href="GRANT.html">GRANT, REVOKE Statement</a>
</div>
<div>
<a href="SETstmt.html">SET Statement</a>
</div>
<div>
<a href="anytimequeries.html">Anytime Queries</a>
</div>
<div>
<a href="besteffortunion.html">Best Effort Union</a>
</div>
<div>
<a href="aggregates.html">Standard and User-Defined Aggregate Functions</a>
</div>
<div class="selected">
<a href="sqloptimizer.html">Virtuoso SQL Optimization</a>
<div>
<a href="#opttechniques" title="Optimization Techniques">Optimization Techniques</a>
<a href="#queryoptions" title="Query Options">Query Options</a>
<a href="#opttechanyorder" title="ANY ORDER">ANY ORDER</a>
<a href="#vdbcoststats" title="VDB Statistics for the SQL Compiler Collection">VDB Statistics for the SQL Compiler Collection</a>
</div>
</div>
<div>
<a href="sqlinverse.html">SQL Inverse Functions</a>
</div>
<div>
<a href="GRAMMAR.html">SQL Grammar</a>
</div>
<div>
<a href="BITMAPINDICES.html">Bitmap Indices</a>
</div>
<div>
<a href="transitivityinsQL.html">Transitivity in SQL</a>
</div>
<div>
<a href="sqlreffastphrasematch.html">Fast Phrase Match Processor</a>
</div>
<br />
</div>
<div id="text">
<a name="sqloptimizer" />
<h2>8.28. Virtuoso SQL Optimization</h2>
<p>Virtuoso provides a cost based SQL optimizer which performs the following
types of query transformation:</p>
<ul>
<li>Join Order</li>
<li>Loop Invariants</li>
<li>Opening derived tables</li>
<li>Migrating enclosing predicates into derived tables or unions</li>
<li>Dropping unreferenced columns or results</li>
<li>Detection of identically false predicates</li>
<li>Index selection</li>
<li>Grouping of co-located remote tables into single remote statements</li>
<li>Selection of join algorithm</li>
</ul>
<p>Virtuoso evaluates various permutations of joined tables against its
cost model and determines the best fit, from which it generates a query graph.
This query graph can be returned as a result set by the
<a href="fn_explain.html">explain()</a> SQL function.
The cost model is based on table row counts, defined indices and uniqueness
constraints, and column cardinalities, i.e. counts of distinct values in
columns. Additionally, histograms can be made for value distribution of
individual columns.</p>
<p>
Virtuoso automatically maintains statistics about tables in the local
database. When tables are attached from known types of remote DBMS's,
Virtuoso also attempts to retrieve statistics information if
available.
The sys_stat_analyze or sys_db_stat stored procedures can be used to force an update of statistics, also recompiling all SQL statements or procedures depending on these statistics. Once this is done, this overrides the automatic statistics. The values of automatic statistics can be seen in the SYS_COL_AUTO_STAT table.
</p>
<p>The stored procedure:</p>
<p>
<a href="fn_sys_stat_analyze.html">DB.DBA.SYS_STAT_ANALYZE
(<span class="paramdef">in <span class="parameter">full_table_name</span> varchar</span>,
<span class="paramdef">in <span class="parameter">prec</span> integer</span>);
</a>
</p>
<p>constructs the basic table statistics and feeds it into the
DB.DBA.SYS_COL_STAT system table.
The <a href="">DB.DBA.SYS_DB_STAT</a> stored procedure performs this operation on the entire database.
</p>
<p>The stored procedure:</p>
<p>
<a href="fn_sys_stat_histogram.html">DB.DBA.SYS_STAT_HISTOGRAM
(<span class="paramdef">in <span class="parameter">full_table_name</span> varchar</span>,
<span class="paramdef">in <span class="parameter">full_column_name</span> varchar</span>,
<span class="paramdef">in <span class="parameter">n_buckets</span> integer</span>,
<span class="paramdef">in <span class="parameter">prec</span> integer</span>);
</a>
</p>
<p>constructs table column histogram and feeds it into the
DB.DBA.SYS_COL_HIST system table. The default value of prec, in both cases,
is 5, which implies that a five percent sample of the table will be used. A percentage of 0 means that the whole table will be read.</p>
<a name="ex_sqlopt1" />
<div class="example">
<div class="exampletitle">Demonstration of the STAT_ANALYSE & STAT_HISTOGRAM Procedures</div>
<p>The following script is intended for use with the ISQL program as the
user dba, in the DB qualifier. The foreach statement is a special feature of
the ISQL utility.</p>
<div>
<pre class="programlisting">
create table "DB"."DBA"."DTTEST" ("ID" integer primary key);
foreach integer between 1 10 insert into "DB"."DBA"."DTTEST" ("ID") values (?);
sys_stat_analyze ('DB.DBA.DTTEST');
select * from DB.DBA.SYS_COL_STAT;
sys_stat_histogram ('DB.DBA.DTTEST', 'ID', 2);
select * from DB.DBA.SYS_COL_HIST;
</pre>
</div>
<p>
That yields:
</p>
<div>
<pre class="screen">
Resultset 1 (from DB.DBA.SYS_COL_STAT)
----------------------------------------
CS_TABLE CS_COL CS_N_DISTINCT CS_MIN CS_MAX CS_N_VALUES CS_N_ROWS
VARCHAR NOT NULL VARCHAR NOT NULL INTEGER VARCHAR VARCHAR INTEGER INTEGER
_______________________________________________________________________________
DB.DBA.DTTEST ID 10 1 10 10 10
Resultset 2 (from DB.DBA.SYS_COL_HIST)
---------------------------------------
CH_TABLE CH_COL CH_NTH_SAMPLE CH_VALUE
VARCHAR NOT NULL VARCHAR NOT NULL INTEGER NOT NULL VARCHAR
_______________________________________________________________________________
DB.DBA.DTTEST id 0 1
DB.DBA.DTTEST id 5 6
DB.DBA.DTTEST id 10 0
</pre>
</div>
</div>
<a name="opttechniques" />
<h3>8.28.1. Optimization Techniques</h3>
<a name="joinorder" />
<h4>8.28.1.1. Join Order</h4>
<p>The SQL compiler constructs permutations of tables in the FROM clause
of a query. The qualified inner join (x inner join y on p syntax) does not
dictate a join order but the outer join syntax does. Otherwise join order is
subject to change by the compiler's decision. This can be disabled with the
OPTION (ORDER) global query option, see sections below.</p>
<p>The first order tried by the optimizer is the initial left to right
order of the FROM clause. The compiler remembers its best result so
far attained in trying various compilations, so it will not explore
branches of the table permutation tree which, while incomplete, exceed
the cost of the best complete result so far. Thus a good guess of
initial table order by the programmer may save compilation time.</p>
<br />
<a name="loopinvariants" />
<h4>8.28.1.2. Loop Invariants</h4>
<p>The compiler will evaluate all expressions and predicates as early as
possible.</p>
<p>Consider the query:</p>
<div>
<pre class="programlisting">
select count (*) from item where i_price > (select avg (i_price) from item);
</pre>
</div>
<p>The compiler will detect that the sub query is data-independent from
the main from and thus will compute the average price before starting the
select for the count. Thus the above query executes in time linear to the
item table count instead of quadratic time, as it would without the removal
of the invariant.</p>
<br />
<a name="openderivedtablesviews" />
<h4>8.28.1.3. Opening Derived Tables & Views</h4>
<p>Views are initially opened into equivalent derived tables. If a
derived table in a FROM clause is sufficiently simple, i.e. has no distinct,
top, group by, and is not a multiple query expression such as a union, it can
be in-lined.</p>
<a name="ex_derivedtablesviews" />
<div class="example">
<div class="exampletitle">Derived Tables & Views</div>
<div>
<pre class="programlisting">
create view i as select * from item;
select i_id from i;
</pre>
</div>
<p>becomes</p>
<div>
<pre class="programlisting">
select i_id from (select * from item) xx;
</pre>
</div>
<p>which becomes</p>
<div>
<pre class="programlisting">
select i_id from item;
</pre>
</div>
<p>In joins involving views representing joins, the in-lining of a derived
table will add degrees of freedom to join order selection, since the tables
joined in the view will not have to be laid out contiguously.</p>
</div>
<br />
<a name="migencpreds" />
<h4>8.28.1.4. Migrating Enclosing Predicates</h4>
<p>When there is a derived table or view expansion which cannot be in-lined,
i.e. it has a group by or is a union or such, predicates of the enclosing
WHERE phrase are migrated into the derived table itself. The set of
predicates that may be thus migrated will be a function of the join order,
thus different combinations will be tried.</p>
<a name="ex_migpreds" />
<div class="example">
<div class="exampletitle">Example of Migrating Predicates</div>
<div>
<pre class="programlisting">
select i_id from (select * from item1 union all select * from item2) f where i_id = 11;
</pre>
</div>
<p>becomes</p>
<div>
<pre class="programlisting">
select i_id from (select i_id from item1
where i_id = 11 union all select i_id from item2 where i_id = 11) f;
</pre>
</div>
</div>
<br />
<a name="dropunrefed" />
<h4>8.28.1.5. Dropping Unreferenced Columns or Results</h4>
<p>It may happen as a result of view expansion that columns get introduced
into derived table selection which are nowhere referenced in the actual query.
The compiler will detect this and will not compile code accessing these.
(see above for example)</p>
<br />
<a name="detoffalsepreds" />
<h4>8.28.1.6. Detection of Identically False Predicates</h4>
<p>It is possible that view expansions introduce predicates that are never
simultaneously true with predicates of the enclosing query. This is recognized
and can result in an empty query being produced or in union terms being dropped.</p>
<p>The rules for combining predicates are as follows:</p>
<div>
<pre class="programlisting">
- a < b and a < c -> a < min (a, b)
</pre>
</div>
<p>And similarly for <=, >, and >=.</p>
<div>
<pre class="programlisting">
a in (constant1) and a in (constant2)
</pre>
</div>
<p>becomes identically false if the intersection of the constant lists is empty.</p>
<div>
<pre class="programlisting">
- a = b and a = c
</pre>
</div>
<p>becomes identically false if b and c are different constants.</p>
<p>This can lead to transformations such as</p>
<div>
<pre class="programlisting">
select * from (select * from item where i_type = 2 union all
select * from item where i_type = 3) xx where i_type = 2;
</pre>
</div>
<p>becomes</p>
<div>
<pre class="programlisting">
select * from (select * from item where i_type = 2 ) xx where i_type = 2;
</pre>
</div>
<br />
<a name="indexselect" />
<h4>8.28.1.7. Index Selection</h4>
<p>Once a join order is hypothesized, the compiler picks various indices
to use for accessing tables. The predicates applicable to the table
in the specific join order being considered, as well as a possible
ORDER BY clause affect the index choice. If there is an ORDER BY and
there is an index which can be used to directly satisfy this, the
compiler tries this index as well as the index which seems best in the
light of the available predicates. Thus having an applicable ORDER BY
does not always imply index selection. If there is a restrictive
index for row selection and an order by which could be done by
following an index for which there are no predicates, the restrictive
index plus sorting will be preferred.</p>
<p>The general rules for index selection based on predicates are:</p>
<ul>
<li>Primary key is preferred if 2 equally good choices exist.</li>
<li>Equality conditions are preferred over all others.</li>
<li>Uniqueness of an index is a plus.</li>
</ul>
<br />
<a name="grpcolocatedtables" />
<h4>8.28.1.8. Grouping Co-Located Tables</h4>
<p>In queries involving distributed data the principal cost factor is
often the RPC latency between the participating databases. In a local
area network environment with no other load we estimate one RPC to be
about 5 times longer than the selection of a single row on primary key
from a table of 1 million, not counting disk I/O. In wide area
contexts and with effective load the difference is still more marked.
Thus when considering a loop join between a local and a remote table, it
becomes obvious that the remote table should be the outer loop, unless there
are extremely restrictive criteria on the local table. The cost model takes
such considerations into account.</p>
<p>The best case of remote statement compilation is when the statement can
be passed through as is to the remote. This is first checked for the whole
query and subsequently for each sub query or derived table. The pass-through
is not possible if:</p>
<ul>
<li>The expression involves tables from multiple
remotes or local and remote ones.</li>
<li>The expression involves predicates or functions
or SQL constructs which do not exist on the remote.</li>
</ul>
<p>Even if a derived table or sub query can be passed through the normal
SQL optimization applies, thus Virtuoso will import predicates and suggest
a join order for these cases, so that the query gets rewritten with the
suggested join order left to right in the text.</p>
<p>If one join has multiple tables from the same remote but also other
tables, the optimizer will attempt to group the co-located tables together.</p>
<div>
<pre class="programlisting">
select * from r1 join t1 on r1.k = t1.k join r2 on r1.k = r2.k;
</pre>
</div>
<p>will preferentially be compiled as</p>
<div>
<pre class="programlisting">
select * from (select r1.k as r1_k, ... from r1, r2
where r1.k = r2.k) xx, t1 where r1_k = t1.k;
</pre>
</div>
<p>Note that above the join order is meant left to right and that the
derived table of r1, r2 is passed through as a unit. This becomes the
leading loop in the join because fewer RPC's are likely to be needed then due
to row prefetch. As for placing function calls, the general rule is to
place them where the arguments are, thus passed through to the remote if they
are defined there. On the other hand, if these are invariant they are always
computed locally. The explain function can be used to see the details of the
compilation. If a function is described as being defined on the particular
remote database, this information is taken into account when compiling.</p>
<br />
<a name="jointypeselect" />
<h4>8.28.1.9. Join Type Selection</h4>
<p>Starting with Virtuoso 3.0, hash joins are used where appropriate.
A hash join is about three to four times faster for a local table than with
a loop join using an exact match of primary key, thus the gain is substantial.</p>
<p>A hash join works by scanning the shorter of two joined tables and
making a hash table from the join columns. This is only possible for
joins involving equality, but most joins tend to have equalities in practice.</p>
<div class="note">
<div class="notetitle">Note:</div>
<p>A hash join does not need indices to be defined and the hash table
constructed for one hash join may be reused by another if the table is not
modified in-between.</p>
</div>
<p>A hash join will be preferred if there is a sufficient number of
estimated accesses to the joined table in the query. If a table is expected
to be accessed only once, there is less point in making the temporary
hash table.</p>
<p>The hash join temporary structures are disk based and reside in the
temporary side of the database. They do not survive a database restart
but will be ad hoc reused if such are left over from earlier queries
and there is no change in the base table between the construction of
the hash and its reuse.</p>
<p>A hash join may be specially beneficial if a remote table is on the
inner loop of a loop join. The n random accesses can then be replaced with
a single sequential scan of the remote table.</p>
<p>The selected join type can be seen in the explain output. The WITH
keyword can be used to explicitly specify a join type for a table, see query
options below.</p>
<br />
<br />
<a name="queryoptions" />
<h3>8.28.2. Query Options</h3>
<p>These are effective from Virtuoso 3.0 onwards.
</p>
<div>
<pre class="programlisting">
<sql_option>:
TABLE OPTION (<table option>,...)
<table option>:
HASH | LOOP | INDEX index_name | INDEX PRIMARY KEY | INDEX TEXT KEY
<option clause>:
OPTION (<query_option>,...)
<query_option>:
ORDER
</pre>
</div>
<p>The TABLE OPTION clause can follow a table's correlation name in a FROM clause.
The OPTION clause can appear at the end of the query, after all ORDER BY and
FOR specifications.</p>
<p>The INDEX table option is used to control
which index is chosen by the optimized SQL compiler for a given table (when you
want to override the one chosen automatically).</p>
<ul>
<li>INDEX index_name - selects a named table index (unprefixed - same name as in
create index).</li>
<li>INDEX PRIMARY KEY - selects the primary key</li>
<li>INDEX TEXT KEY - selects the free-text index as a driving one if there's a
free-text condition over that table in the statement.</li>
</ul>
<a name="ex_optclause" />
<div class="example">
<div class="exampletitle">Use of the OPTION & TABLE OPTION clauses</div>
<div>
<pre class="programlisting">
select count (*) from t1 a, t1 b table option (hash) where .row_no = b.row_no option(order)
</pre>
</div>
<p>This forces the a, b join order and specifies that the join will be
a hash join.</p>
</div>
<a name="ex_optinandjoin" />
<div class="example">
<div class="exampletitle">IN Predicate and Joins</div>
<p>Sometimes statements of the form</p>
<div>
<pre class="programlisting">
select col from t1 where t1.key in (select key from t2)
</pre>
</div>
<p>get optimized into a loop over t2 and a nested lookup on t1, as in </p>
<div>
<pre class="programlisting">
select .... from (select distinct key from t2) f, t1 where t1.key = f.key.
</pre>
</div>
<p>This optimization is mostly done when there is an index on t1.key and the IN subquery selects fewer rows than the other conditions on t1 would select.</p>
<p>In some cases the developer may wish to explicitly force this optimization to be made or not to be made. This is done with the LOOP EXISTS query option, as follows:</p>
<div>
<pre class="programlisting">
select row_no from t1 where row_no in (select row_no from t1 where fi2 = 2) option (loop exists)
</pre>
</div>
<p>will force the IN subquery to become a joined derived table.</p>
<div>
<pre class="programlisting">
select row_no from t1 where row_no in (select row_no from t1 where fi2 = 2) option (do not loop exists)
</pre>
</div>
<div>
<pre class="programlisting">
select row_no from t1 where row_no in (select row_no from t1 where fi2 = 2 option (do not loop exists))
</pre>
</div>
<p>will prevent this optimization. Note that the option (do not loop exists) can be either in the subquery or in the enclosing query.</p>
<p>The same logic applies to IN predicates in searched update or delete statements.</p>
</div>
<br />
<a name="opttechanyorder" />
<h3>8.28.3. ANY ORDER</h3>
<p>When applied to a select with no aggregation or order by, this causes the select to produce results in an order that may vary between consecutive executions and may not correspond to the order of any index.
In a cluster situation, running a query in this manner may be up to several times more efficient. This is not the default since SQL and SPARQL require that two consecutive executions of a query return the same results in the same order even if no order by is specified.
Selects that contain aggregation or order by evaluate the part which generates input to the aggregation or order in this manner automatically.
This option affects the select at the end of which it occurs and all selects inside it.</p>
<p>example:</p>
<div>
<pre class="programlisting">
select a.row_no from t1 a, t1 b wherea.row_no = 1 + b.row_no option (any order);
</pre>
</div>
<br />
<a name="vdbcoststats" />
<h3>8.28.4. VDB Statistics for the SQL Compiler Collection</h3>
<p>In order to correctly estimate the cost of the VDB operations overhead the optimized SQL
compiler should have information for the time it takes to make a "round trip" - send message
and receive response from a given remote database.</p>
<p>Virtuoso will automatically collect such information when you first attach a table from
the remote data source. The information will be cached in the
<span class="computeroutput">DS_CONN_STR</span> field of the
<span class="computeroutput">SYS_DATA_SOURCE</span> system table.</p>
<p>Sometimes it may be desirable to update that values manually, for example
when you change the network connection etc. Virtuoso provides, for the purpose,
the stored procedure:</p>
<p>
<a href="fn_vd_statistics.html">vd_statistics (in <span class="parameter">_dsn</span>, varchar := '%',in <span class="parameter">vd_table_mask</span> varchar := '%')</a>
</p>
<p>The <span class="computeroutput">_dsn</span> parameter is a LIKE-mask for the name of
the DSN as stored in DS_DSN column of SYS_DATA_SOURCE system table. Its default
value is '%' having the effect to update the "round trip" times for all the
remote data sources.</p>
<p>The <span class="computeroutput">vd_table_mask</span> parameter is a LIKE mask for the name of the table ,
as stored in RT_NAME column of SYS_DATA_SOURCE system table. Its
default value of '%' means update all tables.</p>
<p>The current round trip time can be recalculated using the function:</p>
<p>
<a href="fn_vdd_measure_rpc_time.html">vdd_measure_rpc_time (in <span class="parameter">_dsn</span> varchar)</a>
</p>
<p>This will return the estimated round-trip time in milliseconds. Calling this
function will not alter the cached statistics for the datasource.</p>
<p>Before collecting statistics data by querying the (potentially very large) remote
tables the Virtuoso VDB will try to collect the statistics in various less
resource extensive ways.</p>
<p>Generally speaking some of the statistics the SQL compiler needs in
order to do cost based optimization are available through one of the following
channels (in order of preference):</p>
<ul>
<li>by querying the remote DBMS statistics system tables: This will provide
a varying amount of information depending on the remote and the mapping function.</li>
<li> through SQLStatistics() ODBC call CARDINALITY column : A platform/remote
independent way. This will provide information for the row count only.</li>
<li>by querying the remote table to collect various aggregate functions values
(like COUNT (*), AVG() etc) : most complete, but may be very slow for large
remote tables.</li>
</ul>
<p>Unfortunately the first method is DBMS dependent. Therefore some extra setup is to be done
before this can return meaningful data. This is supported off the shelf for Oracle and Virtuoso.</p>
<p>The <a href="fn_sys_stat_analyze.html">SYS_STAT_ANALYZE()</a>
Virtuoso function will do a lookup of the remote DBMS
name and version (as returned by SQL_DBMS_NAME and SQL_DBMS_VER SQLGetInfo ()
ODBC call) in the system table DB.DBA.SYS_STAT_VDB_MAPPERS to find a Virtuoso/PL
stored procedure to call for that DBMS to retrieve the statistics data from the
remote DBMS'es system tables in DBMS dependent way.</p>
<p>The DB.DBA.SYS_STAT_VDB_MAPPERS has the following layout:</p>
<div>
<pre class="programlisting">
create table SYS_STAT_VDB_MAPPERS (
SVDM_TYPE varchar,
SVDM_PROC varchar not null,
SVDM_DBMS_NAME_MASK varchar not null,
SVDM_DBMS_VER_MASK varchar not null,
primary key (SVDM_TYPE, SVDM_DBMS_NAME_MASK, SVDM_DBMS_VER_MASK))
</pre>
</div>
<ul>
<li>SVDM_TYPE is the type of the statistics returned by the procedure. Currently
only 'SYS_COL_STAT' is supported.</li>
<li>SVDM_PROC is the Fully-qualified-name of the Virtuoso/PL stored procedure to be
called.</li>
<li>SVDM_DBMS_NAME_MASK is the LIKE kind of mask used to check the SQL_DBMS_NAME
SQLGetInfo() returned string (in uppercase).</li>
<li>SQL_DBMS_VER_MASK is the LIKE kind of mask use to check the SQL_DBMS_VER
SQLGetInfo() returned string (in uppercase)</li>
</ul>
<p>The <a href="fn_sys_stat_analyze.html">SYS_STAT_ANALYZE()</a>
will use the first matching row in the order of the primary key.</p>
<p>The procedure that collects the remote DBMS statistics (usually by doing
rexecute() against the remote) has the following signature:</p>
<div>
<pre class="programlisting">
create procedure DB.DBA.__VIRTUOSO_SYS_COL_STAT (
in DSN varchar,
in RT_NAME varchar,
in RT_REMOTE_NAME varchar
)</pre>
</div>
<p>Here:</p>
<ul>
<li>DSN is the Virtuoso dsn name.</li>
<li>RT_NAME is the local Virtuoso table name of the attached table (as in the RT_NAME of SYS_REMOTE_TABLE system table).</li>
<li>RT_REMOTE_NAME is the name of the attached table in the remote DBMS (as in RT_REMOTE_NAME of SYS_REMOTE_TABLE system table).</li>
</ul>
<p>The procedure may return a resultset that will be written into the SYS_COL_STAT
system table. The resultset has to have a row for each attached table column and
the following layout:</p>
<ul>
<li>CS_COL - this is the column name</li>
<li>CS_N_DISTINCT - the number of distinct non-null values in that column</li>
<li>CS_MIN - minimum value</li>
<li>CS_MAX - maximum value</li>
<li>CS_AVG_LEN - average data length</li>
<li>CS_N_VALUES - number of non-null values</li>
<li>CS_N_ROWS - total number of rows</li>
</ul>
<p>Virtuoso predefines two such procedures - one for another virtuoso as a remote
and the second for an Oracle DBMS. Other DBMSes can be freely added.</p>
<div>
<pre class="programlisting">
create procedure DB.DBA.__ORACLE_SYS_COL_STAT (in DSN varchar, in RT_NAME
varchar, in RT_REMOTE_NAME varchar)
returns ANY
{
declare _meta, _res any;
rexecute (DSN,
'select c.COLUMN_NAME, c.NUM_DISTINCT, NULL, NULL, c.AVG_COL_LEN, t.NUM_ROWS - c.NUM_NULLS, t.NUM_ROWS ' ||
' from ALL_TABLES t, ALL_TAB_COLUMNS c where t.TABLE_NAME = c.TABLE_NAME and t.OWNER = c.OWNER and ' ||
' t.OWNER = ? and t.TABLE_NAME = ?',
NULL, NULL, vector (name_part (RT_REMOTE_NAME, 1, NULL), name_part(RT_REMOTE_NAME, 2, NULL)), NULL, _meta, _res);
if (isarray (_res) and length (_res) > 0 and isarray (_res[0]) and isarray(_meta) and isarray (_meta[0]))
{
declare _inx, _len integer;
_inx := 0;
_len := length (_res);
exec_result_names (_meta[0]);
while (_inx < _len)
{
exec_result (_res[_inx]);
_inx := _inx + 1;
}
}
return NULL;
};</pre>
</div>
<div>
<pre class="programlisting">
create procedure DB.DBA.__VIRTUOSO_SYS_COL_STAT (in DSN varchar, in RT_NAME
varchar, in RT_REMOTE_NAME varchar)
returns ANY
{
declare _meta, _res any;
rexecute (DSN,
'select CS_COL, CS_N_DISTINCT, encode_base64 (serialize (CS_MIN)), encode_base64 (serialize (CS_MAX)), ' ||
' CS_AVG_LEN, CS_N_VALUES, CS_N_ROWS from ALL_COL_STAT where CS_TABLE = complete_table_name (?, 1)',
NULL, NULL, vector (RT_REMOTE_NAME), NULL, _meta, _res);
if (isarray (_res) and length (_res) > 0 and isarray (_res[0]) and isarray(_meta) and isarray (_meta[0]))
{
declare _inx, _len integer;
_inx := 0;
_len := length (_res);
exec_result_names (_meta[0]);
while (_inx < _len)
{
declare _res_row any;
_res_row := _res[_inx];
_res_row[2] := deserialize (decode_base64 (_res_row[2]));
_res_row[3] := deserialize (decode_base64 (_res_row[3]));
exec_result (_res_row);
_inx := _inx + 1;
}
}
return NULL;
};</pre>
</div>
<p>And here are the respective registration INSERT statements for the above
procedures:</p>
<div>
<pre class="programlisting">
insert soft SYS_STAT_VDB_MAPPERS (SVDM_TYPE, SVDM_PROC, SVDM_DBMS_NAME_MASK, SVDM_DBMS_VER_MASK)
values ('SYS_COL_STAT', 'DB.DBA.__ORACLE_SYS_COL_STAT', '%ORACLE%', '%');
insert soft SYS_STAT_VDB_MAPPERS (SVDM_TYPE, SVDM_PROC, SVDM_DBMS_NAME_MASK, SVDM_DBMS_VER_MASK)
values ('SYS_COL_STAT', 'DB.DBA.__VIRTUOSO_SYS_COL_STAT', '%VIRTUOSO%', '%');
</pre>
</div>
<p>In order to facilitate the access to statistics in Virtuoso the following four
views are created with SELECT granted to PUBLIC:</p>
<ul>
<li>ALL_COL_STAT - subset of SYS_COL_STAT (same row layout) but only returning data
for the tables the user has access to.</li>
<li>USER_COL_STAT - subset of SYS_COL_STAT (same row layout) but returns rows only
for the user-owned tables</li>
<li>ALL_COL_HIST - subset of SYS_COL_HIST (same row layout) but only returning data
for the tables the user has access to.</li>
<li>USER_COL_HIST - subset of SYS_COL_HIST (same row layout) but returns rows only
for the user-owned tables</li>
</ul>
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="aggregates.html" title="Standard and User-Defined Aggregate Functions">Previous</a>
<br />Standard and User-Defined Aggregate Functions</td>
<td align="center" width="34%">
<a href="sqlreference.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="sqlinverse.html" title="SQL Inverse Functions">Next</a>
<br />SQL Inverse Functions</td>
</tr>
</table>
</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
>
880
