Oracle事务-计数表
我有一个表，我需要按类别进行限制，然后查找某个日期范围内的所有重叠日期。这大约需要 2 秒，对于大约 50/秒发生的每笔交易来说，这是不可接受的。另一种方法是创建一些统计表——话又说回来，我不知道这是一个多么好的主意，因为事情可能会不同步。
Date on Rent # Rented Category
9/5/2011 5 CATEGORY1
在Oracle（PL/SQL，如果重要的话）中，我怎样才能保持这种性能，但确保并发事务不会通过使其比实际少一或多一而搞砸增量/减量？
我有两种类型的交易，有点像搜索和租金。只有租金才会更新此统计表（并仅从中读取搜索）。我不介意租金是否放缓，但不希望搜索性能受到影响。租金发生频率可达 5-10 次/秒。
I have a table where I need to constrain by category and then find all overlapping dates against some date range. This takes about 2 seconds, which is unacceptable to do on every transaction which occurs at roughly 50/s. The alternative is to create some tally table -- then again, I don't know how great of an idea this is because things can get out of sync.
Date on Rent # Rented Category
9/5/2011 5 CATEGORY1
In Oracle (PL/SQL, if it matters), how can I maintain this performance, but ensure that concurrent transactions don't screw up the increment / decrement by making it one less or one more than it really is?
I have two types of transactions, kind of like a search and a rent. Only rents will be updating this tally table (and searches just reading from it). I don't mind if rents slow down, but do not want search performance impacted. Rents can occur as frequently as 5-10 / second.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
Oracle 使用锁来确保查询期间数据的一致性。它们工作的细节可能会变得复杂，但效果是它保证对统计表的更新/插入将仅使用主表中的数据，就像查询开始时一样。如果在对统计表进行更新/插入时对主表进行了其他更新或插入，则不会影响它。
您必须对数据进行试验，看看保留汇总/统计表是否对您有帮助或有害。这实际上取决于主表的更新速度、更新统计表所花费的时间与通过在其上进行选择而节省的时间以及您需要选择的最新程度。
Oracle uses locks to ensure data consistency during a query. The details of how they work can get complex, but the effect is that it guarantees that an update/insert to your tally table will only use the data in the main table as it was when the query began. If there is another update or insert to your main table while you are doing an update/insert to the tally table, it won't affect it.
You'll have to experiment with your data to see if keeping a summary/tally table helps or hurts you. It really depends on how quickly the main table is getting updated, how much time you spend updating your tally table vs how much time you save by being able to select on it, and how up-to-date you need selects to be.