如何在多个数据库表之间维护唯一标识符? [英] How Can I Maintain a Unique Identifier Amongst Multiple Database Tables?

问题描述

我的任务是为Oracle 11g数据库创建历史表.我在这篇文章的第一个答案中提出了非常类似于基于记录的解决方案的建议

I have been tasked with creating history tables for an Oracle 11g database. I have proposed something very much like the record based solution in the first answer of this post What is the best way to keep changes history to database fields?

然后我的老板建议，由于某些表是群集的，即表1中的某些数据与表2有关(可以认为这是表归一化之前的格式)，他想在那里是在此集群级别在所有表之间维护的版本号.建议的生成版本号的方法是使用SYS_GUID http ://docs.oracle.com/cd/B12037_01/server.101/b10759/functions153.htm .

Then my boss suggested that due to the fact that some tables are clustered i.e Some data from table 1 is related to table 2 (think of this as the format the tables were in before they were normalised), he would like there to be a version number which is maintained between all the tables at this cluster level. The suggested way to generate the version number is by using a SYS_GUID http://docs.oracle.com/cd/B12037_01/server.101/b10759/functions153.htm.

我考虑过使用触发器执行此操作，因此，当更新其中一个表时，其他表的版本号也会随之更新，但是我可以看到与此相关的一些问题，例如:

I thought about doing this with triggers so when one of this tables is updated, the other tables version numbers are subsequently updated, but I can see some issues with this such as the following:

• 如何停止一个表的触发器，从而触发另一表的触发器?(我们最终将在这里永远调用触发器)
• 如何停止比赛条件? (即，同时更新表1和表2时，我怎么知道哪个是最新版本号?)

我对Oracle数据库开发还很陌生，因此对这是否一个好主意/是否有更好的方法提出一些建议.

I am pretty new to Oracle database development so some suggestions about whether or not this is a good idea/if there is a better way of doing this would be great.

推荐答案

第一个问题的简短答案是不，您不能.".这样做的原因是用户无法停止指定的触发器.我能想象的唯一方法是存储锁定表，例如创建一个中间表，然后在集群表中选择同一行进行更新.但这确实是一种不好的方法，正如您在第二个问题中已经提到的那样.它将导致严重的并发问题.

Short answer to your first question is "No, you cannot.". The reason for this is that there's no way that users can stop a stated trigger. The only method I can imagine is some store of locking table, for example you create a intermediate table, and select the same row for update among your clustered tables. But this is really a bad way, as you've already mentioned in your second question. It will cause dreadful concurrency issue.

对于第二个问题，您说得很对.不同的原始表的不同触发器更新同一审核表将导致严重的争用.记住触发事务的方式是明智的，即在其余事务提交时它们将被提交.因此，如果所有相关表都将更新同一审计表，尤其是针对同一行，则将同时使有理范式未使用.归一化的好处之一就是性能提高，因为当您更新不同的表时，彼此不会感到满意.但是在这种情况下，如果要同步审核表中不同表的操作.它将最终像平面文件一样工作.因此，我的建议是尽最大的努力说服老板使用您的原始建议.

For your second question, you are very right. Different triggers for different original tables to update the same audit table will cause serious contention. It's wise to bear in mind the way triggers work that is they are committed when the rest of transaction commit. So if all related tables will update the same audit table, especially for the same row, simultaneously will render the rational paradigm unused. One benefit of the normalization is performance gain, as when you update different table will not content each other. But in this case if you want synchronize different table's operations in audit table. It will finally work like a flat file. So my suggestion would be trying your best to persuade your boss to use your original proposal.

但是，如果您的应用程序始终在事务中更新这些群集表，并将一个审核信息写入审核表.您可以编写存储过程以首先更新实体，并在事务结束时编写审计.然后，您可以使用序列生成审核表的ID.不会有任何争执.

But if your application always updates these clustered table in a transaction and write one audit information to audit table. You may write a stored procedure to update the entities first and write an audit at end of the transaction. Then you can use sequence to generate the id of audit table. It won't be any contention.

这篇关于如何在多个数据库表之间维护唯一标识符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！