Query mit Eingangsfolge

[Pikachu]

Habt ihr ein QM-Query in SQL? Dann könnt ihr ORDER BY RRN(Datei) angeben. Hier gibt's ein paar Beispiele dazu.

[Fuerchau]

Der Query-Optimizer kann das leider nicht.
Lösung:
a) Kopieren der Daten vorher in eine neue Datei ohne Schlüssel (was ja nach Größe nicht sinnvoll ist)
b) Einen QM-Query mit
Create NewTable as
Select rrn(*), Table.*
from Table
Where ....
Diese neue Tabelle dann mit Query auswerten.

[B.Hauser]

a) Kopieren der Daten vorher in eine neue Datei ohne Schlüssel (was ja nach Größe nicht sinnvoll ist)
b) Einen QM-Query mit
Create NewTable as
Select rrn(*), Table.*
from Table
Where ....
Diese neue Tabelle dann mit Query auswerten.

Eine neue Tabelle bzw. physiche Datei ist nicht erforderlich!
Eine permanente SQL-View, die anstatt der physischen Daten/Tabelle im Query/400 verwendet wird, ist ausreichend.

Create View MySchema/MyView 
      as (Select rrn(a) as RelSatzNr, a.* from MyTable a) 

Im Query/400 wird dann lediglich über die neue Spalte RelSatzNr sortiert.
Was allerdings zu berückisichtigen ist: Wenn mit SQL oder Query nach der relativen Satz-Nr. sortiert oder über die relative Satz-Nr. selektiert wird, erfolgt immer ein Table Scan, d.h. die komplette Datei wird gelesen.

@Alfredo
Auch bei der Verarbeitung von Query/400 werden Zugriffswege verwendet und nicht standardmäßig nach Eingangsfolge gelesen.

Die Angabe einer Sortierung ist bei allen SQL- oder auch Query/400-Abfragen notwendig, wenn die Datensätze in einer bestimmten Reihenfolge ausgegeben werden sollen. Das bedeutet jedoch nicht, dass nach Eingangsfolge gelesen werden muss. Vielmehr ist es so, dass mit Hilfe eines Zugriffsweges (SQL-Index oder geschlüsselte logische Datei) die benötigten Datensätze selektiert und in ein temporäres Objekt ausgegeben werden. In einem weiteren Schritt werden dann die in dem temporären Objekt gespeicherten Daten in der gewünschten Reihenfolge sortiert.
(Deshalb sollte man auch den Order By nicht angeben, wenn er nicht unbedingt erforderlich ist.)

Birgitta

[alfredo]

Was heisst unbedingt notwendig?
Wenn ich eine Abfrage sortiert bzw gruppiert nach dem Key machen will, muss ich sortieren angeben, weil eben QUERY/400 sonst nach Eingangsfolge liest, auch wenn man den Key als Gruppenfeld angibt.
lg

[B.Hauser]

weil eben QUERY/400 sonst nach Eingangsfolge liest

Tut es eben nicht!
Auch beim Query/400 wird der Query Optimizer (wenn auch nur alte Query Engine) aktiviert, der einen optimalen Zugriffsweg ermittelt und diesen verwendet. Die Verarbeitung nach Eingangsfolge (sprich Table Scan) erfolgt nur dann, wenn der Optimizer KEINEN optimalen Zugriffsweg findet!

Nach Deinem Verständnist würde also bei jeder Abfrage, bei der keine Sortierung angegeben wurde die komplette Datei durchgelesen werden, das braucht bei in paar Milliönchen Sätzen schon seine Zeit. Zugegebenermaßen kann der Optimizer bei der CQE z.T. durch die Angabe einer Sortierung beeinflusst werden, aber es gibt dafür keine Garantie.

Beispiel: Aus einer Auftrags-Kopf-Datei werden alle Aufträge für Kunde 4711 ausgewählt, aber nach Bestell-Nr. sortiert. Es sind sowohl Zugriffswege über die Kunden-Nr. als auch die Bestell-Nr. vorhanden. Obwohl die Sortierung nach Bestell-Nr. angegeben wurde, wird wahrscheinlich der Zugriffsweg über die Kunden-Nr. verwendet, alle Sätze ausgelesen und zum Schluss sortiert.

Wenn Du's nicht glaubst, starte einen Database Monitor und analysiere die Ergebnisse. Da wirst Du sehen, dass auch bei Query/400-Abfragen Zugriffswege verwendet werden.

Birgitta

[alfredo]

In der Praxis werden die Anwender wohl lieber eine Auswertung aufsteigend nach dem Gruppierungsbegriff und nicht nach Erstellungszeitpunkt haben wollen.

[alfredo]

Beispiel Datei TESTQUERY, Key=KEY
select key
from testquery
group by key
KEY
7
6
5
4
3
2
1
select key, count(*)
from testquery
group by key
KEY COUNT ( * )
1 1
2 1
3 1
4 1
5 1
6 1
7 1

[Fuerchau]

Du musst unterscheiden zwischen der Query-Gruppierung und einem Group-By !

Beim Query muss eine Sortierung angegeben werden, wenn die Gruppierung sinnvolle Summen auswerfen soll.
Da beim Query sowohl die Einzelsätze als auch die Gruppensummen ausgewiesen werden können (in mehreren Stufen), erfolgt eben erst die Sortierung (falls angegeben) und anschließend eine ganz normale Gruppenwechsel-Verarbeitung.
Der Optimizer betrachtet beim Query im Wesentlichen die Satzauswahl und die Sortierung.
Von der anschließenden Gruppierung weiß er nähmlich nichts.

Bei SQL sieht das anders aus.
Da hat man nur 2 Möglichkeiten:
- nur Einzelsätze
- nur Gruppierung über max. 1 Ebene
Der Optimizer entscheidet hier jeweils über
- where
- Group
- having
- order
Klausel, wobei die Sortierung nicht eindeutig ist wenn man order-by weglässt, egal ob group-by oder nicht.

@Birgitta
Das mit der View geht solange gut, solange man wenige Sätze hat.
Da der Optimizer nun mal immer auf der PF aufsetzt, habe ich zwar für Query nun die Satznummer, aber bei einer Sortierung nach dieser erfolgt eben immer ein Table-Scan und zwar unabhängig von jedweder Verfügbarkeit von LF's (ich habe es mit einer 2,5Mio-Date bei V5R3 ausprobiert).
Lasse ich die Sortierung weg, klappts auch mit den LF's.
Ich denke, das liegt daran, dass die RRN eine Funktion und kein Feld ist. Bei Sortierung nach UDF's siehts genauso bescheiden aus.

Wenn ich aber weiß, dass das Zwischenergebnis nur ein paar 1000 bis 10.000 Sätze sind, ist das über einen Insert/Select bzw. Create/Select um Faktoren schneller.

@Frank
Prüfe mal an deiner Tabelle, ob REUSEDLT(*YES) eingeschaltet ist. Dann ist deine Eingangsfolge nähmlich keine mehr und das Problem löst sich in Luft auf.

[alfredo]

Gut wenn man soviel Zeit zum Philosophieren hat...

[Fuerchau]

Das hat mit Philosophieren nichts zu tun.
Wenn man die Unterschiede nicht kennt und verinnerlicht, wundert man sich halt über das Ergebnis .

Oder fragt hier

[BenderD]

Hallo Birgitta,
ich bin wie meist mit vielem einverstanden, was du da schreibst und empfiehlst; in diesem Punkt würde ich widersprechen wollen.
Auf die Angabe von Sortierungen kann man nur verzichten, wenn einem die Reihenfolge wirklich egal ist (was fast nie der Fall ist) und wenn es einen passenden Index gibt, wird da in der Regel nix sortiert und es wird meist sogar eher schneller als langsamer, da der Query Pessimizer von unsinnigen Entscheidungen fern gehalten wird (was bei der famosen neuen Query Engine oft die letzte Rettung ist).

ansonsten beste Grüße von D*B an F*Z

Dieter Bender

... die benötigten Datensätze selektiert und in ein temporäres Objekt ausgegeben werden. In einem weiteren Schritt werden dann die in dem temporären Objekt gespeicherten Daten in der gewünschten Reihenfolge sortiert.
(Deshalb sollte man auch den Order By nicht angeben, wenn er nicht unbedingt erforderlich ist.)

Birgitta

[B.Hauser]

Hallo Dieter,

das kann ja vielleicht alles stimmen, sofern wirklich wenige aber dafür optimale Indices vorhanden sind und die Datenbank ausserdem sauber designed ist. (... was bei vielen wohl nicht der Fall sein wird.)

Wenn der Optimizer allerdings nun keinen optimalen Index finden kann, wird ein suboptimaler Index oder sogar ein Table scan verwendet und das Ergebnis im Anschluß sortiert. Bei der CQE wird u.U. ein temporärer Index gebildet. Bei der SQE wird, zumindest vor Release V5R4, darauf verzichtet. Der Grund dafür liegt darin, dass zur Erstellung eines Indices sowieso ein Table Scan erforderlich ist. Also bleibt man beim Table Scan, erstellt kein neues Objekt und sortiert anschließend. Ab Release V5R4 können quasi permanente Indices gebildet werden, d.h. Indices die im Plan-Cache gespeichert werden und von allen Jobs verwendet werden können. Diese Indices werden erst dann gelöscht, wenn der letzte Access Plan, der diesen Index verwendet aus dem Plan-Cache verschwindet, oder IPL, bei dem der Plan-Cache gecleart wird, gefahren wird.

(Binary Radix Tree) Indices, werden nur verwendet, wenn das erwartete Ergebnis weniger als 20% der Gesamtzahl der Sätze umfasst. Zwischen 20 und 70% kann der Optimizer Encoded Vector Indices verwenden. Wird allerdings eine Sortierung angegeben können EVIs nur begrenzt eingesetzt werden.

Kann nur ein suboptimaler Index verwendet werden, erfolgt die Sortierung im Nachhinein. Eine nachträgliche Sortierung ist ein zusätzlicher Aufwand, d.h. wenn eine Sortierung nicht erforderlich ist, nicht angeben! Ich muss fairerweise zugestehen, dass man u.U. durch die Angabe einer Order By-Anweisung der Optimizer dazu bringen kann einen Zugriffsweg anstatt eines Table scans zu verwenden. Allerdings sollte man das erst probieren, wenn auch ein FOR OPTIMIZE OF mit einer kleinen Anzahl an Zeilen kein Erfolg gebracht hat.

Die Order By-Anweisung ist auch das letzte, das der Optimizer bei der Suche nach dem optimalen Index in Betracht zieht. Er geht nach der folgenden Reihenfolge vor:

1. Join-Bedingungen und Where-Auswahl
2. Where-Auswahl und Join-Bedingungen
3. Where-Auswahl und Group By-Anweisungen
4. Where-Auswahl und Order By-Anweisungen

... sicher man könnte den optimalen Index anlegen und wo schon hunderte von Zugriffswegen auf den Dateien liegen, tut ein zusätzlicher Index auch nicht mehr weh

Ich weiß, Du liebst die bunten Bilder im iSeries Navigator nicht so sehr, aber mit Visual Explain kann man das Ganze schön sichtbar machen.

Ich habe es vorhin ausprobiert mit einer Standard-Abfrage bei uns.

select * 
   from LLAKOPP
   where FIRNR = 600 and AKKNDB = 'XXX' and AKLAO = 'YYY' and AKSTS = '01'
   Order By FIRNR, AKKNDB, AKLAO, AKSTS, AKKND, AKZUA, AKBNR; 

Ich ging auch davon aus, dass passende Zugriffswege vorhanden sind. Dennoch war das Ergebnis folgenes:

1. CQE ohne Sortierung: Verwendung eines Zugriffsweges nach FIRNR/AKKNDB/AKSTS/AKBNR --> Zeit: 162489 Ms
2. CQE mit Sortierung: Erstellung eines temporären Indices (mit Table scan) --> Zeit: 204752 Ms
3. SQE ohne Sortierung: Verwendung eines Zugriffsweges nach FIRNR/AKKNDB/AKSTS/AKBNR --> Zeit: 56984 Ms
4. SQE mit Sortierung: Verwendung des gleichen Zugriffsweges nach FIRNR/AKKNDB/AKSTS/AKBNR mit anschließender Sortierung --> Zeit 86506 Ms

Die gleichen Statements wurden mehrfach ausgeführt. Die Zeitverhältnisse waren immer ungefähr identisch.

... Man kann noch viel philosophieren und interpretieren. Ich habe jedoch das Gefühl, je mehr man glaubt den Optimizer zu verstehen, desto weniger versteht man ihn. Im Prinzip muss man an jedem einzelnen SQL-Statement solange herum drehen, bis es optimal läuft.

@Fuerchau:

Das mit der View geht solange gut, solange man wenige Sätze hat.
Da der Optimizer nun mal immer auf der PF aufsetzt, habe ich zwar für Query nun die Satznummer, aber bei einer Sortierung nach dieser erfolgt eben immer ein Table-Scan und zwar unabhängig von jedweder Verfügbarkeit von LF's (ich habe es mit einer 2,5Mio-Date bei V5R3 ausprobiert).

Lasse ich die Sortierung weg, klappts auch mit den LF's.
Ich denke, das liegt daran, dass die RRN eine Funktion und kein Feld ist. Bei Sortierung nach UDF's siehts genauso bescheiden aus.

Wenn ich aber weiß, dass das Zwischenergebnis nur ein paar 1000 bis 10.000 Sätze sind, ist das über einen Insert/Select bzw. Create/Select um Faktoren schneller.

Dann machst Du was falsch! Bei meinen Tests hat der Optimizer für die Selektion von ein paar Hundert Sätzen aus der großen Datei mit unterschiedlichen Abfragen jeweils einen Zugriffsweg (DDS beschriebene logische Datei!) verwendet und anschließend nach RRN sortiert. (und zwar sowohl mit der CQE als auch mit der SQE!). Ich habe allerdings auch Zugriffswege über die in den Where-Bedingungen angegebenen Felder gehabt. (Where-Bedingungen werden vom Optimizer den Order By-Anweisungen vorgezogen!)

SQL kann nur Zugriffswege, die auf Original-Feldern liegen verwenden. Du kannst auch nur Indices mit Datei-Feldern anlegen. DDS beschriebene logische Dateien, in denen z.B. Schlüssel auf neugenerierte Felder z.B. durch Verknüpfung oder Substring verwendet werden, können von SQL nicht benutzt werden.

Was man auch machen kann, wäre eine MQT (Materialized Query Table) anzuglegen und über die Spalte Relative Satz-Nr. einen Index generieren. Die MQT muss zwar immer wieder neu aufgebaut werden über das SQL-Statement REFRESH, kann aber dann den Index über die Spalte relative Satz-Nr. verwenden.

Birgitta