Optimierung der Oracle-Abfrage

Question

SELECT MAX(verification_id)
  FROM VERIFICATION_TABLE
 WHERE head = 687422
   AND mbr = 23102
   AND RTRIM(LTRIM(lname)) = '.iq bzw'
   AND  TO_CHAR(dob,'MM/DD/YYYY')= '08/10/2004'
   AND system_code = 'M';

Die Ausführung dieser Abfrage dauert 153 Sekunden. Es gibt Millionen von Zeilen in VERIFICATION_TABLE .

Ich denke, die Abfrage dauert wegen der Funktionen in der Where-Klausel sehr lange. Allerdings muss ich ltrim rtrim auf die Spalten zu tun und auch Datum hat in abgestimmt werden MM/DD/YYYY Format. Wie kann ich diese Abfrage optimieren?

Erklären Sie den Plan:

SELECT STATEMENT, GOAL = ALL_ROWS           80604   1   59
 SORT AGGREGATE                                   1   59
  TABLE ACCESS FULL P181    VERIFICATION_TABLE  80604   1   59

Primärschlüssel:

VRFTN_PK    Primary VERIFICATION_ID 

Indizes:

N_VRFTN_IDX2    head, mbr, dob, lname, verification_id
N_VRFTN_IDX3    last_update_date
N_VRFTN_IDX4    mbr, lname, dob, verification_id
N_VRFTN_IDX4    verification_id

Obwohl, in der erklären Plan sehe ich nicht Indizes/Primärschlüssel verwendet werden. ist das das Problem?

Answer 1

Versuchen Sie dies:

SELECT MAX(verification_id)
  FROM VERIFICATION_TABLE
 WHERE head = 687422
   AND mbr = 23102
   AND TRIM(lname) = '.iq bzw'
   AND TRUNCATE(dob) = TO_DATE('08/10/2004')
   AND system_code = 'M';

Entfernen Sie das TRUNCATE() si dob nicht bereits eine Zeitangabe hat, so wie es aussieht (Geburtsdatum?), darf sie das nicht. Darüber hinaus brauchen Sie etwas Indizierungsarbeit. Wenn Sie so viele Abfragen in diesem Stil machen, würde ich indexieren mbr y head in einem 2-Spalten-Index, wenn Sie sagten, was die Spalten bedeuten, würde es helfen, die beste Indizierung hier zu bestimmen.

Answer 2

Der einzige Index, der für die Verwendung in Ihrer Abfrage in Frage kommt, ist N_VRFTN_IDX2, da er vier der Spalten indiziert, die Sie in Ihrer WHERE-Klausel verwenden: HEAD, MBR, DOB und LNAME.

Da Sie jedoch Funktionen sowohl auf DOB als auch auf LNAME anwenden, kommen sie nicht in Frage. Der Optimierer kann dann entscheiden, diesen Index nicht zu verwenden, weil er der Meinung ist, dass HEAD+MBR für sich genommen eine nicht ausreichend selektive Kombination darstellen. Wenn Sie den TO_CHAR()-Aufruf aus DOB entfernen, haben Sie drei führende Spalten in N_VRFTN_IDX2, was ihn für den Optimierer attraktiver machen könnte. Ist es ebenfalls notwendig, TRIM() LNAME zu verwenden?

Die andere Sache ist, die Notwendigkeit, SYSTEM_CODE nachzuschlagen, bedeutet, dass die Abfrage aus der Tabelle lesen muss (weil diese Spalte nicht indiziert ist). Wenn N_VRFTN_IDX2 ein schlechtes Clustering-Factoring hat, kann sich der Optimierer für einen FULL TABLE SCAN entscheiden, da die indizierten Lesevorgänge einen Overhead darstellen. Wenn Sie hingegen SYSTEM_CODE zum Index hinzufügen, könnte die gesamte Abfrage durch einen INDEX RANGE SCAN erfüllt werden, was wesentlich schneller wäre.

Und schließlich: Wie aktuell sind Ihre Statistiken? Wenn Ihre Statistiken veraltet sind, könnte das den Optimierer dazu verleiten, eine falsche Entscheidung zu treffen. Genauere Statistiken könnten den Optimierer beispielsweise dazu veranlassen, den zusammengesetzten Index auch nur mit den beiden führenden Spalten zu verwenden.

Answer 3

Sie sollten das Literal in ein DATE und nicht die Spalte in ein VARCHAR2 umwandeln, etwa so:

AND  dob = TO_DATE('08/10/2004','MM/DD/YYYY')

Oder verwenden Sie die bevorzugte ANSI-Datumsliteral-Syntax:

AND  dob = DATE '2004-08-10'

Wenn die Spalte dob eine Zeitangabe enthält (ein Geburtsdatum enthält normalerweise keine Zeitangabe, außer vermutlich in einem Krankenhaus!

AND  dob >= DATE '2004-08-10' 
AND  dob <  DATE '2004-08-11'

Answer 4

Überprüfen Sie die Datentypen für HEAD und MBR. Die Werte "687422 und 23102" scheinen recht selektiv zu sein. Das heißt, wenn Sie Hunderttausende von Werten für "head" und Millionen von Datensätzen in der Tabelle haben, scheint es, dass HEAD ziemlich selektiv ist. [Das könnte allerdings völlig irreführend sein.]

Es kann jedoch sein, dass HEAD und/oder MBR tatsächlich als VARCHAR2- oder CHAR-Felder und nicht als NUMBER-Felder gespeichert sind. Wenn dies der Fall ist, würde der Vergleich des Zeichens mit einer Zahl die Verwendung des Indexes verhindern. Versuchen Sie Folgendes (und ich habe die Konvertierung des dob-Prädikats mit einem Datum einbezogen, aber die explizite Formatmaske hinzugefügt).

SELECT MAX(verification_id)
  FROM VERIFICATION_TABLE
 WHERE head = '687422'
   AND mbr = '23102'
   AND RTRIM(LTRIM(lname)) = '.iq bzw'
   AND TRUNCATE(dob) = TO_DATE('08/10/2004','MM/DD/YYYY')
   AND system_code = 'M';

Answer 5

Bitte stellen Sie eine EXPLAIN-Ausgabe für diese Abfrage bereit, damit wir wissen, wo die Verlangsamung auftritt. Zwei Gedanken:

ändern

AND  TO_CHAR(dob,'MM/DD/YYYY')= '08/10/2004'

zu

AND  dob = <date here, not sure which oracle str2date function you need>

und verwenden Sie einen funktionsbasierten Index auf

RTRIM(LTRIM(lname))