在同一个硬盘上对 Oracle 进行分区有什么好处吗？
我正在寻找提高并发插入的插入时间的解决方案。如果 Oracle 分区不为每个分区提供专用硬件，我会得到什么好处吗？
I'm looking for the solution to improve insert time for concurrent inserts. Will I get any benefits from Oracle partitioning not providing dedicated hardware for every partition?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您当前插入流程的瓶颈是什么？我从您问题中的“高并发性”猜测，您正在谈论一个 OLTP 应用程序，其中存在大量单行插入，而不是数据中常见的少量多行插入仓库。
在 OLTP 场景中，分区相对不太可能减少单行插入所需的时间。假设您已经消除了明显的时间浪费，例如表上的触发器，则大部分插入开销可能是索引维护以及用于写入重做日志的一些 I/O。分区可能不会减少任何这些，因为在 OLTP 环境中，您通常无法加载到临时表中并进行分区交换，这会降低索引维护成本。
What is the bottleneck in your current insert process? I'm guessing from the "high concurrency" in your question that you're talking about an OLTP app where there are a large number of single-row inserts rather than a small number of many-row inserts that would be common in a data warehouse.
In an OLTP scenario, it is relatively unlikely that partitioning will decrease the time required to do a single-row insert. Assuming that you've already eliminated the obvious time wasters like triggers on the table, most of the insert overhead is likely to be index maintenance with a bit of I/O for the writes to the redo logs. Partitioning likely wouldn't reduce any of these because in an OLTP environment you generally can't load into a staging table and do a partition exchange which would reduce the index maintenance costs.
好吧，就像其他事情一样，这取决于情况。
分区可以减少争用并消除热块。例如，如果你愿意的话，想象一下一个交易系统。如果您通过某些代理客户 ID 值的哈希进行分区，则每个索引都会明显更小，并且可能更少受到争用和索引根分裂的影响。
如果您遇到并发问题，另一种解决方案是针对“单腿”索引使用反向键索引 - 其中索引序列填充列强制继续进行块分割。但是，使用反向键索引会阻止范围扫描使用该索引，因此请小心。
这实际上取决于哪些 Oracle 等待事件是关键事务路径的一部分。您等待的内容通常会决定哪种解决方案是合适的。
所以它会有所帮助。它还可能使情况变得更糟。如果没有更多关于是什么增加了等待时间的信息（如果有的话），互联网无法帮助解决问题。
Well, like everything else, it depends.
Partitioning can reduce contention and eliminate hot blocks. For example, imagine if you will, a transaction system. If you partitioned by hash across some surrogate customer ID value, each index would be significantly smaller, and potentially less subject to contention and index root splits.
Another solution if you have concurrency problems is the use of reverse-key indexes against "one-legged" indexes - where an indexed sequence-populated column forces continue block-splits. However, using reverse-key indexes prevents range scans from using the index, so beware.
It really depends on what Oracle wait events are part of your critical transaction path. What you're waiting on will generally dictate what solution is appropriate.
So it could help. It could also make the situation worse. Without more information about what's adding wait time - if anything - the internet can't help solve the problem.