Sunday, October 3, 2010

Materialised view in teradata? Is it possible?

Most of the databases uses concept of materialised views  (which stores data ). Sadly, in case of teradata  there is no such concept of materialised views.

The closest option of having materialsed view in case of teradata is by using JOIN index (or aggregate index) .  A view can be created on join INDEX  . The command used to create index is as follows

 "CREATE JOIN INDEX...(Select table options)".

When Join Index is executed, it stores data as a table in spool space, hence making the join much faster

 Please Note:
There are drawbacks on  Join Indices.
1. Optimizer would determine whether The index is beneficial. If yes then it invokes it!
2.As the join index cannot be accessed directly and a view  is created that looks like the join index . However, this approach does not guarantee that the join index will be used when view is called.

No comments:

Post a Comment