Oracle 11g高空值占比ProductId字段建索引优化Join性能咨询

核心问题解答 #

1. Oracle 11g是否支持仅对非空行建立索引？ #

支持，且单列普通B树索引天然具备该特性。
Oracle的B树索引不会存储索引列全为NULL的行，你在允许为NULL的ProductId单列上建立普通B树索引时，所有ProductId为NULL的行自动不会进入索引，完全符合仅索引非空行的需求，不需要额外语法适配。（注：显式的部分索引语法是Oracle 12c及以上版本新增的特性，11g版本不需要用到）

2. 95%空值占比场景的可选索引方案，是否适合直接建普通索引？ #

你的场景非常适合直接建立普通索引，性价比极高，也可以根据查询频率选择更适配的优化方案：

• 方案1：普通单列B树索引（优先通用方案）
  建索引语法：

  CREATE INDEX idx_custrans_prodid ON dbo.CustomerTransaction(ProductId);
  

  该方案优势：索引仅存储不到5%的非空行数据，体积极小，DML维护成本极低，完全匹配你提供的INNER JOIN查询逻辑（NULL的ProductId本来就无法和Product表的非空主键匹配，查询时直接走索引就能拿到所有需要关联的行）。

• 方案2：覆盖索引（适合该查询高频执行场景）
  建索引语法：

  CREATE INDEX idx_custrans_prodid_custid ON dbo.CustomerTransaction(ProductId, CustomerId);
  

  该方案优势：索引本身包含了查询需要的所有CustomerTransaction表字段（ProductId用于关联，CustomerId用于返回结果），查询时不需要回表访问原表，性能比普通单列索引更高。

• 方案3：基于函数的索引（仅适合需要查询空值行的特殊场景）
  如果后续有查询ProductId为NULL的行的需求，普通B树索引无法覆盖，可以建基于函数的索引：

  CREATE INDEX idx_custrans_prodid_null ON dbo.CustomerTransaction(NVL(ProductId, -1));
  

  查询时使用NVL(ProductId, -1) = -1即可匹配空值行，你的当前场景不需要该方案。

注意：不建议使用位图索引，CustomerTransaction为交易表，属于高并发DML场景，位图索引会导致DML操作锁粒度极大，严重影响写入性能。

关联参考信息 #

涉及查询语句 #

select ct.customerId, pr.ProductName
from dbo.CustomerTransaction ct
inner join dbo.Product pr 
    on ct.ProductId = pr.ProductId

表结构定义 #

CREATE TABLE [dbo].[CustomerTransaction](
    [CustomerTransactionId] [int] NOT NULL, -- 主键
    [ProductId] [int] NULL,
    [SalesDate] [datetime] NOT NULL, 
    ...
   )

ProductId值分布样例 #

内容的提问来源于stack exchange，提问作者mattsmith5