db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From chaa...@apache.org
Subject svn commit: r916743 - in /db/derby/docs/trunk/src: devguide/ tuning/
Date Fri, 26 Feb 2010 16:32:13 GMT
Author: chaase3
Date: Fri Feb 26 16:32:13 2010
New Revision: 916743

URL: http://svn.apache.org/viewvc?rev=916743&view=rev
Log:
DERBY-4507: Write user documentation for restricted table functions.

Added new topic, revised others in the Developer's Guide and one in Tuning Derby.

Patch: DERBY-4507-2.diff

Added:
    db/derby/docs/trunk/src/devguide/cdevspecialtfrestr.dita   (with props)
Modified:
    db/derby/docs/trunk/src/devguide/cdevspecialtabfuncs.dita
    db/derby/docs/trunk/src/devguide/cdevspecialtfbasic.dita
    db/derby/docs/trunk/src/devguide/cdevspecialtfexample.dita
    db/derby/docs/trunk/src/devguide/cdevspecialtfgetxxx.dita
    db/derby/docs/trunk/src/devguide/cdevspecialtfoptexample.dita
    db/derby/docs/trunk/src/devguide/cdevspecialtfoptimizer.dita
    db/derby/docs/trunk/src/devguide/cdevspecialtfopttune.dita
    db/derby/docs/trunk/src/devguide/derbydev.ditamap
    db/derby/docs/trunk/src/tuning/ctunperftablefunctions.dita

Modified: db/derby/docs/trunk/src/devguide/cdevspecialtabfuncs.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevspecialtabfuncs.dita?rev=916743&r1=916742&r2=916743&view=diff
==============================================================================
--- db/derby/docs/trunk/src/devguide/cdevspecialtabfuncs.dita (original)
+++ db/derby/docs/trunk/src/devguide/cdevspecialtabfuncs.dita Fri Feb 26 16:32:13 2010
@@ -19,10 +19,13 @@
 limitations under the License.
 -->
 <concept id="cdevspecialtabfuncs" xml:lang="en-us">
-<title>Programming Derby-style table functions</title>
+<title>Programming
+<ph conref="../conrefs.dita#prod/productshortname"></ph>-style table
+functions</title>
 <shortdesc><ph conref="../conrefs.dita#prod/productshortname"></ph> lets
 you create table functions. Table functions are functions which package up external
-data to look like Derby tables. The external data can be an XML
+data to look like <ph conref="../conrefs.dita#prod/productshortname"></ph>
+tables. The external data can be an XML
 file, a table in a foreign database, a live data feed--in short, any
 information source that can be presented as a JDBC <i>ResultSet</i>.
 </shortdesc>
@@ -31,8 +34,11 @@
 </keywords>
 </metadata></prolog>
 <conbody>
-<p>Derby-style table functions let you efficiently import foreign data into Derby tables.
-Table functions let you join Derby tables with any of
+<p><ph conref="../conrefs.dita#prod/productshortname"></ph>-style table
+functions let you efficiently import foreign data into
+<ph conref="../conrefs.dita#prod/productshortname"></ph> tables.
+Table functions let you join
+<ph conref="../conrefs.dita#prod/productshortname"></ph> tables with any of
 the following data sources:
 </p>
 <ul>
@@ -41,9 +47,10 @@
 <li>Streaming data from sensors</li>
 <li>RSS feeds</li>
 </ul>
-<p>See "CREATE FUNCTION statement" in the <cite><ph conref="../conrefs.dita#pub/citref"></ph></cite>
for
-the complete syntax needed to declare Derby-style table functions.
-The following topics
+<p>See "CREATE FUNCTION statement" in the
+<ph conref="../conrefs.dita#pub/citref"></ph> for the complete syntax needed
to
+declare <ph conref="../conrefs.dita#prod/productshortname"></ph>-style table
+functions. The following topics
 provide information on how to write Java methods which wrap
 foreign data sources inside <i>ResultSet</i>s.</p>
 </conbody>

Modified: db/derby/docs/trunk/src/devguide/cdevspecialtfbasic.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevspecialtfbasic.dita?rev=916743&r1=916742&r2=916743&view=diff
==============================================================================
--- db/derby/docs/trunk/src/devguide/cdevspecialtfbasic.dita (original)
+++ db/derby/docs/trunk/src/devguide/cdevspecialtfbasic.dita Fri Feb 26 16:32:13 2010
@@ -19,35 +19,45 @@
 limitations under the License.
 -->
 <concept id="cdevspecialtfbasic" xml:lang="en-us">
-<title>Overview of Derby-style table functions</title>
-<shortdesc>A Derby-style table function is a method which returns a JDBC <i>ResultSet</i>.</shortdesc>
+<title>Overview of
+<ph conref="../conrefs.dita#prod/productshortname"></ph>-style table
+functions</title>
+<shortdesc>A <ph conref="../conrefs.dita#prod/productshortname"></ph>-style
+table function is a method which returns a JDBC <i>ResultSet</i>.</shortdesc>
 <prolog><metadata>
 <keywords><indexterm>Functions<indexterm>table function overview</indexterm></indexterm>
 </keywords>
 </metadata></prolog>
 <conbody>
 <p>Most of the <i>ResultSet</i> methods can be
-written as stubs which simply raise exceptions. However, the Derby-style table function
+written as stubs which simply raise exceptions. However, the
+<ph conref="../conrefs.dita#prod/productshortname"></ph>-style table function
 must implement the following <i>ResultSet</i> methods:</p>
 <ul>
 <li><i>next()</i></li>
 <li><i>close()</i></li>
 <li><i>wasNull()</i></li>
-<li><i>getXXX()</i> - When invoking a Derby-style table function at runtime,
Derby calls a <i>getXXX()</i>
+<li><i>getXXX()</i> - When invoking a
+<ph conref="../conrefs.dita#prod/productshortname"></ph>-style table
+function at runtime, <ph conref="../conrefs.dita#prod/productshortname"></ph>
+calls a <i>getXXX()</i>
         method on each referenced column. The particular <i>getXXX()</i>
         method is based on the column's data type
         as declared in the <codeph>CREATE FUNCTION</codeph> statement.
 <xref href="cdevspecialtfgetxxx.dita#cdevspecialtfgetxxx"></xref>
-explains how Derby selects an appropriate <i>getXXX()</i> method.
+explains how <ph conref="../conrefs.dita#prod/productshortname"></ph> selects
an
+appropriate <i>getXXX()</i> method.
 However, nothing prevents application code from calling other <i>getXXX()</i>
         methods on the <i>ResultSet</i>. The returned <i>ResultSet</i>
-needs to implement the <i>getXXX()</i> methods which Derby will call as well
+needs to implement the <i>getXXX()</i> methods which
+<ph conref="../conrefs.dita#prod/productshortname"></ph> will call as well
         as all <i>getXXX()</i> methods which the application will call.
 </li>
 </ul>
 
 <p>
-A Derby-style table function is materialized by a public static method which returns a <i>ResultSet</i>:
+A <ph conref="../conrefs.dita#prod/productshortname"></ph>-style table function
+is materialized by a public static method which returns a <i>ResultSet</i>:
 </p>
 
 <codeblock>
@@ -55,7 +65,8 @@
 </codeblock>
 
 <p>
-The public static method is then bound to a Derby function name:
+The public static method is then bound to a
+<ph conref="../conrefs.dita#prod/productshortname"></ph> function name:
 </p>
 
 <codeblock>
@@ -75,8 +86,9 @@
 </codeblock>
 
 <p>
-To invoke a table function, wrap it in a TABLE constructor in the FROM list of a query.
-Note that the table alias (in this example "s") is a required part of the syntax:
+To invoke a table function, wrap it in a TABLE constructor in the FROM list of a
+query. Note that the table alias (in this example "s") is a required part of the
+syntax:
 </p>
 
 <codeblock>
@@ -85,5 +97,12 @@
     FROM TABLE (externalEmployees() ) s;
 </codeblock>
 
+<p>
+With a normal table function, you must select its entire contents. You can,
+however, write a restricted table function that lets you limit the rows and
+columns you select. A restricted table function can improve performance greatly.
+See <xref href="cdevspecialtfrestr.dita#cdevspecialtfrestr"></xref> for details.
+</p>
+
 </conbody>
 </concept>

Modified: db/derby/docs/trunk/src/devguide/cdevspecialtfexample.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevspecialtfexample.dita?rev=916743&r1=916742&r2=916743&view=diff
==============================================================================
--- db/derby/docs/trunk/src/devguide/cdevspecialtfexample.dita (original)
+++ db/derby/docs/trunk/src/devguide/cdevspecialtfexample.dita Fri Feb 26 16:32:13 2010
@@ -19,14 +19,15 @@
 limitations under the License.
 -->
 <concept id="cdevspecialtfexample" xml:lang="en-us">
-<title>Example Derby-style table function</title>
-<shortdesc>The following simple table function selects rows from a foreign database.</shortdesc>
+<title>Example <ph conref="../conrefs.dita#prod/productshortname"></ph>-style
+table function</title>
+<shortdesc>The following simple table function selects rows from a foreign
+database.</shortdesc>
 <prolog><metadata>
 <keywords><indexterm>Functions<indexterm>table function example</indexterm></indexterm>
 </keywords>
 </metadata></prolog>
 <conbody>
-
 <codeblock>
 package com.acme.hrSchema;
 
@@ -38,25 +39,31 @@
  */
 public class EmployeeTable
 {
-    public  static  ResultSet   read()
+    public static ResultSet read()
         throws SQLException
     {
-        Connection          conn = getConnection();
-        PreparedStatement   ps = conn.prepareStatement( "select * from hrSchema.EmployeeTable"
);
+        Connection conn = getConnection();
+        PreparedStatement ps = conn.prepareStatement(
+            "select * from hrSchema.EmployeeTable" );
 
         return ps.executeQuery();
     }
 
-    protected  static  Connection    getConnection()
+    protected static Connection getConnection()
         throws SQLException
     {
-        String  EXTERNAL_DRIVER = "com.mysql.jdbc.Driver";
+        String EXTERNAL_DRIVER = "com.mysql.jdbc.Driver";
 
-        try { Class.forName( EXTERNAL_DRIVER ); }
-        catch (ClassNotFoundException e) { throw new SQLException( "Could not find class
" + EXTERNAL_DRIVER ); }
+        try { 
+            Class.forName( EXTERNAL_DRIVER ); 
+        }
+        catch (ClassNotFoundException e) {
+            throw new SQLException( "Could not find class " 
+                + EXTERNAL_DRIVER );
+        }
 
-        Connection          conn = DriverManager.getConnection
-            ( "jdbc:mysql://localhost/hr?user=root&amp;password=mysql-passwd" );
+        Connection conn = DriverManager.getConnection( 
+            "jdbc:mysql://localhost/hr?user=root&amp;password=mysql-passwd" );
 
         return conn;
     }

Modified: db/derby/docs/trunk/src/devguide/cdevspecialtfgetxxx.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevspecialtfgetxxx.dita?rev=916743&r1=916742&r2=916743&view=diff
==============================================================================
--- db/derby/docs/trunk/src/devguide/cdevspecialtfgetxxx.dita (original)
+++ db/derby/docs/trunk/src/devguide/cdevspecialtfgetxxx.dita Fri Feb 26 16:32:13 2010
@@ -19,11 +19,16 @@
 limitations under the License.
 -->
 <concept id="cdevspecialtfgetxxx" xml:lang="en-us">
-<title>Preferred <i>getXXX()</i> methods for Derby-style table functions</title>
-<shortdesc>While scanning a Derby-style table function, Derby
-calls a preferred <i>getXXX()</i> method for each column, based on the
-column's data type. If Derby is running on a small device platform and presenting
-the JSR 169 interface to clients, then the methods which Derby calls are
+<title>Preferred <i>getXXX()</i> methods for
+<ph conref="../conrefs.dita#prod/productshortname"></ph>-style table
+functions</title>
+<shortdesc>While scanning a
+<ph conref="../conrefs.dita#prod/productshortname"></ph>-style table function,
+<ph conref="../conrefs.dita#prod/productshortname"></ph> calls a preferred
+<i>getXXX()</i> method for each column, based on the column's data type. If
+<ph conref="../conrefs.dita#prod/productshortname"></ph> is running on a small
+device platform and presenting the JSR 169 interface to clients, then the
+methods which <ph conref="../conrefs.dita#prod/productshortname"></ph> calls
are
 slightly different. This is because JSR 169 does not support BigDecimal.
 </shortdesc>
 <prolog><metadata>
@@ -31,7 +36,8 @@
 </keywords>
 </metadata></prolog>
 <conbody>
-<p>The following table lists the preferred <i>getXXX()</i> method for each
Derby data type.</p>
+<p>The following table lists the preferred <i>getXXX()</i> method for each
+<ph conref="../conrefs.dita#prod/productshortname"></ph> data type.</p>
 
 <table id="cdevspecialtftab1" rowheader="firstcol">
 
@@ -47,8 +53,8 @@
 <thead>
 <row>
 <entry align="left" colname="1" valign="bottom">Column Type Declared by CREATE FUNCTION</entry>
-<entry align="left" colname="2" valign="bottom"><i>getXXX()</i> Method
Called by Derby for JDBC 3.0 and 4.0</entry>
-<entry align="left" colname="3" valign="bottom"><i>getXXX()</i> Method
Called by Derby for JSR 169</entry>
+<entry align="left" colname="2" valign="bottom"><i>getXXX()</i> Method
Called by <ph conref="../conrefs.dita#prod/productshortname"></ph> for JDBC 3.0
and 4.0</entry>
+<entry align="left" colname="3" valign="bottom"><i>getXXX()</i> Method
Called by <ph conref="../conrefs.dita#prod/productshortname"></ph> for JSR 169</entry>
 </row>
 </thead>
 

Modified: db/derby/docs/trunk/src/devguide/cdevspecialtfoptexample.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevspecialtfoptexample.dita?rev=916743&r1=916742&r2=916743&view=diff
==============================================================================
--- db/derby/docs/trunk/src/devguide/cdevspecialtfoptexample.dita (original)
+++ db/derby/docs/trunk/src/devguide/cdevspecialtfoptexample.dita Fri Feb 26 16:32:13 2010
@@ -45,48 +45,56 @@
 /**
  * Tuned table function.
  */
-public class TunedEmployeeTable extends EmployeeTable implements VTICosting
+public class TunedEmployeeTable extends EmployeeTable 
+    implements VTICosting
 {
     public TunedEmployeeTable() {}
 
-    public double getEstimatedRowCount( VTIEnvironment optimizerState ) throws SQLException
+    public double getEstimatedRowCount( VTIEnvironment optimizerState ) 
+        throws SQLException
     {
         return getRowCount( optimizerState );
     }
 
-    public double getEstimatedCostPerInstantiation( VTIEnvironment optimizerState ) throws
SQLException
+    public double getEstimatedCostPerInstantiation( 
+        VTIEnvironment optimizerState ) throws SQLException
     {
-        double      I = 100.0;  // optimizer imprecision
-        double      P = 10.0;   // cost per row in milliseconds
-        double      E = 0.0;    // cost of instantiating the external ResultSet
-        double      N = getRowCount( optimizerState );
+        double I = 100.0;  // optimizer imprecision
+        double P = 10.0;   // cost per row in milliseconds
+        double E = 0.0;    // cost of instantiating the external 
+                            //   ResultSet
+        double N = getRowCount( optimizerState );
 
         return I * ( ( P * N ) + E );
     }
     
-    public boolean supportsMultipleInstantiations( VTIEnvironment optimizerState ) throws
SQLException
+    public boolean supportsMultipleInstantiations( 
+        VTIEnvironment optimizerState ) throws SQLException
     {
         return true;
     }
 
-    //////////////////////////////////////////////////////////////////////////////
+    //////////////////////////////////////////////////////////////////
 
     private double  getRowCount( VTIEnvironment optimizerState )
         throws SQLException
     {
-        String            ROW_COUNT_KEY = "rowCountKey";
-        Double          estimatedRowCount = (Double) getSharedState( optimizerState, ROW_COUNT_KEY
);
+        String ROW_COUNT_KEY = "rowCountKey";
+        Double estimatedRowCount = (Double) getSharedState( 
+            optimizerState, ROW_COUNT_KEY );
         
         if ( estimatedRowCount == null )
         {
-            Connection                  conn = getConnection();
-            PreparedStatement       ps = conn.prepareStatement( "select count(*) from hrSchema.EmployeeTable"
);
-            ResultSet                      rs = ps.executeQuery();
+            Connection        conn = getConnection();
+            PreparedStatement ps = conn.prepareStatement( 
+                "select count(*) from hrSchema.EmployeeTable" );
+            ResultSet         rs = ps.executeQuery();
 
             rs.next();
             estimatedRowCount = new Double( rs.getDouble( 1 ) );
             
-            setSharedState( optimizerState, ROW_COUNT_KEY, estimatedRowCount );
+            setSharedState( optimizerState, ROW_COUNT_KEY, 
+                estimatedRowCount );
 
             rs.close();
             ps.close();
@@ -96,10 +104,17 @@
         return estimatedRowCount.doubleValue();
     }
 
-    private Serializable  getSharedState( VTIEnvironment optimizerState, String key )
-    { return (Serializable) optimizerState.getSharedState( key ); }
-    private void    setSharedState( VTIEnvironment optimizerState, String key, Serializable
value )
-    { optimizerState.setSharedState( key, value ); }
+    private Serializable getSharedState( 
+        VTIEnvironment optimizerState, String key )
+    { 
+        return (Serializable) optimizerState.getSharedState( key ); 
+    }
+
+    private void setSharedState( VTIEnvironment optimizerState, 
+        String key, Serializable value )
+    { 
+        optimizerState.setSharedState( key, value ); 
+    }
 }
 </codeblock>
 

Modified: db/derby/docs/trunk/src/devguide/cdevspecialtfoptimizer.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevspecialtfoptimizer.dita?rev=916743&r1=916742&r2=916743&view=diff
==============================================================================
--- db/derby/docs/trunk/src/devguide/cdevspecialtfoptimizer.dita (original)
+++ db/derby/docs/trunk/src/devguide/cdevspecialtfoptimizer.dita Fri Feb 26 16:32:13 2010
@@ -19,20 +19,23 @@
 limitations under the License.
 -->
 <concept id="cdevspecialtfoptimizer" xml:lang="en-us">
-<title>Optimizer support for Derby-style table functions</title>
-<shortdesc>This topic explains how to fine-tune the Derby
-optimizer's decision about where to place a table function in the
-join order.</shortdesc>
+<title>Optimizer support for
+<ph conref="../conrefs.dita#prod/productshortname"></ph>-style table
+functions</title>
+<shortdesc>This topic explains how to fine-tune the
+<ph conref="../conrefs.dita#prod/productshortname"></ph> optimizer's decision
+about where to place a table function in the join order.</shortdesc>
 <prolog><metadata>
-<keywords><indexterm>Functions<indexterm>costing table functions</indexterm></indexterm>
+<keywords>
+<indexterm>functions<indexterm>costing table functions</indexterm></indexterm>
 <indexterm>Optimizer<indexterm>costing table functions</indexterm></indexterm>
 </keywords>
 </metadata></prolog>
 <conbody>
 
 <p>
-By default, the Derby optimizer makes the following assumptions about
-a table function:
+By default, the <ph conref="../conrefs.dita#prod/productshortname"></ph>
+optimizer makes the following assumptions about a table function:
 </p>
 
 <ul>
@@ -63,9 +66,9 @@
 <li><b>VTICosting</b> - The class must also implement
 <i>org.apache.derby.vti.VTICosting</i>. This involves
 implementing the following methods as described in
-<xref href="cdevspecialtfopttune.dita#cdevspecialtfopttune">Measuring the cost of Derby-style
table functions</xref>
+<xref href="cdevspecialtfopttune.dita#cdevspecialtfopttune"></xref>
 and
-<xref href="cdevspecialtfoptexample.dita#cdevspecialtfoptexample">Example VTICosting
implementation</xref>:
+<xref href="cdevspecialtfoptexample.dita#cdevspecialtfoptexample"></xref>:
   <ul>
   <li><i>getEstimatedCostPerInstantiation()</i> - This method estimates
the
             cost of invoking the table function and looping
@@ -74,10 +77,10 @@
     <ul>
     <li>Empty table - This is the cost of invoking the
     table function, even if it contains 0 rows. See the description of variable <b>E</b>
-    in <xref href="cdevspecialtfopttune.dita#cdevspecialtfopttune">Measuring the cost
of Derby-style table functions</xref>.</li>
+    in <xref href="cdevspecialtfopttune.dita#cdevspecialtfopttune"></xref>.</li>
     <li>Scanning - This is the cost of looping through all of the
     rows returned by the table function. See the calculation of <b>P*N</b>
-    in <xref href="cdevspecialtfopttune.dita#cdevspecialtfopttune">Measuring the cost
of Derby-style table functions</xref>.</li>
+    in <xref href="cdevspecialtfopttune.dita#cdevspecialtfopttune"></xref>.</li>
     </ul>
   </li>
   <li><i>getEstimatedRowCount()</i> - This guesses the number of rows
@@ -90,9 +93,5 @@
 </li>
 </ul>
 
-<p>
-For more information, see:
-</p>
-
 </conbody>
 </concept>

Modified: db/derby/docs/trunk/src/devguide/cdevspecialtfopttune.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevspecialtfopttune.dita?rev=916743&r1=916742&r2=916743&view=diff
==============================================================================
--- db/derby/docs/trunk/src/devguide/cdevspecialtfopttune.dita (original)
+++ db/derby/docs/trunk/src/devguide/cdevspecialtfopttune.dita Fri Feb 26 16:32:13 2010
@@ -19,9 +19,12 @@
 limitations under the License.
 -->
 <concept id="cdevspecialtfopttune" xml:lang="en-us">
-<title>Measuring the cost of Derby-style table functions</title>
-<shortdesc>This topic shows how to measure the cost of a Derby-style
-table function.</shortdesc>
+<title>Measuring the cost of
+<ph conref="../conrefs.dita#prod/productshortname"></ph>-style table
+functions</title>
+<shortdesc>This topic shows how to measure the cost of a
+<ph conref="../conrefs.dita#prod/productshortname"></ph>-style table
+function.</shortdesc>
 <prolog><metadata>
 <keywords><indexterm>Functions<indexterm>calculating table function costs</indexterm></indexterm>
 <indexterm>Optimizer<indexterm>calculating table function costs</indexterm></indexterm>
@@ -70,8 +73,9 @@
 </ul>
 
 <p>
-To estimate these values, turn on Derby statistics collection and run
-        the following experiment several times, averaging the results:
+To estimate these values, turn on
+<ph conref="../conrefs.dita#prod/productshortname"></ph> statistics collection
+and run the following experiment several times, averaging the results:
 </p>
 
 <ul>
@@ -128,14 +132,16 @@
 
 <p>
 You may know that <b>E</b> is basically 0. If so, you can skip this step.
-Otherwise, to estimate <b>E</b>, turn on Derby statistics collection and run
-        the following experiment several times, averaging the results:
+Otherwise, to estimate <b>E</b>, turn on
+<ph conref="../conrefs.dita#prod/productshortname"></ph> statistics collection
+and run the following experiment several times, averaging the results:
 </p>
 
 <ul>
 <li><b>Short-circuit</b> = Short-circuit the next() method of the
 <i>ResultSet</i>
-returned by your Derby-style Table Function so that it returns
+returned by your <ph conref="../conrefs.dita#prod/productshortname"></ph>-style
+table function so that it returns
           <i>false</i> the first time it is called. This makes it
           appear that the
 <i>ResultSet</i>
@@ -151,8 +157,9 @@
 </ul>
 
 <p>
-To estimate <b>P</b>, turn on Derby statistics collection and run
-        the following experiment several times, averaging the results:
+To estimate <b>P</b>, turn on
+<ph conref="../conrefs.dita#prod/productshortname"></ph> statistics collection
+and run the following experiment several times, averaging the results:
 </p>
 
 <ul>

Added: db/derby/docs/trunk/src/devguide/cdevspecialtfrestr.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevspecialtfrestr.dita?rev=916743&view=auto
==============================================================================
--- db/derby/docs/trunk/src/devguide/cdevspecialtfrestr.dita (added)
+++ db/derby/docs/trunk/src/devguide/cdevspecialtfrestr.dita Fri Feb 26 16:32:13 2010
@@ -0,0 +1,206 @@
+<?xml version="1.0" encoding="utf-8"?>
+
+<!DOCTYPE concept PUBLIC "-//OASIS//DTD DITA Concept//EN"
+ "../dtd/concept.dtd">
+<!-- 
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements.  See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License.  You may obtain a copy of the License at      
+
+   http://www.apache.org/licenses/LICENSE-2.0  
+
+Unless required by applicable law or agreed to in writing, software  
+distributed under the License is distributed on an "AS IS" BASIS,  
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.  
+See the License for the specific language governing permissions and  
+limitations under the License.
+-->
+<concept id="cdevspecialtfrestr" xml:lang="en-us">
+<title>Writing restricted table functions</title>
+<shortdesc>Restricted table functions are
+<ph conref="../conrefs.dita#prod/productshortname"></ph>-style table functions
+which perform more efficiently because they can be told in advance which columns
+they will be asked to fetch along with simple limits on those columns. This
+feature exploits the expressiveness of the Java programming language and does
+not require any extensions to SQL.
+</shortdesc>
+<prolog><metadata>
+<keywords><indexterm>Functions<indexterm>programming table functions</indexterm></indexterm>
+</keywords>
+</metadata></prolog>
+<conbody>
+<p>A table function returns a rectangular chunk of data. If you use a restricted
+table function, <ph conref="../conrefs.dita#prod/productshortname"></ph> can
+tell the table function to return a shorter and  narrower rectangle.</p>
+<p>
+Consider the following scan of a table in a foreign database:</p>
+<codeblock>    select id, firstName, lastName
+    from table( foreignDatabaseEmployeeTable() ) s
+    where lastName = 'Stone'
+</codeblock>
+<p>
+If <i>foreignDatabaseEmployeeTable</i> is a restricted table function,
+<ph conref="../conrefs.dita#prod/productshortname"></ph> can tell the table
+function to fetch only the <i>id</i>, <i>firstName</i>, and <i>lastName</i>
+columns. In addition, <ph conref="../conrefs.dita#prod/productshortname"></ph>
+can tell the table function that it does not need to scan the entire
+foreign table; instead, the table function only needs to retrieve information
+for employees whose last name is "Stone".</p>
+<p>
+Depending on the table function and query, this feature can support 1000X, 
+1000000X, or even greater performance improvements.</p>
+<section><title>How to use restricted table functions</title>
+<p>
+Creating and using a restricted table function involves the following steps:</p>
+<ol>
+<li><b>Implement</b> - You must write a class which implements both
+<i>java.sql.ResultSet</i> and the
+<ph conref="../conrefs.dita#prod/productshortname"></ph>-specific interface
+<i>org.apache.derby.vti.RestrictedVTI</i>. This interface defines an
+<i>initScan()</i> method. When executing a query,
+<ph conref="../conrefs.dita#prod/productshortname"></ph> uses that method to
+tell the table function what columns it will have to fetch and what bounds
+should be applied to those columns in order to reduce the number of rows
+returned. For the rest of this discussion, this user-written class will be
+referred to as <i>MyVTIClass</i>.</li>
+<li><b>Publish</b> - You must publish the table function by creating a
public
+static method which returns a <i>MyVTIClass</i>. This is important. The
+<ph conref="../conrefs.dita#prod/productshortname"></ph> compiler must be able
+to see that the table function returns an object which implements both
+<i>java.sql.ResultSet</i> and <i>org.apache.derby.vti.RestrictedVTI</i>.</li>
+<li><b>Declare</b> - You declare the table function to
+<ph conref="../conrefs.dita#prod/productshortname"></ph> using the same CREATE
+FUNCTION syntax you are already familiar with. This syntax does not change.</li>
+<li><b>Invoke</b> - You then use the table function in a query. When
+<ph conref="../conrefs.dita#prod/productshortname"></ph> compiles the query,
it
+sees that the return type of the table function implements
+<i>org.apache.derby.vti.RestrictedVTI</i>. Armed with this information, at
+runtime <ph conref="../conrefs.dita#prod/productshortname"></ph> calls the
+<i>initScan()</i> method once before calling any of the <i>ResultSet</i>
methods.
+</li>
+</ol>
+<p>For example, you would declare the function as follows:</p>
+<codeblock>
+public class MyVTIClass implements ResultSet, RestrictedVTI
+{
+    ...
+
+    public void initScan(java.lang.String[] columnNames, 
+        org.apache.derby.vti.Restriction restriction ) 
+        throws SQLException {
+         ... 
+    }
+}
+</codeblock>
+<p>Then you publish the table function method:</p>
+<codeblock>
+public static MyVTIClass foreignDatabaseEmployeeTable() 
+    throws SQLException {
+    ... 
+}
+</codeblock>
+<p>Then you declare the table function to
+<ph conref="../conrefs.dita#prod/productshortname"></ph>:</p>
+<codeblock>
+create function foreignDatabaseEmployeeTable()
+returns table
+(
+    id int,
+    birthday date,
+    taxPayerID varchar( 50 ),
+    firstName varchar( 50 ),
+    lastName varchar( 50 )
+)
+language java
+parameter style DERBY_JDBC_RESULT_SET
+no sql
+external name 'com.acme.portal.ForeignQueries.foreignDatabaseEmployeeTable'
+</codeblock>
+<p>Finally, you invoke the table function in a query:</p>
+<codeblock>
+select id, firstName, lastName
+from table( foreignDatabaseEmployeeTable() ) s
+where lastName = 'Stone'
+</codeblock>
+<p>When you invoke this query,
+<ph conref="../conrefs.dita#prod/productshortname"></ph> does the following:</p>
+<ul>
+<li><b>Prepare</b> - When
+<ph conref="../conrefs.dita#prod/productshortname"></ph> prepares the query,
+<ph conref="../conrefs.dita#prod/productshortname"></ph> sees that the
+<i>foreignDatabaseEmployeeTable()</i> method returns an object which implements
+<i>org.apache.derby.vti.RestrictedVTI</i>. This is all that
+<ph conref="../conrefs.dita#prod/productshortname"></ph> needs to know in order
+to compile a plan which takes advantage of this feature.</li>
+<li><b>Execute</b> - When
+<ph conref="../conrefs.dita#prod/productshortname"></ph> executes the query,
+<ph conref="../conrefs.dita#prod/productshortname"></ph> calls
+<i>initScan()</i>. In this example,
+<ph conref="../conrefs.dita#prod/productshortname"></ph> calls
+<i>initScan()</i> with the following arguments:
+<codeblock>
+initScan( new String[] { "ID", null, null, "FIRSTNAME", "LASTNAME" }, 
+    new Restriction.ColumnQualifier(
+        "LASTNAME", ORDER_OP_EQUALS, "Stone" ) )
+</codeblock>
+<p>This, in turn, causes the following to happen:</p>
+  <ul>
+  <li><i>Width</i> - The call to <i>initScan()</i> told the
table function what
+   columns should be fetched.</li>
+  <li><i>Length</i> - The call to <i>initScan()</i> told the
table function how
+   to filter the rows it returns.</li>
+  <li><i>Loop</i> - <ph conref="../conrefs.dita#prod/productshortname"></ph>
then
+   calls <i>MyVTIClass.next()</i> and retrieves rows until
+   <i>MyVTIClass.next()</i> returns false. For each row,
+   <ph conref="../conrefs.dita#prod/productshortname"></ph> calls:
+     <ul>
+     <li><i>MyVTIClass.getInt( 1 )</i> to get the <i>id</i>
column.</li>
+     <li><i>MyVTIClass.getString( 4 )</i> to get the <i>firstName</i>
+      column.</li>
+     <li><i>MyVTIClass.getString( 5 )</i> to get the <i>lastName</i>
+      column.</li>
+     </ul>
+  </li>
+  </ul>
+</li>
+</ul>
+</section>
+<section><title>Contract</title>
+<p><ph conref="../conrefs.dita#prod/productshortname"></ph> calls
+<i>initScan()</i> before calling any other method on the <i>ResultSet</i>.
The
+call to <i>initScan()</i> merely passes hints, which the restricted table
+function can exploit in order to perform better.
+<ph conref="../conrefs.dita#prod/productshortname"></ph> enforces the
+restriction outside the table function. Therefore, a restricted table function
+can still fetch extra columns and can ignore part or all of the restriction set
+by the call to <i>initScan()</i>.</p>
+</section>
+<section><title>Affected Operations</title>
+<p>Compared to ordinary table functions, a restricted table function can perform
+better in queries involving the following comparisons of its columns to
+constants:</p>
+<codeblock>
+&lt;
+&lt;=
+=
+&gt;
+&gt;=
+IS NULL
+IS NOT NULL
+</codeblock>
+<p>In addition, performance gains can be realized for queries involving the
+following operators on the columns of the restricted table function:</p>
+<codeblock>
+LIKE
+BETWEEN
+</codeblock>
+<p>However, this feature does not boost performance either for the IN operator,
+or in situations where <ph conref="../conrefs.dita#prod/productshortname"></ph>
+transforms OR lists into IN lists. See "Or transformations" in
+<ph conref="../conrefs.dita#pub/cittuning"></ph> for more information.</p>
+</section>
+</conbody>
+</concept>

Propchange: db/derby/docs/trunk/src/devguide/cdevspecialtfrestr.dita
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/docs/trunk/src/devguide/derbydev.ditamap
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/derbydev.ditamap?rev=916743&r1=916742&r2=916743&view=diff
==============================================================================
--- db/derby/docs/trunk/src/devguide/derbydev.ditamap (original)
+++ db/derby/docs/trunk/src/devguide/derbydev.ditamap Fri Feb 26 16:32:13 2010
@@ -1993,6 +1993,8 @@
 </topicref>
 <topicref href="cdevspecialtfexample.dita" navtitle="Example Derby-style table function">
 </topicref>
+<topicref href="cdevspecialtfrestr.dita" navtitle="Writing restricted table functions">
+</topicref>
 <topicref href="cdevspecialtfoptimizer.dita" navtitle="Optimizer support for Derby-style
table functions">
 <topicref href="cdevspecialtfopttune.dita" navtitle="Measuring the cost of Derby-style
table functions">
 </topicref>

Modified: db/derby/docs/trunk/src/tuning/ctunperftablefunctions.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/tuning/ctunperftablefunctions.dita?rev=916743&r1=916742&r2=916743&view=diff
==============================================================================
--- db/derby/docs/trunk/src/tuning/ctunperftablefunctions.dita (original)
+++ db/derby/docs/trunk/src/tuning/ctunperftablefunctions.dita Fri Feb 26 16:32:13 2010
@@ -18,10 +18,10 @@
 limitations under the License.
 -->
 <concept id="ctunperftablefunctions" xml:lang="en-us">
-<title>Customize the optimizer methods for table functions</title>
+<title>Improve the performance of table functions</title>
 <prolog><metadata>
-<keywords><indexterm>Functions<indexterm>table function cost</indexterm></indexterm>
-<indexterm>Optimizer<indexterm>table function cost</indexterm></indexterm>
+<keywords><indexterm>Functions<indexterm>table functions</indexterm></indexterm>
+<indexterm>Optimizer<indexterm>table function performance</indexterm></indexterm>
 </keywords>
 </metadata></prolog>
 <conbody>
@@ -29,9 +29,10 @@
 user-written Derby-style table function. For this reason, the
 optimizer may place a table function in an inefficient position in the
 join order. You can give the optimizer more information so that it
-makes better choices. See
-"Programming Derby-style table functions" in the
-<cite><ph conref="../conrefs.dita#pub/citdevelop"></ph></cite>.
-</p>
+makes better choices. See "Programming Derby-style table functions" in the
+<ph conref="../conrefs.dita#pub/citdevelop"></ph> for details.</p>
+<p>Using restricted table functions can also improve performance greatly. See
+"Writing restricted table functions" in the
+<ph conref="../conrefs.dita#pub/citdevelop"></ph>.</p>
 </conbody>
 </concept>



Mime
View raw message