db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From j..@apache.org
Subject svn commit: r378759 - in /db/derby/docs/trunk/src/tuning: ctundepthoptover.dita ctunoptimzoverride.dita tuningderby.ditamap
Date Sat, 18 Feb 2006 19:14:50 GMT
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">



Mime
View raw message