hello,
i have a simple view:
CREATE VIEW "V_ALL_SAMPLES" AS
SELECT  "ID", "LASTMODIFICATION" FROM T_SAMPLES
UNION ALL
SELECT  "ID", "LASTMODIFICATION" FROM T_SAMPLES_VERSION

both used tables have an index on "LASTMODIFICATION". but when i use the view like
select * from v_all_samples where lastmodification > DATE'2013-09-25' no index is used but a row scan is performed.
is there a chance to enforce the index-usage? otherwise views are a bit useless in terms of performance to me ...
the workaround always to select via a union all is a lot of extra work ...

thank you

Gruetzmacher,

I'm guessing the optimizer doesn't know which table to apply the  "lastmodification > DATE'2013-09-25'" to. I don't think it is smart enough to realize the Where clause can be applied to all of the tables in the Union.

For now the only thing I can think of is to use a query like:

SELECT  "ID", "LASTMODIFICATION" FROM T_SAMPLES  where lastmodification > DATE '2013-09-25'
UNION ALL
SELECT  "ID", "LASTMODIFICATION" FROM T_SAMPLES_VERSION where lastmodification > DATE '2013-09-25'

and not use a view at all.

Barry

thank you barry,
yes - this is excactly the 'workaround' i use now ... sadly this makes views a bit unusable. however for the primary key it works ...

Hendrik,

<< both used tables have an index on "LASTMODIFICATION". but when i use the
view like select * from v_all_samples where lastmodification >
DATE'2013-09-25' no index is used but a row scan is performed. is there a
chance to enforce the index-usage? otherwise views are a bit useless in
terms of performance to me ...the workaround always to select via a union
all is a lot of extra work ... >>

The issue is that EDB doesn't currently allow you to define indexes on
views, and the view definition that you're using generates an insensitive
result set.  The workaround for now would be to do the following:

CREATE VIEW "V_ALL_SAMPLES" AS
SELECT  "ID", "LASTMODIFICATION" FROM T_SAMPLES
UNION ALL
SELECT  "ID", "LASTMODIFICATION" FROM T_SAMPLES_VERSION
ORDER BY "LASTMODIFICATION"

The ORDER BY should create the index that you're looking for.

Tim Young
Elevate Software
www.elevatesoft.com