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.
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
Thank you :
- kareem