Indizes vernünftig aufbauen

Ne. Wenn du das im Index hast ändert das where hier in diesem Fall nix.
Das blickt der Query optimizer und sortiert halt nach dem was er hat und braucht.
Doof und ganz doof ist zu viel im Index nicht.
Man könnte mehr rausholen, aber es wirkt sich erst Mal nicht negativ aus.
Doof wäre es, wenn du einen Index mit Jahr DESC und Monat DESC hast, deine Abfrage aber DESC + ASC hat.

Grundsätzlich gilt: wenn man die Queries an die Tabelle in der Hand hat und das überschaubar ist, dann versucht man Covering Indexe zuachen. Also den Index mit Feldern und Sortierung genau wie die Abfrage.

Wenn nicht, dann lieber einzelne Felder in einzelne Indexe. Der Query Optimizer nimmt dann die Indexe die er braucht jeweils. Außerdem lernt er auch, wenn er eine Konstellation findet, die oft genutzt wird, und kompiliert sich das dann schon Mal im vorraus, so daß er da nicht mehr gucken muss.

Auch nicht zu vernachlässigen ist die korrekte Verwendung von Grouped und nongrouped Indexen!

Reden wir hier von MSSQL?

deshalb kann man ja auch mehrere kleinere Indexe bauen.

Das impliziert ja, das man nur einen Index hat, der dann alle Felder beinhaltet. Das wäre ein beschissener Index.
Das wäre mir neu. Evtl. OracleDB\HANA speziefisches verhalten. glaube ich aber nicht. Wäre ja eine total idiotische Vorgehensweise.

Ich würde hier einen Convering mit JAHR MONAT TAG machen, und den WochenTag als Include definieren. Da man bei der suche nach einem definierten Datum ja höchstwahrscheinlich den WT dazu auch haben will, hängt der im Ergebnis schon mal an und muss nicht mehr einzeln gepicked werden.
Dazu einen Index auf jedes Feld einzeln. Damit ist für den Optimizer jede Spalte vorsortiert erreichbar und er kann sich das zurechtlegen wie er es braucht. Damit sind Abfragen wie "gib alle Freitage, die ein 13er sind auch schnell erledigt.

Dein Bsp. ist ein bisschen blöd. Das behandelt ein äusserst seltenes Szenario bei dem ein Index eigentlich eine untergeordnete Rolle spielt.
Hier sind kaum Sortiervorgänge vorhanden, was ja das eigentliche Performanceproblem bei SQL ist.
Das reine raussuchen von einem Datensatz anhand einer Bedingung ist für den SQL trivial, da die Daten per Heap und Leaf ja fix auffindbar sind.
Interessanter wird es wie gesagt, wenn grössere Ergebnissmengen vorhanden sind und diese sortiert werden.

Bsp:
Personalnummer [int]
Name [string],
Vorname [string],
Geburtstag [date],
Geschlecht [int] (würde ja bool sagen... aber das ist ja ein heißes Eisen ;) )

Sagen wir, wir haben hier 5000 Einträge und darunter befinden sich 20 Karen's


Jetzt willst du hier alle "Karen"'s haben, von Alt nach Jung. Ohne zusätzliche Indexe ist hier erst mal weder Heap noch Leaf vorhanden.
Suchst du jetzt nach "where name=Karen order by geburtstag ASC", wird erst mal eine FullTableScan über den Namen gefahren. FTS bedeutet: JEDER Datensatz wird angefasst und nach "Startet mit "K?" Wenn ja: "Geht weiter mit A?" usw abgearbeitet. Dann sind irgendwann alle Karens bekannt.
Hierbei entsteht eine Schnittmenge, die den Namen enthält und einen Verweis auf den Datensatz.
Diese Schnittmenge wird jetzt um die dahinterliegenden Geburtstage erweitert. Erweitern bedeutet in dem Fall:
Gehe über den Verweis zum Datensatz und lies das Geburtsdatum aus.
Dann wird die Schnittmenge sortiert und ausgegeben.

Baue ich einen (covering)Index auf Name und Geburtstag, ändert sich das in:
Der Index Schaut nach dem Leaf mit "K*", (sagen wir er unterteilt hier nicht weiter und der Leaf endet hier. (Je nach Datenmenge würde er das aber tun) und es fallen ein paar Datensätze raus, die die Personalnummer, Name und Datum sowie einen Verweis zum Datensatz enthält. Diese sind nach Datum ASC sortiert.
Jetzt werden die Datensätze noch über die obige logik nach Karen gefiltert. Sortiert ist das alles schon, also kann das in die Ausgabe.

Der langsamste Vorgang in dem ganzen ist erst mal der FTS, weil 5000 Datensätze angefasst werden müssen.
Danach die Eingrenzung auf die Textsuche nach Karen an den Datensätzen.
Danach das "folgen" vom Verweis mit dem Pick des zusätzlichen Datensatzen. Das entfällt allerdings beim Index, weil hier die Daten schon im Index mitgegeben wurden.
Dann die Sortierung nach dem Datum. Auch das entfällt beim Index, weil schon vorsortiert

Mit dem Index fällt der FTS weg. Hier würden vielleicht noch 200 Datensätze angefasst werden müssen, weil sich 200 Namen in der Tabelle befinden, die mit K anfangen.
Sortieren muss man das auch nicht mehr.


Will ich jetzt noch den Vornamen dazu haben, wäre ein zusätzlicher Schritt nötig, diesen vom Datensatz auszulesen.
Ohne Index muss erst mal alles so weit gefiltert werden wie bisher.
Dann folgt man den Verweisen und holt den zusätzlichen Datensatz.
Jetzt kann ich an dem Index ja eine Included Collumn mitgeben. Das bedeutet: An diesem Index wird DIESE Spalte immer mit gespeichert. Wird der Index verwendet, liegen diese Daten also sofort vor, ohne das man dem Verweis auf den eigentlichen Datensatz folgen muss.
Das hat den Vorteil, das es etwas schneller ist, aber auch den Nachteil, das diese Spalte also mindestens 2x existiert. 1x am Datensatz selbst natürlich, und am Index. Das kostet natürlich Speicherplatz. Ausserdem muss bei jeder Datenänderung(Update, Delete, insert) diese Information 2x gespeichert\geändert werden.
Hier muss man abwägen. Eine Tabelle die statisch ist, braucht halt etwas mehr Speicher, kann schneller Ergebnisse liefern und braucht keine zusätzlichen IOs für die Pflege des Indexes (weil sich ja nix oder wenig an den Daten ändert).
Eine Tabelle die permanente Änderungen hat, kann zwar auch schneller abgefragt werden und braucht mehr Speicher, erzeugt aber eine höhere Grundlast am System, weil die Indexe ja bei jeder Änderung mitgepflegt werden.

Ergo: Statische Tabellen: Lieber mehr Indexe mit passenden Includes. Speicher ist heute kaum noch ein Problem. Der performancegewinn überwiegt in der Regel.
Häufig geänderte Tabelle: Sparsamere Indexe vergeben. Convering wo möglich. Includes mit bedacht wählen.

Bei Clustered Indexen ist diese Problematik der sich ändernden Tabellen übrigens noch genauer zu betrachten.
Ein Clustered Index gibt die physikalische Speicherung der Daten auf der Festplatte vor. Ist dein CI z.B auf einem Text, dann wird bei einer Insert die richtige Position für den Datensatz gesucht, der Part darunter wegkopiert, der neue Datensatz eingefügt und das verschobene wieder drunter gehängt. Viel Arbeit also.
Entsprechend sollten Clustered Indexe so gewählt werden, das die Datensätze immer nur unten angehängt werden, und nicht irgendwo mitten drin eingefügt werden, und das dieser Index sich dann auch nicht mehr ändert. Eine Unique ID mit Autoincrement ist also der perfekte Clustered Index. Ausserdem ist der Clustered Index in jedem normalen Index als Include vorhanden.


Ok ... das wurde jetzt ein bisschen länger ...

Warum spaltest Du ein Datum so auf? Das ist Murks.




Die Antwort gibt EXPLAIN.



Ähm, nein. Die Idee, ein Datum derart zu zertrümmern ist einfach Müll. DAS ist das Wichtigste, was Du verstehen solltest.

ist doch nur ein Bsp ... Schlecht gewählt, ja. Aber es geht ja nur um den grundsätzlichen Aufbau.

Hat Oracle sowas auch ?