Re: Materialized Views

On Fri, 21 May 2004 12:49:32 -0400, rkusenet wrote: > "Art S. Kagel" <kagel@bloomberg.net> wrote > >> On Fri, 21 May 2004 07:31:19 -0400, Markus Bschorer wrote: >> >> Table? Just create a table with the joined data columns and add triggers >> to the underlying tables to maintain them live. Better than materialized >> views. > > Could you explain how this is better than MV. The whole concept of MV is to Not better, but since IDS does not have MV... > avoid the cost of joining and aggrgrating big tables. That's why MV is Hence the pre-built table. > created as a point-in-time snapshot and the queries use it automatically > when needed. Trigger to keep it in sync with the live data. Since Markus states that the tables we would use this on are fairly stable the overhead of the triggers to keep it in synch would be FAR lower than the overhead of a true VIEW. > If you are going to have a trigger, then the same performance penalty is > introduced. In that case why bother about any new table. Just let the SQL do > the work at query time. or am I missing anything. Same answer to the last two questions. The overhead of using a trigger to replicate the mods to a relatively quiet table to the 'view-table' will be minor compared to repeating the query overhead each time the data is needed from a true VIEW. If the tables are actively updated, then the added overhead of the view-table combined with the 'cost' of the data getting out-of-synch but depended on, is too high to violate the Normal Form rules in this way. But with static or low activity tables that are frequently joined, the cost savings would out weigh the additional headache. And this join-table idea is BETTER than MV because the data is live and up-to-date. Another option occurs to me. IDS 8 supports pre-join indexes which are VERY efficient for frequently joined tables. If you are using IDS 9.xx one could implement something like that using the VII or perhaps even functional index feature which would give the performance benefit of MV with NO additional overhead beyond index maintenance. Art S. Kagel