Why the sql is not executed in parallel mode, does the sql has some problem?
with sql1 as
(select a.*
from snaps a
where a.f_date between to_date('2018-03-05', 'yyyy-MM-dd') and
to_date('2018-03-11', 'yyyy-MM-dd')
),
sql2 as
(select '1' as pId, PM_TO as pValue, type_code as typeCode, version_no as versionNo,
bs as bs, l.order_rule as orderRule
from sql1, qfpl l
where PM_TO is not null
and l.pid = 1
union all
select '2' as pId,
PRTO as pValue,
type_code as typeCode, version_no as versionNo,
bs as bs, l.order_rule as orderRule
from sql1, qfpl l
where PRTO is not null
and l.pid = 2
union all
select '3' as pId,
PRATO as pValue,
type_code as typeCode, version_no as versionNo,
bs as bs, l.order_rule as orderRule
from sql1, qfpl l
where PRATO is not null
and l.pid = 3
),
sql4 as (
select typeCode, pId, orderRule, versionNo,
row_number() over(partition by pId, typeCode order by pValue) as rnn
from sql2
),
sql5 as (
select sql4.typeCode as typeCode,
sql4.pId as pId,
sql4.orderRule as orderRule,
t.pValue as pValue,
sql4.versionNo as versionNo
from sql4,
(select sql2.typeCode,sql2.pId,sql2.orderRule,
(case when sql2.orderRule = 1 then
PERCENTILE_DISC(0.05) WITHIN GROUP(ORDER BY sql2.pValue)
else
PERCENTILE_DISC(0.95) WITHIN GROUP(ORDER BY sql2.pValue)
end) as pValue,
(case when sql2.orderRule = 1 then
(case when round(count(1) * 0.05) - 1 < 0 then 1
else round(count(1) * 0.05)
end)
else
(case when round(count(1) * 0.95) - 1 < 0 then 1
else round(count(1) * 0.95)
end)
end) as rnn
from sql2
group by sql2.typeCode, sql2.pId, sql2.orderRule) t
where sql4.typeCode = t.typeCode
and sql4.pId = t.pId
and sql4.orderRule = t.orderRule
and sql4.rnn = t.rnn
),
sql6 as (
select sql2.pId, sql2.typeCode as typeCode, count(1) as fCount
from sql2, sql5
where sql2.pId = sql5.pId
and sql2.typeCode = sql5.typeCode
and ((sql2.orderRule = 2 and sql2.pValue >= sql5.pValue) or
(sql2.orderRule = 1 and sql2.pValue <= sql5.pValue))
and sql2.pId != '22'
group by sql2.pId, sql2.typeCode
union
select sql5.pId, sql5.typeCode, 0 as fCount
from sql5
where sql5.pId = '22'
group by sql5.pId, sql5.typeCode
)
select sql5.pId,
sql5.typeCode,
(case when sql5.pId = '22' then
(select p.d_chn
from qlp p
where p.version_no = sql5.versionNo
and p.cno = sql5.pValue
and (p.typeCode = sql5.typeCode or p.typeCode is null))
else
sql5.pValue || ''
end) pValue,
sql6.fCount,
(case when d.delta = 'Y' then d.dy_val
else d.y_val
end) yVal,
(case when d.is_delta = 'Y' then d.dr_val
else d.r_val
end) rVal,
f.p_no pNo,
f.p_name ||(case when f.unit = '' then ''
else '('|| f.unit ||')'
end) pName,
f.pe_name || (case when f.unit = '' then ''
else '(' || f.unit || ')'
end) peName,
c.fp_name fpName,
f.order_rule as orderRule,
f.pflag pFlag,
f.pdesc as pDesc
from sql5, sql6, qfpl f, qpa d,qfp c
where sql5.pId = sql6.pId
and sql5.typeCode = sql6.typeCode
and sql5.pId = f.pid||''
and f.deleted = 0
and f.pid = d.pid
and sql5.typeCode = d.typeCode
and f.fp_id = c.fp_id
order by f.t_sort, c.fp_id,f.p_no
