您将需要 parent_id上的索引。 CTE的递归部分将始终使用嵌套循环连接，并且不会渗透连接提示（结果添加到堆栈假脱机，并且按LIFO顺序逐行处理行）

You would need an index on parent_id. The recursive part of a CTE will always use a nested loops join and is impervious to join hints (Results are added to a stack spool and the rows are processed one by one in LIFO order)

没有索引在 parent_id 上，它将需要在嵌套循环的内侧多次扫描表。性能将随着行数成倍下降。

Without an index on parent_id it will need to scan the table multiple times on the inner side of the nested loops. Performance will degrade exponentially with number of rows.

您无需递归的查询将能够使用不同的联接类型（哈希或合并），每个联接类型仅对表进行两次扫描递归。在这种情况下，很可能是哈希联接，因为您没有避免排序的有用索引。

Your query without recursion will be able to use different join types (hash or merge) that only scan the table twice for each level of recursion. Most likely hash join in this case as you have no useful indexes that would avoid a sort.