77 lines
5.4 KiB
HTML
77 lines
5.4 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="Specifying CYCLE considerations" />
|
||
|
|
<meta name="abstract" content="Recognizing that data in the tables used in a recursive query might be cyclic in nature is important to preventing infinite loops." />
|
||
|
|
<meta name="description" content="Recognizing that data in the tables used in a recursive query might be cyclic in nature is important to preventing infinite loops." />
|
||
|
|
<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="rzajqrctecycle" />
|
||
|
|
<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>Specifying CYCLE considerations</title>
|
||
|
|
</head>
|
||
|
|
<body id="rzajqrctecycle"><a name="rzajqrctecycle"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Specifying CYCLE considerations</h1>
|
||
|
|
<div><p>Recognizing that data in the tables used in a recursive query might
|
||
|
|
be cyclic in nature is important to preventing infinite loops.</p>
|
||
|
|
<div class="section"><p>The SQL architecture allow for the optional checking for cyclic
|
||
|
|
data and will discontinue the repeating cycles at that point. This additional
|
||
|
|
checking is done by the use of the CYCLE option. The correct join recursion
|
||
|
|
value must be specified on the CYCLE request and a cyclic indicator must be
|
||
|
|
specified. Note that the cyclic indicator may be optionally output in the
|
||
|
|
main query and can be used to help determine and correct errant cyclic data.</p>
|
||
|
|
<pre><strong>WITH</strong> destinations (departure, arrival, connects, cost , itinerary) <strong>AS</strong>
|
||
|
|
(
|
||
|
|
<strong>SELECT</strong> f.departure, f.arrival, 1 , ticket, CAST(f.departure||f.arrival <strong>AS</strong> VARCHAR(2000))
|
||
|
|
<strong>FROM</strong> flights f
|
||
|
|
<strong>WHERE</strong> f.departure='New York'
|
||
|
|
<strong>UNION ALL</strong>
|
||
|
|
<strong>SELECT</strong> r.departure,b.arrival, r.connects+1 ,
|
||
|
|
r.cost+b.ticket, cast(r.itinerary||b.arrival <strong>AS</strong> varchar(2000))
|
||
|
|
<strong>FROM</strong> destinations r, flights b
|
||
|
|
<strong>WHERE</strong> r.arrival = b.departure)
|
||
|
|
<strong>CYCLE</strong> arrival <strong>SET</strong> cyclic <strong>TO</strong> '1' <strong>DEFAULT</strong> '0' <strong>USING</strong> Cycle_Path
|
||
|
|
|
||
|
|
<strong>SELECT</strong> departure, arrival, itinerary, cyclic
|
||
|
|
<strong>FROM</strong> destinations</pre>
|
||
|
|
<p>When a cycle is determined to be repeating, the output of that
|
||
|
|
cyclic sequence of rows is stopped. To check for a 'repeated' value however,
|
||
|
|
the query engine needs to represent the entire ancestry of the join values
|
||
|
|
leading to up to the current row in order to look for the repeating join value.
|
||
|
|
This ancestral history is information that is appended to with each recursive
|
||
|
|
cycle and put in a field on the queue entry. To implement this, the query
|
||
|
|
engine uses a compressed representation of the recursion values on the ancestry
|
||
|
|
chain so that the query engine can do a fixed length, quicker scan through
|
||
|
|
the accumulating ancestry to determine if the value has been seen before.
|
||
|
|
This compressed representation is determined by the use of a distinct node
|
||
|
|
in the query tree.</p>
|
||
|
|
<p>Do not use the CYCLE option unless you know your
|
||
|
|
data is cyclic or you want to use it specifically to help find the cycles
|
||
|
|
for correction or verification purposes. There is additional CPU and memory
|
||
|
|
overhead to manage and check for repeating cycles before a given row is materialized.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><img src="rzajq565.gif" alt="Example with CYCLE option" /></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>
|