Zeitverhalten bei Unterabfragen mit order by

[Dirk Reichert]

Ich habe eine große Datentabelle, in welcher ich Datensätze nach beliebigen Suchkriterien suchen kann. Über eine Unterabfrage stelle ich sicher, daß nur der zeitlich neuste Datensatz eines Kunden ausgewertet wird. Suche ich jetzt nach Kundenname Like 'M$' zum Beispiel, ist das Zeitverhalten bei ca. 2000 gefundenen Datensätzen akzeptabel (2-3-Sekunden). Füge ich jetzt aber noch eine Sortierung mittels order by z.B. Kundennamen, geht das Zeitverhalten in den Keller (ca. 50 sekunden). Das gleiche geschieht auch, wenn ich über RecordCount bzw. Select Count(*) nur die Anzahl der gefundenen Datensätze haben will. Dann ist das Zeitverhalten für den Anwender unakzeptabel. Wer weiß hier Rat.

Viele Grüße

Dirk Reichert

[Falk Prüfer]

Hallo Dirk,

ich gehe einfach mal davon aus, das die Spalten nach den du sortieren möchtest indiziert sind. Der Knackpunkt ist aber ob der Server die Indizes auch verwendet!. Der Oracle-Optimizer stellt sich hier manchmal etwas doof an. So richtig zufreidenstellend arbeitet er nur, wenn (gerade große Tabellen und deren Indizes) aktuell analysiert sind (mit DBMS_STATS.GATHER_TABLE_STATS und DBMS_STATS.GATHER_INDEX_STATS).
Du solltest dir mal die Ausführungspfade deiner SQL-Statements anzeigen lassen und überprüfen, ob deine Indizes verwendet werden oder jede Menge FULL-Table-Scans ausgeführt werden.
Zum Count: Ein select count(*) ist meistens einfacher geschrieben, aber ein select count(primary_key_feld) u.U. schneller.
Um deine Frage aber genauer beantworten zu können, müßte man schon wissen, wie die Abfrage (in etwa) aussieht.

Gruß Fal

[Stefan Frank]

Hallo,

die Sache mit den Statistiken funktioniert aber nur, wenn der Optimizer auch auf CostBased eingestellt ist.

Zwei Tipps:

1. Ein LIKE auf ein Index-Feld funktioniert nur, wenn du Like 'Muel%' schreibst. Schreibst du aber `%ueller' kann der Index nicht mehr verwendet werden. Wichtig ist auch, dass die Felder in der Reihenfolge des Index in der Query auftauchen. Bsp: Index über A, B, C. Eine Abfrage, die nur B und C benutzt führt wieder zu einem Full Scann. Generell sollte man dafuer sorgen, dass die Spalten, die in der Abfrage stehen (im WherePart) auch im Index vorkommen. Darf natuerlich auch nicht zu groß werden.

2.Man kann bei den Statement sog. HINTS angeben. Damit kann man das Verwenden eines bestimmten Index explizit erzwingen. Das ist ein ziemlich maechtiges Tool. Genauere Information in der Doku nach Hint und Optimezer suchen.

3. Solltest du dir mal den ExecutionPlan der Abfrage ansehen. Der sagt einem dann, welcher Teil der Abfrage oder Subquery den TableScan ausloest. Den ExecutionPlan (bitte auch in der Doku nachsehen) muss man allerdings aktivieren, indem eine best.Tabelle installiert wird. Da gibt es ein Script fuer (siehe Doku).

Gruss

Stefa

[Dirk Reichert]

Mit den Index ist das so eine Sache. Der Anwender kann zur Laufzeit selbst das zu sortierende Feld innerhalb der View bestimmen. Ich geb mal ein Beispiel für die SQL-Abfrage:

SELECT *
FROM VIEW_EINJAHRGANGSDATEN OUTER
WHERE NAME LIKE 'Muster%'
AND OUTER.DATUM =
( SELECT MAX(DATUM)
FROM EIN_JAHRGANGSDATEN
WHERE ID_MIETER = OUTER.ID_MIETER )
ORDER BY NAME

Für die Anzahl der Datensätze wird dann ausgeführt:

SELECT COUNT(*)
FROM VIEW_EINJAHRGANGSDATEN OUTER
WHERE NAME LIKE 'Muster%'
AND OUTER.DATUM =
( SELECT MAX(DATUM)
FROM EIN_JAHRGANGSDATEN
WHERE ID_MIETER = OUTER.ID_MIETER )
ORDER BY NAME

Beide Abfragen benötigen doch beachtliche Zeit, wenn die Datenmenge der äußeren Abfrage viele Datensätze (ca. 1000) zurückgibt, für welce dann die Unterabfrage ausgeführt wird. Wie gesagt statt dem Sortierfeld Name kann ein/mehrere beliebige Sortierfelder durch den Anwender fewählt werden. Die äußere Abfrage läuft über eine View, die Unterabfrage über die Haupttabelle der View. Wie kann man dies optimieren?
Vielen Dank für Euer Bemühen.

Gruß Dir

[Dirk Reichert]

Sorry,

die order by Zeile ist in der zweiten Select Count(*) Anweisung natürlich nicht mit enthalten.

Dir

[Stefan Frank]

1. Fuer das Zaehlen der Treffermenge braucht man das SELECT nicht noch mal auszufuehren, sondern fragt die Rowcount Variable ab (heisst glaub ich sql%rowcount unter SQL.) In PL/SQL geht das auch, da heisst die Varable aber irgendwie anders (bitte doku nachschauen, ich habs z.Z. nicht parat).

2. Da in dem ersten Select ein VIEW drin ist, beeinflusst der auch die Performance. Was hat es denn mit dem View auf sich?

3. Man koennte das auch so umformulieren:

SELECT <br>
FROM tab_a, <br>
(SELECT max(datum) as datum <br>
FROM tab_a a1 <br>
WHERE a1.id = tab_a.id) b <br>
WHERE tab_a.name LIKE 'SDD%' <br>
AND tab_a.datum = b.datum <br>

Mglweise wird das aber intern zum selben ergebnis fuehren. Bitte mal testen.

Gruss

Stefa

[Dirk Reichert]

Danke für die Tips.

1. Mit der Rowcount-Variablen ist das so eine Sache. Ich entwickle unter Delphi. Kann man da diese Variable auch abfragen? Allerdings bin ich bemüht neben Oracle auch noch MS-SQL als mögliche Datenplattform dem Anwender zur Verfügung zu stellen?

2. Ich habe in der Tabelle viele ID-Felder drin. Über die View hole ich mir aus Stammtabellen die für den Anwender sprechenden Bezeichnungen für diese Felder. Deshalb frage ich über die View ab. Als Hauptadtentabelle ist aber nur ene Tabelle enthalten. Die View erweiterte diese um Bezeichnungen aus Stammtabellen.

3. Mit dem Statment erhalte ich im SQL Plus Worksheet den Fehler in Zeile 2 ORA-00936: Ausdruck fehlt. Ich formuliere wie folgt:

SELECT
FROM VIEW_EINJAHRGANGSDATEN,
(SELECT max(LEISTVON) as datum
FROM EIN_JAHRGANGSDATEN a1
WHERE a1.id_jahrgangssatz = VIEW_EINJAHRGANGSDATEN.id_jahrgangssatz) b
WHERE VIEW_EINJAHRGANGSDATEN.NAME LIKE 'Mon%'
AND VIEW_EINJAHRGANGSDATEN.LEISTVON = b.datum

VIEW_EINJAHRGANGSDATEN ist meine View und EIN_JAHRGANGSDATEN die zugehörige Hauptdatentabelle, id_jahrgangssatz ist der primary key.

Nochmals vielen Dank für die bisherige Hilfe.

Ich wünsche nach Ostern eine schöne Woche.

Grüße Dir

[Dirk Reichert]

So formuliert

SELECT *
FROM VIEW_EINJAHRGANGSDATEN,
(SELECT max(LEISTVON) as datum
FROM EIN_JAHRGANGSDATEN a1
WHERE a1.ID_MIETER = VIEW_EINJAHRGANGSDATEN.ID_MIETER) b
WHERE VIEW_EINJAHRGANGSDATEN.NAME LIKE 'Mon%'
AND VIEW_EINJAHRGANGSDATEN.LEISTVON = b.datum

führt zum Fehler ORA-00904: ungültiger Spaltenname in Zeile

[Stefan Frank]

Na, dann wird da wohl ein Attribut anders heissen als im Statement genannt. Da ich die Tabellen nicht kenne, kann ich da nicht helfen. Soweit ich weiss, muss man aber einen Alias auch benutzen, wenn man ihn mal angegeben hat. Schlage daher MAX(a1.LEISTVON) vor. Vielleicht ist das ja der Fehler...

[Dirk Reichert]

Vielen Dank,

das wars. Jetzt läuft das Statment und auch sehr schnell.
Das mit der RowCount-Variable würde mich noch interessieren.

Viele Grüße Dir

[Stefan Frank]

Hallo,

bei einem Cursor fragt man die SQL%ROWCOUNT Variable ab.
Man kann aber auch die Fehlernummer des SQL-Statements abfragen, das man ausgefuehrt hat. Wenn meine Erinnerung mich nicht taeuscht, kommt da entweder eine Fehlernummer zurueck oder die Anzahl der Saetze, wenn kein Fehler auftrat. Leider habe ich schon ein 3/4Jahr nix mehr programmiert, so dass ich die genaue Abfrage nicht im Kopf habe. WErde alt....

Stefa

[Dirk Reichert]

Hallo,

brauche nochmals Hilfe zum SQL-Statement.
Ich führe aus:

SELECT *
FROM VIEW_EINJAHRGANGSDATEN,
(SELECT MAX(A1.LEISTVON) AS DATUM FROM EIN_JAHRGANGSDATEN A1 WHERE A1.ID_MIETER = VIEW_EINJAHRGANGSDATEN.ID_MIETER) B
WHERE UPPER(NAME) LIKE 'MON%'
AND VIEW_EINJAHRGANGSDATEN.LEISTVON = B.DATUM

und bekomme den Fehler ORA-00904 ungültiger Spaltenname für VIEW_EINJAHRGANGSDATEN.ID_MIETER.
Verwende ich für die VIEW einen Alias wie folgt:

SELECT *
FROM VIEW_EINJAHRGANGSDATEN A2,
(SELECT MAX(A1.LEISTVON) AS DATUM FROM EIN_JAHRGANGSDATEN A1 WHERE A1.ID_MIETER = A2.ID_MIETER) B
WHERE UPPER(A2.NAME) LIKE 'MON%'
AND A2.LEISTVON = B.DATUM

ist A2.ID_MIETER ein ungültiger Spaltenname. Ich kann scheinbar keine Felder der äußeren View VIEW_EINJAHRGANGSDATEN in der zweiten Feldabfrage verwenden?

Viele Grüße

Dir

[Falk Prüfer]

Hallo Dirk,

das Select innerhalb der From-Klausel deines SQL verhält sich wie eine separate View, d.h. du kannst darin nicht auf Felder oder Werte einer anderen Tabelle/View des äußeren Select zugreifen (könntest du ja bei einer "normalen" View auch nicht.) - hast du also schon richtig vermutet. Du mußt die Einschränckung stattdesen in die Where-Klausel des äußeren Select packen - z.B.:
<pre>
SELECT * FROM VIEW_EINJAHRGANGSDATEN A2,
(SELECT A1.ID_MIETER, MAX(A1.LEISTVON) AS DATUM
FROM EIN_JAHRGANGSDATEN A1
GROUP BY A1.ID_MIETER) B
WHERE B.ID_MIETER = A2.ID_MIETER
AND UPPER(A2.NAME) LIKE 'MON%' AND A2.LEISTVON = B.DATUM </pre>

Gruß Fal

[Falk Prüfer]

Hallo Dirk,

eine andere variante für diese Art von Abfrage wäre auch, das Subselect nicht in die From-Klausel sondern in den Select-Bereich zu packen:
<pre>
SELECT A2.*,
(SELECT MAX(A1.LEISTVON) AS DATUM
FROM EIN_JAHRGANGSDATEN A1
WHERE A1.ID_MIETER = A2.ID_MIETER) DATUM
FROM VIEW_EINJAHRGANGSDATEN A2
WHERE UPPER(A2.NAME) LIKE 'MON%' AND A2.LEISTVON = B.DATUM <pre>

Gruß Fal

[Dirk Reichert]

Hallo Falk,

erste Anweisung funktioniert gut und schnell. In zweiter ist B.Datum am Ende unbekannt. Die erste aber ist klasse.

Vielen Dank

Dir