I have tables “T1” in the database that are broken down by month of the form (table_082020, table_092020, table_102020). Each contains several million records.
+----+----------+-------+ | id | date | value | +----+----------+-------+ | 1 | 20200816 | abc | +----+----------+-------+ | 2 | 20200817 | xyz | +----+----------+-------+
+----+----------+-------+ | id | date | value | +----+----------+-------+ | 1 | 20200901 | cba | +----+----------+-------+ | 2 | 20200901 | zyx | +----+----------+-------+
There is a second table “T2” that stores a reference to the primary key of the first one and actually to the table itself only without the word “table_”.
+------------+--------+--------+--------+--------+ | rec_number | period | field1 | field2 | field3 | +------------+--------+--------+--------+--------+ | 777 | 092020 | aaa | bbb | ccc | +------------+--------+--------+--------+--------+ | 987 | 102020 | eee | fff | ggg | +------------+--------+--------+--------+--------+ | 123456 | 082020 | xxx | yyy | zzz | +------------+--------+--------+--------+--------+
There is also a third table “T3”, which is the ratio of the period and the table name.
+--------+--------------+ | period | table_name | +--------+--------------+ | 082020 | table_082020 | +--------+--------------+ | 092020 | table_092020 | +--------+--------------+ | 102020 | table_102020 | +--------+--------------+
Tell me how you can combine 3 tables to get dynamic data for several periods. For example: from 15082020 to 04092020, where the data will be located in different tables, respectively
Advertisement
Answer
There really is no good reason for storing data in this format. It makes querying a nightmare.
If you cannot change the data format, then add a view each month that combines the data:
create view t as
select '202010' as YYYYMM, t.*
from table_102020
union all
select '202008' as YYYYMM, t.*
from table_092020
union all
. . .;
For a once-a-month effort, you can spend 10 minutes writing the code and do so with a calendar reminder. Or, better yet, set up a job that uses dynamic SQL to generate the code and run this as a job after the underlying tables are using.
What should you be doing? Well, 5 million rows a months isn’t actually that much data. But if you are concerned about it, you can use table partitioning to store the data by month. This can be a little tricky; for instance, the primary key needs to include the partitioning key.