Grundsätzlich - kann DB mehrere Indizes kombinieren?

[IBExpert]

Mehrere Indizes pro Tabelle in einer Abfrage kombinieren ist bei Firebird kein Problem. Wenn es passende Multicolumn Indizes gibt, dann werden meistens die vom Optimierer bevorzugt, ansonsten werden aber auch mehrere Indizes pro Tabelle kombiniert benutzt. Wenn mehrere Indizes kombiniert werden, entscheidet die Selektivität über die Reihenfolge

Beispiel auf der IBExpert Demo DB, Tabelle Product mit 10000 Datensätzen, Firebird 2.5.4

Code:

select * from product where category_id=1 and special=1

Code:

PLAN (PRODUCT INDEX (IX_PROD_CATEGORY, IX_PROD_SPECIAL))

Datenmengen:
category_id=1 -> 327 Datensätze (insgesamt gibt es 17 unterschiedliche Werte, daher ist die Selektivität 1/17)
special=1 -> 184 Datensätze (es gibt relativ wenige Specials mit 1, der Rest ist 0, Selektivität 1/2)

Der Optimierer beginnt mit dem selektiveren Index, nutzt aber weitere Indizes sofern sinnvoll trotzdem.

Wenn man o.a. SQL ausführt bekommt man die Ergebnismenge von 9 Datensätzen mit 9 indizierten Reads

Code:

Query
------------------------------------------------
select * from product
where category_id=1 and
special=1

Plan
------------------------------------------------
PLAN (PRODUCT INDEX (IX_PROD_CATEGORY, IX_PROD_SPECIAL))

Query Time
------------------------------------------------
Prepare       : 15,00 ms
Execute       : 31,00 ms
Avg fetch time: 3,44 ms

Memory
------------------------------------------------
Current: 34.728.216
Max    : 35.150.104
Buffers: 2.048

Operations
------------------------------------------------
Read   : 0
Writes : 0
Fetches: 27
Marks  : 0


Enchanced Info:
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|          Table Name           |  Records  |  Indexed  | Non-Indexed | Updates | Deletes | Inserts | Backouts |  Purges  | Expunges |
|                               |   Total   |   reads   |    reads    |         |         |         |          |          |          |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|PRODUCT                        |         0 |         9 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

Wichtig: Die Feldreihenfolge im SQL ist für den Plan unwichtig, sofern alle den gleichen Operator (in diesem Falle = ) benutzen


Metadaten

Code:

/******************************************************************************/
/****              Generated by IBExpert 08.09.2015 23:46:34               ****/
/******************************************************************************/

/******************************************************************************/
/****     Following SET SQL DIALECT is just for the Database Comparer      ****/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/



CREATE TABLE PRODUCT (
    ID           NUMERIC(18,0) NOT NULL,
    CATEGORY_ID  NUMERIC(18,0) NOT NULL,
    TITLE        VARCHAR(50) NOT NULL,
    ACTOR        VARCHAR(50) NOT NULL,
    PRICE        NUMERIC(12,2) NOT NULL,
    SPECIAL      SMALLINT
);




/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE PRODUCT ADD CONSTRAINT PK_PRODUCT PRIMARY KEY (ID);


/******************************************************************************/
/****                             Foreign keys                             ****/
/******************************************************************************/

ALTER TABLE PRODUCT ADD CONSTRAINT FK_PRODUCT FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORY (ID);


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE INDEX IX_PROD_ACTOR ON PRODUCT (ACTOR);
CREATE INDEX IX_PROD_CATEGORY ON PRODUCT (CATEGORY_ID);
CREATE INDEX IX_PROD_SPECIAL ON PRODUCT (SPECIAL);
CREATE INDEX IX_PROD_TITLE ON PRODUCT (TITLE);
CREATE DESCENDING INDEX PRODUCT_IDX2 ON PRODUCT (ID);


/******************************************************************************/
/****                              Privileges                              ****/
/******************************************************************************/

[jobo]

Ich habe nur mit Oracle in nennenswerten Größenordnungen Erfahrung. Die ist aber genau so, dass die Indizierung einzelner Felder besser ist, als kombinierte Indizes.
Die Regel, die Du da nennst, würde ich demnach bestätigen. Was Du aber später schreibst, widerspricht ja dieser Regel oder habe ich das mit den 8 gezielten Indizes (sprich kombiniert über die notwendigen Felder?) falsch verstanden?

Wenn man von 200Mio DS spricht, ist natürlich ein Index zum Testen nicht mal eben per Augenblinzeln erstellt. Und wenn er da ist, ist noch nicht in allen Lebenslagen getestet.
Das Problem, dass die sich sträuben, liegt glaub ich eher darin begründet, dass so etwas eine Menge Ausführungspläne für Reports oder sonst irgendwelche online Kram dramatisch über den Haufen werfen kann. Das macht man wohl nicht ohne Not für eine Abfrage, die nur in irgendeinem Report mal gebraucht wird.

Wenn das auf einem Testsystem ausprobiert wird (was anders ist nicht empfehlenswert), sollte das System möglichst identisch sein zur Produktion, was Mengengerüste und vor allem die Statistiken angeht (im Zweifel die Statistiken mit aus dem Prodsystem übernehmen und Statistik Aktualisierungen im Testsystem abschalten).

Meine Erfahrungswerte aus V10 und V11 sind da mit dem Optimizer ganz gut. Da würde ich bei der Gelegenheit nach Deinem Test mit einem zusätzlichen Index im Nachgang aus Spaß und Neugier gleich alle kombinierten Indizes löschen und durch einzelne ersetzen, testhalber. Prognose: Kann nur besser werden. Das muss allein schon massig Platz sparen, wahrscheinlich auch redundanten Platz (also faktorenmäßig), weil ein Feld in mehreren Indizes eingebunden ist und so mehrfach Indexplatz kostet. (Das müsste man separat mit vorher/nachher Tablespace- bzw. Segmentanalysen abfragen). Einzelne Indizes, also kleinere, bringen in der Größenordnung vielleicht auch noch mal extra Performance, unabhängig von den Ausführungsplänen.

Andererseits: Wenn die Index "Politik" des Kunden so rigide ist, ist es auch möglich, dass einzelne Abfragen von den Entwicklern mangels Alternativen auf die verfügbaren Indizes hin optimiert wurden. Wenn dann dort gerodet oder umgepflanzt wird, können besonders optimierte Abfragen (siehe auch Optimizer Hints) zu Zombies werden.

Unter V12 hatte ich bis jetzt eine derbe Enttäuschung (eine komplexe Abfrage), also Verschlechterung nach Upgrade. Da würde ich mehr testen.

[hoika]

Hallo,
ein Join über 2 Tabellenfelder bei großen Tabellen sollte schneller sein, wenn beide Felder indiziert sind.
Das Bsp. von IBExpert zeigt das auch (ok, ist kein Join).
Mehrfelder-Indizes können z.B. bei Firebird nur benutzt werden, wenn die Abfrage die Felder in der Index-Reihenfolge benutzt: Index auf Tab1 (A,B) wird bei Where B=5 nicht benutzt.
Bei Oracle ist das aber glaube ich anders.

Bei vielen ist immer noch in Gedanken: Index verlangsamt Insert/Update, ist ja richtig.
Die Beschleunigung des Select steht im Hintergrund.

Es gibt doch bei Oracle auch einen Optimierer, der bei einer DB Vorschläge macht, wie die schneller arbeitet, also bei einer Query sagt, mit Index auf Feld X geht es schneller.
Ich meine jetzt nicht den internen SQL-Optimizer.

Ich hatte bei Interbase damals den IBPlanalyzer benutzt, um unseren Chef von Indizes zu überzeugen und auch mit realen Daten (große DB, Sekunden Ausführungszeit pro reale Funktion) gearbeitet.

Wenn eine Inventur oder Statistik am Wochenende gemacht wird und 10 Stunden dauert und es dem Kunden egal ist, kann man natürlich nicht so viel machen.


Heiko

[BlackbirdBerlin]

Hallo zusammen.
Vielen Dank für Euren Input.
Noch ganz kurz zur Erklärung, es handelt sich um eine verhältnismäßig zentrale Tabelle mit Bewegungsdaten, die permanent recht viele Schreibzugriffe (haupts. Insert) erfährt. In den Systemen, in welchen auf meinen Wunsch hin der Index angelegt wurde, ergab sich subjektiv keine Verschlechterung der Schreibperformance, jedoch eine massive Verbesserung des Lesezugriffs (s.o.).
Die QS-Systeme, auf denen man das jeweils aufgrund der Transportreihenfolge (SAP Dev, QS, Prod) prüfen kann, sind meist 1:1 Kopien vom Produktivsystem.
Die Software, die vom Index nutznießen würde, läuft täglich. Es handelt sich um ein Schnittstellenprogramm.
Die Datenbanken sind meist Oracle oder DB2.
Viele Grüße
Tim

[Dejan Vu]

Hier Einiges zum Thema:
http://use-the-index-luke.com/sql/wh...ge-performance

Und speziell für Oracle:
http://www.dba-oracle.com/art_9i_indexing.htm

[frankyboy1974]

hallo,

wenn der optimizer einer Datenbank, nicht den richtigen Weg wählt, um zum richtigen Weg auf optimale Weise zu gelangen, würde ich verschachteltest SQL empfehlen. Du überlegst dir selber, wie der optimale Weg ist und zwingst die Datenbank diesen Weg zu nehmen. Anstatt also zu hoffen das Oracle immer den richtigen Weg bei 5 Tabellen findet, splittest du die Anfrage in 4 (verschachtelte)Anfrage aus und zwingst die Datenbank, immer den dafür vorhergesehenden Index zu benutzen.

mfg

[BlackbirdBerlin]

Hi nochmal.
Es handelt sich um keinen Join sondern um eine einfache Tabelle, zu welcher bisher das für meinen Zugriff erforderliche Schlüsselfeld nicht indiziert ist.
Viele Grüße
Tim

[frankyboy1974]

hallo,

jetzt verstehe ich die Frage nicht mehr, welchen Sinn würde es machen mehrere Indizes (Wie lautet die Mehrzahl auf Index, ich musste erstmal google) auf eine Tabelle zu benutzen?

mfg

[mkinzler]

Wenn sich mehrere Spalten in der Einschränkung befinden (Name = "Mayer" und Vorname = "Peter"), dann ist es am optimalsten wenn ein Index über genau diese beiden Spalten existiert.
Je nach DBMS kann nun auch ein 2 Indizes (einer auf Name und einer auf Vorname) herangezogen werden, diese Lösung ist imho immer scghlechter als ein passender Index mit den beiden Spalten, aber besser als ein full table scan.

[BlackbirdBerlin]

Hi Frankyboy,
grundsätzlich ergibt es viel Sinn, für eine Tabelle mehrere Indizes zu definieren, die verschiedene Abfragen optimieren.
Die Frage war aber, ob es Sinn ergibt, mehrere "einfeldige" Indizes anzulegen, anstelle spezieller Indizes mit mehreren Feldern und darauf zu bauen, dass die DB diese in Kombination verwendet (was wohl unter dem Schlagwort Bitmapped Indexes so zu sein scheint).
Und wenn es die Steigerung von optimal gäbe, dann wäre es wohl am "optimalsten", wenn man einen konkreten Index definiert, welcher die Schlüsselfelder der Abfrage indiziert.
Grüße,
Tim