通过创建物化视图优化查询效率

背景：有一个视图需要查询关联很多张表，每一张表的数据量都极大，然而关联的时候关联条件即使加了索引，但是由于关联字段有几位是用空格补位的，去掉空格后则是另外一张表的一个字段的值，由于做rtrim函数处理，即使两张表的关联字段加了索引也是会索引失效。又不能更改原表结构，那可以添加一个物化视图，存放去空格的security_code，再为security_code添加索引，关联这两张表时直接关联，不用函数rtrim处理了，这样理论上索引是生效的

创建物化视图

注意： with security_base as使用了两个表联合，如果创建物化视图涉及多张表，那么无法使用ON COMMIT 在基表发生数据变化时，物化视图随之刷新，因为创建时无法创建，会报错

使用orcale用户登录sqlplus 为bond用户授权创建物化视图权限：

sqlplus / as sysdba

SQL> GRANT CREATE MATERIALIZED VIEW TO bond WITH ADMIN OPTION;

创建物化视图及创建索引语句：

如果要删除索引：drop index idx_security_code

CREATE MATERIALIZED VIEW bond.mv_bond_security      
REFRESH FORCE ON COMMIT    
AS      
SELECT       
    RTRIM(security_code) AS security_code,    
    RTRIM(security_name) AS security_name,         
    ccy      
FROM       
    bond.security;
	
--创建索引
CREATE INDEX idx_security_code ON MV_BOND_SECURITY(security_code);

如果多表使用on commit：

 

为一个表创建物化视图使用on commit则没有问题

我这里虽然联合查询了两个表，但是有一个表是不需要去除空格去关联的，那么我直接使用左连接去关联该表即可。如果两张表都需要去掉空格，则需要创建各自的物化视图，然后各自左连接其他表：

经过测试，该方法比之前的CTE方式全表扫描两张表的速度明显提升了几倍！！！

可以通过执行计划分析并比较优化前的语句的成本和基数