svn commit: r436908 - /db/derby/docs/trunk/src/tuning/ctuntransform25857.dita

[jt...@apache.org]

Author: jta
Date: Fri Aug 25 12:07:43 2006
New Revision: 436908

URL: http://svn.apache.org/viewvc?rev=436908&view=rev
Log:
DERBY-1601 : Add documentation to the Tuning Guide about the new subquery materialization via hash 
join behavior that was introduced with DERBY-781.

Committed patch derby1601_tuning2.diff by Laura Stewart <sc...@gmail.com>.

Modified:
    db/derby/docs/trunk/src/tuning/ctuntransform25857.dita

Modified: db/derby/docs/trunk/src/tuning/ctuntransform25857.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/tuning/ctuntransform25857.dita?rev=436908&r1=436907&r2=436908&view=diff
==============================================================================
--- db/derby/docs/trunk/src/tuning/ctuntransform25857.dita (original)
+++ db/derby/docs/trunk/src/tuning/ctuntransform25857.dita Fri Aug 25 12:07:43 2006
@@ -1,4 +1,7 @@
 <?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
@@ -15,33 +18,47 @@
 See the License for the specific language governing permissions and  
 limitations under the License.
 -->
-<!DOCTYPE concept PUBLIC "-//OASIS//DTD DITA Concept//EN" "../dtd/concept.dtd">
-<concept xml:lang="en-us" id="ctuntransform25857">
+<concept id="ctuntransform25857" xml:lang="en-us">
 <title>Materialization</title>
+<shortdesc><term>Materialization</term> means that a subquery is evaluated
+only once. There are several types of subqueries that can be materialized.</shortdesc>
 <prolog><metadata>
-<keywords>
-<indexterm>Subqueries<indexterm>materialization of</indexterm></indexterm>
-</keywords>
-</metadata>
-</prolog>
+<keywords><indexterm>subqueries<indexterm>materialization</indexterm></indexterm>
+<indexterm><indexterm>materialization</indexterm>subqueries</indexterm></keywords>
+</metadata></prolog>
 <conbody>
-<p><i>Materialization</i> means that a subquery is evaluated
-only once. A subquery can be materialized if it is a noncorrelated expression
-subquery. A correlated subquery is one that references columns in the outer
-query, and so has to be evaluated for each row in the outer query. </p>
-<p>For example:   
-<codeblock><b>SELECT * FROM Staff WHERE id = (SELECT MAX(manager) FROM Org)</b></codeblock></p>
-<p>In this statement, the subquery needs to be evaluated only once.</p>
-<p>This type of subquery must return only one row. If evaluating the subquery
-causes a cardinality violation (if it returns more than one row), an exception
-will be thrown at the beginning of execution. </p>
-<p>Subquery materialization is detected prior to optimization, which allows
-the optimizer to see a materialized subquery as an unknown constant value.
-The comparison is thus optimizable.</p>
-<p>In other words, the original statement is transformed into the following
-two statements:   
-<codeblock><b><b><i>constant</i></b> = SELECT MAX(manager) FROM Org
+<section><title>Expression subqueries that are not correlated</title><p>A
+subquery can be materialized if it is a noncorrelated expression subquery.
+A correlated subquery is one that references columns in the outer query, and
+so has to be evaluated for each row in the outer query.</p><p>For example:
+   <codeblock><b>SELECT * FROM Staff WHERE id = (SELECT MAX(manager) FROM Org)</b></codeblock></p><p>In
+this statement, the subquery needs to be evaluated only once.</p><p>This type
+of subquery must return only one row. If evaluating the subquery causes a
+cardinality violation (if it returns more than one row), an exception is thrown
+when the subquery is run. </p><p>Subquery materialization is detected before
+optimization, which allows the <ph conref="tunconrefs.dita#prod/productshortname"></ph> optimizer
+to see a materialized subquery as an unknown constant value. The comparison
+is therefore optimizable. </p><p>The original statement is transformed into
+the following two statements:    <codeblock><b><i>constant</i></b> = SELECT MAX(manager) FROM Org
 SELECT * FROM Staff
-WHERE id = <b><i>constant</i></b></b></codeblock></p>
-<p>The second statement is optimizable.</p>
-</conbody></concept>
+WHERE id = <b><i>constant</i></b></codeblock></p><p>The second statement is
+optimizable.</p></section>
+<section><title>Subqueries that cannot be flattened</title><p>Materialization
+of a subquery can also occur when the subquery is nonflattenable and there
+is an equijoin between the subquery and another FROM table in the query. </p><p>For
+example:   <codeblock>SELECT i, a  FROM t1, 
+   (SELECT DISTINCT a FROM T2) x1  
+WHERE t1.i = x1.a AND t1.i in (1, 3, 5, 7) </codeblock>In this example, the
+subquery x1 is noncorrelated because it does not reference any of the columns
+from the outer query. The subquery is nonflattenable because of the DISTINCT
+keyword. <ph conref="tunconrefs.dita#prod/productshortname"></ph> does not
+flatten DISTINCT subqueries. This subquery is eligible for materialization.
+Since there is an equijoin predicate between the subquery x1 and the table
+t1 (namely, t1.i = x1.a), the <ph conref="tunconrefs.dita#prod/productshortname"></ph> optimizer
+will consider performing a hash join between t1 and x1 (with x1 as the inner
+operand). If that approach yields the best cost, <ph conref="tunconrefs.dita#prod/productshortname"></ph> materializes
+the subquery x1 to perform the hash join. The subquery is evaluated only a
+single time and the results are stored in an in-memory hash table. <ph conref="tunconrefs.dita#prod/productshortname"></ph> then
+executes the join using the in-memory result set for x1.</p></section>
+</conbody>
+</concept>