Oracle现有带索引表列表分区及视图改写技术咨询

1. 是否需要删除现有索引，创建本地或全局分区索引？ #

• 如果查询场景以单分区访问为主（比如按schema_name过滤查询），建议删除现有普通索引，创建本地分区索引：本地索引与表分区一一对应，自动继承表的分区策略，分区维护（如删除、归档旧分区）时索引会自动同步，无需额外重建，且能利用分区裁剪提升查询性能。
• 如果存在大量跨分区的高频查询，且索引键不包含分区键schema_name，可以考虑创建全局分区索引，但需注意：全局分区索引维护成本高，表分区发生变更（如添加、合并分区）时可能导致索引失效，需要重建，且存储资源消耗更大。
• 若现有索引是唯一索引：如果唯一键包含schema_name，可以创建本地唯一分区索引；如果唯一键不包含分区键，只能保留全局唯一索引（或全局分区唯一索引，需按索引键分区）。

2. 保留现有非分区索引的影响 #

• 性能损耗：当查询仅访问单个分区时，非分区索引无法利用分区裁剪，仍会扫描整个索引树，导致查询延迟增加，尤其是数据量较大的表。
• 维护成本飙升：对分区表执行分区维护操作（如DROP PARTITION、TRUNCATE PARTITION）时，全局非分区索引会直接失效，必须重建；大表的索引重建会占用大量CPU、IO资源，且耗时极长。
• 存储浪费：非分区索引无法随表分区进行归档或清理，即使旧分区的数据已被归档，对应的索引数据仍会占用存储空间，长期下来会导致存储资源紧张。

3. 改写视图V_INVOICE_GETUSAGE以查询分区表 #

假设原视图的基础SQL如下：

CREATE OR REPLACE VIEW V_INVOICE_GETUSAGE AS
SELECT ii.col1, ii.col2, ie.col_a, ie.col_b
FROM INVOICE_ITEMS ii
JOIN INVOICE_ITEMS_EVENTS ie 
  ON ii.item_obj_3 = ie.item_obj_3 
  AND ii.itemtype = ie.itemtype;

改写方案： #

• 固定查询指定分区：如果视图仅需访问某个特定schema_name的分区，直接在WHERE子句中添加分区键过滤条件，同时确保两张表的分区键一致，避免跨分区关联：

CREATE OR REPLACE VIEW V_INVOICE_GETUSAGE AS
SELECT ii.col1, ii.col2, ie.col_a, ie.col_b
FROM INVOICE_ITEMS ii
JOIN INVOICE_ITEMS_EVENTS ie 
  ON ii.item_obj_3 = ie.item_obj_3 
  AND ii.itemtype = ie.itemtype
  AND ii.schema_name = ie.schema_name -- 确保关联同分区数据
WHERE ii.schema_name = 'TARGET_SCHEMA'; -- 指定目标分区

• 支持动态选择分区：如果需要视图能灵活切换不同分区，可以通过绑定变量或参数化方式实现（以Oracle为例，可借助函数包装视图）：

CREATE OR REPLACE FUNCTION FN_GET_INVOICE_USAGE(p_schema_name VARCHAR2)
RETURN SYS_REFCURSOR
IS
  v_cursor SYS_REFCURSOR;
BEGIN
  OPEN v_cursor FOR
    SELECT ii.col1, ii.col2, ie.col_a, ie.col_b
    FROM INVOICE_ITEMS ii
    JOIN INVOICE_ITEMS_EVENTS ie 
      ON ii.item_obj_3 = ie.item_obj_3 
      AND ii.itemtype = ie.itemtype
      AND ii.schema_name = ie.schema_name
    WHERE ii.schema_name = p_schema_name;
  RETURN v_cursor;
END;
/

或者保留视图的灵活性，让调用方自行传入分区过滤条件，视图仅保留关联逻辑：

CREATE OR REPLACE VIEW V_INVOICE_GETUSAGE AS
SELECT ii.col1, ii.col2, ie.col_a, ie.col_b, ii.schema_name
FROM INVOICE_ITEMS ii
JOIN INVOICE_ITEMS_EVENTS ie 
  ON ii.item_obj_3 = ie.item_obj_3 
  AND ii.itemtype = ie.itemtype
  AND ii.schema_name = ie.schema_name;

调用时只需添加WHERE schema_name = 'XXX'即可定向访问目标分区。

内容的提问来源于stack exchange，提问作者to-find