将 INDEX 添加到 CTE

Question

我可以将 INDEX 添加到公用表表达式 (CTE) 中吗？

Answer 1

我也有过同样的要求。无法将索引添加到 CTE。然而，在 CTE select 中，在连接字段上添加 ORDER BY 子句将执行时间从 20 分钟或更长减少到 10 秒以下。

（您还需要添加 SELECT TOP 100 PERCENT 以允许在 CTE 选择中使用 ORDER BY。）

[编辑以添加下面评论中的解释引用]：
如果 CTE 中有 DISTINCT，则 TOP 100 PERCENT 不起作用。这种作弊方法始终可用：在选择中根本不需要 TOP，将 ORDER BY 语句更改为：
排序依据 [Blah] 偏移 0 行

Answer 2

不可以

。CTE 是临时的“内联”视图 - 您无法向此类构造添加索引。

如果需要索引，请使用 CTE 的 SELECT 创建常规视图，并将其设为索引视图（通过向视图添加聚集索引）。您需要遵守此处列出的一组规则：创建索引视图< /a>.

Answer 3

您无法为 CTE 建立索引，但方法是 CTE 可以利用基础索引。

WITH cte AS (
    SELECT myname, SUM(Qty) FROM t GROUP BY myname
)
SELECT *
FROM t a JOIN cte b ON a.myname=b.myname 

在上面的查询中，由于 GROUP BY，a JOIN b 无法使用 t.myname 上的索引。

另一方面，

WITH cte AS (
    SELECT myname, SUM(Qty) OVER (PARTITION BY myname) AS SumQty, 
                ROW_NUMBER() OVER (PARTITION BY myname ORDER BY myname, Qty) AS n
)
SELECT * FROM t a JOIN cte b ON a.myname=b.myname AND b.n=1 

在后一个查询中，a JOIN b 可以使用 t.myname 上的索引。

Answer 4

/* 下面的构造将我的查询时间从 2 分钟减少到 4 秒 */

WITH First_cte AS(
    SELECT Field1, Field2, etc
    FROM etc
    ORDER BY Field1, Field2 OFFSET 0 ROWS
),
Second_cte AS(
    SELECT Field1, Field2, etc
    FROM etc
    ORDER BY Field1, Field2 OFFSET 0 ROWS
)
SELECT
    First_cte.Field1, First_cte.Field2, etc
    FROM First_cte
    INNER JOIN Second_cte
        ON Second_cte.Field1 = First_cte.Field1
        AND Second_cte.Field2 = First_cte.Field2
    WHERE Second_cte.Field3 <> First_cte.Field3`

Answer 5

另一种技术是插入临时表而不是使用 CTE
然后，您可以向临时表添加索引

。通过执行此操作，我将 9 分钟的查询减少到 3 秒的查询。

有些人可能强烈反对临时表。

对于我们其他人来说，试图让事情正常运转……需要考虑一些事情。

（我确实尝试了前 100000 个... order by）我没有意识到时间减少。