SQL: DISTINCT und RRN?

[Akku]

Hallo Leute,

folgendes Problem wurde an mich heran getragen, konnte aber keine Erklärung dafür finden.

CREATE VIEW qtemp/abc AS SELECT DISTINCT
f2prod, f2fmg1, f2fmg2, f2fmg3, f2fmg4, f2fme1, f2fme2, f2fme3,
f2fme4, f2vkme, f2mein, f2mge1, f2mei1, f2mge2, f2mei2, f2mge3,
f2mei3, f2mge4, f2mei4,
DEC( (SELECT SUM(nemge0) FROM mne
WHERE nephas = '1' AND nestat = '1' AND neprod = a.f2prod
AND nelort = a.f2lort AND nemge1 = a.f2fmg1
AND nemge2 = a.f2fmg2 AND nemge3 = a.f2fmg3
AND nemge4 = a.f2fmg4), 15, 2) AS lbestand,
DEC( (SELECT SUM(jhbdmg - jhgymg) FROM pjhm1
WHERE jhvbnr = a.f2prod AND jhlort = a.f2lort
AND jhfmg1 = a.f2fmg1 AND jhfmg2 = a.f2fmg2
AND jhfmg3 = a.f2fmg3 AND jhfmg4 = a.f2fmg4), 15, 2)
AS abgang
FROM pf2m1 a WHERE f2kate = 'CRD' and f2prod = '11050'

1. Bei einfachem Aufruf (ohne Satznr.) : SELECT * FROM abc -> 3 Sätze

Produkt| F-Mge1| F-Mge2| Menge-3|ME3|Menge-4|ME4|LBest.|ABGANG
11050 | 42,000 | 80,000| 130,000|DM | 45,000|FLG| ---- | ----
11050 |152,000 |120,000| 130,000|DM | 45,000|FLG| ---- | ----
11050 |202,000 |120,000| 130,000|DM | 45,000|FLG| ---- | ----
******** Datenende ********

2. mit Satznummer: SELECT RRN(abc), abc.* FROM abc -> 3 Sätze (wie oben)

RRN|Produkt|F-Mge1 |F-Mge2
618| 11050 | 42,000| 80,000
608| 11050 |152,000|120,000
603| 11050 |202,000|120,000

Füge ich obiger View noch ein weiteres Feld hinzu, welches aus einer 3. Datei summiert wird:

, DEC( (SELECT SUM(vo3011) FROM vo3m1
WHERE vo3026 <> 'END' AND vo3092 <> 'J' AND vo3008 = a.f2prod
AND vo3041 = a.f2fmg1 AND vo3042 = a.f2fmg2
AND vo3039 = a.f2fmg3 AND vo3040 = a.f2fmg4), 15, 2) AS zugang

und rufe obige Selects auf, erhalte ich
1. ohne Satznummer: wie oben -> 3 Sätze
2. mit Satznummer : siehe unten

RRN |Produkt| F-Mge1| F-Mge2|LBest.|ABGANG|ZUGANG
603 | 11050 |202,000|120,000| ---- | ---- | ----
604 | 11050 |202,000|120,000| ---- | ---- | ----
605 | 11050 |202,000|120,000| ---- | ---- | ----
608 | 11050 |152,000|120,000| ---- | ---- | ----
609 | 11050 |152,000|120,000| ---- | ---- | ----
610 | 11050 |152,000|120,000| ---- | ---- | ----
618 | 11050 | 42,000| 80,000| ---- | ---- | ----
620 | 11050 | 42,000| 80,000| ---- | ---- | ----

Dies sind alle Sätze aus der Basis-Datei, die vorher durch das DISTINCT zusammengelegt wurden. Letzteres Resultat finde ich eigentlich gar nicht so verwunderlich. Was mich irritiert, sind die Abfragen, die nur 3 Sätze bringen. Sogar incl. einer konkreten Satznummer (in allen Fällen übrigens niedrigste Satznr.), obwohl ja auch hier bereits mehrere Sätze zusammengeflossen sind. Ist das lediglich Zufall?
Wieso reagiert die View-Abfrage mit Satznummer plötzlich unterschiedlich, nachdem die 3. Dateisumme dazugekommen ist?
Vielleicht liegt es ja auch einfach daran, dass im DISTINCT-Fall eine Satznummer eigentlich keinen Sinn macht und SQL deshalb nicht so recht damit umgehen kann?!

Für eine Erklärung wäre ich Euch sehr dankbar.

Viele Grüße, Akku

[BenderD]

Hallo,

was soll uns das jetzt sagen??? Wenn du das mit den Daten vor der Tastatur schon nicht verstehst, wie soll man dir da ohne Daten erklären ob und warum das falsch oder richtig ist??????
Da hilft nur eins:
Ergebnismenge mit DISTINCT vergleichen mit Ergebnismenge ohne DISTINCT

mfg

Dieter Bender

BTW: unter V5R3 gibt es da gelöste und ungelöste Probleme mit SELECT DISTINCT, da habe ich auch schon falsche Ergebnisse gesehen!!!


Hallo Leute,

folgendes Problem wurde an mich heran getragen, konnte aber keine Erklärung dafür finden.

CREATE VIEW qtemp/abc AS SELECT DISTINCT
f2prod, f2fmg1, f2fmg2, f2fmg3, f2fmg4, f2fme1, f2fme2, f2fme3,
f2fme4, f2vkme, f2mein, f2mge1, f2mei1, f2mge2, f2mei2, f2mge3,
f2mei3, f2mge4, f2mei4,
DEC( (SELECT SUM(nemge0) FROM mne
WHERE nephas = '1' AND nestat = '1' AND neprod = a.f2prod
AND nelort = a.f2lort AND nemge1 = a.f2fmg1
AND nemge2 = a.f2fmg2 AND nemge3 = a.f2fmg3
AND nemge4 = a.f2fmg4), 15, 2) AS lbestand,
DEC( (SELECT SUM(jhbdmg - jhgymg) FROM pjhm1
WHERE jhvbnr = a.f2prod AND jhlort = a.f2lort
AND jhfmg1 = a.f2fmg1 AND jhfmg2 = a.f2fmg2
AND jhfmg3 = a.f2fmg3 AND jhfmg4 = a.f2fmg4), 15, 2)
AS abgang
FROM pf2m1 a WHERE f2kate = 'CRD' and f2prod = '11050'

1. Bei einfachem Aufruf (ohne Satznr.) : SELECT * FROM abc -> 3 Sätze

Produkt| F-Mge1| F-Mge2| Menge-3|ME3|Menge-4|ME4|LBest.|ABGANG
11050 | 42,000 | 80,000| 130,000|DM | 45,000|FLG| ---- | ----
11050 |152,000 |120,000| 130,000|DM | 45,000|FLG| ---- | ----
11050 |202,000 |120,000| 130,000|DM | 45,000|FLG| ---- | ----
******** Datenende ********

2. mit Satznummer: SELECT RRN(abc), abc.* FROM abc -> 3 Sätze (wie oben)

RRN|Produkt|F-Mge1 |F-Mge2
618| 11050 | 42,000| 80,000
608| 11050 |152,000|120,000
603| 11050 |202,000|120,000

Füge ich obiger View noch ein weiteres Feld hinzu, welches aus einer 3. Datei summiert wird:

, DEC( (SELECT SUM(vo3011) FROM vo3m1
WHERE vo3026 <> 'END' AND vo3092 <> 'J' AND vo3008 = a.f2prod
AND vo3041 = a.f2fmg1 AND vo3042 = a.f2fmg2
AND vo3039 = a.f2fmg3 AND vo3040 = a.f2fmg4), 15, 2) AS zugang

und rufe obige Selects auf, erhalte ich
1. ohne Satznummer: wie oben -> 3 Sätze
2. mit Satznummer : siehe unten

RRN |Produkt| F-Mge1| F-Mge2|LBest.|ABGANG|ZUGANG
603 | 11050 |202,000|120,000| ---- | ---- | ----
604 | 11050 |202,000|120,000| ---- | ---- | ----
605 | 11050 |202,000|120,000| ---- | ---- | ----
608 | 11050 |152,000|120,000| ---- | ---- | ----
609 | 11050 |152,000|120,000| ---- | ---- | ----
610 | 11050 |152,000|120,000| ---- | ---- | ----
618 | 11050 | 42,000| 80,000| ---- | ---- | ----
620 | 11050 | 42,000| 80,000| ---- | ---- | ----

Dies sind alle Sätze aus der Basis-Datei, die vorher durch das DISTINCT zusammengelegt wurden. Letzteres Resultat finde ich eigentlich gar nicht so verwunderlich. Was mich irritiert, sind die Abfragen, die nur 3 Sätze bringen. Sogar incl. einer konkreten Satznummer (in allen Fällen übrigens niedrigste Satznr.), obwohl ja auch hier bereits mehrere Sätze zusammengeflossen sind. Ist das lediglich Zufall?
Wieso reagiert die View-Abfrage mit Satznummer plötzlich unterschiedlich, nachdem die 3. Dateisumme dazugekommen ist?
Vielleicht liegt es ja auch einfach daran, dass im DISTINCT-Fall eine Satznummer eigentlich keinen Sinn macht und SQL deshalb nicht so recht damit umgehen kann?!

Für eine Erklärung wäre ich Euch sehr dankbar.

Viele Grüße, Akku

[Fuerchau]

RRN ist bei Views sowieso nicht zu gebrauchen. Wozu soll diese auch gut sein ?
RRN geht auch immer zu lasten der Performance.

[Akku]

@Fuerchau:
Sofern ich in der View keine Sätze gruppiere (mit GROUP oder DISTINCT) kann ich doch wohl RRN() verwenden?!

@Dieter Bender

Hinweis:
Der Inhalt von „Summenfeld 3” ist für alle 3 bzw. 8 Sätze NULL (undefiniert, da keine Daten gefunden)

1. Select ohne DISTINCT ohne „Summenfeld 3“ -> 8 Sätze (siehe unterste Tabelle)
2. Select ohne DISTINCT mit „Summenfeld 3“ -> 8 Sätze (dito)

3. Select mit DISTINCT ohne „Summenfeld 3“ -> 3 Sätze (siehe erste/zweite Tabelle)
4. Select mit DISTINCT mit „Summenfeld 3“ -> 3 Sätze (dito)

Ergo : Kein Unterschied mit oder ohne „Summenfeld 3“


5. CREATE VIEW abc AS wie 3.
und danach SELECT rrn(abc), abc.* from abc -> 3 Sätze

6. CREATE VIEW abc AS wie 4.
und danach SELECT rrn(abc), abc.* from abc -> 8 Sätze

Ergo : Unterschied, ob mit oder ohne „Summenfeld 3“, obwohl im direkt ausgeführten Fall dasselbe Summenfeld keineswegs zu getrennten Sätzen führt (siehe 4.)

Was konkret sorgt also bei 6. dafür, dass die Sätze getrennt werden?!

RRN()? -> ABER: Dann müssten auch im 5. Fall acht Sätze herauskommen

Ermittlung „Summenfeld 3“? -> ABER: Wieso habe ich dann bei der interaktiven Ausführung (4. Fall) keine Trennung?

Die Art wie SQL im Augenblick arbeitet, finde ich jedenfalls unlogisch, zumal im ähnlich gelagerten „GROUP BY“-Fall obige Selects auf die Views auch verständlicherweise eine Fehlermeldung produzieren würden (sowohl 5. als auch 6.!).

Falls niemand eine andere Erklärung hat, werde ich den vorliegenden Fall also wirklich unter V5R3 ungelöste Probleme mit SELECT DISTINCT verbuchen.

Vielen Dank trotzdem.

[Fuerchau]

Dazu muss man wissen, wie der Optimizer arbeitet.
Häufig wird der eigene SQL intern umgebaut und ergänzt um bessere Ergebnisse zu erzielen.
D.h., dass der SQL aus der VIEW entnommen wird und mit den zusätzleichen Feldern/Where/group/having ergänzt wird.
Folge:
aus
"select rrn(abc), ... from (select distinct ...) abc"
wird
"select distinct rrn(abc), ....."

und siehe da, RRN kann nie distinct sein und somit taucht jeder Satz auf.

Hieran kann man die Probleme von RRN sehr eindeutig erkennen und eben besser mit Schlüsseln arbeiten.
Ein anschliessender "select * from file where rrn(file)=nnn" führt nicht zum direkten Zugriff sondern zum Table-Scan.

RRN liefert nur die temporäre Satznummer und ist keine reguläre SQL-Funktion.

Hintergrund:
Bei einem Select auf eine View oder LF wird nie das Ergebnis verwendet, da aus Optimizer-Sicht sonst erst die gesamte View verarbeitet werden müsste bevor man zum eigentliche Select käme.
Durch obiges Umbauen kann aber häufiger schneller zugegriffen werden.

Deswegen nennt Dieter diesen ja auch "Pessimizer".

Übrigens:
Mit RRN verhindert man mit Sicherheit eine temporäre Kopie (meistens nur ein Auszug) der Daten in den Internspeicher (bzw. *QUERYnnnn in QTEMP).

[BenderD]

@Baldur:
bist du da sicher mit der RRN? Ich habe das noch als die physikalische Record Number der ersten Datei im Hinterkopf, die er am Wickel hat, kann also durchaus auch bei unterschiedlicher Zugriffsstrategie von einer anderen Table gezogen werden! Im übrigen führt RRN zu Full Table scans.

Naj, das mit dem Pessimizer, schreiben und lesen soll ja auch Spass machen, ich bin durchaus ein Anhänger davon grob granulare Anforderungen an die Datenbank zu adressieren und den Rest der Query Engine zu überlassen und nur dann dran rumzufummeln, wenn es wirklich zu langsam ist; im statistischen Mittel trifft der Automatismus bessere Entscheidungen als der Programmierer, lediglich das selektive Gedächtnis des letzteren lässt das anders erscheinen!

@Akku:
Ich finde solche Rätsel zu anstrengend, das ist Zeit raubend und klarere Fragen ermöglichen bessere Antworten.

mfg

Dieter Bender






RRN liefert nur die temporäre Satznummer und ist keine reguläre SQL-Funktion.

Deswegen nennt Dieter diesen ja auch "Pessimizer".

Übrigens:
Mit RRN verhindert man mit Sicherheit eine temporäre Kopie (meistens nur ein Auszug) der Daten in den Internspeicher (bzw. *QUERYnnnn in QTEMP).

[Fuerchau]

Dieter:
Für den Moment stimmt das mit der RRN, aber leider gibt es Anwendungen, die Änderungen an Sätzen mit Delete/Insert realisieren, und dann REUSEDLT(*YES) !
Und wie gesagt, RRN verhindert eine temporäre Kopie von Daten auch für das Ergebnis.

[Akku]

Guten Morgen, hatte etwas Urlaub und komme deshalb erst jetzt wieder dazu, in Ruhe ins Forum zu sehen.

Wahrscheinlich ist der Optimizer also der Grund, warum das RRN() in einem Fall mit in das DISTINCT einfließt und im anderen Fall nicht.

Vielen Dank für die geduldigen Erklärungen.

Viele Grüße,
Akku