Why the sql is not executed in parallel mode

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


 


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux