Добрый вечер. Помогите,пожалуйста,разобраться, почему код на sql в tode прогружается быстро , а в интерфейсе через zope ну ооочень долго. Проблема может быть только в самом коде.
with spec as
(
SELECT distinct IDTP, IDBASE, DECODE(s.DIRECTION, NULL, s.SPECIALITY, s.DIRECTION||DECODE(s.SPECIALITY, NULL, '', '/'||s.SPECIALITY))||' '||DECODE (S.NAMESPEC,null,S.NAMEDIRECT,S.NAMESPEC) as spec_code
from IVANOV.SPECIALITY_V s
) ,
sg as(
SELECT /*+ RULE*/ STUDENT, MAX(DECODE(PAY, 1, 1, 2, 1, 0)) PAY
FROM IVANOV.STUDENT_GROUP_V
GROUP BY STUDENT
),
sd as(
SELECT /*+ RULE*/ ID_STUDENT, MAX(DECODE(CHK_SIGNED, NULL, 0, 1)) CHK_SIGNED
FROM IVANOV.STUDENT_DOGOVORA_V
WHERE
((NVL(':p_date1', '0') = '0') OR (TO_DATE(':p_date1', 'dd.mm.yyyy') <= DOG_DATE)) AND
((NVL(':p_date2', '0') = '0') OR (TO_DATE(':p_date2', 'dd.mm.yyyy') >= DOG_DATE))
GROUP BY ID_STUDENT
),
sp as(
SELECT /*+ RULE*/ STUDENT, MAX(DECODE(RECIEVED_DATE, NULL, 0, 1)) RECIEVED_DATE
FROM IVANOV.STUDENT_PAYMENT_V sp
WHERE
((NVL(':p_date1', '0') = '0') OR (TO_DATE(':p_date1', 'dd.mm.yyyy') <= sp.RECIEVED_DATE)) AND
((NVL(':p_date2', '0') = '0') OR (TO_DATE(':p_date2', 'dd.mm.yyyy') >= sp.RECIEVED_DATE))
GROUP BY sp.STUDENT
),
namec as(
select St.STUDY_TYPE type, a.ID||'-'||C.ID as numb,DECODE(s.DIRECTION, NULL, s.SPECIALITY, s.DIRECTION||DECODE(s.SPECIALITY, NULL, '', '/'||s.SPECIALITY))||' '||DECODE (S.NAMESPEC,null,S.NAMEDIRECT,S.NAMESPEC) code, ET.NAME names,C.PLAN_PRIEM pcnt
from IVANOV.ABT_COMPETITIVEGROUPITEM_T st,IVANOV.SPECIALITY_V s,IVANOV.ABT_CAMPAIGNS_T a,IVANOV.ABT_COMPETITIVEGROUPS_T c, IVANOV.STUDENT_V t,IVANOV.EDUCATION_TYPE_T et
where st.ID=S.IDTP and
C.ID=st.COMPETITIVEGROUPID and
A.ID=C.CAMPAIGN_ID and
T.ID_SPECIALITY=s.idtp and
ET.ID=S.IDBASE
group by St.STUDY_TYPE,
a.ID||'-'||C.ID,DECODE(s.DIRECTION, NULL, s.SPECIALITY, s.DIRECTION||DECODE(s.SPECIALITY, NULL, '', '/'||s.SPECIALITY))||' '||DECODE (S.NAMESPEC,null,S.NAMEDIRECT,S.NAMESPEC),
ET.NAME,C.PLAN_PRIEM
),
am as(SELECT Id_student,passed FROM TABLE (CAST(IVANOV.ABITURIENT_INFO.EXAM_PASSED_PERIOD(TO_DATE(DECODE(':p_date1', '0', NULL, ':p_date1'), 'dd.mm.yyyy'), TO_DATE(DECODE(':p_date2', '0', NULL, ':p_date2'), 'dd.mm.yyyy')) AS IVANOV.ABT_EXAM_PASSED_TABLE)))
select
dc.code spec,
db.pl pcnt,
db.zav z_a, -- Подали заявления
db.vst vst,
dc.pln pl,
dc.z zav,
dc.dog dog,
dc.pay pay,
dc.nms,
decode(db.nb,null,decode(dc.nm,null,' ',dc.nm),decode(dc.nm,null,db.nb,db.nb||'/'||dc.nm)) numb,to_char(sysdate,'dd.mm.yyyy') dat
/* (case dc.nms when 'сред.' then 'Образовательные программы с полным сроком обучения'
when 'спо' then 'Сокращенные программы на базе СПО'
when 'высш.' then 'Сокращенные программы на базе ВПО'
end) nabaze */
from(
SELECT
namec.names as nms,
namec.numb nm,
namec.code code,
namec.pcnt pln,
SUM(1) z,
SUM(NVL(sd.CHK_SIGNED, 0)) dog, -- Заключили договор
SUM(NVL(sp.RECIEVED_DATE, 0)) PAY --оплатили квитанции
FROM
IVANOV.STUDENT_V s,
IVANOV.EDUCATION_TYPE_T et, spec, sd,sp,am,sg,namec
WHERE
namec.code=spec.spec_code and
ET.NAME=namec.names and
s.ID_SPECIALITY =spec.IDTP AND
spec.IDBASE=et.ID AND
s.ID = sg.STUDENT AND
s.ID = sd.ID_STUDENT (+) AND
s.ID = sp.STUDENT(+) and
s.ID = am.ID_STUDENT(+) and
namec.type='c' and
S.STUDENT_TYPE <> 'i' and
((NVL(:p_region, 0) = 0) OR (s.ID_REGION = :p_region)) AND
((NVL(':p_date1', '0') = '0') OR (TO_DATE(':p_date1', 'dd.mm.yyyy') <= TRUNC(s.DATE_INSERT, 'dd'))) AND
((NVL(':p_date2', '0') = '0') OR (TO_DATE(':p_date2', 'dd.mm.yyyy') >= TRUNC(s.DATE_INSERT, 'dd')))
GROUP BY
namec.names,
namec.code,
namec.pcnt,
namec.numb
ORDER BY namec.code) dc,
(SELECT
namec.code cod,
namec.pcnt pl,
SUM(1) zav, -- Подали заявления
sum(nvl(am.passed,0)) vst,
namec.numb nb
FROM
IVANOV.STUDENT_V s,
IVANOV.EDUCATION_TYPE_T et, spec, sd,sp,am,sg,namec
WHERE
namec.code=spec.spec_code and
ET.NAME=namec.names and
s.ID_SPECIALITY=spec.IDTP AND
spec.IDBASE=et.ID AND
s.ID = sg.STUDENT AND
s.ID = sd.ID_STUDENT (+) AND
s.ID = sp.STUDENT(+) and
s.ID = am.ID_STUDENT(+) and
namec.type='b' and
S.STUDENT_TYPE <> 'i' and
((NVL(:p_region, 0) = 0) OR (s.ID_REGION = :p_region)) AND
((NVL(':p_date1', '0') = '0') OR (TO_DATE(':p_date1', 'dd.mm.yyyy') <= TRUNC(s.DATE_INSERT, 'dd'))) AND
((NVL(':p_date2', '0') = '0') OR (TO_DATE(':p_date2', 'dd.mm.yyyy') >= TRUNC(s.DATE_INSERT, 'dd')))
GROUP BY
namec.pcnt,
namec.numb,
namec.code
ORDER BY namec.code)db
where dc.code=db.cod(+)
ORDER BY dc.nms desc,dc.code
в tode :p_date2, а не ':p_date2' прописываю.