View viel langsamer als im Oracle. Umwandeln von "(+)"

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

Der erste join zwischen ‘prod a’ und ‘sched c’ ist im Original kein left outer sondern ein direkter join (inner join).
The first join between ‘prod a’ and ‘sched c’ isn’t a left outer one in the original form, but a plain join (inner join).

Da ein Outer join viel größere Datenmengen ausgeben kann, scheint es mit gut möglich, dass er viel langsamer ist.
Since outer join could produce a lot more data output, it seems resonable to me, that it is much slower.

Hi,

FROM Prod A
inner 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.PROD_DATE) = date_trunc(‘day’,A.PROD_DATE) )

Funktioniert nicht Nicht einmal langsam aber hängen.
doesn’t work. select not even slow but hang on.


Thank you,

caveman

Ok, with the given information I can’t help You at this point.
Mit den vorliegenden Infos kann ich nicht weiterhelfen.

Performance and consistent results for a given select statement can’t get determined only by the statement itself.
You need complete DDL of related tables, including key, constraint and index definition.
You need explain plan results, which indicate usage of indexing (which isn’t deterministic by statement and ddl, indexing)
You could offer some statistics about table data size or affected record counts per table, which is a bit like 'sorry I don’t have explain analyze, but at least these counts…
plus
You could provide some nice(!) formating which should be helpful against ‘eye cancer’ and attract more community members than some choked out snippels in ultra wide screen layout.

Performanz und konstistente Ergebnisse für ein Select Statement können nicht durch das Statement selbst bestimmt werden.
Man braucht das komplette DDL der verwendeten Tabellen, inklusive Schlüssel-, Beschränkungs- und Indexangaben.
Man braucht explain plan Ausgaben, die die Verwendung der Indexierung anzeigen (was sich nicht deterministisch verhält)
Man könnte einige Zahlen zum Mengengerüst liefern. Tabellengröße, Anzahl betroffener Datensätze …
und
Man könnte schön formatierte Statements liefern, gut gegen Augenkrebs und attraktiv für Forenmitglieder, die helfen könnten. Jedenfalls besser als hingekleisterte Statements im Breitbildformat.

formated Example of object in question (oracle version):

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'))

First of all, I apologize for ‘eye cancer’ formatting…
Ok, I will prepare all you have suggested and I will be back.

Zuerst entschuldige ich mich für “eye cancer” Formatierung …
Ok, ich werde alles vorbereiten, was du vorgeschlagen hast und ich bin wieder da.

Thank you,
caveman

Ok, we’ll see! :wink:
It’s nothing to worry about. At least I like it far more, and I’m convinced, a pretty looking code is most attractive to others and more important, most understandable, also towards You.
So why not give it a try and start getting used to it?! And I missed to mention in previous post, it is -hopefully- available by Your favorite tool, or if not, by a set of online formatters.