SEO

Hi,

I heard somthing about materialized views for oracle and sql-Server. I
think, this technique is very interesting, especially when you have to join
certain "less-change"-tables very often.

Is there any comparable technique for Informix (IDS 9.4)?

Thanks in advance!

Bye
Markus

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.

Art S. Kagel

> Hi,
>
> I heard somthing about materialized views for oracle and sql-Server. I
> think, this technique is very interesting, especially when you have to join
> certain "less-change"-tables very often.
>
> Is there any comparable technique for Informix (IDS 9.4)?
>
> Thanks in advance!
>
> Bye
> Markus

"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 avoid the cost of joining and aggrgrating big tables. That's why
MV is created as a point-in-time snapshot and the queries use it
automatically when needed.
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.

I think there are a couple of issues here.
One is whether the MQT(Materialized Query Table)/Matview/Indexed view is
refreshed occasionally or immediately.
Another question is whether the MQT is routed to transparently to the
user or whether the MQT is picked by the user explicitly.

Triggers are viable where the user explictly chooses the MQT and the MQT
is refreshed immediately (i.e. it's up to date at all times).
Explicit reference of the MQT and no immediate refresh is essentially a
normal sidetable usage.
Things are getting interesting when the user is not aware of the
existence of the MQT and, further more, the MQT is not an obvious
syntactic subset of the query.
At this point the DBA can trade disk space and update cost for query
speed without the applications knowledge.

In DB2 the technology used to maintain MQTs is identical to the one used
to execute triggers, but sinec teh query compiler knows much better what
to do for MQT maintenance than it does of an arbitrary coded trigger
dedicated maintenace is a lot faster than explicit maintenance through a
trigger.
I have of course no idea whether the same would hold true for IDS.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

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

Just so everyone is aware...

MQT (Materialized query table) is the 8.x evolution of the 7.x AST
(Automatic Summary Table). The MQT is roughly equivalent to the Oracle
Materialized View.

The key thing about the MQT is the routing aspect. That means that the
query is matched against any MQTs based on the base tables and the query
will be dynamically routed to the MQT if it appears that it will reduce the
query cost. By using a trigger based approch to maintain a seperate table,
there is no virtualization of the query which means that you must make code
modifications to take advantage of it.

The MQT can be static, manually refreshed, or continuously refreshed as
updates are made against the base table.


"Serge Rielau" <srielau@ca.eye-be-em.com> wrote in message
news:c8leje$ih8$1@hanover.torolab.ibm.com...
> I think there are a couple of issues here.
> One is whether the MQT(Materialized Query Table)/Matview/Indexed view is
> refreshed occasionally or immediately.
> Another question is whether the MQT is routed to transparently to the
> user or whether the MQT is picked by the user explicitly.
>
> Triggers are viable where the user explictly chooses the MQT and the MQT
> is refreshed immediately (i.e. it's up to date at all times).
> Explicit reference of the MQT and no immediate refresh is essentially a
> normal sidetable usage.
> Things are getting interesting when the user is not aware of the
> existence of the MQT and, further more, the MQT is not an obvious
> syntactic subset of the query.
> At this point the DBA can trade disk space and update cost for query
> speed without the applications knowledge.
>
> In DB2 the technology used to maintain MQTs is identical to the one used
> to execute triggers, but sinec teh query compiler knows much better what
> to do for MQT maintenance than it does of an arbitrary coded trigger
> dedicated maintenace is a lot faster than explicit maintenance through a
> trigger.
> I have of course no idea whether the same would hold true for IDS.
>
> --
> Serge Rielau
> DB2 SQL Compiler Development
> IBM Toronto Lab