I turned a not too complex query using a WINDOW clause with two windows into a view, basically something like:

create view dbo.MyView
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 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:)

asked 22 Feb, 15:53

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

edited 23 Feb, 03:01

Any hint is still welcome... :)

(14 Mar, 15:44) Volker Barth
Replies hidden

Just to add a similar case:

The same seems to apply when a view does use a group by: Although the group by expression is based on the base table's primary key, so there is an according index the optimizer could use, the view will generally do a full table scan. IMHO, that is a contradiction to the paragraph "Predicate push-down in UNION or GROUPed views and derived tables" of the mentioned whitepaper.

Again, my workaround is to create a stored procedure that has the PK value as input parameter, and use that procedure with a CROSS APPLY to supply the according PK value from the base table...

(20 Jul, 09:20) Volker Barth
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 22 Feb, 15:53

question was seen: 639 times

last updated: 20 Jul, 10:37