Oracle 10g - 优化 WHERE IS NOT NULL
我们有 Oracle 10g，我们需要查询 1 个表(无连接)并过滤掉其中 1 列为空的行.当我们这样做时 - WHERE OurColumn IS NOT NULL - 我们对一个非常大的表进行全表扫描 - BAD BAD BAD.该列上有一个索引，但在此实例中被忽略.有什么解决办法吗?
We have Oracle 10g and we need to query 1 table (no joins) and filter out rows where 1 of the columns is null. When we do this - WHERE OurColumn IS NOT NULL - we get a full table scan on a very large table - BAD BAD BAD. The column has an index on it but it gets ignored in this instance. Are there any solutions to this?
谢谢
推荐答案
优化器认为全表扫描会更好.
The optimizer thinks that the full table scan will be better.
如果只有几个 NULL 行，则优化器是正确的.
If there are just a few NULL rows, the optimizer is right.
如果您绝对确定索引访问会更快(也就是说，您有超过 75% 行且 col1 IS NULL)，则提示您的查询:
If you are absolutely sure that the index access will be faster (that is, you have more than 75% rows with col1 IS NULL), then hint your query:
SELECT /*+ INDEX (t index_name_on_col1) */
*
FROM mytable t
WHERE col1 IS NOT NULL
为什么是 75%?
因为使用 INDEX SCAN 来检索索引未涵盖的值意味着 ROWID 上的隐藏连接，其成本约为 4 的两倍作为表扫描.
Because using INDEX SCAN to retrieve values not covered by the index implies a hidden join on ROWID, which costs about 4 times as much as table scan.
如果索引范围包含超过 25% 的行，则表扫描通常会更快.
If the index range includes more than 25% of rows, the table scan is usually faster.
正如Tony Andrews 所提到的，聚类因子是衡量这个值的更准确的方法，但25% 仍然是一个很好的经验法则.
As mentioned by Tony Andrews, clustering factor is more accurate method to measure this value, but 25% is still a good rule of thumb.
相关文章