组合索引优化SQL续-优快云博客

本文探讨在数据仓库环境中，如何通过利用等价的物化视图来优化SQL查询，避免使用组合索引，特别是在面对等价物化视图的情况下，物化视图能提供更高的性能优势。特别强调了DISTINCT关键字在查询中的使用不当会导致性能下降，并通过将DISTINCT关键字移至外部，使得查询能够充分利用索引，提高执行效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

昨天我说，用组合索引优化SQL，并不是最优的，这是因为在8亿的表上面有个等价的物化视图，这个物化视图可以代替我在之前在表上面建立的组合索引。

SQL> explain plan for SELECT distinct * from (select

2 (PROD_9005_GDF_WK_SS_FDIM.PROD_4_NAME),

3 PROD_9005_GDF_WK_SS_FDIM.PROD_5_NAME

4 FROM

5 GLOBL_DEMND_FRCST_WK_FCT,

6 PROD_9005_GDF_WK_SS_FDIM,

7 GDF_SRCE_REGN_LKP

8 WHERE

9 ( GDF_SRCE_REGN_LKP.SRCE_REGN_ID=GLOBL_DEMND_FRCST_WK_FCT.SRCE_REGN_ID )

10 AND ( GLOBL_DEMND_FRCST_WK_FCT.PROD_SKID=PROD_9005_GDF_WK_SS_FDIM.PROD_SKID )

11 AND

12 (GDF_SRCE_REGN_LKP.SRCE_REGN_ID IN ('AA','GC','NE')));

已解释。

已用时间: 00: 00: 00.90

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 900446578

-----------------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 258K| 9582K| | 13588 (9)| 00:01:59 | | |

| 1 | SORT UNIQUE | | 258K| 9582K| 236M| 13588 (9)| 00:01:59 | | |

|* 2 | HASH JOIN | | 4887K| 177M| | 3325 (20)| 00:00:29 | | |

| 3 | MERGE JOIN CARTESIAN | | 4242 | 120K| | 3 (0)| 00:00:01 | | |

| 4 | INLIST ITERATOR | | | | | | | | |

|* 5 | INDEX UNIQUE SCAN | GDF_SRCE_REGN_LKP_PK | 3 | 9 | | 1 (0)| 00:00:01 | | |

| 6 | BUFFER SORT | | 1414 | 36764 | | 2 (0)| 00:00:01 | | |

| 7 | PARTITION RANGE SINGLE | | 1414 | 36764 | | 1 (0)| 00:00:01 | 1 | 1 |

| 8 | INDEX FAST FULL SCAN | PROD_9005_GDF_WK_SS_L5MV_IX1 | 1414 | 36764 | | 1 (0)| 00:00:01 | 1 | 1 |

| 9 | PARTITION RANGE ALL | | 8146K| 69M| | 3254 (18)| 00:00:29 | 1 | 106 |

| 10 | PARTITION LIST INLIST | | 8146K| 69M| | 3254 (18)| 00:00:29 |KEY(I) |KEY(I) |

| 11 | MAT_VIEW REWRITE ACCESS FULL| GDFW_M_9005P5_2MV | 8146K| 69M| | 3254 (18)| 00:00:29 | KEY | KEY |

-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("PROD_9005_GDF_WK_SS_L5MV"."PROD_5_SKID"="GDFW_M_9005P5_2MV"."PROD_5_SKID" AND

"GDF_SRCE_REGN_LKP"."SRCE_REGN_ID"="GDFW_M_9005P5_2MV"."SRCE_REGN_ID")

5 - access("GDF_SRCE_REGN_LKP"."SRCE_REGN_ID"='AA' OR "GDF_SRCE_REGN_LKP"."SRCE_REGN_ID"='GC' OR

"GDF_SRCE_REGN_LKP"."SRCE_REGN_ID"='NE')

已选择26行。

由于有等价的物化视图，它的开销比组合索引更低，所以这里我放弃了组合索引优化，还是采用物化视图优化

最终的执行9秒钟就出结果了

其实这次优化得到的经验就是 DISTINCT关键字

最开始的SQL是这样写的：

SELECT
distinct(PROD_9005_GDF_WK_SS_FDIM.PROD_4_NAME),
PROD_9005_GDF_WK_SS_FDIM.PROD_5_NAME
FROM
GLOBL_DEMND_FRCST_WK_FCT,
PROD_9005_GDF_WK_SS_FDIM,
GDF_SRCE_REGN_LKP
WHERE
( GDF_SRCE_REGN_LKP.SRCE_REGN_ID=GLOBL_DEMND_FRCST_WK_FCT.SRCE_REGN_ID )
AND ( GLOBL_DEMND_FRCST_WK_FCT.PROD_SKID=PROD_9005_GDF_WK_SS_FDIM.PROD_SKID )
AND
( 'ASIA'
=
Case When GDF_SRCE_REGN_LKP.SRCE_REGN_ID IN ('AA','GC','NE')
then 'ASIA'
When GDF_SRCE_REGN_LKP.SRCE_REGN_ID IN ('NA','LA')
Then 'NALA'
When GDF_SRCE_REGN_LKP.SRCE_REGN_ID IN ('WE','CE')
Then 'EMEA'
End );

DISTINCT 关键字在里面，而且从执行计划中可以看到它导致了SORT UNIQUE 操作，并且是最耗资源的操作，

而且由于DISTINCT关键字写在里面，导致无法使用索引，从而走全分区扫描，全分区扫描出来的结果再做DISTINCT 排序，这样肯定效率低下。我把DISTINCT关键字放在查询最外面，这样CBO就选择了索引扫描，那么这样返回的结果集就比全分区扫描小得多，而且DISTINCT排序由于结果集的减少，从而大大减少排序的开销。

因为是数据仓库环境，有物化视图的存在，所以我选择物化视图作为优化方法，如果是在OLTP系统上面，建议采用组合索引的方法优化之，因为OLTP上面的表变化是非常频繁的。