Monday, 15 June 2015

sql - Oracle Query on 24 tables with same columns -


I have 24 table tables 1, table 2, table 3 ... customer on the hourly basis with the same column Rate ID is a primary key in all 24 other tables, in order to keep track of data and rate rates that are applied for a specific time, based on the date and time, to bring data from those tables Dynamic queries are required. Can anyone provide an example or guide me for that query?

You should not store the same data 24 different table partitions (outlined in a comment) is a very good The solution is when you have a lot of data and it is divided for performance reasons.

In any case, there is a way to organize your query: choose

 . From (Select * from * Table 1) * All Associations (Select * from all tables) Association (Select All * Table from 24)) Where & lt; Whatever you want & gt;   

Any other tables you like it like (for example, using rateId ), filter on the field, or whatever.

If you need to know the table where something came from, you can also get it:

  select t. From (Select T. *, 1, from which Table 1 T) Union All (Select T. *, 2, from which Table 2 T) Union All ... (Select T., 24, which is the table 24 T)) where & lt; Whatever you want & gt;   

Note: I am using * because OP clearly states that tables have the same format. However, it is possible that it is a good idea to list all the columns of each sub-class.

EDIT:

As Bill says in the comment, you want to convert it to a view. In this way, you can write a lot of questions on the tables without worrying about the elaborate tables. (And, better yet, you can fix the data structure by combining tables, then change the scene, and existing questions will work).

No comments:

Post a Comment