只对row_number()进行筛选的表的索引

Question

我得到了下面的表格(缩小为MWE)：小提琴在这里。

CREATE TABLE weekly
( yearnum int,
  weeknum int,
  location varchar2(5),
  weekly_count int,
  weekly_sum int);

INSERT INTO weekly values (2012,1,'X',5,10);
INSERT INTO weekly values (2012,2,'X',6,8);
INSERT INTO weekly values (2012,3,'X',7,14);
INSERT INTO weekly values (2012,4,'X',5,12);
INSERT INTO weekly values (2012,3,'Y',1,22);
INSERT INTO weekly values (2012,4,'Y',5,100);
INSERT INTO weekly values (2012,1,'Z',100,1022);
INSERT INTO weekly values (2012,2,'Z',120,1205);
INSERT INTO weekly values (2012,3,'Z',60,700);

我需要按位置分列的前26条条目的平均值，所以我得到了以下信息：

SELECT 
  t.location,
  sum(t.weekly_sum)/sum(t.weekly_count) AS avg_26
FROM
(
  SELECT 
    w.location,
    w.weekly_sum, 
    w.weekly_count,
    row_number() over (partition BY w.location 
                        ORDER BY w.yearnum DESC,w.weeknum DESC) rn
  FROM weekly w
) t
WHERE t.rn <= 26
GROUP BY t.location;

我的问题是，它看起来，这将永远是一个完整的表扫描weekly。由于我只能通过row_number()结果对其进行筛选，所以我可以做什么索引或策略来改进它吗？

Answer 1

如果我们可以将location、yearnum和weeknum声明为NOT NULL，则可以在location, yearnum, weeknum上创建一个复合索引，该索引可以用于代替进行全表扫描。下面是一个显示这种方法的木琴。请注意，我应用了一个提示来强制使用索引，因为示例中没有足够的数据使对索引的全面扫描比对表的完全扫描更便宜。假设每个位置都有超过过去26周的数据，索引全扫描路径在放大示例中可能会更有效。不过，这可能不会是一个数量级的改善，除非该表比指数大得多。