1:58 p.m.
Author: shawkins
Date: 2011-08-01 14:58:02 -0400 (Mon, 01 Aug 2011)
New Revision: 3356
Modified:
branches/7.4.x/documentation/caching-guide/src/main/docbook/en-US/content/codetable.xml
branches/7.4.x/documentation/reference/src/main/docbook/en-US/content/scalar_functions.xml
Log:
TEIID-1692 refined lookup docs
Modified:
branches/7.4.x/documentation/caching-guide/src/main/docbook/en-US/content/codetable.xml
===================================================================
---
branches/7.4.x/documentation/caching-guide/src/main/docbook/en-US/content/codetable.xml 2011-08-01
16:08:41 UTC (rev 3355)
+++
branches/7.4.x/documentation/caching-guide/src/main/docbook/en-US/content/codetable.xml 2011-08-01
18:58:02 UTC (rev 3356)
@@ -29,7 +29,7 @@
<para>See the Reference for more information on use of the lookup
function.</para>
<example>
<title>Country Code Lookup</title>
- <programlisting>lookup(‘ISOCountryCodes, ‘CountryName’, ‘CountryCode’,
‘US’)</programlisting>
+ <programlisting>lookup('ISOCountryCodes', 'CountryName',
'CountryCode', 'US')</programlisting>
</example>
</section>
<section>
@@ -47,7 +47,12 @@
</section>
<section>
<title>Materialized View Alternative</title>
- <para>The lookup function is a shortcut to create an internal materialized view.
In many situations, it may be better to directly create the analogous materialized view
rather than to use a code table.</para>
+ <para>The lookup function is a shortcut to create an internal materialized view
with an appropriate primary key. In many situations, it may be better to directly create
the analogous materialized view rather than to use a code table.</para>
+ <example>
+ <title>Country Code Lookup Against A Mat View</title>
+ <programlisting>SELECT (SELECT CountryCode From MatISOCountryCodes WHERE
CountryName = tbl.CountryName) as cc FROM tbl</programlisting>
+ <para>Here MatISOCountryCodes is a view selecting from ISOCountryCodes that has
been marked as materialized and has a primary key or index on CountryName. The scalar
subquery will use the index to lookup the country code for each country name in
tbl.</para>
+ </example>
<itemizedlist>
<title>Reasons to use a materialized view:</title>
<listitem>
@@ -63,7 +68,7 @@
<para>The ability to use <link linkend="nocache">OPTION
NOCACHE</link>.</para>
</listitem>
<listitem>
- <para>Usage of a materialized view lookup as an uncorrelated subquery is no
different than the use of the lookup function.</para>
+ <para>There is almost no performance difference.</para>
</listitem>
</itemizedlist>
<orderedlist>
@@ -72,7 +77,7 @@
<para>Create a view selecting the appropriate columns from the desired table.
In general, this view may have an arbitrarily complicated transformation
query.</para>
</listitem>
<listitem>
- <para>Designate the appropriate column(s) as the primary key.</para>
+ <para>Designate the appropriate column(s) as the primary key. Additional
indexes can be added if needed.</para>
</listitem>
<listitem>
<para>Set the materialized property to true.</para>
Modified:
branches/7.4.x/documentation/reference/src/main/docbook/en-US/content/scalar_functions.xml
===================================================================
---
branches/7.4.x/documentation/reference/src/main/docbook/en-US/content/scalar_functions.xml 2011-08-01
16:08:41 UTC (rev 3355)
+++
branches/7.4.x/documentation/reference/src/main/docbook/en-US/content/scalar_functions.xml 2011-08-01
18:58:02 UTC (rev 3356)
@@ -1795,20 +1795,21 @@
<para><synopsis>LOOKUP(codeTable, returnColumn, keyColumn,
keyValue)</synopsis></para>
<para>In the lookup table codeTable, find the row where
keyColumn has the value keyValue and return the
- associated returnColumn. codeTable must be a fully-qualified string
- literal containing metadata identifiers, keyValue datatype
- must match datatype of the keyColumn, return datatype
- matches that of returnColumn. returnColumn and
- keyColumn parameters should use their shortened names.
+ associated returnColumn value or null if no matching key is found. codeTable must
be a string literal that is the fully-qualified name of the target table.
+ returnColumn and key Column must also be string literals of just the relevant
column names.
+ The keyValue can be any expression that must match the datatype of the keyColumn.
The return datatype
+ matches that of returnColumn.
</para>
- <para>For example, a StatePostalCodes table used to translate postal codes to
- complete state names might represent an example of this type of
- lookup table. One column, PostalCode, represents a key column.
- Other tables refer to this two-letter code. A
- second column, StateDisplayName, would represent the complete name
- of the state. Hence, a query to this lookup table would typically
- provide the PostalCode and expect the StateDisplayName in response.
- </para>
+ <example>
+ <title>Country Code Lookup</title>
+ <programlisting>lookup('ISOCountryCodes', 'CountryName',
'CountryCode', 'US')</programlisting>
+ <para>A ISOCountryCodes table used to translate country name to
+ ISO codes. One column, CountryName, represents a key column.
+ A second column, CountryCode, would represent the ISO code of the country.
+ Hence, a query to this lookup table would
+ provide a CountryName, shown above as 'US', and expect a CountryCode value
in response.
+ </para>
+ </example>
<para>When you call this function for any combination of codeTable,
returnColumn, and
keyColumn for the first time, the Teiid System caches the result.
The Teiid System uses this cache for all
@@ -1817,6 +1818,7 @@
the Teiid System. Thus, you should not use this function for
data that is subject to updates. Instead, you can use it against
static data that does not change over time.</para>
+ <para>See the Caching Guide for more on the caching aspects of the lookup
function.</para>
<note>
<itemizedlist>
<listitem>