Thanks for the proposal,but all the joins are include the big one....
The only solution ,for my mind,is to split the big one to at least 10 small ones and then do a union all.
Furthemore,I am thinking to change my PI to a column which is joined to other tables....
Thanks one more time.
I have a question on similar lines. I am trying to understand from Architecture presepctive how Teradata will behave for the below scenario. Any help would be appreciated. Especially expecting some adivce from @dnoeth
We have a Fact table (Sales Transaction) having approx 2years of data with 900 million records. The Sales data granularity is at, Region,DC,Client,Product,Vendor, Week, Day and the PI is created on the same key combination keys. We have also partitioned table on Wk id since most of data reporting is at week level.
Direct retrival from this table is very fast, no issues. Issue happens when user try to report data from tool joining with Dimension. For e.g. There is a Report that uses Sls Fact , joining with 4 dimension Region, Client, Vendor and Product and tries to get aggregated result at Week level with some filter conditions on Region, Client and VenDor type etc.
All the tables have stats on PI, join columns, where clause columns, partition collected. When we check explain plan what happens is TeraData distributes Region, Client, Vendor, product on all AMPS. Then it selects Sales data (700 Million, date selection is on 2 years) into a spool, joins that with Spool of Client. It pulls one more Sales data spool and joins with Vendor, Again it pulls one more Sales data spool and joins with Product and keeps going. Then it tries to combine all the 700 millions Sales data spool to give final output. This finally doesnt come out with any data or returns after 45 min.
Why does teradata takes the fact into spool for multiple times though the indivual dimension keys are part of Primary index and straining performance. Is there a way we can avoid this TD behaviour and make it pull Fact only once in spool and use all DIM to join the same spool.
Thanks in Advance.