关于同一架构或不同架构中的归档表
我正在开发一个以 Oracle 作为后端的 Java 项目。我们有一些表 - 包含大量数据。数据将在6个月后归档到归档表中。
我有 2 个选项：
- 主表和存档表处于同一架构中。
- 一个模式中的主表和另一模式中的存档表。
根据各个用户的选项，我有时需要从主表和存档表中获取数据。
这两个选项中哪一个是更好的设计？各自有什么优点？
谢谢
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?
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我刚刚做了类似的事情，并反驳 Jim 的 答案实际上更喜欢不同的架构路线。
有几个原因......
您可以将表命名为相同的名称。它很有用，而且很容易看到发生了什么。我将架构命名为
archive，因此所有选择均来自archive.my_table。如果您想将两个表合并在一起，例如，它读起来非常好并且明确。
您可以获取所有表定义（包括拨款等）并执行它们。无需费心创建大量具有不同名称的新定义。唯一的区别是您的原始架构必须能够从存档架构中进行选择。
我确实同意吉姆关于不同表空间和不同索引表空间的观点（如果您使用它们，则必须对它们建立索引）。如果您使用旋转磁盘，则可以将存档表空间放在您拥有的任何较慢的磁盘上，因为对它们的查询速度慢一点也没关系。
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
archiveso all selects are fromarchive.my_table. If you want to union the two tables together you, for example,, 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.
我在两种不同的产品中处理类似的场景。我认为最好的办法是将它们保持在相同的模式中，但为了性能，您可能希望将它们放在不同的表空间中，每个表空间的数据文件位于不同的物理磁盘上（即每个表空间在 SAN 上有不同的 LUN）。这就是我们在数据仓库软件中所做的事情；我们有活动数据所在的表，以及在一天结束时将数据移动到 HIST 表的移动历史记录过程。
除了相似表的组织之外，不同模式的唯一真正好处是您是否希望模式能够从不同的服务器/实例运行。因为听起来您希望能够在某些情况下连接表，所以您将无法在不同的实例中拥有它们（没有 DBLink 就不行，并且要避免这些）。因此，使用不同的模式并没有多大好处。
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.