Issue when Creating a AJI

Database

Issue when Creating a AJI

Hello All -

In my scenario we are thinking of creating multi-level views on top of some 10 - 15 tables altogether for business to query the views as needed. The moment we get to 2nd level our performance has degraded considerably, making us think if this is the right approach and if we have any alternatives, we need 1 more level to go. ANy thoughts on this is greatly appreciated.

Now, After creating view 1 [join of 8 - 10 tables] and view 2 [join of 5-6 tables], i'm trying to create a AJI on top of these two views, i hope it works well but i get this error, 

CREATE JOIN INDEX ji_ABC AS

SELECT <<SOME 600 COLUMNS>> FROM VIEW1

UNION

SELECT <<SOME 600 COLUMNS>> FROM VIEW2 b WHERE <<CONDITION>>

PRIMARY INDEX ( COL1, COL2, COL3 );

The SELECT Query Works Well.

Error: Syntax Error: expected something between the word VIEW1 and UNION

Can someone please check.

Tags (1)
1 REPLY
Teradata Employee

Re: Issue when Creating a AJI

Hello, 

You might want to build two separate Join Indices, one for View1 and another for View2.   And not for views, but for the SELECTs underneath them.   

As for the performance degradation - see EXPLAINs before and after introducing a new view level...

Regards,

Vlad.