Query about Materialisation of queries based on views

General

Query about Materialisation of queries based on views

Hi,

Can anyone suggest best approach for materialising/physically storing results of a complex query in Teradata ,where source is view and not table .

Any help is appreciated.

Thanks in Advance ,

Sanket

3 REPLIES
Senior Apprentice

Re: Query about Materialisation of queries based on views

Hi Sanket,

CREATE TABLE tab AS (your query) WITH DATA PRIMARY INDEX (...)

Enthusiast

Re: Query about Materialisation of queries based on views

Perchance,if you are okay, you can share the reasons why you resort to physical storing. It looks like you have joins of complex views in semantic and you want to store that way,and it is one-off and no more change(s) adn you opt  physical storage. The reason I ask , is that if so, maybe you can tweak in the model, if possible. It is just my opinion.

Re: Query about Materialisation of queries based on views

Thanks Dieter !,

Raja,

Thank you! ,the issue we are facing is multilayer views(one view dependant on other and so on) on which complex queries are being run, this model was forklifted and migrated from Oracle to TD .these complex queries were easily materialised in Oracle using MVs.We are finding it difficult to materialise in TD with greater no of views and tables.

About tweaking model , I agree with your opinion, it does not seem functionally feasible as of now.

Thanks,

Sanket