Oracle-记一次SQL优化(sum)

忘是亡心i 2022-02-03 02:41 650阅读 0赞

## 原始SQL ##

SELECT
    substr( kemu_id, 1, 4 ),
    '8888',
    0,
    0,
    '',
    '',
    (
    	sum( ( CASE WHEN sign( pz_jiefang_jine ) = 0 THEN 0 ELSE 1 END ) * npz_number ) - sum( ( CASE WHEN sign( pz_daifang_jine ) = 0 THEN 0 ELSE 1 END ) * npz_number ) 
    ),
    sum( pz_jiefang_waibi - pz_daifang_waibi ),
    sum( pz_jiefang_jine - pz_daifang_jine ),
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0 
    FROM
    	cw_pz2 
    WHERE 
    	instr( '14', zt_no ) > 0 
    	AND REGEXP_LIKE ( kemu_id, '^(1001|1002|1012)' ) 
    	AND ( to_char ( pz_date, 'yyyy-mm-dd' ) < '2019-05-01' ) 
    GROUP BY
    	kemu_id

## 优化前 ##

Cost值为93207 ，也没有走索引  
![优化前][20190508165846400.png]

## 创建复合索引 ##

create index INX_CW_PZ2_KMRQ_2 on CW_PZ2 (ZT_NO, KEMU_ID, PZ_DATE, PZ_JIEFANG_JINE, PZ_DAIFANG_JINE, PZ_JIEFANG_WAIBI, PZ_DAIFANG_WAIBI, NPZ_NUMBER)
      tablespace USERS
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
        initial 64K
        next 1M
        minextents 1
        maxextents unlimited
      );

## 优化后情况 ##

现在Cost值是17864  
![结果][20190508164827759.png]

## 进一步优化(待续) ##

##### 第一次根据复合索引的特性进行优化 #####

> 了解了前缀性(Prefixing)和可选性(Selectivity)

查询各个字段的可选性，进行索引字段的排序

select count(distinct ZT_NO) ZT_NO, count(distinct KEMU_ID) KEMU_ID, 
           count(distinct PZ_DATE) PZ_DATE, count(distinct PZ_JIEFANG_JINE) PZ_JIEFANG_JINE, 
           count(distinct PZ_DAIFANG_JINE) PZ_DAIFANG_JINE, count(distinct PZ_JIEFANG_WAIBI) PZ_JIEFANG_WAIBI, 
           count(distinct PZ_DAIFANG_WAIBI) PZ_DAIFANG_WAIBI, count(distinct NPZ_NUMBER) NPZ_NUMBER
    from cw_pz2 t

![在这里插入图片描述][20190509111221956.png]  
下面索引修改了顺序

create index INX_CW_PZ2_KMRQ_2 on CW_PZ2 (PZ_DAIFANG_WAIBI, PZ_JIEFANG_WAIBI, PZ_JIEFANG_JINE, PZ_DAIFANG_JINE, NPZ_NUMBER, PZ_DATE, KEMU_ID, ZT_NO)
      tablespace USERS
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
        initial 64K
        next 1M
        minextents 1
        maxextents unlimited
      );

##### 发现并没有什么效果 #####

![在这里插入图片描述][20190509111638425.png]

##### 第？次 #####

> 发现REGEXP\_LIKE 里面的字段可能是不走索引INX\_CW\_PZ2\_KMRQ （该索引只有where后面的字段） 因为单纯的对like “xxx%” 它是走INX\_CW\_PZ2\_KMRQ 索引（该索引只有where后面的字段）

重新建了个索引

create index INX_CW_PZ2_KMRQ on CW_PZ2 (ZT_NO, KEMU_ID, PZ_DATE)
      tablespace USERS
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
        initial 64K
        next 1M
        minextents 1
        maxextents unlimited
      );

## 结论 ##

> 1.以前只对着where里面的字段进行索引创建，发现对sum里的字段创建也有效果  
> 2.发现根据复合索引的前缀性以及可选性进行优化并没有什么效果  
> 3.发现REGEXP\_LIKE 里面的字段可能是不走索引（该索引只有where后面的字段） 因为单纯的对like “xxx%” 它是走索引（该索引只有where后面的字段）

[20190508165846400.png]: /images/20220203/83711d38f4f74fda8cdb3a1139219cf1.png
[20190508164827759.png]: /images/20220203/940c805b23484972bf6ee86fc05fe4de.png
[20190509111221956.png]: /images/20220203/3364b2a987254a4f967b00de6800f601.png
[20190509111638425.png]: /images/20220203/0b6ec27c31664c19bd5ee47429ecd359.png