Multiple initialization and iterative fullselects

 

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.

For example, the following query allows for final destinations accessible from Chicago by both flight and train travel..

WITH destinations (departure, arrival, connects, cost ) AS
(
   SELECT f.departure, f.arrival, 0 , ticket 
		FROM flights f 
		WHERE f.departure='Chicago' 
   UNION ALL
   SELECT t.departure, t.arrival, 0 , ticket 
		FROM trains t 
		WHERE t.departure='Chicago' 
   UNION ALL 
   SELECT
		r.departure,b.arrival, r.connects + 1 ,
		r.cost + b.ticket 
		FROM destinations r, flights b 
		WHERE r.arrival=b.departure 
   UNION ALL
		SELECT
			r.departure,b.arrival, r.connects+1 ,
			r.cost+b.ticket 
			FROM destinations r, trains b 
			WHERE r.arrival=b.departure) 


SELECT departure, arrival, connects,cost 
			FROM destinations;

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.

 

Parent topic:

Recursive query optimization