SQL优化案例（分区表）

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196

SELECT COUNT(1) cnt FROM( SELECT MAX(TT.ORG_NO) 服务区域,        MAX(TT.MR_SECT_NO) ,        MAX(CBDMC) ,        MAX(CBYDM) ,        MAX(CBYXM) ,        TT.CONS_NO ,        MAX(HM) ,        MAX(DZ) ,        MAX(YHFL) ,        ROUND(SUM(DF) / COUNT(*), 2) ,        MAX(DF) ,        MAX(ZWLXX) ,        MAX(DYLXX) ,        DECODE(TT.WARN_MDOE,               '01',               '催缴通知',               '02',               '预收代扣',               '88',               '免发短信') ,tt.pd_value   FROM (SELECT B.ORG_NO,                B.YM,                  B.MR_SECT_NO,                (SELECT RR.NAME FROM R_SECT RR WHERE MR_SECT_NO = B.MR_SECT_NO) CBDMC,                (SELECT R.OPERATOR_NO                   FROM R_OPER_ACTIVITY R                  WHERE R.MR_SECT_NO = B.MR_SECT_NO                    AND R.ACT_CODE = '03') CBYDM,                (SELECT USER_TITLE                   FROM AMBER.INDY_USER                  WHERE USER_NAME = (SELECT R.OPERATOR_NO                                       FROM R_OPER_ACTIVITY R                                      WHERE R.MR_SECT_NO = B.MR_SECT_NO                                        AND R.ACT_CODE = '03')) CBYXM,                B.CONS_NO,                MAX(B.CONS_NAME) HM,                MAX(B.ELEC_ADDR) DZ,                MAX(B.CONS_SORT_CODE) YHFL,                SUM(A.T_AMT) DF,                MAX((SELECT MAX(A1.MOBILE)                      FROM SGPM.C_CONTACT A1, SGPM.C_CUST_CONS_RELA A2                     WHERE A1.CUST_ID = A2.CUST_ID                       AND A2.ORG_NO = B.ORG_NO                       AND A2.CONS_ID = B.CONS_ID                       AND A2.RELA_TYPE LIKE '%02%')) ZWLXX,                MAX((SELECT MAX(PHONE)                      FROM SGPM.A_CONS_SUB S                     WHERE S.CONS_NO = B.CONS_NO                       AND S.SUB_ITEM = '01')) DYLXX,                C.WARN_MDOE,c.pd_value           FROM ARC_E_CONS_PRC_AMT A, ARC_E_CONS_SNAP B, SGPM.C_RCA_CONS C          WHERE B.YM BETWEEN '20190501'  AND '20200430'            AND B.ORG_NO LIKE '3340660'  || '%'            AND B.MR_SECT_NO BETWEEN '334066001212001'  AND '334066001212020'            AND A.CALC_ID = B.CALC_ID            AND A.ORG_NO = B.ORG_NO            AND A.YM = B.YM            AND A.PRC_CODE NOT IN ('9999',                                   '9997',                                   '9998',                                   '1180',                                   '1110',                                   '1111',                                   '9111',                                   '9110')            AND B.CONS_SORT_CODE != '01'            AND C.CONS_NO = B.CONS_NO            AND B.CONS_ID = C.CONS_ID            AND C.ORG_NO LIKE '3340660'  || '%'            AND C.RCA_FLAG = '1'            AND (c.WARN_MDOE= null  OR null  IS NULL)          GROUP BY B.ORG_NO,                   B.MR_SECT_NO,                   B.YM,                   B.CONS_NO,                   B.CONS_ID,                   C.WARN_MDOE,c.pd_value         HAVING SUM(A.T_AMT) <> '0') TT  GROUP BY TT.CONS_NO, TT.WARN_MDOE,tt.pd_value);         SQL Monitoring Report   Global Information ------------------------------  Status              :  EXECUTING                                                                    Instance ID         :  4                                                                            Session             :  SGPM (3062:34431)                                                            SQL ID              :  4sy2hm59ttc05                                                                SQL Execution ID    :  67109425                                                                     Execution Started   :  06/22/2020 10:07:42                                                          First Refresh Time  :  06/22/2020 10:07:58                                                          Last Refresh Time   :  06/22/2020 10:08:28                                                          Duration            :  47s                                                                          Module/Action       :  .SelfCustQueryImplService.getLSQueryDataListPage/TH63-@dyxepm1_1:0622100635  Service             :  app1                                                                         Program             :  JDBC Thin Client                                                              Binds ======================================================================================================================== | Name | Position |     Type      |                                       Value                                        | ======================================================================================================================== | :1   |        1 | VARCHAR2(32)  | 20190501                                                                           | | :2   |        2 | VARCHAR2(32)  | 20200430                                                                           | | :3   |        3 | VARCHAR2(128) | 3340660                                                                            | | :4   |        4 | VARCHAR2(128) | 3xxxxxx0                                                                    | | :5   |        5 | VARCHAR2(128) | 3xxxxxx0                                                                    | | :6   |        6 | VARCHAR2(128) | 33xxxx0                                                                            | | :7   |        7 | VARCHAR2(32)  |                                                                                    | | :8   |        8 | VARCHAR2(32)  |                                                                                    | | :9   |        9 | NUMBER        | 2650                                                                               | | :10  |       10 | NUMBER        | 1300                                                                               | ========================================================================================================================   Global Stats ================================================================== | Elapsed |   Cpu   | Concurrency | Cluster  |  Other   | Buffer | | Time(s) | Time(s) |  Waits(s)   | Waits(s) | Waits(s) |  Gets  | ================================================================== |      46 |      14 |        0.00 |     0.01 |       31 |     6M | ==================================================================   SQL Plan Monitoring Details (Plan Hash Value=317986502) ========================================================================================================================================================================================= | Id    |                    Operation                     |              Name              |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Mem | Activity | Activity Detail | |       |                                                  |                                | (Estim) |      | Active(s) | Active |       | (Actual) |     |   (%)    |   (# samples)   | ========================================================================================================================================================================================= |     0 | SELECT STATEMENT                                 |                                |         |      |           |        |       |          |     |          |                 | |     1 |   TABLE ACCESS BY INDEX ROWID                    | R_SECT                         |       1 |    1 |           |        |       |          |     |          |                 | |     2 |    INDEX UNIQUE SCAN                             | R_SECT_PK                      |       1 |    1 |           |        |       |          |     |          |                 | |     3 |   TABLE ACCESS BY INDEX ROWID                    | R_OPER_ACTIVITY                |       1 |    1 |           |        |       |          |     |          |                 | |     4 |    INDEX RANGE SCAN                              | R_SECT_R_OPER_ACTIVITY_FK      |      11 |    1 |           |        |       |          |     |          |                 | |     5 |   TABLE ACCESS BY INDEX ROWID                    | INDY_USER                      |       1 |    1 |           |        |       |          |     |          |                 | |     6 |    INDEX UNIQUE SCAN                             | UK_USER_NAME                   |       1 |    1 |           |        |       |          |     |          |                 | |     7 |     TABLE ACCESS BY INDEX ROWID                  | R_OPER_ACTIVITY                |       1 |    1 |           |        |       |          |     |          |                 | |     8 |      INDEX RANGE SCAN                            | R_SECT_R_OPER_ACTIVITY_FK      |      11 |    1 |           |        |       |          |     |          |                 | |  -> 9 |   SORT AGGREGATE                                 |                                |       1 |      |        31 |    +16 | 17171 |    17171 |     |          |                 | | -> 10 |    NESTED LOOPS                                  |                                |       1 |    2 |        31 |    +16 | 17171 |    18019 |     |          |                 | | -> 11 |     NESTED LOOPS                                 |                                |       1 |    2 |        31 |    +16 | 17171 |    18019 |     |          |                 | | -> 12 |      PARTITION RANGE SINGLE                      |                                |       1 |    1 |        31 |    +16 | 17171 |    18012 |     |          |                 | | -> 13 |       TABLE ACCESS BY LOCAL INDEX ROWID          | C_CUST_CONS_RELA               |       1 |    1 |        31 |    +16 | 17171 |    18012 |     |          |                 | | -> 14 |        INDEX RANGE SCAN                          | IDX_C_CUST_CONS_RELA_CONS_ID   |       1 |    1 |        31 |    +16 | 17171 |    33069 |     |          |                 | | -> 15 |      INDEX RANGE SCAN                            | C_CUST_C_CONTACT_FK            |       1 |    1 |        31 |    +16 | 18012 |    18019 |     |          |                 | | -> 16 |     TABLE ACCESS BY GLOBAL INDEX ROWID           | C_CONTACT                      |       1 |    1 |        31 |    +16 | 18019 |    18019 |     |          |                 | | -> 17 |   SORT AGGREGATE                                 |                                |       1 |      |        31 |    +16 | 19523 |    19523 |     |          |                 | |    18 |    PARTITION RANGE ALL                           |                                |       1 |   32 |        39 |     +8 | 19523 |    15462 |     |     6.52 | Cpu (3)         | |    19 |     TABLE ACCESS BY LOCAL INDEX ROWID            | A_CONS_SUB                     |       1 |   32 |        43 |     +4 |    2M |    15462 |     |    13.04 | Cpu (6)         | | -> 20 |      INDEX RANGE SCAN                            | IDX_A_CONS_SUB_CONSP           |       3 |   31 |        46 |     +2 |    2M |    55226 |     |    69.57 | Cpu (32)        | |    21 |   VIEW                                           |                                |       1 | 2525 |           |        |     1 |          |     |          |                 | |    22 |    COUNT STOPKEY                                 |                                |         |      |           |        |     1 |          |     |          |                 | |    23 |     VIEW                                         |                                |       1 | 2525 |           |        |     1 |          |     |          |                 | |    24 |      SORT GROUP BY STOPKEY                       |                                |       1 | 2525 |           |        |     1 |          |     |          |                 | |    25 |       VIEW                                       |                                |       1 | 2525 |           |        |     1 |          |     |          |                 | |    26 |        FILTER                                    |                                |         |      |           |        |     1 |          |     |          |                 | |    27 |         HASH GROUP BY                            |                                |       1 | 2525 |        31 |    +16 |     1 |        0 | 61M |     2.17 | Cpu (1)         | | -> 28 |          FILTER                                  |                                |         |      |        31 |    +16 |     1 |    25305 |     |          |                 | | -> 29 |           NESTED LOOPS                           |                                |       1 | 2524 |        31 |    +16 |     1 |    25305 |     |          |                 | | -> 30 |            NESTED LOOPS                          |                                |       1 | 2524 |        31 |    +16 |     1 |    25305 |     |          |                 | | -> 31 |             NESTED LOOPS                         |                                |       1 | 2523 |        31 |    +16 |     1 |    20379 |     |          |                 | | -> 32 |              PARTITION RANGE ITERATOR            |                                |       1 | 2522 |        31 |    +16 |     1 |    22004 |     |          |                 | | -> 33 |               PARTITION LIST ITERATOR            |                                |       1 | 2522 |        31 |    +16 |     2 |    22004 |     |          |                 | | -> 34 |                TABLE ACCESS BY LOCAL INDEX ROWID | ARC_E_CONS_SNAP                |       1 | 2522 |        31 |    +16 |    19 |    22004 |     |          |                 | |    35 |                 INDEX RANGE SCAN                 | LOC_ARC_E_CONS_SNAP_SECT_NO    |     237 | 2520 |        31 |    +16 |    19 |    22004 |     |     2.17 | Cpu (1)         | | -> 36 |              TABLE ACCESS BY GLOBAL INDEX ROWID  | C_RCA_CONS                     |       1 |    1 |        31 |    +16 | 22004 |    20379 |     |          |                 | | -> 37 |               INDEX UNIQUE SCAN                  | PK_C_RCA_CONS                  |       1 |    1 |        31 |    +16 | 22004 |    20520 |     |          |                 | |    38 |             PARTITION RANGE AND                  |                                |       1 |    1 |        31 |    +16 | 20379 |    25305 |     |     4.35 | Cpu (2)         | |    39 |              PARTITION LIST AND                  |                                |       1 |    1 |        46 |     +1 | 20379 |    25305 |     |     2.17 | Cpu (1)         | | -> 40 |               INDEX RANGE SCAN                   | IDX_ARC_E_CONS_PRC_AMT_CALC_ID |       1 |    1 |        31 |    +16 | 20379 |    25305 |     |          |                 | | -> 41 |            TABLE ACCESS BY LOCAL INDEX ROWID     | ARC_E_CONS_PRC_AMT             |       1 |    1 |        31 |    +16 | 25305 |    25305 |     |          |                 | =========================================================================================================================================================================================   从执行计划上很明显能看出 | -> 17 |   SORT AGGREGATE                                 |                                |       1 |      |        31 |    +16 | 19523 |    19523 |     |          |                 | |    18 |    PARTITION RANGE ALL                           |                                |       1 |   32 |        39 |     +8 | 19523 |    15462 |     |     6.52 | Cpu (3)         | |    19 |     TABLE ACCESS BY LOCAL INDEX ROWID            | A_CONS_SUB                     |       1 |   32 |        43 |     +4 |    2M |    15462 |     |    13.04 | Cpu (6)         | | -> 20 |      INDEX RANGE SCAN                            | IDX_A_CONS_SUB_CONSP           |       3 |   31 |        46 |     +2 |    2M |    55226 |     |    69.57 | Cpu (32)        | 该步骤消耗大量资源和时间。   对应的是sql中的标量子查询部分 MAX((SELECT MAX(PHONE)                      FROM SGPM.A_CONS_SUB S                     WHERE S.CONS_NO = B.CONS_NO                       AND S.SUB_ITEM = '01')) DYLXX, A_CONS_SUB表是按照org_no字段做的分区表。 表数据量7千万。distinct cons_no有3千万（选择性ok） PARTITION RANGE ALL 范围分区全扫面。 很明显是使用分区表未加分区条件。 修改后： MAX((SELECT MAX(PHONE)                      FROM SGPM.A_CONS_SUB S                     WHERE S.CONS_NO = B.CONS_NO                       AND S.ORG_NO = B.ORG_NO                       AND S.SUB_ITEM = '01')) DYLXX, 4秒内出结果。