We have 8 Tables in 3rd normal form conatining demographic information about an organization. We want to join this data to data sets containing transactional data generated by the organizaiton.
- for each of the 8 Entities containing demographic information, it is equally likely logically that we would need to join to the table based on an internally generated orgaization identifier or by an alteranate key to to the data in that table.
- The demographic tables have between 6 million and 100 million rows in them.
- Frequently we know that we will need to join 3- 6 of these tables to the transactional data to retireve the data the users want (and the users are pulling data for online analytic tasks)
In a traditional RDBMS such as Oracle, we would definitely be building the alternate index to each table.
My question is: are these table sizes so small that Teradata is unlikely to have better performance if the alternate indexes are created.
If there is a document or white paper that gives guidance for this kind of modeling / architecture question let me know as well