Thanks in advance for your help :)
I'm writing to ask about how to provide fast queries against very wide and also deep tables.
The 4 tables are large, ranging from 40cols/300m rows to 500cols/30m rows.
They are keyed on Customer ID. These tables are used in real time by the call center software.
The Analysts also want to use these tables, but they query on a variety of fields other than Customer ID. The analysts and various non-real-time apps share a copy of these large tables in the prod-copy environment.
We thought of indexing the various columns they query on, but when prod-copy is built each day, building these indexes would take forever. Also, there is a strong preference that the tables in prod-copy stay the same as production.
A solution has been proposed - As the large tables are truncated & loaded daily by the ETL software, to have the ETL software also load "Index Tables". Each index-table would contain one query-by column along with customer ID. A view would bring all these index-tables together and join them to the base table, making them invisible to the analysts. (these index tables could be IOT's, or whatever is fastest)
One drawback is that if they suddenly want to do a query on one of the other 500 columns (in a rapid way), that the ETL process needs to be changed to build an additional index-table.
(1) What is your opinion of this solution? Do you know of any other solutions?
(2) What are these tables called? I've tried to search the internet but I'm not aware of what this index-table strategy is known by.
Thank you very much,