環境：aix 7.1, oracle12.1.0.2 cdb
最適化前のSQL
select *
from (select row_.*, rownum rownum_
from (select '弱いカバレッジ' as question_type,
city as city_name,
district as county_name,
cell_id,
'LTE' as network_type
from (select city,
district,
cell_id,
case
when signal_sum > 0 and count > 0 and
signal_sum / count < 0.95 then
1
else
0
end coverage_flag
from (select city,
district,
cell_id,
sum(signal_strength) signal_sum,
count(cell_id) count
from (select case
when lte_signal_strength > -105 then
1
else
0
end signal_strength,
city,
district,
cell_id
from NETWORK_DATA.measurements
where is_macro_station = 1
and cell_id is not null
and upload_time >=
to_date('2017-12-02 00:00:00',
'yyyy/mm/dd hh24:mi:ss')
and upload_time <=
to_date('2017-12-02 23:59:59',
'yyyy/mm/dd hh24:mi:ss')) t1
group by t1.city,
t1.district,
t1.cell_id) t2) t3
where coverage_flag >= 1
group by city, district, cell_id
union all
select 'コントロールセルなし' as question_type,
city as city_name,
district county_name,
cell_id,
'LTE' as network_type
from (select case
when ci_ratio / cell_count > 0.3 then
1
else
0
end control_flag,
city,
district,
cell_id,
lte_ci
from (select count(case
when t1.network_type = 'LTE' then
t1.lte_ci
when t1.network_type = 'GSM' then
t1.gsm_ci
when t1.network_type = 'TD' then
t1.td_ci
else
null
end) ci_count,
(select count(cell_id)
from NETWORK_DATA.measurements
where cell_id = t1.cell_id
and is_macro_station = 1
and upload_time >=
to_date('2017-12-02 00:00:00',
'yyyy/mm/dd hh24:mi:ss')
and upload_time <=
to_date('2017-12-02 23:59:59',
'yyyy/mm/dd hh24:mi:ss')) cell_count,
t1.city,
t1.district,
t1.cell_id,
lte_ci
from NETWORK_DATA.measurements t1
where t1.cell_id is not null
and t1.is_macro_station = 1
and upload_time >=
to_date('2017-12-02 00:00:00',
'yyyy/mm/dd hh24:mi:ss')
and upload_time <=
to_date('2017-12-02 23:59:59',
'yyyy/mm/dd hh24:mi:ss')
group by t1.city,
t1.district,
t1.cell_id,
t1.lte_ci
) t2) t3
group by city, district, cell_id
having sum(control_flag) >= 3
union all
select '品質低下' as question_type,
city as city_name,
district as county_name,
cell_id,
'LTE' as network_type
from (select city,
district,
cell_id,
case
when signal_sum > 0 and count > 0 and
signal_sum / count > 0.05 then
1
else
0
end quality_flag
from (select city,
district,
cell_id,
sum(signal_strength) signal_sum,
count(cell_id) count
from (select case
when lte_signal_strength > -100 and
lte_sinr < 0 then
1
else
0
end signal_strength,
city,
district,
cell_id
from NETWORK_DATA.measurements
where is_macro_station = 1
and cell_id is not null
and upload_time >=
to_date('2017-12-02 00:00:00',
'yyyy/mm/dd hh24:mi:ss')
and upload_time <=
to_date('2017-12-02 23:59:59',
'yyyy/mm/dd hh24:mi:ss')) t1
group by t1.city,
t1.district,
t1.cell_id) t2) t3
where quality_flag >= 1
group by city, district, cell_id
union all
select 'セルオーバーラップ' as question_type,
city as city_name,
district as county_name,
cell_id,
'LTE' as network_type
from (select tc.*, tl.latitude, tl.longitude
from (select *
from (select t.city,
t.district,
t.cell_id,
t.lte_ci,
t.lte_tac,
nvl(t.cell_longitude, 0) cell_longitude,
nvl(t.cell_latitude, 0) cell_latitude,
count(lte_ci) /
(select count(cell_id)
from NETWORK_DATA.measurements a
where a.cell_id = t.cell_id
and a.is_macro_station = 1
and upload_time >=
to_date('2017-12-02 00:00:00',
'yyyy/mm/dd hh24:mi:ss')
and upload_time <=
to_date('2017-12-02 23:59:59',
'yyyy/mm/dd hh24:mi:ss')) as ci_ratio
from NETWORK_DATA.measurements t
where t.cell_id is not null
and t.is_macro_station = 1
and t.cell_longitude is not null
and t.cell_latitude is not null
and lte_ci is not null
and lte_tac is not null
and upload_time >=
to_date('2017-12-02 00:00:00',
'yyyy/mm/dd hh24:mi:ss')
and upload_time <=
to_date('2017-12-02 23:59:59',
'yyyy/mm/dd hh24:mi:ss')
group by t.city,
t.district,
t.cell_id,
t.lte_ci,
t.lte_tac,
t.cell_longitude,
t.cell_latitude)
where ci_ratio > 0.6) tc,
CELL_LOCATION_INFO.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 site_num
from CELL_LOCATION_INFO.tdl_cm_cell
where region_name = tt.city
and ((longitude >= tt.longitude and
longitude < tt.cell_longitude) or
(longitude < tt.longitude and
longitude >= tt.cell_longitude))
and ((latitude >= tt.latitude and
longitude < tt.cell_latitude) or
(latitude < tt.latitude and
latitude >= tt.cell_latitude)))
where site_num > 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 | MEASUREMENTS | 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 | MEASUREMENTS | 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 | MEASUREMENTS | 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 | MEASUREMENTS | 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 | MEASUREMENTS | 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 | MEASUREMENTS | 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_SIGNAL_STRENGTH")>(-105) THEN 1 ELSE 0 END )>0 AND COUNT("CELL_ID")>0 AND SUM(CASE WHEN TO_NUMBER("LTE_SIGNAL_STRENGTH")>(-105) THEN 1 ELSE 0 END
)/COUNT("CELL_ID")<0.95) THEN 1 ELSE 0 END >=1)
* 10 - filter("CELL_ID" IS NOT NULL AND "IS_MACRO_STATION"=1 AND "UPLOAD_TIME"<=TO_DATE(' 2017-12-02 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
* 11 - filter(SUM("CONTROL_FLAG")>=3)
* 14 - filter("CELL_ID"=:B1 AND "IS_MACRO_STATION"=1 AND "UPLOAD_TIME"<=TO_DATE(' 2017-12-02 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
* 19 - filter("T1"."CELL_ID" IS NOT NULL AND "T1"."IS_MACRO_STATION"=1 AND "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_SIGNAL_STRENGTH")>(-100) AND TO_NUMBER("LTE_SINR")<0) THEN 1 ELSE 0 END )>0 AND COUNT("CELL_ID")>0 AND SUM(CASE WHEN (TO_NUMBER("LTE_SIGNAL_STRENGTH")>(-100) AND
TO_NUMBER("LTE_SINR")<0) THEN 1 ELSE 0 END )/COUNT("CELL_ID")>0.05) THEN 1 ELSE 0 END >=1)
* 25 - filter("CELL_ID" IS NOT NULL AND "IS_MACRO_STATION"=1 AND "UPLOAD_TIME"<=TO_DATE(' 2017-12-02 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
* 26 - filter(COUNT("LTE_CI")/ (SELECT COUNT("CELL_ID") FROM "NETWORK_DATA"."MEASUREMENTS" "A" WHERE "A"."CELL_ID"=:B1 AND "A"."IS_MACRO_STATION"=1 AND "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(*) "SITE_NUM" FROM "CELL_LOCATION_INFO"."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"."CELL_ID" IS NOT NULL AND "T"."CELL_LONGITUDE" IS NOT NULL AND "T"."CELL_LATITUDE" IS NOT NULL AND "LTE_CI" IS NOT NULL AND "LTE_TAC" IS NOT NULL AND "T"."IS_MACRO_STATION"=1 AND
"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"."CELL_ID"=:B1 AND "A"."IS_MACRO_STATION"=1 AND "UPLOAD_TIME"<=TO_DATE(' 2017-12-02 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
このSQLは実行が停止し、結果が返ってきません。問題点は以下の通りです：
measurementsテーブルの不要な複数回スキャンtdl_cm_cellテーブルの非効率な問い合わせ（ネステッドループ + フルスキャン）- サブクエリの不適切な使用
これは、データベースの専門家でない人が作成したコードである可能性が高いです。
最適化のアプローチ
- テーブルスキャン回数を減らし、可能な限り1回にする（WITH句とGROUP BYを活用）
- サブクエリを排除し、JOINに置き換える
- 必要なテーブルにインデックスを作成する
最適化後
with cell_counts as
(select count(cell_id) as total_cells, cell_id
from NETWORK_DATA.measurements
where is_macro_station = 1
and upload_time >=
to_date('2017-12-03 00:00:00', 'yyyy/mm/dd hh24:mi:ss')
and upload_time <=
to_date('2017-12-03 23:59:59', 'yyyy/mm/dd hh24:mi:ss')
group by cell_id),
signal_aggregates as
(select city,
district,
cell_id,
sum(signal_strength_100) signal_sum_100,
sum(signal_strength_105) signal_sum_105,
count(cell_id) count
from ( -- 信号強度と品質の条件を計算
select case
when lte_signal_strength > -100 and lte_sinr < 0 then
1
else
0
end signal_strength_100,
case
when lte_signal_strength > -105 then
1
else
0
end signal_strength_105,
city,
district,
cell_id
from NETWORK_DATA.measurements
where is_macro_station = 1
and cell_id is not null
and upload_time >=
to_date('2017-12-03 00:00:00', 'yyyy/mm/dd hh24:mi:ss')
and upload_time <=
to_date('2017-12-03 23:59:59', 'yyyy/mm/dd hh24:mi:ss')) t1
group by t1.city, t1.district, t1.cell_id),
cell_analysis as
(select t.city,
t.district,
t.cell_id,
t.lte_ci,
t.lte_tac,
t.cell_longitude cell_longitude,
t.cell_latitude cell_latitude,
count(case
when t.network_type = 'LTE' then
t.lte_ci
when t.network_type = 'GSM' then
t.gsm_ci
when t.network_type = 'TD' then
t.td_ci
else
null
end) ci_count,
cell_counts.total_cells,
case
when cell_counts.total_cells = 0 then
0
else
count(lte_ci) / cell_counts.total_cells
end as ci_ratio,
grouping_id(t.lte_tac) as group_id
from NETWORK_DATA.measurements t
join cell_counts
on cell_counts.cell_id = t.cell_id
where t.cell_id is not null
and t.is_macro_station = 1
-- and t.city='Ningde' -- and t.district='Xiapu County'
and upload_time >=
to_date('2017-12-03 00:00:00', 'yyyy/mm/dd hh24:mi:ss')
and upload_time <=
to_date('2017-12-03 23:59:59', 'yyyy/mm/dd hh24:mi:ss')
group by grouping sets((t.city, t.district, t.cell_id, t.lte_ci, cell_counts.total_cells, t.lte_tac, t.cell_longitude, t.cell_latitude),(t.city, t.district, t.cell_id, t.lte_ci, cell_counts.total_cells)))
select *
from (select row_.*, rownum rownum_
from (select '弱いカバレッジ' as question_type,
city as city_name,
district as county_name,
cell_id,
'LTE' as network_type
from (select city,
district,
cell_id,
case
when signal_sum > 0 and count > 0 and
signal_sum / count < 0.95 then
1
else
0
end coverage_flag
from (select city,
district,
cell_id,
signal_sum_105 as signal_sum,
count
from signal_aggregates))
where coverage_flag >= 1
group by city, district, cell_id
union all -- e1
select 'コントロールセルなし' as question_type,
city as city_name,
district county_name,
cell_id,
'LTE' as network_type
from (select case
when ci_count / total_cells > 0.3 then
1
else
0
end control_flag,
city,
district,
cell_id,
lte_ci
from cell_analysis
where group_id = 1)
group by city, district, cell_id
having sum(control_flag) >= 3
union all -- e2
select '品質低下' as question_type,
city as city_name,
district as county_name,
cell_id,
'LTE' as network_type
from (select city,
district,
cell_id,
case
when signal_sum > 0 and count > 0 and
signal_sum / count > 0.05 then
1
else
0
end quality_flag
from (select city,
district,
cell_id,
signal_sum_100 as signal_sum,
count
from signal_aggregates))
where quality_flag >= 1
group by city, district, cell_id
union all -- e3
select 'セルオーバーラップ' as question_type,
city as city_name,
district as county_name,
cell_id,
'LTE' as network_type
from (select tc.*, tl.latitude, tl.longitude
from (select city,
district,
cell_id,
lte_ci,
lte_tac,
cell_longitude,
cell_latitude,
ci_ratio
from cell_analysis
where group_id = 0
and ci_ratio > 0.6) tc,
CELL_LOCATION_INFO.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 site_num
from CELL_LOCATION_INFO.tdl_cm_cell s
where region_name = tt.city
and ((longitude >= tt.longitude and
longitude < tt.cell_longitude) or
(longitude < tt.longitude and
longitude >= tt.cell_longitude))
and ((latitude >= tt.latitude and
longitude < tt.cell_latitude) or
(latitude < tt.latitude and
latitude >= tt.cell_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 | MEASUREMENTS | 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 | MEASUREMENTS | 71006 | 1136096 | 553 | 00:00:01 |
| 13 | PARTITION RANGE SINGLE | | 18603 | 1023165 | 553 | 00:00:01 |
| * 14 | TABLE ACCESS FULL | MEASUREMENTS | 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("CELL_ID" IS NOT NULL AND "IS_MACRO_STATION"=1 AND "UPLOAD_TIME"<=TO_DATE(' 2017-12-03 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
* 8 - access("CELL_COUNTS"."CELL_ID"="T"."CELL_ID")
* 12 - filter("IS_MACRO_STATION"=1 AND "UPLOAD_TIME"<=TO_DATE(' 2017-12-03 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
* 14 - filter("T"."CELL_ID" IS NOT NULL AND "T"."IS_MACRO_STATION"=1 AND "T"."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 ("SIGNAL_SUM_105">0 AND "COUNT">0 AND "SIGNAL_SUM_105"/"COUNT"<0.95) THEN 1 ELSE 0 END >=1)
* 22 - filter(SUM(CASE WHEN "CI_COUNT"/"TOTAL_CELLS">0.3 THEN 1 ELSE 0 END )>=3)
* 24 - filter("GROUP_ID"=1)
* 27 - filter(CASE WHEN ("SIGNAL_SUM_100">0 AND "COUNT">0 AND "SIGNAL_SUM_100"/"COUNT">0.05) THEN 1 ELSE 0 END >=1)
* 29 - filter( (SELECT /*+ INDEX ("S" "IDX_TDL_CM_CELL_CITYNAME") */ COUNT(*) FROM "CELL_LOCATION_INFO"."TDL_CM_CELL" "S"<not feasible>)
* 30 - access("LTE_CI"=TO_CHAR("TL"."ECI") AND "TL"."ENBAJ08"=TO_NUMBER("LTE_TAC"))
* 32 - filter("GROUP_ID"=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">=TO_NUMBER(: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作成に精通していない場合が多いです。