• oracle-sql优化-通过分组和缓存减少不必要的读


    环境:aix 7.1,oracle12.1.0.2 cdb

    优化前SQL

    select *
      from (select row_.*, rownum rownum_
              from (select '弱覆盖' as questionType,
                           city_name as cityName,
                           county_name as countyName,
                           grid_id as gridId,
                           'LTE' as netWorkType
                      from (select city_name,
                                   county_name,
                                   grid_id,
                                   case
                                     when rsrpSum > 0 and counts > 0 and
                                          rsrpSum / counts < 0.95 then
                                      1
                                     else
                                      0
                                   end rfgSum
                              from (select city_name,
                                           county_name,
                                           grid_id,
                                           sum(rsrp_value) rsrpSum,
                                           count(grid_id) counts
                                      from (select case
                                                     when LTE_RSRP > -105 then
                                                      1
                                                     else
                                                      0
                                                   end rsrp_value,
                                                   city_name,
                                                   county_name,
                                                   grid_id
                                              from SJCL.measured_data
                                             where IS_MACRO_STATION = 1
                                               and grid_id is not null
                                               and terminal_upload_time >=
                                                   to_date('2017-12-02 00:00:00',
                                                           'yyyy/mm/dd hh24:mi:ss')
                                               and terminal_upload_time <=
                                                   to_date('2017-12-02 23:59:59',
                                                           'yyyy/mm/dd hh24:mi:ss')) t1
                                     group by t1.city_name,
                                              t1.county_name,
                                              t1.grid_id) t2) t3
                     where rfgSum >= 1
                     group by city_name, county_name, grid_id
                    union all
                    select '无主控覆盖' as questionType,
                           city_name as cityName,
                           county_name countyName,
                           grid_id gridId,
                           'LTE' as netWorkType
                      from (select case
                                     when ci_count / grid_count > 0.3 then
                                      1
                                     else
                                      0
                                   end gr_ci,
                                   city_name,
                                   county_name,
                                   grid_id,
                                   LTE_CI
                              from (select count(case
                                                   when t1.network_type = 'LTE' then
                                                    t1.lte_ci
                                                   when t1.network_type = 'GSM' then
                                                    t1.gsm_cid
                                                   when t1.network_type = 'TD' then
                                                    t1.td_cid
                                                   else
                                                    null
                                                 end) ci_count,
                                           (select count(grid_id)
                                              from SJCL.measured_data
                                             where grid_id = t1.grid_id
                                               and IS_MACRO_STATION = 1
                                               and terminal_upload_time >=
                                                   to_date('2017-12-02 00:00:00',
                                                           'yyyy/mm/dd hh24:mi:ss')
                                               and terminal_upload_time <=
                                                   to_date('2017-12-02 23:59:59',
                                                           'yyyy/mm/dd hh24:mi:ss')) grid_count,
                                           t1.city_name,
                                           t1.county_name,
                                           t1.grid_id,
                                           LTE_CI
                                      from SJCL.measured_data t1
                                     where t1.grid_id is not null
                                       and t1.IS_MACRO_STATION = 1
                                       and terminal_upload_time >=
                                           to_date('2017-12-02 00:00:00',
                                                   'yyyy/mm/dd hh24:mi:ss')
                                       and terminal_upload_time <=
                                           to_date('2017-12-02 23:59:59',
                                                   'yyyy/mm/dd hh24:mi:ss')
                                     group by t1.city_name,
                                              t1.county_name,
                                              t1.grid_id,
                                              t1. LTE_CI
                                     ) t2) t3
                     group by city_name, county_name, grid_id
                    having sum(gr_ci) >= 3
                    union all
                    select '质差' as questionType,
                           city_name as cityName,
                           county_name as countyName,
                           grid_id as gridId,
                           'LTE' as netWorkType
                      from (select city_name,
                                   county_name,
                                   grid_id,
                                   case
                                     when rsrpSum > 0 and counts > 0 and
                                          rsrpSum / counts > 0.05 then
                                      1
                                     else
                                      0
                                   end rfgSum
                              from (select city_name,
                                           county_name,
                                           grid_id,
                                           sum(rsrp_value) rsrpSum,
                                           count(grid_id) counts
                                      from (select case
                                                     when LTE_RSRP > -100 and
                                                          LTE_SINR < 0 then
                                                      1
                                                     else
                                                      0
                                                   end rsrp_value,
                                                   city_name,
                                                   county_name,
                                                   grid_id
                                              from SJCL.measured_data
                                             where IS_MACRO_STATION = 1
                                               and grid_id is not null
                                               and terminal_upload_time >=
                                                   to_date('2017-12-02 00:00:00',
                                                           'yyyy/mm/dd hh24:mi:ss')
                                               and terminal_upload_time <=
                                                   to_date('2017-12-02 23:59:59',
                                                           'yyyy/mm/dd hh24:mi:ss')) t1
                                     group by t1.city_name,
                                              t1.county_name,
                                              t1.grid_id) t2) t3
                     where rfgSum >= 1
                     group by city_name, county_name, grid_id
                    union all
                    select '越区覆盖' as questionType,
                           city_name as cityName,
                           county_name as countyName,
                           grid_id as gridId,
                           'LTE' as netWorkType
                      from (select tc.*, tl.latitude, tl.longitude
                              from (select *
                                      from (select t.city_name,
                                                   t.county_name,
                                                   t.grid_id,
                                                   t. LTE_CI,
                                                   t. LTE_TAC,
                                                   nvl(t.grid_longitude, 0) grid_longitude,
                                                   nvl(t.grid_latitude, 0) grid_latitude,
                                                   count(LTE_CI) /
                                                   (select count(grid_id)
                                                      from SJCL.measured_data a
                                                     where a.grid_id = t.grid_id
                                                       and a.is_macro_station = 1
                                                       and terminal_upload_time >=
                                                           to_date('2017-12-02 00:00:00',
                                                                   'yyyy/mm/dd hh24:mi:ss')
                                                       and terminal_upload_time <=
                                                           to_date('2017-12-02 23:59:59',
                                                                   'yyyy/mm/dd hh24:mi:ss')) as ci_ratio
                                              from SJCL.measured_data t
                                             where t.grid_id is not null
                                               and t.is_macro_station = 1
                                               and t.grid_longitude is not null
                                               and t.grid_latitude is not null
                                               and LTE_CI is not null
                                               and LTE_TAC is not null
                                               and terminal_upload_time >=
                                                   to_date('2017-12-02 00:00:00',
                                                           'yyyy/mm/dd hh24:mi:ss')
                                               and terminal_upload_time <=
                                                   to_date('2017-12-02 23:59:59',
                                                           'yyyy/mm/dd hh24:mi:ss')
                                             group by t.city_name,
                                                      t.county_name,
                                                      t.grid_id,
                                                      t.               LTE_CI,
                                                      t.               LTE_TAC,
                                                      t.grid_longitude,
                                                      t.grid_latitude)
                                     where ci_ratio > 0.6) tc,
                                   SJCL.tdl_cm_cell tl
                             where REGEXP_SUBSTR(tl.ci, '[^-]+', 1, 3) * 256 +
                                   REGEXP_SUBSTR(tl.ci, '[^-]+', 1, 4) = tc.lte_ci
                               and tl.ENBAJ08 = tc.lte_tac) tt
                     where exists (select *
                              from (select count(1) as siteNum
                                      from SJCL.tdl_cm_cell
                                     where region_name = tt.city_name
                                       and ((longitude >= tt.longitude and
                                           longitude < tt.grid_longitude) or
                                           (longitude < tt.longitude and
                                           longitude >= tt.grid_longitude))
                                       and ((latitude >= tt.latitude and
                                           longitude < tt.grid_latitude) or
                                           (latitude < tt.latitude and
                                           latitude >= tt.grid_latitude)))
                             where siteNum > 4)) row_
             where rownum <= 100)
     where rownum_ >= 90

    执行计划如下:

    Plan Hash Value  : 4283313742 
    
    ----------------------------------------------------------------------------------------------------
    | Id   | Operation                        | Name          | Rows   | Bytes    | Cost    | Time     |
    ----------------------------------------------------------------------------------------------------
    |    0 | SELECT STATEMENT                 |               |    100 |    41200 | 8556469 | 00:05:35 |
    |  * 1 |   VIEW                           |               |    100 |    41200 | 8556469 | 00:05:35 |
    |  * 2 |    COUNT STOPKEY                 |               |        |          |         |          |
    |    3 |     VIEW                         |               |   2983 |  1190217 | 8556469 | 00:05:35 |
    |    4 |      UNION-ALL                   |               |        |          |         |          |
    |    5 |       HASH GROUP BY              |               |    994 |    10934 |    1106 | 00:00:01 |
    |    6 |        VIEW                      |               |    994 |    10934 |    1106 | 00:00:01 |
    |  * 7 |         FILTER                   |               |        |          |         |          |
    |    8 |          HASH GROUP BY           |               |    994 |    25844 |    1106 | 00:00:01 |
    |    9 |           PARTITION RANGE SINGLE |               |  19877 |   516802 |    1104 | 00:00:01 |
    | * 10 |            TABLE ACCESS FULL     | MEASURED_DATA |  19877 |   516802 |    1104 | 00:00:01 |
    | * 11 |       FILTER                     |               |        |          |         |          |
    |   12 |        SORT AGGREGATE            |               |      1 |       15 |         |          |
    |   13 |         PARTITION RANGE SINGLE   |               |     16 |      240 |    1104 | 00:00:01 |
    | * 14 |          TABLE ACCESS FULL       | MEASURED_DATA |     16 |      240 |    1104 | 00:00:01 |
    |   15 |        HASH GROUP BY             |               |    994 |    13916 | 8551024 | 00:05:35 |
    |   16 |         VIEW                     |               |  19877 |   278278 | 8551024 | 00:05:35 |
    |   17 |          HASH GROUP BY           |               |  19877 |   775203 | 8551024 | 00:05:35 |
    |   18 |           PARTITION RANGE SINGLE |               |  19877 |   775203 |    1104 | 00:00:01 |
    | * 19 |            TABLE ACCESS FULL     | MEASURED_DATA |  19877 |   775203 |    1104 | 00:00:01 |
    |   20 |       HASH GROUP BY              |               |    994 |    10934 |    1106 | 00:00:01 |
    |   21 |        VIEW                      |               |    994 |    10934 |    1106 | 00:00:01 |
    | * 22 |         FILTER                   |               |        |          |         |          |
    |   23 |          HASH GROUP BY           |               |    994 |    28826 |    1106 | 00:00:01 |
    |   24 |           PARTITION RANGE SINGLE |               |  19877 |   576433 |    1104 | 00:00:01 |
    | * 25 |            TABLE ACCESS FULL     | MEASURED_DATA |  19877 |   576433 |    1104 | 00:00:01 |
    | * 26 |       FILTER                     |               |        |          |         |          |
    |   27 |        HASH GROUP BY             |               |      1 |       91 |    3233 | 00:00:01 |
    | * 28 |         FILTER                   |               |        |          |         |          |
    | * 29 |          HASH JOIN               |               |     59 |     5369 |    2168 | 00:00:01 |
    |   30 |           PARTITION RANGE SINGLE |               |    387 |    16641 |    1104 | 00:00:01 |
    | * 31 |            TABLE ACCESS FULL     | MEASURED_DATA |    387 |    16641 |    1104 | 00:00:01 |
    |   32 |           TABLE ACCESS FULL      | TDL_CM_CELL   | 216734 | 10403232 |    1063 | 00:00:01 |
    |   33 |          VIEW                    |               |      1 |          |    1064 | 00:00:01 |
    | * 34 |           FILTER                 |               |        |          |         |          |
    |   35 |            SORT AGGREGATE        |               |      1 |       18 |         |          |
    | * 36 |             TABLE ACCESS FULL    | TDL_CM_CELL   |      1 |       18 |    1064 | 00:00:01 |
    |   37 |        SORT AGGREGATE            |               |      1 |       15 |         |          |
    |   38 |         PARTITION RANGE SINGLE   |               |     16 |      240 |    1104 | 00:00:01 |
    | * 39 |          TABLE ACCESS FULL       | MEASURED_DATA |     16 |      240 |    1104 | 00:00:01 |
    ----------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ------------------------------------------
    * 1 - filter("ROWNUM_">=90)
    * 2 - filter(ROWNUM<=100)
    * 7 - filter(CASE WHEN (SUM(CASE WHEN TO_NUMBER("LTE_RSRP")>(-105) THEN 1 ELSE 0 END )>0 AND COUNT("GRID_ID")>0 AND SUM(CASE WHEN TO_NUMBER("LTE_RSRP")>(-105) THEN 1 ELSE 0 END
      )/COUNT("GRID_ID")<0.95) THEN 1 ELSE 0 END >=1)
    * 10 - filter("GRID_ID" IS NOT NULL AND "IS_MACRO_STATION"=1 AND "TERMINAL_UPLOAD_TIME"<=TO_DATE(' 2017-12-02 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
    * 11 - filter(SUM("GR_CI")>=3)
    * 14 - filter("GRID_ID"=:B1 AND "IS_MACRO_STATION"=1 AND "TERMINAL_UPLOAD_TIME"<=TO_DATE(' 2017-12-02 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
    * 19 - filter("T1"."GRID_ID" IS NOT NULL AND "T1"."IS_MACRO_STATION"=1 AND "TERMINAL_UPLOAD_TIME"<=TO_DATE(' 2017-12-02 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
    * 22 - filter(CASE WHEN (SUM(CASE WHEN (TO_NUMBER("LTE_RSRP")>(-100) AND TO_NUMBER("LTE_SINR")<0) THEN 1 ELSE 0 END )>0 AND COUNT("GRID_ID")>0 AND SUM(CASE WHEN (TO_NUMBER("LTE_RSRP")>(-100) AND
      TO_NUMBER("LTE_SINR")<0) THEN 1 ELSE 0 END )/COUNT("GRID_ID")>0.05) THEN 1 ELSE 0 END >=1)
    * 25 - filter("GRID_ID" IS NOT NULL AND "IS_MACRO_STATION"=1 AND "TERMINAL_UPLOAD_TIME"<=TO_DATE(' 2017-12-02 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
    * 26 - filter(COUNT("LTE_CI")/ (SELECT COUNT("GRID_ID") FROM "SJCL"."MEASURED_DATA" "A" WHERE "A"."GRID_ID"=:B1 AND "A"."IS_MACRO_STATION"=1 AND "TERMINAL_UPLOAD_TIME"<=TO_DATE(' 2017-12-02
      23:59:59', 'syyyy-mm-dd hh24:mi:ss'))>0.6)
    * 28 - filter( EXISTS (SELECT 0 FROM (SELECT COUNT(*) "SITENUM" FROM "SJCL"."TDL_CM_CELL" "TDL_CM_CELL" WHERE "REGION_NAME"=:B1 AND ("LONGITUDE">=:B2 AND "LONGITUDE"<TO_NUMBER(:B3) OR "LONGITUDE"<:B4
      AND "LONGITUDE">=TO_NUMBER(:B5)) AND ("LATITUDE">=:B6 AND "LONGITUDE"<TO_NUMBER(:B7) OR "LATITUDE"<:B8 AND "LATITUDE">=TO_NUMBER(:B9)) HAVING COUNT(*)>4) "from$_subquery$_021"))
    * 29 - access(TO_NUMBER( REGEXP_SUBSTR ("TL"."CI",'[^-]+',1,3))*256+TO_NUMBER( REGEXP_SUBSTR ("TL"."CI",'[^-]+',1,4))=TO_NUMBER("T"."LTE_CI") AND "TL"."ENBAJ08"=TO_NUMBER("T"."LTE_TAC"))
    * 31 - filter("T"."GRID_ID" IS NOT NULL AND "T"."GRID_LONGITUDE" IS NOT NULL AND "T"."GRID_LATITUDE" IS NOT NULL AND "LTE_CI" IS NOT NULL AND "LTE_TAC" IS NOT NULL AND "T"."IS_MACRO_STATION"=1 AND
      "TERMINAL_UPLOAD_TIME"<=TO_DATE(' 2017-12-02 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
    * 34 - filter(COUNT(*)>4)
    * 36 - filter("REGION_NAME"=:B1 AND ("LONGITUDE">=:B2 AND "LONGITUDE"<TO_NUMBER(:B3) OR "LONGITUDE"<:B4 AND "LONGITUDE">=TO_NUMBER(:B5)) AND ("LATITUDE">=:B6 AND "LONGITUDE"<TO_NUMBER(:B7) OR
      "LATITUDE"<:B8 AND "LATITUDE">=TO_NUMBER(:B9)))
    * 39 - filter("A"."GRID_ID"=:B1 AND "A"."IS_MACRO_STATION"=1 AND "TERMINAL_UPLOAD_TIME"<=TO_DATE(' 2017-12-02 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))

    卡住,结果无法出来。问题在于:
    1.多个不当扫描measured_data

    2.不当查询tdl_cm_cell,简单而然即nest loop+full scan

    3.不当使用子查询

    这肯定是非专业人士写的。

    改造思路

    1.减少表扫描次数,尽量一次,可以利用with和group by达到

    2.消除子查询,改为join之类

    3.对必要的表创建索引

    修改之后

    with x1 as
     (select count(grid_id) as grid_count, grid_id
        from SJCL.measured_data
       where IS_MACRO_STATION = 1
         and terminal_upload_time >=
             to_date('2017-12-03 00:00:00', 'yyyy/mm/dd hh24:mi:ss')
         and terminal_upload_time <=
             to_date('2017-12-03 23:59:59', 'yyyy/mm/dd hh24:mi:ss')
       group by grid_id),
    s1 as
     (select city_name,
             county_name,
             grid_id,
             sum(rsrp_value_100) rsrpSum_100,
             sum(rsrp_value_105) rsrpSum_105,
             count(grid_id) counts
        from ( --3
              select case
                        when LTE_RSRP > -100 and LTE_SINR < 0 then
                         1
                        else
                         0
                      end rsrp_value_100,
                      case
                        when LTE_RSRP > -105 then
                         1
                        else
                         0
                      end rsrp_value_105,
                      city_name,
                      county_name,
                      grid_id
                from SJCL.measured_data
               where IS_MACRO_STATION = 1
                 and grid_id is not null
                 and terminal_upload_time >=
                     to_date('2017-12-03 00:00:00', 'yyyy/mm/dd hh24:mi:ss')
                 and terminal_upload_time <=
                     to_date('2017-12-03 23:59:59', 'yyyy/mm/dd hh24:mi:ss')) t1
       group by t1.city_name, t1.county_name, t1.grid_id),
    vw_ttl as
     (select t.city_name,
             t.county_name,
             t.grid_id,
             t. LTE_CI,
             t. LTE_TAC,
             t.grid_longitude grid_longitude,
             t.grid_latitude grid_latitude,
             count(case
                     when t.network_type = 'LTE' then
                      t.lte_ci
                     when t.network_type = 'GSM' then
                      t.gsm_cid
                     when t.network_type = 'TD' then
                      t.td_cid
                     else
                      null
                   end) ci_count,
             x1.grid_count,
             case
               when x1.grid_count = 0 then
                0
               else
                count(LTE_CI) / x1.grid_count
             end as ci_ratio,
             grouping_id(t.LTE_TAC) as gid
        from SJCL.measured_data t
        join x1
          on x1.grid_id = t.grid_id
       where t.grid_id is not null
         and t.is_macro_station = 1
            --and t.city_name='宁德' --and t.county_name='霞浦县'
         and terminal_upload_time >=
             to_date('2017-12-03 00:00:00', 'yyyy/mm/dd hh24:mi:ss')
         and terminal_upload_time <=
             to_date('2017-12-03 23:59:59', 'yyyy/mm/dd hh24:mi:ss')
       group by grouping sets((t.city_name, t.county_name, t.grid_id, t.LTE_CI, x1.grid_count, t.LTE_TAC, t.grid_longitude, t.grid_latitude),(t.city_name, t.county_name, t.grid_id, t.LTE_CI, x1.grid_count)))
    select *
      from (select row_.*, rownum rownum_
              from (select '弱覆盖' as questionType,
                           city_name as cityName,
                           county_name as countyName,
                           grid_id as gridId,
                           'LTE' as netWorkType
                      from (select city_name,
                                   county_name,
                                   grid_id,
                                   case
                                     when rsrpSum > 0 and counts > 0 and
                                          rsrpSum / counts < 0.95 then
                                      1
                                     else
                                      0
                                   end rfgSum
                              from (select city_name,
                                           county_name,
                                           grid_id,
                                           rsrpsum_105 as rsrpSum,
                                           counts
                                      from s1))
                     where rfgSum >= 1
                     group by city_name, county_name, grid_id
                    union all --e1
                    select '无主控覆盖' as questionType,
                           city_name as cityName,
                           county_name countyName,
                           grid_id gridId,
                           'LTE' as netWorkType
                      from (select case
                                     when ci_count / grid_count > 0.3 then
                                      1
                                     else
                                      0
                                   end gr_ci,
                                   city_name,
                                   county_name,
                                   grid_id,
                                   LTE_CI
                              from vw_ttl
                             where gid = 1)
                     group by city_name, county_name, grid_id
                    having sum(gr_ci) >= 3
                    union all --e2
                    select '质差' as questionType,
                           city_name as cityName,
                           county_name as countyName,
                           grid_id as gridId,
                           'LTE' as netWorkType
                      from (select city_name,
                                   county_name,
                                   grid_id,
                                   case
                                     when rsrpSum > 0 and counts > 0 and
                                          rsrpSum / counts > 0.05 then
                                      1
                                     else
                                      0
                                   end rfgSum
                              from (select city_name,
                                           county_name,
                                           grid_id,
                                           rsrpsum_100 as rsrpSum,
                                           counts
                                      from s1))
                     where rfgSum >= 1
                     group by city_name, county_name, grid_id
                    
                    union all --e3 
                    select '越区覆盖' as questionType,
                           city_name as cityName,
                           county_name as countyName,
                           grid_id as gridId,
                           'LTE' as netWorkType
                      from (select tc.*, tl.latitude, tl.longitude
                              from (select city_name,
                                           county_name,
                                           grid_id,
                                           LTE_CI,
                                           LTE_TAC,
                                           grid_longitude,
                                           grid_latitude,
                                           ci_ratio
                                      from vw_ttl
                                     where gid = 0
                                       and ci_ratio > 0.6) tc,
                                   SJCL.tdl_cm_cell tl
                             where tc.lte_ci = to_char(tl.eci)
                               and tl.ENBAJ08 = tc.lte_tac) tt
                     where (select /*+index(s IDX_TDL_CM_CELL_CITYNAME) */
                             count(1) as siteNum
                              from SJCL.tdl_cm_cell s
                             where region_name = tt.city_name
                               and ((longitude >= tt.longitude and
                                   longitude < tt.grid_longitude) or
                                   (longitude < tt.longitude and
                                   longitude >= tt.grid_longitude))
                               and ((latitude >= tt.latitude and
                                   longitude < tt.grid_latitude) or
                                   (latitude < tt.latitude and
                                   latitude >= tt.grid_latitude))) > 4
                    --e4                               
                    ) row_
             where rownum <= 100)
     where rownum_ >= 1

    计划如下:

     Plan Hash Value  : 3577282419 
    
    -------------------------------------------------------------------------------------------------------------
    | Id   | Operation                      | Name                        | Rows   | Bytes    | Cost | Time     |
    -------------------------------------------------------------------------------------------------------------
    |    0 | SELECT STATEMENT               |                             |    100 |    41200 | 5905 | 00:00:01 |
    |    1 |   TEMP TABLE TRANSFORMATION    |                             |        |          |      |          |
    |    2 |    LOAD AS SELECT              | SYS_TEMP_0FDA2B063_13545153 |        |          |      |          |
    |    3 |     HASH GROUP BY              |                             |  18603 |   576693 |  554 | 00:00:01 |
    |    4 |      PARTITION RANGE SINGLE    |                             |  18603 |   576693 |  553 | 00:00:01 |
    |  * 5 |       TABLE ACCESS FULL        | MEASURED_DATA               |  18603 |   576693 |  553 | 00:00:01 |
    |    6 |    LOAD AS SELECT              | SYS_TEMP_0FDA2B064_13545153 |        |          |      |          |
    |    7 |     SORT GROUP BY ROLLUP       |                             |  38788 |  2831524 | 1782 | 00:00:01 |
    |  * 8 |      HASH JOIN                 |                             |  38788 |  2831524 | 1110 | 00:00:01 |
    |    9 |       VIEW                     |                             |   8799 |   158382 |  557 | 00:00:01 |
    |   10 |        HASH GROUP BY           |                             |   8799 |   140784 |  557 | 00:00:01 |
    |   11 |         PARTITION RANGE SINGLE |                             |  71006 |  1136096 |  553 | 00:00:01 |
    | * 12 |          TABLE ACCESS FULL     | MEASURED_DATA               |  71006 |  1136096 |  553 | 00:00:01 |
    |   13 |       PARTITION RANGE SINGLE   |                             |  18603 |  1023165 |  553 | 00:00:01 |
    | * 14 |        TABLE ACCESS FULL       | MEASURED_DATA               |  18603 |  1023165 |  553 | 00:00:01 |
    | * 15 |    VIEW                        |                             |    100 |    41200 | 3568 | 00:00:01 |
    | * 16 |     COUNT STOPKEY              |                             |        |          |      |          |
    |   17 |      VIEW                      |                             |  39189 | 15636411 | 3568 | 00:00:01 |
    |   18 |       UNION-ALL                |                             |        |          |      |          |
    |   19 |        HASH GROUP BY           |                             |  18603 |   725517 |   25 | 00:00:01 |
    | * 20 |         VIEW                   |                             |  18603 |   725517 |   23 | 00:00:01 |
    |   21 |          TABLE ACCESS FULL     | SYS_TEMP_0FDA2B063_13545153 |  18603 |   576693 |   23 | 00:00:01 |
    | * 22 |        FILTER                  |                             |        |          |      |          |
    |   23 |         HASH GROUP BY          |                             |   1940 |   100880 |  110 | 00:00:01 |
    | * 24 |          VIEW                  |                             |  38788 |  2016976 |  107 | 00:00:01 |
    |   25 |           TABLE ACCESS FULL    | SYS_TEMP_0FDA2B064_13545153 |  38788 |  2831524 |  107 | 00:00:01 |
    |   26 |        HASH GROUP BY           |                             |  18603 |   725517 |   25 | 00:00:01 |
    | * 27 |         VIEW                   |                             |  18603 |   725517 |   23 | 00:00:01 |
    |   28 |          TABLE ACCESS FULL     | SYS_TEMP_0FDA2B063_13545153 |  18603 |   576693 |   23 | 00:00:01 |
    | * 29 |        FILTER                  |                             |        |          |      |          |
    | * 30 |         HASH JOIN              |                             |     43 |    41022 | 3279 | 00:00:01 |
    |   31 |          TABLE ACCESS FULL     | TDL_CM_CELL                 | 216734 |  5418350 | 1063 | 00:00:01 |
    | * 32 |          VIEW                  |                             |  38788 | 36034052 |  107 | 00:00:01 |
    |   33 |           TABLE ACCESS FULL    | SYS_TEMP_0FDA2B064_13545153 |  38788 |  2831524 |  107 | 00:00:01 |
    |   34 |         SORT AGGREGATE         |                             |      1 |       18 |      |          |
    |   35 |          CONCATENATION         |                             |        |          |      |          |
    | * 36 |           INDEX RANGE SCAN     | IDX_TDL_CM_CELL_CITYNAME    |      1 |       18 |    3 | 00:00:01 |
    | * 37 |           INDEX RANGE SCAN     | IDX_TDL_CM_CELL_CITYNAME    |      1 |       18 |    3 | 00:00:01 |
    -------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ------------------------------------------
    * 5 - filter("GRID_ID" IS NOT NULL AND "IS_MACRO_STATION"=1 AND "TERMINAL_UPLOAD_TIME"<=TO_DATE(' 2017-12-03 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
    * 8 - access("X1"."GRID_ID"="T"."GRID_ID")
    * 12 - filter("IS_MACRO_STATION"=1 AND "TERMINAL_UPLOAD_TIME"<=TO_DATE(' 2017-12-03 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
    * 14 - filter("T"."GRID_ID" IS NOT NULL AND "T"."IS_MACRO_STATION"=1 AND "T"."TERMINAL_UPLOAD_TIME"<=TO_DATE(' 2017-12-03 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
    * 15 - filter("ROWNUM_">=1)
    * 16 - filter(ROWNUM<=100)
    * 20 - filter(CASE WHEN ("RSRPSUM_105">0 AND "COUNTS">0 AND "RSRPSUM_105"/"COUNTS"<0.95) THEN 1 ELSE 0 END >=1)
    * 22 - filter(SUM(CASE WHEN "CI_COUNT"/"GRID_COUNT">0.3 THEN 1 ELSE 0 END )>=3)
    * 24 - filter("GID"=1)
    * 27 - filter(CASE WHEN ("RSRPSUM_100">0 AND "COUNTS">0 AND "RSRPSUM_100"/"COUNTS">0.05) THEN 1 ELSE 0 END >=1)
    * 29 - filter( (SELECT /*+ INDEX ("S" "IDX_TDL_CM_CELL_CITYNAME") */ COUNT(*) FROM "SJCL"."TDL_CM_CELL" "S"<not feasible>)
    * 30 - access("LTE_CI"=TO_CHAR("TL"."ECI") AND "TL"."ENBAJ08"=TO_NUMBER("LTE_TAC"))
    * 32 - filter("GID"=0 AND "CI_RATIO">0.6)
    * 36 - access("REGION_NAME"=:B1 AND "LONGITUDE">=TO_NUMBER(:B2) AND "LONGITUDE"<:B3)
    * 36 - filter("LATITUDE">=:B1 AND "LONGITUDE"<TO_NUMBER(:B2) OR "LATITUDE"<:B3 AND "LATITUDE">=TO_NUMBER(:B4))
    * 37 - access("REGION_NAME"=:B1 AND "LONGITUDE">=:B2 AND "LONGITUDE"<TO_NUMBER(:B3))
    * 37 - filter(("LATITUDE">=:B1 AND "LONGITUDE"<TO_NUMBER(:B2) OR "LATITUDE"<:B3 AND "LATITUDE">=TO_NUMBER(:B4)) AND (LNNVL("LONGITUDE"<:B5) OR LNNVL("LONGITUDE">=TO_NUMBER(:B6))))

    结果:1秒内出现结果
    效率提升几千倍!

    所以,把专业的事情给专业的人做很重要。做前端开发的并不擅长数据库设计和SQL编写。

  • 相关阅读:
    0/1背包问题
    假如爱有天意(中文版)
    tomcat集群
    分布式锁
    centos7安装Harbor(转载)
    isEmpty和isBlank的区别
    单体应用架构和分布式架构的比较
    微服务鉴权
    mysql的时区设置
    RSA非对称加密算法
  • 原文地址:https://www.cnblogs.com/lzfhope/p/8081335.html
Copyright © 2020-2023  润新知