We are trying to build reports in crystal, where the underlying Teradata object is a view.
The view inturn has 5 to 6 tables and overall record count of the view is around 1 Billion.
The tables are created with proper indices and partitions. And requried stats are in place .
We have left outer join and so creating AJIs is not helping us.
Tried using Union ALL clause in the views hoping that AJI's would be used but did not help.
The overall report refresh time is huge and the users are not happy with that
Any suggestions would be of great use.
It's next to impossible to give suggestions with no more information than this. You have to look at how the reports filter the rows they need and understand why the database doesn't / can't apply that filtering early in the query processing. Sometimes the answer is not to build reports on a single view but instead to have the report queries include some joins & aggregations.
Assuming join elimination is possible becuase one or more tables are not filtered on or participating in the SELECT list, is soft RI a possible improvement?