Falscher Index wird bei der Auswahl der obersten Zeilen verwendet

Durch das Einfügen von ROWNUM werden die Berechnungen des Optimierers dahingehend geändert, welcher der effizienteste Pfad ist.

Wenn Sie eine top-n-Abfrage wie diese ausführen, bedeutet dies nicht unbedingt, dass Oracle alle Zeilen erhält, sie vollständig sortiert und dann die obersten Zeilen zurückgibt. Die Operation COUNT STOPKEY im Ausführungsplan gibt an, dass Oracle die zugrunde liegenden Vorgänge nur ausführt, bis die Anzahl der angeforderten Zeilen gefunden wurde.

Der Optimierer hat berechnet, dass die vollständige Abfrage 77.000 Zeilen erfasst und sortiert. Wenn es diesen Plan für die Top-N-Abfrage verwendet, müsste es eine große Art dieser Zeilen machen, um die obersten 200 zu finden (es müsste sie nicht unbedingt vollständig sortieren, da es die genaue Reihenfolge nicht interessiert von Reihen über die Spitze hinaus, aber es müsste über alle diese Reihen schauen).

Der Plan für die Top-N-Abfrage verwendet den anderen Index, um zu vermeiden, dass überhaupt sortiert werden muss. Sie betrachtet jede Zeile in der richtigen Reihenfolge, prüft, ob sie mit dem Prädikat übereinstimmt und gibt sie zurück. Wenn es 200 Zeilen zurückgegeben hat, ist es fertig. Seine Berechnungen haben gezeigt, dass dies effizienter ist, um eine kleine Anzahl von Reihen zu erhalten. (Es kann natürlich nicht richtig sein; Sie haben nicht gesagt, wie die relative Leistung dieser Abfragen ist.)

Wenn der Optimierer diesen Plan auswählen würde, wenn Sie nach allen Zeilen fragen, müsste er den gesamten Index in absteigender Reihenfolge durchlesen, wobei jede Zeile von der ROWID aus der Tabelle abgerufen wird, um gegen das Prädikat zu prüfen. Dies würde zu einer Menge zusätzlicher E / A führen und viele Zeilen prüfen, die nicht zurückgegeben würden. In diesem Fall entscheidet es, dass der Index für customerpostcode effizienter ist.

Wenn Sie die Anzahl der Zeilen, die von der Top-n-Abfrage zurückgegeben werden sollen, schrittweise erhöhen, finden Sie wahrscheinlich einen Wendepunkt, an dem der Plan von der ersten zur zweiten wechselt. Nur von den Kosten der beiden Pläne, würde ich vermuten, dass es etwa 1.200 Zeilen sein könnte.

Wenn Sie sicher sind, dass Ihre Statistiken aktuell sind und der Index selektiv genug ist, könnten Sie oracle anweisen, den Index

(Ich würde diesen Ansatz nur als letzten Ausweg empfehlen)

Wenn ich das tun würde, würde ich zuerst die Abfrage tkprof, um zu sehen, wie viel Arbeit es tatsächlich macht,

z. B. die Kosten von Indexbereich-Scans könnten weit entfernt sein

habe vergessen zu erwähnen .... Sie sollten die tatsächliche Kardinalität überprüfen:

und vergleichen Sie es dann mit der Kardinalität im Abfrageplan.

Sie scheinen keinen perfekt passenden Index zu haben. Der Index CR_PROPOSALSEARCH_I1 kann verwendet werden, um die Zeilen in absteigender Reihenfolge des Attributs PROPOSALNUMBER abzurufen. Es wurde wahrscheinlich ausgewählt, weil Oracle vermeiden kann, alle übereinstimmenden Zeilen abzurufen, sie nach der ORDER BY-Klausel zu sortieren und dann alle Zeilen außer den ersten Zeilen zu verwerfen.

Ohne die ROWNUM-Bedingung verwendet Oracle den XIF25CR_PROPOSALSEARCH-Index (Sie haben keine Angaben dazu gemacht), weil es wahrscheinlich ziemlich selektiv in Bezug auf die WHERE-Klausel ist. Aber es wird erforderlich sein, das Ergebnis danach zu sortieren. Dies ist wahrscheinlich der effizientere Plan basierend auf der Annahme, dass Sie alle Zeilen abrufen.

Da jeder Index ein Trade-off ist (einer ist besser zum Sortieren, der andere besser zum Anwenden der WHERE-Klausel), bestimmen Details wie ROWNUM, welchen Ausführungsplan Oracle wählt.

Diese Bedingung:

ist nicht kontinuierlich, dh Sie können keinen einzelnen geordneten Bereich dafür beibehalten.

Es gibt also zwei Möglichkeiten, diese Abfrage auszuführen:

1. Sortiere nach Nummer und dann nach Code filtern.
2. Nach Code filtern und dann nach Nummer sortieren.

Die Methode 1 kann einen Index für die Zahl verwenden, der Ihnen eine lineare Ausführungszeit bietet (top 100 rows wäre 2 mal schneller als top 200 ausgewählt, vorausgesetzt Nummer und Code stimmen nicht überein) .

Die Methode 2 kann eine Bereichsüberprüfung für die grobe Filterung von Code verwenden (die Bereichsbedingung wäre etwa code >= 'MK3' AND code < 'MK4' ), erfordert jedoch eine Sortierung, da die Reihenfolge der Zahlen in einem zusammengesetzten Index nicht beibehalten werden kann .

Die Sortierzeit hängt von der Anzahl der obersten Zeilen ab, die Sie auswählen, aber diese Abhängigkeit ist im Gegensatz zur Methode 1 nicht linear (Sie benötigen immer mindestens einen Bereichsscan).

Die Filterbedingung in der Methode 2 ist jedoch selektiv genug, damit RANGE SCAN mit einer nachfolgenden Sortierung effizienter ist als eine FULL SCAN für die gesamte Tabelle.

Dies bedeutet, dass es einen Wendepunkt gibt: Für diese Bedingung: ROWNUM <= X existiert ein Wert von X , so dass die Methode 2 effizienter wird, wenn dieser Wert überschritten wird.

Aktualisierung:

Wenn Sie immer nach mindestens den ersten Symbolen von 3 suchen, können Sie einen Index wie diesen erstellen:

und verwenden Sie es in dieser Abfrage:

Auf diese Weise wird die Zahlenreihenfolge getrennt für jede Gruppe von Codes erhalten, die die ersten 3 Buchstaben teilen, was Ihnen einen dichteren Index-Scan geben wird.