78 lines
5.0 KiB
HTML
78 lines
5.0 KiB
HTML
|
<?xml version="1.0" encoding="UTF-8"?>
|
||
|
|
<!DOCTYPE html
|
||
|
|
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
||
|
|
<html lang="en-us" xml:lang="en-us">
|
||
|
|
<head>
|
||
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
||
|
|
<meta name="security" content="public" />
|
||
|
|
<meta name="Robots" content="index,follow" />
|
||
|
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
||
|
|
<meta name="DC.Type" content="reference" />
|
||
|
|
<meta name="DC.Title" content="Multiple initialization and iterative fullselects" />
|
||
|
|
<meta name="abstract" content="The use of multiple initialization and iterative fullselects specified in the recursive query definition allows for a multitude of data sources and separate selection requirements to feed the recursion process." />
|
||
|
|
<meta name="description" content="The use of multiple initialization and iterative fullselects specified in the recursive query definition allows for a multitude of data sources and separate selection requirements to feed the recursion process." />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqrecursive.htm" />
|
||
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Format" content="XHTML" />
|
||
|
|
<meta name="DC.Identifier" content="rzajqrcteinitial" />
|
||
|
|
<meta name="DC.Language" content="en-us" />
|
||
|
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
||
|
|
<!-- US Government Users Restricted Rights -->
|
||
|
|
<!-- Use, duplication or disclosure restricted by -->
|
||
|
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ic.css" />
|
||
|
|
<title>Multiple initialization and iterative fullselects</title>
|
||
|
|
</head>
|
||
|
|
<body id="rzajqrcteinitial"><a name="rzajqrcteinitial"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Multiple initialization and iterative fullselects</h1>
|
||
|
|
<div><p>The use of multiple initialization and iterative fullselects specified
|
||
|
|
in the recursive query definition allows for a multitude of data sources and
|
||
|
|
separate selection requirements to feed the recursion process. </p>
|
||
|
|
<div class="section"><p>For example, the following query allows for final destinations
|
||
|
|
accessible from Chicago by both flight and train travel.. </p>
|
||
|
|
<pre><strong>WITH</strong> destinations (departure, arrival, connects, cost ) <strong>AS</strong>
|
||
|
|
(
|
||
|
|
<strong>SELECT</strong> f.departure, f.arrival, 0 , ticket
|
||
|
|
<strong>FROM</strong> flights f
|
||
|
|
<strong>WHERE</strong> f.departure='Chicago'
|
||
|
|
<strong>UNION ALL</strong>
|
||
|
|
<strong>SELECT</strong> t.departure, t.arrival, 0 , ticket
|
||
|
|
<strong>FROM</strong> trains t
|
||
|
|
<strong>WHERE</strong> t.departure='Chicago'
|
||
|
|
<strong>UNION ALL</strong>
|
||
|
|
<strong>SELECT</strong>
|
||
|
|
r.departure,b.arrival, r.connects + 1 ,
|
||
|
|
r.cost + b.ticket
|
||
|
|
<strong>FROM</strong> destinations r, flights b
|
||
|
|
<strong>WHERE</strong> r.arrival=b.departure
|
||
|
|
<strong>UNION ALL</strong>
|
||
|
|
<strong>SELECT</strong>
|
||
|
|
r.departure,b.arrival, r.connects+1 ,
|
||
|
|
r.cost+b.ticket
|
||
|
|
<strong>FROM</strong> destinations r, trains b
|
||
|
|
<strong>WHERE</strong> r.arrival=b.departure)
|
||
|
|
|
||
|
|
<strong>SELECT</strong> departure, arrival, connects,cost
|
||
|
|
<strong>FROM</strong> destinations;
|
||
|
|
</pre>
|
||
|
|
<p>As all rows coming out of the RCTE/View are part of the recursion
|
||
|
|
process and need to be fed back in, when there are multiple fullselects referencing
|
||
|
|
the common table expression, the query is rewritten by the optimizer to process
|
||
|
|
all non-recursive initialization fullselect first and then using a single
|
||
|
|
queue feed those same rows and all other row results equally to the remaining
|
||
|
|
iterative fullselects. No matter how you order the initialization and iterative
|
||
|
|
fullselects in the definition of the RCTE/view, the initialization fullselects
|
||
|
|
will run first and the iterative fullselects will share equal access to the
|
||
|
|
contents of the queue.</p>
|
||
|
|
<img src="rzajq564.gif" alt="Example with multiple
initialization and recursive fullselects" /></div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rzajqrecursive.htm" title="Certain applications and data are recursive by nature. Examples of such applications are a bill-of-material, reservation, trip planner or networking planning system where data in one results row has a natural relationship (call it a parent, child relationship) with data in another row or rows. Although the kinds of recursion implemented in these systems can be performed by using SQL Stored Procedures and temporary results tables, the use of a recursive query to facilitate the access of this hierarchical data can lead to a more elegant and better performing application.">Recursive query optimization</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|