[ About archive tables in same schema or different ]
I am working on a Java project with Oracle as backend. We have some tables - with a large volume of data. The data is to be archived after 6 months to archive tables.
I have 2 options:
- Main tables and Archive tables to be in same schema.
- Main Tables in one schema and archive tables in another schema.
Based on individual users options, I sometimes need to fetch data from both the main and archive tables.
Which of the two options is the better design? What are advantages of each?
I've just done something like this and to counter Jim's answer actually prefer the different schema route.
There are a few reasons...
You can call your tables the same name. It's useful and it's easy to see what's happening. I called the schema
archive so all selects are from
archive.my_table. If you want to union the two tables together you, for example,
select * from prod.my_table
select * from archive.my_table
, which reads very well and is unambiguous.
You can take all your table definitions including grants etc and just execute them. No need to bother creating a load of new definitions with a different name. The only difference is that your original schema must be able to select from your archive schema.
I do agree with Jim on the different tablespaces, and different index tablespaces as well ( you'll have to index them if you're using them). If you're using spinning discs though you can put the archive tablespaces on any slower discs you have as it doesn't matter if queries on them go a little bit slower.
I work with a similar scenario in two different products. The best thing in my opinion is to keep them in the same schema, but for performance you probably want to have them in different tablespaces, with datafiles for each tablespace on different physical disks (i.e. different LUNs on the SAN for each tablespace). That's what we do in our data warehousing software; we have tables where active data goes, and a move history procedure that moves the data to a HIST table at the end of the day.
The only real benefit to different schemas, other than organization of like-tables, is if you would like the schemas to be able to be run from different servers/instances. Since it sounds like you'd like to be able to join the tables in certain scenarios, you wouldn't be able to have them in different instances (not without a DBLink, and those are to be avoided). So there's not much benefit of having a different schema.