INDEX on a table where I'm only filtering on a row_number()

Question

I've got the following table (scaled down to a MWE): Fiddle here.

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);

I need the average of the last 26 entries by location, so I've got this:

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;

My problem is that it appears this is always going to be a full table scan of weekly. Is there any index or strategy I could do to improve this, since I can only filter it by the row_number() result?

Answer 1

If we can declare location, yearnum, and weeknum as NOT NULL, you can create a composite index on location, yearnum, weeknum that can be used instead of doing the full table scan. Here is a sqlfiddle showing that approach. Note that I applied a hint to force the index to be used since there isn't enough data in the example for a full scan of the index to be less expensive than a full scan of the table. The index full scan path would probably be more efficient in the scaled up example assuming each location has data for many more than the past 26 weeks. That's probably not going to be an order of magnitude improvement, though, unless the table is substantially larger than the index.