使用CASE与GROUP BY优化视图查询性能的技术求助

1. 改写查询逻辑，提前过滤数据 #

当前视图的核心问题是：查询时数据库需要先完成全表关联+分组，再对FIELD_TYPE做过滤，导致大量不必要的计算。可以把过滤逻辑拆分成两种场景下推到关联步骤前，大幅减少待处理的数据量：

改写后的查询（替代直接查询视图） #

SELECT
    CASE
        WHEN t2.TYPE = 1 THEN t1.field_type_1
        ELSE t1.field_type_2
    END FIELD_TYPE,
    t1.USER,
    COUNT(*)
FROM table_1 t1 
JOIN table_2 t2 ON t1.ID = t2.FK_ID
-- 拆分过滤条件，提前筛除不符合的数据
WHERE (t2.TYPE = 1 AND t1.field_type_1 LIKE 'some text')
   OR (t2.TYPE != 1 AND t1.field_type_2 LIKE 'some text')
GROUP BY t1.USER,
    CASE WHEN t2.TYPE = 1 THEN t1.field_type_1 ELSE t1.field_type_2 END;

2. 添加针对性索引 #

针对关联、过滤字段创建索引，避免全表扫描：

• 给table_2创建关联+过滤的组合索引：

  CREATE INDEX idx_t2_fk_type ON table_2 ("FK_ID", "TYPE");
  

• 给table_1创建覆盖查询字段的组合索引（避免回表查询）：

  CREATE INDEX idx_t1_id_ft1_ft2_user ON table_1 ("ID", "FIELD_TYPE_1", "FIELD_TYPE_2", "USER");
  

如果LIKE是前缀匹配（如'some text%'），还可以给文本字段创建前缀索引，进一步优化过滤速度：

CREATE INDEX idx_t1_ft1_prefix ON table_1 ("FIELD_TYPE_1"(20));
CREATE INDEX idx_t1_ft2_prefix ON table_1 ("FIELD_TYPE_2"(20));

3. 使用物化视图预计算结果 #

如果业务可以接受非实时数据，或者数据更新频率较低，可创建物化视图预计算分组结果，并在物化视图上建索引：

创建物化视图 #

CREATE MATERIALIZED VIEW mv_t1
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND -- 按需刷新，也可设置定时刷新
AS
SELECT
    CASE
        WHEN t2.TYPE = 1 THEN t1.field_type_1
        ELSE t1.field_type_2
    END FIELD_TYPE,
    t1.USER,
    COUNT(*) AS cnt
FROM table_1 t1 JOIN table_2 t2 ON t1.ID = t2.FK_ID
GROUP BY t1.USER,
    CASE WHEN t2.TYPE = 1 THEN t1.field_type_1 ELSE t1.field_type_2 END;

给物化视图加索引 #

CREATE INDEX idx_mv_ft ON mv_t1 ("FIELD_TYPE");
CREATE INDEX idx_mv_user ON mv_t1 ("USER");

之后直接查询物化视图即可获得秒级响应：

SELECT * FROM mv_t1 WHERE field_type LIKE 'some text';

4. 强制条件下推（针对必须使用视图的场景） #

如果业务要求必须使用原视图，可通过Oracle的PUSH_PRED提示强制数据库将外层过滤条件下推到内层关联步骤：

SELECT /*+ PUSH_PRED(vw) */ * FROM vw_t1 vw WHERE field_type LIKE 'some text';

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