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 jo...@apache.org on 2008/05/07 15:50:19 UTC
svn commit: r654105 - in /db/derby/site/trunk: build/site/faq.html
src/documentation/content/xdocs/faq.xml
Author: johnemb
Date: Wed May 7 06:50:18 2008
New Revision: 654105
URL: http://svn.apache.org/viewvc?rev=654105&view=rev
Log:
DERBY-232: FAQ: Updated answer for LIMIT question. Added examples
for Statement.setMaxRows() as well as ROW_NUMBER (new in 10.4),
and a couple of links.
Modified:
db/derby/site/trunk/build/site/faq.html
db/derby/site/trunk/src/documentation/content/xdocs/faq.xml
Modified: db/derby/site/trunk/build/site/faq.html
URL: http://svn.apache.org/viewvc/db/derby/site/trunk/build/site/faq.html?rev=654105&r1=654104&r2=654105&view=diff
==============================================================================
--- db/derby/site/trunk/build/site/faq.html (original)
+++ db/derby/site/trunk/build/site/faq.html Wed May 7 06:50:18 2008
@@ -775,10 +775,83 @@
<div style="margin-left: 15px">
<p>
Derby supports limiting the number of rows returned by a query through
- <a class="external" href="http://mail-archives.apache.org/mod_mbox/db-derby-dev/200411.mbox/%3c41A95632.3010301@debrunners.com%3e">JDBC</a>.
+ <a class="external" href="http://mail-archives.apache.org/mod_mbox/db-derby-dev/200411.mbox/%3c41A95632.3010301@debrunners.com%3e">JDBC</a>. For example, to fetch the first 5 rows of a large table:
+ </p>
+<p>
+
+<span class="codefrag">Statement stmt = con.createStatement();</span>
+<br>
+
+<span class="codefrag">stmt.setMaxRows(5);</span>
+<br>
+
+<span class="codefrag">ResultSet rs = stmt.executeQuery("SELECT * FROM myLargeTable");</span>
+<br>
+
+</p>
+<p>
+ Some related tuning tips are available in
+ <a class="external" href="http://www.onjava.com/pub/a/onjava/2007/01/31/tuning-derby.html?page=1">this
+ external article</a>.
+ </p>
+<p>
+ Starting with the 10.4.1.3 release Derby also supports limiting the number of
+ rows using the <span class="codefrag">ROW_NUMBER</span> function.
+ </p>
+<p>
+ For example, to fetch the first 5
+ rows of a large table:
+ </p>
+<p>
+
+<span class="codefrag">SELECT * FROM (</span>
+<br>
+ <span class="codefrag">SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.*</span>
+<br>
+ <span class="codefrag">FROM myLargeTable</span>
+<br>
+
+<span class="codefrag">) AS tmp</span>
+<br>
+
+<span class="codefrag">WHERE rownum <= 5;</span>
+<br>
+
+</p>
+<p>
+ The <span class="codefrag">ROW_NUMBER</span> function can also be used to select a
+ limited number of rows starting with an offset, for example:
+ </p>
+<p>
+
+<span class="codefrag">SELECT * FROM (</span>
+<br>
+ <span class="codefrag">SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.*</span>
+<br>
+ <span class="codefrag">FROM myLargeTable</span>
+<br>
+
+<span class="codefrag">) AS tmp</span>
+<br>
+
+<span class="codefrag">WHERE rownum > 200000 AND rownum <= 200005;</span>
+<br>
+
+</p>
+<p>
+ For more information, refer to the ROW_NUMBER built-in function in
+ the Derby Reference Manual (available from the
+ <a href="manuals/index.html">Documentation page</a>). Development
+ notes are available on the
+ <a class="external" href="http://wiki.apache.org/db-derby/OLAPRowNumber">OLAPRowNumber
+ wiki page</a>.
+ </p>
+<p>
+ The <span class="codefrag">LIMIT</span> keyword is not defined in the SQL standard,
+ and is currently not supported.
</p>
</div>
-<a name="N1018E"></a><a name="schema_exist"></a>
+<a name="N101F4"></a><a name="schema_exist"></a>
<h4 class="faq">5.3.
Why do I get the error 'schema does not exist'?
</h4>
@@ -810,7 +883,7 @@
to create in a particular schema and no explicit CREATE SCHEMA was issued.
</p>
</div>
-<a name="N1019F"></a><a name="debug_lock_timeout"></a>
+<a name="N10205"></a><a name="debug_lock_timeout"></a>
<h4 class="faq">5.4.
I get a lock timeout error. How do I debug this?
</h4>
@@ -839,7 +912,7 @@
to narrow down which statements may be holding the locks.
</p>
</div>
-<a name="N101B9"></a><a name="identity"></a>
+<a name="N1021F"></a><a name="identity"></a>
<h4 class="faq">5.5.
Can Derby generate unique identifiers like sequences?
</h4>
@@ -856,9 +929,9 @@
function returns the most recently assigned number.
</p>
</div>
-<a name="N101C9"></a><a name="users_client"></a>
+<a name="N1022F"></a><a name="users_client"></a>
<h3 class="boxed">6. Using Derby: Client Programming</h3>
-<a name="N101CD"></a><a name="clientserv"></a>
+<a name="N10233"></a><a name="clientserv"></a>
<h4 class="faq">6.1.
Can you use Derby for client-server applications?
</h4>
@@ -879,7 +952,7 @@
<a class="external" href="http://sequoia.continuent.org/HomePage">Sequoia</a>.
</p>
</div>
-<a name="N101E9"></a><a name="netclient"></a>
+<a name="N1024F"></a><a name="netclient"></a>
<h4 class="faq">6.2.
Does Derby include a client-server ("network") jdbc driver?
</h4>
@@ -895,7 +968,7 @@
and it became available starting with Derby 10.1.
</p>
</div>
-<a name="N101F5"></a><a name="db2_jcc_errors"></a>
+<a name="N1025B"></a><a name="db2_jcc_errors"></a>
<h4 class="faq">6.3.
How can I get the message text for an error using the DB2 JDBC
Universal Driver?
@@ -913,7 +986,7 @@
mail list topic.
</p>
</div>
-<a name="N10204"></a><a name="derby_xa"></a>
+<a name="N1026A"></a><a name="derby_xa"></a>
<h4 class="faq">6.4.
Can you execute a query that spans two Derby databases
across different JVMs?
@@ -929,7 +1002,7 @@
<a href="http://db.apache.org/derby/docs/10.2/adminguide/cadminapps811478.html">Admin and Server Guide</a>.
</p>
</div>
-<a name="N10210"></a><a name="derby_faster"></a>
+<a name="N10276"></a><a name="derby_faster"></a>
<h4 class="faq">6.5.
Are there any tips to make Derby go faster?
</h4>
@@ -964,7 +1037,7 @@
</ol>
</div>
-<a name="N10229"></a><a name="jdbc_lob"></a>
+<a name="N1028F"></a><a name="jdbc_lob"></a>
<h4 class="faq">6.6.
Where is an example that shows how to insert a CLOB?
</h4>
@@ -982,7 +1055,7 @@
provides another example.
</p>
</div>
-<a name="N1023C"></a><a name="jdbc4"></a>
+<a name="N102A2"></a><a name="jdbc4"></a>
<h4 class="faq">6.7.
Does Derby support JDBC 4.0?
</h4>
Modified: db/derby/site/trunk/src/documentation/content/xdocs/faq.xml
URL: http://svn.apache.org/viewvc/db/derby/site/trunk/src/documentation/content/xdocs/faq.xml?rev=654105&r1=654104&r2=654105&view=diff
==============================================================================
--- db/derby/site/trunk/src/documentation/content/xdocs/faq.xml (original)
+++ db/derby/site/trunk/src/documentation/content/xdocs/faq.xml Wed May 7 06:50:18 2008
@@ -412,7 +412,60 @@
<answer>
<p>
Derby supports limiting the number of rows returned by a query through
- <a href="http://mail-archives.apache.org/mod_mbox/db-derby-dev/200411.mbox/%3c41A95632.3010301@debrunners.com%3e">JDBC</a>.
+ <a href="http://mail-archives.apache.org/mod_mbox/db-derby-dev/200411.mbox/%3c41A95632.3010301@debrunners.com%3e">JDBC</a>. For example, to fetch the first 5 rows of a large table:
+ </p>
+ <!-- Using <code> and <br/> tags etc. as a workaround for including
+ multi-line formatted code examples, since Forrest's XML
+ validation fails when using other HTML tags (such as <pre>) in
+ this DTD element.
+ -->
+ <p>
+ <code>Statement stmt = con.createStatement();</code><br/>
+ <code>stmt.setMaxRows(5);</code><br/>
+ <code>ResultSet rs = stmt.executeQuery("SELECT * FROM myLargeTable");</code><br/>
+ </p>
+ <p>
+ Some related tuning tips are available in
+ <a href="http://www.onjava.com/pub/a/onjava/2007/01/31/tuning-derby.html?page=1">this
+ external article</a>.
+ </p>
+ <p>
+ Starting with the 10.4.1.3 release Derby also supports limiting the number of
+ rows using the <code>ROW_NUMBER</code> function.
+ </p>
+ <p>
+ For example, to fetch the first 5
+ rows of a large table:
+ </p>
+ <p>
+ <code>SELECT * FROM (</code><br/>
+ <code>SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.*</code><br/>
+ <code>FROM myLargeTable</code><br/>
+ <code>) AS tmp</code><br/>
+ <code>WHERE rownum <= 5;</code><br/>
+ </p>
+ <p>
+ The <code>ROW_NUMBER</code> function can also be used to select a
+ limited number of rows starting with an offset, for example:
+ </p>
+ <p>
+ <code>SELECT * FROM (</code><br/>
+ <code>SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.*</code><br/>
+ <code>FROM myLargeTable</code><br/>
+ <code>) AS tmp</code><br/>
+ <code>WHERE rownum > 200000 AND rownum <= 200005;</code><br/>
+ </p>
+ <p>
+ For more information, refer to the ROW_NUMBER built-in function in
+ the Derby Reference Manual (available from the
+ <a href="manuals/index.html">Documentation page</a>). Development
+ notes are available on the
+ <a href="http://wiki.apache.org/db-derby/OLAPRowNumber">OLAPRowNumber
+ wiki page</a>.
+ </p>
+ <p>
+ The <code>LIMIT</code> keyword is not defined in the SQL standard,
+ and is currently not supported.
</p>
</answer>
</faq>