Views or Intermediate objects that can hold data

Database

Views or Intermediate objects that can hold data

hi,

I would like to know if we could select data from tables and store them in views. The views should have read, write access. THis is similar to materialized views in Oracle. I have looked into the join index concept, but iam not convinced with the same. Can any one help is suggesting a different methid ?

Thanks,
teradata user
6 REPLIES

Re: Views or Intermediate objects that can hold data

No, you can't store data in a view in Teradata. There are join indexes, which do allow you to do simple joins between between tables, but those are much more limited than a view and probably not what you're looking for.

If you want to store data from a view, you'll probably want to create a table and insert into the table from the view. This can be done pretty easily with a "CREATE TABLE AS (SELECT * FROM yourview)..." statement. I would suggest that if these are being created/destroyed often, that you keep these in a separate database from your normal tables.

Teradata does have the ability to have temporary tables (VOLATILE and GLOBAL TEMPORARY) that allow you to store data in them. However, these tables are specific to the session. So, if you wanted to select the data from your view into one of these tables, you could do that, but only that particular session would be able to see the table.

Hope this helps.

Re: Views or Intermediate objects that can hold data

Can you explain the business reason for an attempt to store data in views? If it is store intermediate results then as Barry suggested you can go for temporary tables. If it is for security purposes then may be the view as such will help that requirement.

Re: Views or Intermediate objects that can hold data

Hi,

Thanks for response. The requirement is to develop reports in reporting tool. This tool will access data from teradata. We are resistant in accessing tables of teradata for execution of each report. Hence concluded to have a intermediate layer to store data from base tables. This would be same concept of materialized views in ORACLE.

Kindly throw light on same.

Thanks,
Teradata user
N/A

Re: Views or Intermediate objects that can hold data

JOIN INDEX in Teradata give you something equivalent to materialized views in Oracle...

Re: Views or Intermediate objects that can hold data

JOIN Indexes helps is specifying predefined joins . I would like to store data in object. Any other suggestions ?

Re: Views or Intermediate objects that can hold data

Join Index + view combinations is TD's way of implementing a materialized view concept of Oracle. And data is "stored" in the index (object). Any changes to base table are immediately visible to JI.

While it's generally used for predefined joins, it's also at times built with a broader range of queries in mind (what we call as covered queries), or just to project a subset of columns from a base table (Single table JI). Or just some record of a join (Spa rse JI) and so on ....

Probably if you can list out a scenario, some one could suggest the right combo.