on 2018 Feb 22 3:53 PM
I turned a not too complex query using a WINDOW clause with two windows into a view, basically something like:
create view dbo.MyView as select pk, fk_1, fk_2, col3, col4, ... isnull(first_value(dat_1), dat_1) over wnd_prev as datStart, isnull(last_value (dat_2), dat_2) over wnd_next as datEnd from MyTable window wnd_prev as (partition by fk_1, fk_2 order by pk rows between 1 preceding and 1 preceding), wnd_next as (partition by fk_1, fk_2 order by pk rows between 1 following and unbounded following) order by fk_1, fk_2, pk;
Both windows partition by two FOREIGN KEY columns (fk_1 and fk_2) of the underlying table, and therefore there are automatically generated indexes on both columns. Both windows use the same partition clause and order, but for different row ranges.
This view is generally used with a WHERE clause specifying a particular value for fk_1, such as
select * from MyView where fk_1 = 12345;
This will typically return a few rows from a table with some million rows, so the FK values are certainly selective enough to allow an index scan.
While the basic query with an added WHERE clause does use the according index to select the according rows, I noticed that the WHERE clause on the view does not push down the predicate into the WINDOW calculations. Instead, two table scans appear, making the view very inefficient. I'm running 16.0.0.2546 here.
My question: Is there a general restriction for views that columns specified in the WHERE clause used with a view are not pushed down into the view's query's WINDOW definition?
I'm asking as there are some limitations of view predicate push-down documented in this great white paper by Ani and also in this FAQ, but they do not talk about WINDOW definitions.
Aside: My workaround is to use a stored procedure instead a view, where the FK values are supplied as optional procedure parameters. That allows the desired index scan, and because SQL Anywhere allows stored procedures to be used in the FROM clause, it is easy to substitute the ineffective view with the very efficient procedure call - thanks for that architecture:)
Request clarification before answering.
User | Count |
---|---|
52 | |
8 | |
5 | |
5 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.