将行插入维度表时 oracle 服务器挂起
有两张桌子。第一个是事实表，大约有 1 亿行。另一种是只有100行左右的维度表。我在事实表上创建了一个位图连接索引 优化查询。
但是，当我向维度表中插入几行时，数据库挂起。
有谁知道可能是什么原因？
There are two tables. The first is a fact table, about 0.1 billion rows. The other is a dimension table with only about 100 rows. I have made a bitmap-join index on the fact table to
optimize the query.
However, when I insert a few rows into the dimension table, the database hangs.
Does anyone know what could be the reason?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
位图索引是一个矩阵，其中每个不同值有一列，索引表中的每条记录都有一行。同样的原理也适用于位图连接索引：DIMENSION 表中的每个不同值有一列，FACT 表中的一行。
由此可见，向 DIMENSION 表中插入一行将在索引中生成 1 亿个条目。这需要很长时间。
你说你正在插入“几行”。那么，老实说，您认为生成所有这些条目的合理时间是什么？
这就是位图索引的权衡：它可以节省大量查询时间，但维护开销极高。因此，在部署位图索引之前我们需要仔细考虑。有时可以降低这些成本，但这取决于具体情况。
关于位图连接索引，还需要记住一件事，这也可能导致服务器挂起：
您应该阅读文档。 了解更多信息。
A bitmap index is a matrix, with a column for each distinct value and a row for each record in the indexed table. The same principle applies in a bitmap join index: you have one column for each distinct value in the DIMENSION table and one row in the FACT table.
From which it should be apparent that inserting one row into the DIMENSION table will generate 100 million entries in the index. That will take a long time.
You say you are inserting "a few rows". So, honestly, what do you think would be a reasonable time to generate all those entries?
This is the bitmap index trade-off: it can save a lot of time of queries but the overhead for maintenance is extremely high. Consequently we need to think carefully before deploying a bitmap index. Sometimes it is possible to ameliorate those costs, but that depends on the particular circumstances.
There is one further thing to bear in mind with bitmap join indexes, which might also appear to cause the server to hang:
You should read the documentation. Find out more.
当加载大量行时，位图索引确实会带来相关的性能损失。为了获得您想要的效率，只需将索引标记为不可用，加载记录，然后重建索引。您应该会看到加载时间随着您所讨论的数据量的增加而缩短。
Bitmap indexes do have an associated performance penalty when loading large number of rows. To get the efficiency you are looking for, simply mark the index as unusable, load your records, then rebuild the index. You should see your load times improve with the amount of data you are talking about.