SQL优化

首先介绍一下面临的问题，每天数据量有百万级，使用count计数每天数据有十几秒，老板要求优化（oracle已经进行按月分表操作）：
1、优化前的sql：

SELECT COUNT(1) FROM B_CAR  WHERE 1=1  AND(WZSJ>=to_date('2018/8/22 0:00:00','yyyy-mm-dd hh24:mi:ss') ) AND(WZSJ<=to_date('2018/8/22 18:13:28','yyyy-mm-dd hh24:mi:ss') ) 

---

select * from(select LINE,PK_CAR,WZLK,LKDM,CPHM,WZSJ,WZLX,HPZL,HPYS,XSFX,CDH,CLSD,ZT,TPDZ,CLLX,WJJ,PIC1,PIC2,PIC3,FLAG,ZJWJLJ,CSYS,CLPP from( SELECT 
                        ROWNUM AS LINE,
                        PK_CAR,
                        B.LKMC AS WZLK ,
                        B.LKDM AS LKDM,
                        CPHM,
                        WZSJ,
                        C.LXMC AS WZLX,
                        D.LXMC AS HPZL,
                        E.YSMC AS HPYS,
                        F.CONFIG AS XSFX, 
                        G.CONFIG AS CDH,
                        CLSD,
                        ZT,
                        TPDZ,
                        CLLX,
                        WJJ,
                        PIC1,
                        PIC2,
                        PIC3,
                        FLAG,
                        ZJWJLJ,
                        H.CAR_COLOR_NAME AS CSYS,
                        I.LOGO_NAME AS CLPP 
                        FROM
                        ( ( SELECT * FROM B_CAR
                                   WHERE ROWID IN (
                                          SELECT RID FROM
                                          (
                                              SELECT RID, ROWNUM AS rn
                                               FROM (
                                                 SELECT ROWID RID  FROM  B_CAR
                                                 WHERE (WZSJ>=to_date('2018/8/22 17:33:42','yyyy-mm-dd hh24:mi:ss') ) AND(WZSJ<=to_date('2018/8/22 17:34:24','yyyy-mm-dd hh24:mi:ss') )
                                                 order by WZSJ desc) t1 WHERE  1=1
                                          ) t2 WHERE 1=1
                                  ) order by WZSJ DESC
                          ) A
                        LEFT OUTER JOIN (SELECT LKDM,LKMC FROM C_CROSSING )B ON  A.WZLK =B.LKDM  --路口
                        LEFT OUTER JOIN (SELECT WFDM,LXMC FROM C_ILLEGALTYPE )C ON  A.WZLX =C.WFDM --违章类型
                        LEFT OUTER JOIN (SELECT LXDM,LXMC FROM C_PLATETYPE )D ON  A.HPZL =D.LXDM  --号牌种类
                        LEFT OUTER JOIN (SELECT YSDM,YSMC FROM C_PLATECOLOR )E ON  A.HPYS =E.YSDM  --车牌颜色
                        LEFT OUTER JOIN (SELECT CONFIG,CONFIGCODE FROM C_SYSCONFIG WHERE TYPECODE=1)F ON  A.XSFX =F.CONFIGCODE --行驶方向
                        LEFT OUTER JOIN (SELECT CONFIG,CONFIGCODE FROM C_SYSCONFIG WHERE TYPECODE=2)G ON  A.CDH =G.CONFIGCODE  ---车道号
                        LEFT OUTER JOIN (SELECT CAR_CODE,CAR_COLOR_NAME FROM CAR_COLOR)H ON  A.CSYS =H.CAR_CODE  ---车身颜色
                        LEFT OUTER JOIN (SELECT LOGO_CODE,LOGO_NAME FROM CAR_LOGO)I ON  A.CAR_LOGO =I.LOGO_CODE  ---车辆品牌
                          )where 1=1 ORDER BY WZSJ DESC）WHERE  ROWNUM<=200 )where LINE>0

2、优化的思路
对于统计的sql，通过查找资料，建议是建立位图索引，然后进行并行操作；
创建位图索引：
create bitmap index 索引名 on table（字段名）；
注：位图索引不适用于大并发的数据
创建位图索引并行：
alter index 索引名 parallel n；
注：禁止并行的语句： alter index 索引名 noparallerl；
优化后的sql：

      SELECT /*+ parallel(B_CAR 8)*/
 COUNT(*）
  FROM B_CAR
 WHERE (WZSJ >= to_date('2018/8/16　0:00:00', 'yyyy-mm-dd hh24:mi:ss'))
 AND　(WZSJ <= to_date('2018/8/16 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))

这样写表示在使用这句sql时，进行并行；

---

第二条sql的优化思路：
首先在筛选出需要的条件后，在进行外链接减少数据量；
其次修改语句不使用in；
优化后的sql

SELECT 
                        PK_CAR,
                        B.LKMC AS WZLK ,
                        B.LKDM AS LKDM,
                        CPHM,
                        WZSJ,
                        C.LXMC AS WZLX,
                        D.LXMC AS HPZL,
                        E.YSMC AS HPYS,
                        F.CONFIG AS XSFX, 
                        G.CONFIG AS CDH,
                        CLSD,
                        ZT,
                        TPDZ,
                        CLLX,
                        WJJ,
                        PIC1,
                        PIC2,
                        PIC3,
                        FLAG,
                        ZJWJLJ,
                        H.CAR_COLOR_NAME AS CSYS,
                        I.LOGO_NAME AS CLPP 
                        FROM
                       （

                          SELECT   PK_CAR,WZLK , CPHM, WZSJ, WZLX,HPZL, HPYS, XSFX, CDH, CLSD,ZT,TPDZ,CLLX, WJJ,PIC1, PIC2,PIC3, FLAG, ZJWJLJ, CSYS,CAR_LOGO　from 
                            (SELECT ROWNUM AS LINE,  PK_CAR,WZLK , CPHM, WZSJ, WZLX,HPZL, HPYS, XSFX, CDH, CLSD,ZT,TPDZ,CLLX, WJJ,PIC1, PIC2,PIC3, FLAG, ZJWJLJ, CSYS,CAR_LOGO　FROM  B_CAR
                             WHERE (WZSJ>=to_date('2018/8/24 00:00:00','yyyy-mm-dd hh24:mi:ss') ) AND(WZSJ<=to_date('2018/8/24 23:59:59','yyyy-mm-dd hh24:mi:ss') )order by WZSJ DESC
                             )　 where LINE<=400 and LINE>200
                            ) A
                        LEFT OUTER JOIN (SELECT LKDM,LKMC FROM C_CROSSING )B ON  A.WZLK =B.LKDM  --路口
                        LEFT OUTER JOIN (SELECT WFDM,LXMC FROM C_ILLEGALTYPE )C ON  A.WZLX =C.WFDM --违章类型
                        LEFT OUTER JOIN (SELECT LXDM,LXMC FROM C_PLATETYPE )D ON  A.HPZL =D.LXDM  --号牌种类
                        LEFT OUTER JOIN (SELECT YSDM,YSMC FROM C_PLATECOLOR )E ON  A.HPYS =E.YSDM  --车牌颜色
                        LEFT OUTER JOIN (SELECT CONFIG,CONFIGCODE FROM C_SYSCONFIG WHERE TYPECODE=1)F ON  A.XSFX =F.CONFIGCODE --行驶方向
                        LEFT OUTER JOIN (SELECT CONFIG,CONFIGCODE FROM C_SYSCONFIG WHERE TYPECODE=2)G ON  A.CDH =G.CONFIGCODE  ---车道号
                        LEFT OUTER JOIN (SELECT CAR_CODE,CAR_COLOR_NAME FROM CAR_COLOR)H ON  A.CSYS =H.CAR_CODE  ---车身颜色
                        LEFT OUTER JOIN (SELECT LOGO_CODE,LOGO_NAME FROM CAR_LOGO)I ON  A.CAR_LOGO =I.LOGO_CODE  ---车辆品牌

1、在数据库中不要使用or使用union（去掉重复数据） 或者union all（这个不去掉重复）；
2、不要使用in；