Hi All,
Actually many views frommy db works so fast on postgresql as fast in Oracle. These views are (in sql context - i dont know how to say) similar to view I have posted week ago.
But Ihave another one guy which is much slower on postgres. I can see that is different in SQL. It contajns “(+)” which after googling I have
translated to (left outer join).
CREATE OR REPLACE VIEW vw_days_of_prod (selected, gen, line, hatch, NM, CD) AS SELECT A.SELECTED, A.GEN, A.LINE, A.HATCH,
NVL(B.Non_Mating, 0) NM,
SUM((CASE WHEN TRUNC(A.PDATE,‘DDD’) <=LEAST( NVL(C.SDATE, SYSDATE), NVL(C.EDATE, SYSDATE)) THEN 1 ELSE 0 END))
OVER (PARTITION BY A.SELECTED, A.GEN, A.LINE, A.HATCH ORDER BY A.PDATE ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) CD
FROM Prod A, SCHED C, sDays B
WHERE A.SELECTED = C.SELECTED AND A.GEN = C.GEN AND A.LINE = C.LINE AND A.HATCH = C.HATCH AND
(A.SELECTED = B.SELECTED(+) AND A.GEN = B.GEN(+) AND A.LINE = B.LINE(+) AND
A.HATCH = B.HATCH(+) AND TRUNC(A.PDATE,‘DDD’) = TRUNC(B.PDATE(+), ‘DDD’) )
CREATE OR REPLACE VIEW vw_days_of_prod (selected, gen, line, hatch,NM, CD) AS SELECT A.SELECTED, A.GEN, A.LINE, A.HATCH,
coalesce(B.Non_Mating, 0) NM,
SUM((CASE WHEN date_trunc(‘day’,A.PDATE) <=LEAST( coalesce(C.SDATE, localtimestamp), coalesce(C.EDATE, localtimestamp)) THEN 1 ELSE 0 END))
OVER (PARTITION BY A.SELECTED, A.GEN, A.LINE, A.HATCH ORDER BY A.PDATE ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) CD
FROM Prod A
left outer join sched C on (c.SELECTED = a.SELECTED AND c.GEN = a.GEN AND c.LINE = a.LINE AND c.HATCH = a.HATCH)
left outer join sDays B on (B.SELECTED = A.SELECTED AND B.GEN = A.GEN AND B.LINE = A.LINE AND B.HATCH = A.HATCH AND date_trunc(‘day’,B.PDATE) = date_trunc(‘day’,A.PDATE) )
can you please take o look if I converted it properly?
all indexes and primary keys was succesfully generated.
Thank you,
caveman