Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
Optimizer does not use index on view |
Mon, Oct 7 2013 9:20 AM | Permanent Link |
Gruetzmacher | 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 |
Tue, Oct 8 2013 2:13 AM | Permanent Link |
Barry | 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 |
Tue, Oct 8 2013 3:25 PM | Permanent Link |
Gruetzmacher | 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 ... |
Tue, Oct 8 2013 5:39 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |