Using views on tables with same schema
Using views on tables with same schema
We are using multiple tables to persist data of an entity month-wise. Meaning, we have table names as xx01, xx02...xx12. The table definition for all these table is same except the name. We are doing like this so that we can drop a table when we think it is old. Ex: drop Feb table in Dec. The table drop will be essentially free.
My question is, can I create a view which can read from any of these tables?
The view should be able to fetch data from any of these tables, potentially from more than one table for range queries.
DB: Oracle 12c.
PS: We can't have a column for month and partition on it because we have to partition and subpartition basing on other columns for read performance.
What are the columns for partitions? With VIRTUAL columns you can define partitions even on more than just one column.
– Wernfried Domscheit
Aug 31 at 6:29
In order to keep read performance you may consider Bitmap-Indexes for such columns. What is your typical query?
– Wernfried Domscheit
Aug 31 at 6:33
You can partition and subpartition on multiple columns, in case that helps. But if partitioning is really not an option, yes you can create a view over all of the tables. Make sure you expose a pseudo-partition key (e.g. the business date or month etc) in the view definition, and use it when querying the view. You'll need to rebuild the view each time the list of tables changes.
– William Robertson
Aug 31 at 9:59
1 Answer
1
I think there may be a better way, and it would work on Oracle: you should just have a single table xx, with a column for month (or month/year) - but make it a partitioned table on that month (or month/year) column.
What Oracle will do is create a separate storage area for each value of the partition column, and it will be smart enough to only look at the appropriate storage area(s), if your queries include a value (or range) for that partition column.
If you ever decided you did not want to keep a particular month (or month/year) any more, you can tell Oracle to drop that one partition, while keeping the rest of the table as is.
I edited the question. We already do partition and subpartition on other two columns. So cannot do it on months.
– user2116990
Aug 31 at 5:58
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Get rid of all these tables and views and create a partitioned table, which is made to solve this exact problem. Once you get past the initial learning curve it's much simpler to create, maintain, and use. Basic info here
– Bob Jarvis
Aug 31 at 4:05