You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by jt...@apache.org on 2006/02/18 20:14:50 UTC
svn commit: r378759 - in /db/derby/docs/trunk/src/tuning:
ctundepthoptover.dita ctunoptimzoverride.dita tuningderby.ditamap
Author: jta
Date: Sat Feb 18 11:14:48 2006
New Revision: 378759
URL: http://svn.apache.org/viewcvs?rev=378759&view=rev
Log:
DERBY-855 Applied Eric Radzinski's patch that documents optimizer override
support.
Added:
db/derby/docs/trunk/src/tuning/ctundepthoptover.dita
db/derby/docs/trunk/src/tuning/ctunoptimzoverride.dita
Modified:
db/derby/docs/trunk/src/tuning/tuningderby.ditamap
Added: db/derby/docs/trunk/src/tuning/ctundepthoptover.dita
URL: http://svn.apache.org/viewcvs/db/derby/docs/trunk/src/tuning/ctundepthoptover.dita?rev=378759&view=auto
==============================================================================
--- db/derby/docs/trunk/src/tuning/ctundepthoptover.dita (added)
+++ db/derby/docs/trunk/src/tuning/ctundepthoptover.dita Sat Feb 18 11:14:48 2006
@@ -0,0 +1,68 @@
+<?xml version="1.0" encoding="utf-8"?>
+
+<!DOCTYPE concept PUBLIC "-//IBM//DTD DITA Concept//EN"
+ "../dtd/concept.dtd">
+<concept id="ctundepthoptover" xml:lang="en-us">
+<title>Optimizer overrides</title>
+<conbody>
+<p>RUNTIMESTATISTICS provides information about user-specified optimizer hints
+that were specified by using a -- DERBY-PROPERTIES clause.</p>
+<p>The following example shows a SELECT statement in which the optimizer was
+forced to use a particular index:<codeblock><b>SELECT * FROM t1 -- DERBY-PROPERTIES index = t1_c1
+FOR UPDATE OF c2, c1</b></codeblock></p>
+<p>RUNTIMESTATISTICS returns the following information about this statement:<codeblock>Statement Name:
+ null
+Statement Text: select * from t1 --DERBY-PROPERTIES index = t1_c1
+for update of c2, c1
+
+
+Parse Time: 0
+Bind Time: 0
+Optimize Time: 0
+Generate Time: 0
+Compile Time: 0
+Execute Time: 0
+Begin Compilation Timestamp : null
+End Compilation Timestamp : null
+Begin Execution Timestamp : null
+End Execution Timestamp : null
+Statement Execution Plan Text:
+Index Row to Base Row ResultSet for T1:
+Number of opens = 1
+Rows seen = 4
+Columns accessed from heap = {0, 1, 2}
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ User supplied optimizer overrides on T1 are { index=T1_C1 }
+ Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level
+ using exclusive row locking chosen by the optimizer
+ Number of opens = 1
+ Rows seen = 4
+ Rows filtered = 0
+ Fetch Size = 1
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched=All
+ Number of columns fetched=2
+ Number of deleted rows visited=0
+ Number of pages visited=1
+ Number of rows qualified=4
+ Number of rows visited=4
+ Scan type=btree
+ Tree height=1
+ start position:
+ None
+ stop position:
+ None
+ qualifiers:
+None
+
+</codeblock></p>
+</conbody>
+</concept>
Added: db/derby/docs/trunk/src/tuning/ctunoptimzoverride.dita
URL: http://svn.apache.org/viewcvs/db/derby/docs/trunk/src/tuning/ctunoptimzoverride.dita?rev=378759&view=auto
==============================================================================
--- db/derby/docs/trunk/src/tuning/ctunoptimzoverride.dita (added)
+++ db/derby/docs/trunk/src/tuning/ctunoptimzoverride.dita Sat Feb 18 11:14:48 2006
@@ -0,0 +1,107 @@
+<?xml version="1.0" encoding="utf-8"?>
+
+<!DOCTYPE concept PUBLIC "-//IBM//DTD DITA Concept//EN"
+ "../dtd/concept.dtd">
+<concept id="ctunoptimzoverride" xml:lang="en-us">
+<title>Overriding the default optimizer behavior</title>
+<conbody>
+<p>You can override the default behavior of the Derby query optimizer by including
+a --DERBY-PROPERTIES clause and an associated property as a comment within
+an SQL statement.</p>
+<p>Because optimizer overrides are expressed as comments, they must be included
+at the end of a line. You can specify optimizer override properties for an
+entire FROM clause, for tables in the FROM clause, or for both. </p>
+<p>The syntax for a FROM clause property is:<codeblock><b>FROM [ -- DERBY-PROPERTIES <xref
+href="ctunoptimzoverride.dita#ctunoptimzoverride/joinorder">joinOrder</xref> = { FIXED | UNFIXED } ]
+Â Â Â Â Â Â Â <i>TableExpression</i> [,<i>TableExpression</i>]*</b></codeblock></p>
+<p>The syntax for table optimizer override properties, which must be included
+at the end of a TableExpression, is:<codeblock><b>{<i>table-Name</i> | <i>view-Name</i> }
+Â Â Â Â Â Â Â [ [ AS ] <i>correlation-Name</i>
+Â Â Â Â Â Â Â Â [ (<i>Simple-column-Name</i> [ , <i>Simple-column-Name</i> ]* ) ] ]
+Â Â Â Â Â Â Â [ -- DERBY-PROPERTIES { <xref href="ctunoptimzoverride.dita#ctunoptimzoverride/constraint">constraint</xref> = <i>constraint-Name</i> | <xref
+href="ctunoptimzoverride.dita#ctunoptimzoverride/index">index</xref> = <i>index-Name</i> | <xref
+href="ctunoptimzoverride.dita#ctunoptimzoverride/joinstrat">joinStrategy</xref> = { NESTEDLOOP | HASH } } ]</b></codeblock></p>
+<p>The space between -- and DERBY-PROPERTIES is optional.</p>
+<note type="important">Make sure that you adhere to the correct syntax when
+using the -- DERBY-PROPERTIES clause. Failure to do so can cause the parser
+to interpret it as a comment and ignore it. To verify that the parser interpreted
+your overrides correctly, you can use RunTimeStatistics. See <xref href="ctundepthoptover.dita#ctundepthoptover">Optimizer
+overrides</xref> for more information.</note>
+<p>The following four properties are available for use in a --DERBY-PROPERTIES
+clause:<dl><dlentry>
+<dt id="constraint">constraint</dt>
+<dd>To force the use of the index that enforces a primary key, a foreign key,
+or unique constraint, use the constraint property and specify the unqualified
+name of the constraint. The constraint property can be used only within a
+TableExpression, and it can be specified only on base tables; it cannot be
+specified on views or derived tables.</dd>
+</dlentry><dlentry>
+<dt id="index">index</dt>
+<dd>The index property is similar to the constraint property. To force use
+of a particular index, specify the unqualified index name. To force a table
+scan, specify null for the index name. The index property can be used only
+within a TableExpression, and it can be specified only on base tables; it
+cannot be specified on views or derived tables.</dd>
+</dlentry><dlentry>
+<dt id="joinorder">joinOrder</dt>
+<dd>Use the joinOrder property to override the optimizerâs choice of join
+order for two tables. When the value FIXED is specified, the optimizer will
+choose the order of tables as they appear in the FROM clause as the join order.
+ Valid values for the joinOrder property include FIXED and UNFIXED.<p>The
+joinOrder property can be used with a FROM clause.</p></dd>
+</dlentry><dlentry>
+<dt id="joinstrat">joinStrategy</dt>
+<dd>Use the joinStrategy property to override the optimizerâs choice of join
+strategy. The two types of join strategy are called <i>nested loop</i> and <i>hash</i>.
+In a nested loop join strategy, for each qualifying row in the outer table,
+Derby uses the appropriate access path (index or table scan) to find the matching
+rows in the inner table. In a hash join strategy, Derby constructs a hash
+table that represents the inner table. For each qualifying row in the outer
+table, Derby does a quick lookup on the hash table to find the matching rows
+in the inner table. Derby needs to scan the inner table or index only once
+to create the hash table. The âDERBY-PROPERTIES parameter must immediately
+follow the inner table. Valid values include HASH and NESTEDLOOP.<p>The joinStrategy
+property can be used only within a TableExpression, but it must be used in
+conjunction with the joinOrder property. Do not let the optimizer choose the
+join order.</p></dd>
+</dlentry></dl></p>
+<p>The following examples illustrate the use of the â DERBY-PROPERTIES clause:<dl>
+<dlentry>
+<dt>constraint</dt>
+<dd><codeblock><b>CREATE TABLE t1 (c1 int, c2 int, c3 int, CONSTRAINT cons1 PRIMARY KEY
+(c1, c2))
+INSERT INTO t1 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4)
+SELECT * FROM t1 --DERBY-PROPERTIES constraint=cons1
+FOR UPDATE</b></codeblock></dd>
+</dlentry><dlentry>
+<dt>index</dt>
+<dd><codeblock><b>CREATE TABLE t1 (c1 int, c2 int, c3 int, CONSTRAINT cons1 PRIMARY KEY
+(c1, c2))
+INSERT INTO t1 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4)
+CREATE INDEX t1_c1 ON t1(c1)
+SELECT * FROM t1 --DERBY-PROPERTIES index=t1_c1
+WHERE c1=1</b></codeblock></dd>
+</dlentry><dlentry>
+<dt>joinOrder</dt>
+<dd><codeblock><b>CREATE TABLE t1 (c1 int, c2 int, c3 int, CONSTRAINT cons1 PRIMARY KEY
+(c1, c2));
+CREATE TABLE t2 (c1 int not null, c2 int not null, c3 int, CONSTRAINT
+cons2 UNIQUE(c1, c2))
+INSERT INTO t1 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4)
+INSERT INTO t2 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4)
+SELECT * FROM --DERBY-PROPERTIES joinOrder=FIXED
+t1, t2
+WHERE t1.c1=t2.c1</b></codeblock></dd>
+</dlentry><dlentry>
+<dt>joinStrategy</dt>
+<dd><codeblock><b>CREATE TABLE t1 (c1 int, c2 int, c3 int, CONSTRAINT cons1 PRIMARY KEY
+(c1, c2));
+CREATE TABLE t2 (c1 int not null, c2 int not null, c3 int, CONSTRAINT
+cons2 UNIQUE(c1, c2))
+INSERT INTO t1 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4)
+INSERT INTO t2 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4)
+SELECT * FROM t1 a, t1 b --DERBY-PROPERTIES joinStrategy=nestedloop
+WHERE a.c1=b.c1</b></codeblock></dd>
+</dlentry></dl></p>
+</conbody>
+</concept>
Modified: db/derby/docs/trunk/src/tuning/tuningderby.ditamap
URL: http://svn.apache.org/viewcvs/db/derby/docs/trunk/src/tuning/tuningderby.ditamap?rev=378759&r1=378758&r2=378759&view=diff
==============================================================================
--- db/derby/docs/trunk/src/tuning/tuningderby.ditamap (original)
+++ db/derby/docs/trunk/src/tuning/tuningderby.ditamap Sat Feb 18 11:14:48 2006
@@ -1,4 +1,5 @@
<?xml version="1.0" encoding="utf-8"?>
+
<!DOCTYPE map PUBLIC "-//IBM//DTD DITA Map//EN"
"../dtd/map.dtd">
<!--
@@ -135,6 +136,7 @@
<topicref href="ctundepth853133.dita" navtitle="Statement execution plan">
</topicref>
<topicref href="ctundepth853228.dita" navtitle="Optimizer estimates"></topicref>
+<topicref href="ctundepthoptover.dita" navtitle="Optimizer overrides"></topicref>
</topicref>
</topicref>
</topicref>
@@ -204,6 +206,8 @@
</topicref>
<topicref href="ctunoptimz22111.dita" navtitle="The MIN() and MAX() optimizations">
</topicref>
+</topicref>
+<topicref href="ctunoptimzoverride.dita" navtitle="Overriding the default optimizer behavior">
</topicref>
</topicref>
<topicref collection-type="family" href="ctunstats18908.dita" navtitle="Selectivity and cardinality statistics">