Peter Hunsberger <peter.hunsberger@gmail.com> wrote:
> [...]
>> or a recursive query (which I always find very hard to com-
>> prehend):
>> | WITH RECURSIVE RecCols (LeftBoundary, Value) AS
>> | (SELECT col, col FROM t WHERE (col - 1) NOT IN (SELECT col FROM t)
>> | UNION ALL SELECT p.LeftBoundary, c.col FROM RecCols AS p, t AS c WHERE c.col = p.Value + 1)
>> | SELECT LeftBoundary, MAX(Value) AS RightBoundary FROM RecCols
>> | GROUP BY LeftBoundary
>> | ORDER BY LeftBoundary;
>> Could you run both against your data set and find out which
>> one is faster for your six million rows?
> Turns out the server is v 8.3, looks like I need to get them to
> upgrade it so I get recursive and windowing :-(. If this happens any
> time soon I'll let you know the results.
> Many thanks.
After some tests with a data set of 7983 rows (and 1638 ran-
ges): Don't! :-) The recursive solution seems to be more
than double as slow as the iterative. I'll take it to -per-
formance.
Tim