Indizes für Gruppensuchfelder erstellen?

Question

Müssen Sie in einer Oracle-Datenbank einen Index für Felder der Gruppierung nach Feldern erstellen?

Zum Beispiel:

select * 
from some_table
where field_one is not null and field_two = ?
group by field_three, field_four, field_five

Ich habe die Indizes getestet, die ich für die obige Abfrage erstellt habe, und der einzige relevante Index für diese Abfrage ist ein für field_two erstellter Index. Andere Einzelfeld- oder zusammengesetzte Indizes, die für eines der anderen Felder erstellt wurden, werden für die obige Abfrage nicht verwendet. Ist dies korrekt?

Answer 1

Es könnte richtig sein, aber das hängt davon ab, wie viele Daten Sie haben. Normalerweise würde ich einen Index für die Spalten erstellen, die ich in einer GROUP BY verwende, aber in Ihrem Fall könnte der Optimierer entschieden haben, dass nach der Verwendung des field_two-Index nicht genug Daten zurückgegeben werden, um die Verwendung des anderen Index für die GROUP BY zu rechtfertigen.

Answer 2

Nein, das kann falsch sein.

Wenn Sie einen großen Tisch haben, Oracle kann es vorziehen, die Felder aus den Indizes statt aus der Tabelle abzuleiten, auch wenn es keinen einzigen Index gibt, der alle Werte abdeckt.

Im neuesten Artikel in meinem Blog:

• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL : Oracle

gibt es eine Abfrage, bei der Oracle verwendet keinen vollständigen Tabellenscan, sondern verknüpft zwei Indizes, um die Spaltenwerte zu erhalten:

SELECT  l.id, l.value
FROM    t_left l
WHERE   NOT EXISTS
        (
        SELECT  value
        FROM    t_right r
        WHERE   r.value = l.value
        )

Der Plan ist:

SELECT STATEMENT
 HASH JOIN ANTI
  VIEW , 20090917_anti.index$_join$_001
   HASH JOIN
    INDEX FAST FULL SCAN, 20090917_anti.PK_LEFT_ID
    INDEX FAST FULL SCAN, 20090917_anti.IX_LEFT_VALUE
  INDEX FAST FULL SCAN, 20090917_anti.IX_RIGHT_VALUE

Wie Sie sehen können, gibt es keine TABLE SCAN en t_left hier.

Stattdessen, Oracle nimmt die Indizes auf id y value gesellt sich zu ihnen auf rowid und erhält die (id, value) Paare aus dem Verknüpfungsergebnis.

Nun zu Ihrer Frage:

SELECT  *
FROM    some_table
WHERE   field_one is not null and field_two = ?
GROUP BY
        field_three, field_four, field_five

Erstens wird es nicht kompiliert, da Sie die Option * aus einer Tabelle mit einer GROUP BY Klausel.

Sie müssen Folgendes ersetzen * mit Ausdrücken, die auf den Gruppierungsspalten und den Aggregaten der Nicht-Gruppierungsspalten basieren.

Der folgende Index wird Ihnen höchstwahrscheinlich von Nutzen sein:

CREATE INDEX ix_sometable_23451 ON some_table (field_two, field_three, field_four, field_five, field_one)

da sie alles enthält, was für die Filterung von field_two , Sortierung nach field_three, field_four, field_five (nützlich für GROUP BY ) und stellen sicher, dass field_one es NOT NULL .

Answer 3

Müssen Sie in einer Oracle-Datenbank einen Index für Felder der Gruppierung nach Feldern erstellen?

Nein. Das ist nicht nötig, denn eine Abfrage wird unabhängig davon ausgeführt, ob Indizes vorhanden sind oder nicht. Indizes werden bereitgestellt, um die Abfrageleistung zu verbessern.

Er kann jedoch hilfreich sein, aber ich würde zögern, einen Index hinzuzufügen, nur um einer Abfrage zu helfen, ohne die möglichen Auswirkungen des neuen Index auf die Datenbank zu bedenken.

...der einzige relevante Index für diese Abfrage ist ein für field_two erstellter Index. Andere Einzelfeldindizes oder zusammengesetzte Indizes, die für eines der anderen Felder erstellt wurden, werden für die obige Abfrage nicht verwendet. Klingt das korrekt?

Nicht immer. Oft erfordert ein GROUP BY, dass Oracle eine Sortierung durchführt (aber nicht immer); und Sie können den Sortiervorgang vermeiden, indem Sie einen geeigneten Index für die zu sortierende(n) Spalte(n) angeben.

Die Frage, ob Sie sich tatsächlich um die Leistung von GROUP BY kümmern müssen, ist jedoch eine wichtige Frage, über die Sie nachdenken sollten.