Leyne, Sean wrote:

>
> Recursion??? There is no recursion.
>
> The engine does not treat the derived table like an IN (SELECT ...
>
> Rather it resolves the inner result set and then passes it to the next
> level for operation.

In fact, it depends on the generated plan. We almost never materialize
the result sets, so unless SORT MERGE is in game, the derived table is
evaluated in a nested loop. However, it benefits from a proper indexing
much and hence it's not a big deal in practice.

> As for performance, the statement:
>
> SELECT * FROM (SELECT FIRST 5 * FROM Table ORDER BY 1 DESC) ORDER BY 1
>
> Executes without any perceptible time difference, as compared to:
>
> SELECT FIRST 5 * FROM Table ORDER BY 1 DESC

Not a surprise at all :-)


Dmitry