Specifying CYCLE considerations
Recognizing that data in the tables used in a recursive query might be cyclic in nature is important to preventing infinite loops.
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.
WITH destinations (departure, arrival, connects, cost , itinerary) AS
(
SELECT f.departure, f.arrival, 1 , ticket, CAST(f.departure||f.arrival AS VARCHAR(2000))
FROM flights f
WHERE f.departure='New York'
UNION ALL
SELECT r.departure,b.arrival, r.connects+1 ,
r.cost+b.ticket, cast(r.itinerary||b.arrival AS varchar(2000))
FROM destinations r, flights b
WHERE r.arrival = b.departure)
CYCLE arrival SET cyclic TO '1' DEFAULT '0' USING Cycle_Path
SELECT departure, arrival, itinerary, cyclic
FROM destinations
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.
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.
