Oracle之SQL优化-通过分组和缓存减少不必要的读

共计 15857 个字符，预计需要花费 40 分钟才能阅读完成。

环境：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 编写。

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-12/149960.htm

Oracle之SQL优化-通过分组和缓存减少不必要的读

申请腾讯混元的API Key并且使用LobeChat调用混元AI

基于Docker快速搭建一个开源的IT人员在线工具箱-it-tools

Docker部署搭建一个开源强大的图书管理系统

让每个人都可以轻松使用Git-腾讯自研Git客户端

使用Docker部署开源的WPS-Office

案例：php利用淘宝IP库获取用户ip地理位置

linux下的网络设备文件配置

CentOS+OpenVZ+Vtonf实现Linux虚拟化

Linux下Docker安装配置

Mariadb学习总结（七）：单表查询

	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'))

	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))))