Oracle数据仓库表设计：事实表嵌套及多索引协同使用咨询

这两个问题都是Oracle数据仓库和优化器实践里挺常见的痛点，我结合实际经验给你梳理下：

先明确下，你说的事实表嵌套应该是指事实表包含嵌套表类型的列，把多条附属明细存在单个字段里的情况吧？这种场景下可以分两种思路处理：

• 优先拆分重构（推荐）：如果嵌套表存储的是低基数、重复度高的附属数据（比如订单事实里的多条折扣明细），最好把嵌套表拆成独立的子事实表，用主事实表的主键做关联。这样回到星型模型的标准设计，子事实表的维度列依然可以建位图索引，主事实表按常规方式处理（比如分区+必要的B树/位图索引），查询复杂度会低很多，CBO也更容易生成高效的执行计划。
• 保留嵌套结构的优化：如果业务逻辑上必须保留嵌套表，那可以这么优化：
  • 把嵌套表设置为索引组织表（IOT），尤其是当嵌套表的主键包含主事实表的主键时，这样主表和嵌套表关联查询时，能快速定位嵌套数据，避免额外的表扫描。
  • 对嵌套表内的维度列（比如嵌套表中的产品ID、渠道ID）创建位图索引——注意Oracle是把嵌套表存在单独的存储表里的，创建索引时需要用TABLE()语法指定，比如：

    CREATE BITMAP INDEX idx_nested_prod_id 
    ON TABLE(emp_facts.nested_details)(product_id);
    

  • 查询时要明确关联主表和嵌套表，比如用TABLE()函数展开嵌套表，让CBO能识别并使用对应的索引。

答案是肯定的，Oracle的基于成本的优化器（CBO）完全支持同时使用多种类型的索引，包括单个B树索引、多个B树索引（索引合并），以及Oracle Text索引，只要这种组合能带来更低的查询成本。
举你提到的员工事实表的例子：

• 如果你的查询是SELECT * FROM emp_facts WHERE CONTAINS(first_name || ' ' || last_name, 'Smith') = 1 AND department_id = 20;，CBO会根据数据量、索引选择性等因素评估：要么先用Oracle Text索引过滤出名字匹配的行，再用department_id上的B树索引进一步筛选；要么先过滤部门，再用Text索引匹配名字——哪种成本低就选哪种。
• 你可以用EXPLAIN PLAN来验证CBO的选择，执行以下命令就能看到执行计划里是否同时用到了多种索引：

  EXPLAIN PLAN FOR SELECT * FROM emp_facts WHERE CONTAINS(first_name || ' ' || last_name, 'Smith') = 1 AND department_id = 20;
  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
  

• 这里有个小提醒：Oracle Text索引的维护成本比普通B树索引高，尤其是在数据频繁插入/更新的场景下，要权衡查询性能和维护开销。另外，如果查询中用到多个索引，CBO会评估索引合并的成本，比如用B树索引的交集、并集过滤数据，再结合Text索引的结果，只要总成本比全表扫描低，就会采用这种方案。

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