Grundsätzlich - kann DB mehrere Indizes kombinieren?

[BlackbirdBerlin]

Hallo zusammen.

In den Entwicklungsrichtlinien eines Kunden von mir steht sinngemäß, dass ein Index nur wenige Felder enthalten sollte und die Datenbanken in der Lage seien, zwei oder mehr Indizes gemeinsam zu nutzen, um eine Abfrage auszuführen.

Gemeint ist damit, wenn es eine Tabelle mit Kunde, Artikel, Warengruppe, ... gibt und es einen Index auf Warengruppe und einen Index auf Kunde gibt, dass dann ein Zugriff SELECT ... FROM ... WHERE Warengruppe = ... AND Kunde = ... so gut ausgeführt werden würde, als gäbe es EINEN Index auf Warengruppe UND Kunde.

Aus meinen bisherigen Datenbankerfahrungen halte ich das für Unsinn, möchte aber nicht ausschließen, dass Datenbanken im Gegensatz zu mir (in diesem konkreten Fall) dazugelernt haben...

Wenn ich mir Execution-Plans ansehe, sehe ich doch auch immer nur den einen Index, für den sich die DB entschieden hat...

Kann jemand hier die Kunden-Vorgabe bestätigen oder liege eher ich richtig?

Viele Grüße
Tim

[nahpets]

'ne richtige Antwort hab' ich nicht. Zur Lektüre eventuell dieses: http://wikis.gm.fh-koeln.de/wiki_db/...ken/SQL-Tuning
Vermutlich haben Dein Kunde und Du recht, es dürfte auf die Datenbank ankommen.

[BlackbirdBerlin]

Hi Stefan,
wenn ich das eben richtig überflogen habe, wird dort aber auch explizit vom Zugriff über einen Index gesprochen und nicht davon, dass die Datenbank in der Lage ist, mehrere Indizes zum Zugriff für eine Abfrage zu verwenden...
Viele Grüße
Tim

[nahpets]

Hallo Tim,
wenn sich diese Erkenntnis für alle Datenbanken generalisieren lässt, hast Du recht und nicht der Kunde.

Es steht also der Beweis aus, dass der Kunde recht hat. Bezieht er sich auf Datenbanken allgemein oder hat er eine bestimmte im Einsatz, so dass man hier gezielt die Richtigkeit seiner Aussage beweisen (oder halt auch widerlegen) kann?

[BlackbirdBerlin]

Der Kunde hat Oracle und weigert sich standhaft, eine Abfrage, die ohne Index ca. 10 Std. (!) läuft, auf wenige Minuten durch einen Index zu reduzieren.
Tabelle hat knapp 200 Mio Einträge...
Das ist in meinen Augen deren pers. Pech - damit kann ich gut umgehen...
Ich will nur gern wissen, ob die Entwicklungsrichtlinien Unsinn vorschreiben oder nicht, da ich gern, falls dem so ist, nicht auf altem Wissen stehen bleiben möchte. Ich finde nur nichts, was die Aussage bestätigt.

Viele Grüße
Tim

[Phoenix]

Also mir persönlich ist jetzt keine Datenbank bekannt, die Indexe kombinieren könnte. Ich habe allerdings auch nur Erfahrungen mit Microsofts SQL Server, Oracle, DB2 und MySQL* bzw. MariaDB*.

Bei denen bin ich mir sicher, dass sie bei der Ermittlung des Ausführungsplans prüfen, welcher der Indexe am besten passt, und diesen dann verwenden (und nein, der sogenannte "index_merge" den MariaDB / MySQL manchmal versucht zählt nicht, der ist eher theoretischer Natur und greift im echten Leben fast nie).

Grundsätzlich sollte man sich also anschauen:
Nach welchen Spalten wird in aller Regel gemeinsam gefiltert? Diese sollten gemeinsam in einen Index. Es sollte dabei darauf geachtet werden, lieber einen Index mehr zu machen (also z.B. einen Index auf A, B und D und einen auf A, B, D und E), als eine Spalte zu viel in den Index aufzunehmen (z.B. nur einen Index auf A, B, D und E, wenn oft nur auf A, B, D abgefragt würde).

Dabei sollte man dann allerdings die Spalten, die in aller Regel mit selektiert (aber nicht gefiltert) werden in den Index als non-key Spalten mit zu includen (sofern die DB das kann).

Hintergrund: Selbst wenn ein Index dazu führt, dass die betroffenen Spalten schnell identifiziert werden können: Ohne die included columns muss die Datenbank dann trotzdem wieder table seeks machen um die eigentlichen Daten lesen und ausliefern zu können. Wenn die Spalten aber schon als non-key im Index enthalten sind, kann sich die Datenbank den lookup sparen denn die Daten sind beim index schon gelesen und ist dann deutlichst schneller beim beantworten der queries. Hier z.B. Info dazu für den SQL Server: https://msdn.microsoft.com/en-us/library/ms190806.aspx

[Phoenix]

Zitat:

Zitat von BlackbirdBerlin (Beitrag 1315189)

Der Kunde hat Oracle und weigert sich standhaft, eine Abfrage, die ohne Index ca. 10 Std. (!) läuft, auf wenige Minuten durch einen Index zu reduzieren.

Oha. Naja, klassisches "selber schuld". Oracle nutzt den Index, der am selektivsten ist, und nur genau den (ausser sie meint, ein full table scan wäre besser).

Infos dazu z.B.: http://stackoverflow.com/questions/1...-work-together

[BlackbirdBerlin]

Hi Sebastian,
das entspricht auch in etwa meinem Wissenstand. Bis auf die Non-Key-Geschichte. Das ist mir neu und das vertiefe ich nochmal - DANKE dafür :)
Viele Grüße und besten Dank für Deine Antwort
Tim

[Uwe Raabe]

Bei Interbase gibt es da tatsächlich eine Besonderheit (aus Optimizing InterBase SQL and Metadata - emphasize by me):

Zitat:

When we design our metadata, there are a few approaches we can take:

Create a single index for only one of the columns. If there are only 10 records in the table for any given date, for example, then a single index on FOO_DATE is enough to allow us to find any record in the table quickly.
Create one index for each column in the WHERE clause (three total). InterBase will combine the indices when the query is run.
Create a single index on all three columns (one index total).
If solution (1) works for your particular application, great. If not, we need to examine (2) and (3).

Let's imagine you choose solution (2), and create indices on FOO_DATE, FOO_COLOR, and FOO_DSC. When you type a WHERE clause like the one above, the InterBase optimizer will notice that all three indices could be useful in performing the query. It makes a kind of bitmap of each index, where a bit is turned on if there's an index node for a particular value, and turned off if there isn't. It then combines the bitmaps using a binary AND operation, and uses the resulting bitmap as a single index into the table. There is some overhead in doing this, but it isn't too bad in most cases.

If you choose solution (3), then InterBase can use your multi-column index as-is, without any combination of separate indices. So (3) may produce the fastest query results for this particular SELECT. But what about other statements; will the index still be useful?

[BlackbirdBerlin]

Hi Uwe,
sowas ähnliches hab ich inzw. auch zu Oracle gefunden. Dennoch bleibt der Hinweis, dass der spezielle Index am hilfreichsten sein sollte.
Ich muss versuchen, eine ähnliche Datensammlung zu Testzwecken auf eine Maschine zu bekommen, so dass ich das mal in beiden Konstellationen prüfen kann.
Problem ist aktuell, dass die bestehenden 8 Indizes nicht jeweils aus einem Feld bestehen, sondern gezielte Indizes sind, die für die meisten Abfragen zutreffen. Ob es da sinnvoll ist, genau das eine Feld, welches zur Zeit noch fehlt, in einem separaten Index aufzunehmen, wäre vermutlich nur durch einen Versuch zu klären.
Auch Dir vielen Dank!
Grüße,
Tim

[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

[frankyboy1974]

hallo Blackbird,

ich verstehe die Frage jetzt immer noch nicht,

Zitat:

Wie realisieren Datenbanken einen Index???

Warum legt nicht die Datenbank automatisch auf jedes Feld einen Index an. Dann würde die Suche nach diesem Feld wohl schneller funktionieren?? Und wenn ich nach zwei Felder suche, dann kombiniert die DB einfach die beide Inizes und ist dann immer noch schneller als ein Tablescan? Also mein Gedanke wäre immer noch, bei der ersten Abfrage veruchst du einen Index zu erwischen, und den Rest programmierst du selbst.

mfg

[mkinzler]

Ein Index beschleunigt die Suche, verlangsamt aber Insert/Update/Delete Vorgänge, weil auf Grund dieser ja Aktualsierung der Indizes notwendig wird. Deshalb wird automatisch nur ein Index für den Primärindex erzeugt.

[Sir Rufo]

Jeder Index auf einer Tabelle ist intern eine weitere Tabelle mit den Inhalten der indizierten Felder. Darum macht z.B. ein Index mit allen Feldern keinen Sinn, weil ich damit nur die Tabelle dupliziere.

Jeder Index belegt auch Speicher. Jeder Index muss wie die Tabelle aktualisiert werden. Mit einem Index-Wildwuchs kann ich den Plattenplatz verbraten und die Einfüge-Geschwindigkeit in den Keller drücken.

Es ist also ein Balance-Akt zwischen Speicherplatz und Abfrage-/Einfüge-Geschwindigkeit.

Für riesige Datenmengen mit vielen Abhängigkeiten werden für die Auswertung auch gerne mal separate Systeme genutzt. Die sind dann zwar nicht unbedingt absolut auf dem aktuellsten Stand, dafür aber bei der Abfrage höllenschnell. Wenn ich wissen will was letzten Monat passiert ist, dann ist mir aber der Stand von vor einer Sekunde so was von egal.

[BlackbirdBerlin]

Zitat:

Zitat von Sir Rufo (Beitrag 1315285)

...Darum macht z.B. ein Index mit allen Feldern keinen Sinn, weil ich damit nur die Tabelle dupliziere.

Kann man uneingeschränkt so nicht gelten lassen, finde ich.

Es müsste heißen: Darum ergibt z.B. ein Index mit allen Feldern in identischer Reihenfolge keinen Sinn, weil ich damit nur die Tabelle dupliziere.

Beispiel:

Code:

 Tabelle:
  BelegNr
  BelegJahr
  Kunde
  Artikel

 Index:
  Kunde
  Artikel
  BelegJahr
  BelegNr

Hier würde der Index m.E. durchaus Sinn ergeben, obwohl alle Felder enthalten sind!

Aber auch hier lasse ich mich gern eines Besseren belehren

Viele Grüße und sehr vielen Dank für Eure Beteiligung!
Tim

[nahpets]

Versuchen wir ein Beispiel und sei dazu folgende Tabelle gegeben:
Kundennummer, Postleitzahl, Ort, Rufnummer, eMail

Code:

Select * from tabelle where kundennummer = 1

Ein Index auf Kundennummer wäre hier sinnvoll.

Code:

Select * from tabelle where Ort = "Bärlin"

Ein Index auf Ort wäre hier sinnvoll.

Code:

Select * from tabelle where Kundennummer = 1 and Ort = "Bärlin"

Ein Index auf die Kombination aus Kundennummer und Ort wäre hier sinnvoll.

Der Kunde sagt aber: Ein Index auf Kundennummer und ein Index auf Ort reichen aus.

Hier im Beispiel hätten wir (im Kundenidealfall) auf jede der Spalten einen Index. Bei Abfragen, die über beliebige Kombinationen der Spalten gehen, soll gefälligst die Datenbank schauen, welcher Index bzw. welche Kombination aus 1:n Indizes hier die richtige ist.

frankyboy1974 macht dazu einen Vorschlag in der Art:

Code:

select * from (
  select * from tabelle where kundennummer = 1
) where Ort = "Bärlin"

Wenn die Datenbank "klug" ist, nutzt sie für die innere Abfrage den Index auf Kundennummer, aber ob sie für die so gefundene Teilmenge noch den Index auf die Spalte Ort der Tabelle nutzen kann, wage ich zu bezweifeln. Bei der äußeren Abfrage macht sie eher ein Full-Table-Scan, und wenn das Ergebnis komplett im Speicher liegt, geht das sicherlich sehr schnell. Ist die Datenmenge aber riesig (ein paar Millionen Sätze) so muss sie da wohl eher im Temp-Table-Space "rumwühlen".
Aber: Die Erfahrung, dass derartige Konstrukte deutlich performanter sind, als eine "innere" Abfrage, die alles abfackelt, habe ich schon mehr als nur einmal gemacht. Es kommt hier wohl wieder auf das Datenbanksystem und ein bisserl Versuch und Irrtum an.

Zumindest bei Oracle haben wir uns regelmäßig die Ausführungspläne angeschaut und ggfls. einen neuen Index angelegt, der für die Abfrage sinnvoller zu nutzen war, als das, was die Datenbank mit den vorhandenen Indizes machen konnte. Sehr vereinzelt kam es auch vor, dass die Datenbank nicht den bestmöglichen Index genutzt hat, dann wurde das SQL-Statement mit einem entsprechenden Hint versehen.

Bei Oracle müsste man aber doch eigentlich aus den Ausführungsplänen der Abfragen entnehmen können, ob der Kunde mit seiner Aussage recht hat oder eben auch nicht.

@BlackbirdBerlin
Hast Du beim Kundensystem irgendwie die Möglichkeit, für alle Abfragen an die Ausführungspläne zu kommen und sie dahingehend zu überprüfen, ob die Aussage des Kunden (zumindest in einem Fall) zutreffend sein könnte?

Da es sich hier ja eher um eine akademische Diskussion, die sich hauptsächlich um die Theorie dreht (und ggfls. die Fakten außen vor lässt), handelt, kannst Du die Kundenbehauptung vermutlich nur durch handfeste Fakten widerlegen.

Zitat:

Zitat von frankyboy1974

Warum legt nicht die Datenbank automatisch auf jedes Feld einen Index an. Dann würde die Suche nach diesem Feld wohl schneller funktionieren?? Und wenn ich nach zwei Felder suche, dann kombiniert die DB einfach die beide Inizes und ist dann immer noch schneller als ein Tablescan? Also mein Gedanke wäre immer noch, bei der ersten Abfrage veruchst du einen Index zu erwischen, und den Rest programmierst du selbst.

Die Frage hier ist aber doch gerade: kann die Datenbank "dann kombiniert die DB einfach die beide Inizes" das?

Sorry, wenn ich das jetzt mal überspitzt umformuliere:

Zitat:

Zitat von frankyboy1974

Also mein Gedanke wäre immer noch, bei der ersten Abfrage veruchst du einen Index zu erwischen, und den Rest programmierst du selbst.

Wenn die Datenbank so schlecht ist, programmiere doch selber eine.

Das halte ich für eine sehr schlechte Alternative. Warum nicht die Fähigkeiten der Datenbank vollumfänglich ausnutzen, sondern nur, weil der Kunde irgendwelche Restriktionen einführt, die die Leistungsfähigkeit der Datenbank einschränken, irgendwas drumherum programmieren?

Zitat:

Zitat von mkinzler

Ein Index beschleunigt die Suche, verlangsamt aber Insert/Update/Delete Vorgänge, weil auf Grund dieser ja Aktualsierung der Indizes notwendig wird. Deshalb wird automatisch nur ein Index für den Primärindex erzeugt.

Update und Delete machen eine DB bei der Pflege der Daten sicherlich langsamer, durch die Indexpflege. Aber der zu pflegende Index sorgt auch dafür, dass die zu ändernden oder zu löschenden Daten schneller gefunden werden, als durch einen Full-Table-Scan.
Es wäre jetzt also der Beweis zu erbringen, was aufwändiger ist: Die zusätzliche Pflege oder die Suche der zu ändernden/löschenden Datensätze ohne Index.

Zitat:

Zitat von Sir Rufo

Jeder Index auf einer Tabelle ist intern eine weitere Tabelle mit den Inhalten der indizierten Felder. Darum macht z.B. ein Index mit allen Feldern keinen Sinn, weil ich damit nur die Tabelle dupliziere.

Jeder Index belegt auch Speicher. Jeder Index muss wie die Tabelle aktualisiert werden. Mit einem Index-Wildwuchs kann ich den Plattenplatz verbraten und die Einfüge-Geschwindigkeit in den Keller drücken.

Es ist also ein Balance-Akt zwischen Speicherplatz und Abfrage-/Einfüge-Geschwindigkeit.

Ein Index oder eine Indexkombination über mehrere Felder ist natürlich nur für solche Felder sinnvoll, auf die häufig zur Datenauswahl zugegriffen wird.
Die unterschiedliche Reihenfolge identischer Spalten in mehrere Indizes kann dagegen durchaus sinnvoll sein. Nicht nur für die Auswahl einer zu selektierenden Teilmenge, sondern auch für deren sortierte Ausgabe in der im Index enthaltenen Reihenfolge.
Selbst bei einer uneingeschränkten Ausgabe aller Daten in der im Index festgelegten Reihenfolge, kann dies zu deutlichen Laufzeitverkürzungen führen.

Der letzte Satz von Sir Rufos Aussage ist sehr wesentlich, es ist ein Balance-Akt, der hier aber vom Kunden durch Vorgaben ggfls. sehr starkt behindert werden kann.
Wenn der optimale Index für eine große, immer wiederkehrende Abfrage ein Index aus einer Spaltenkombination ist, so wird hier durch die Einschränkung ein Index = eine Spalte, die Möglichkeit des Ausbalanzierens doch deutlich eingeschränkt.

[OT]die Steigerung von optimal, optimaler, am Optimalsten nutzen wir eigentlich nur, wenn irgendetwas so richtig Sch.....adedaskeinsalzdranwar ist.[/OT]

[mkinzler]

Zitat:

Update und Delete machen eine DB bei der Pflege der Daten sicherlich langsamer, durch die Indexpflege. Aber der zu pflegende Index sorgt auch dafür, dass die zu ändernden oder zu löschenden Daten schneller gefunden werden, als durch einen Full-Table-Scan.
Es wäre jetzt also der Beweis zu erbringen, was aufwändiger ist: Die zusätzliche Pflege oder die Suche der zu ändernden/löschenden Datensätze ohne Index.

Wenn automatisch für jedes Feld ein Index angelegt wird, wird m.E. bei zunehmender Anzahl der Felder schnell das Finden des DS belanglos werden.

Zitat:

Es müsste heißen: Darum ergibt z.B. ein Index mit allen Feldern in identischer Reihenfolge keinen Sinn, weil ich damit nur die Tabelle dupliziere.

Der Index auf den Schlüssel hätte sda aber den selben Effekt.

[nahpets]

@BlackbirdBerlin
Kennst Du diese Seite Database Administrator's Guide, 16 Managing Indexes?

Unter Order Index Columns for Performance wird (meiner Meinung nach) Deine Aussage bestätigt:

Zitat:

Zitat von BlackbirdBerlin (Beitrag 1315286)

Zitat:

Zitat von Sir Rufo (Beitrag 1315285)

...Darum macht z.B. ein Index mit allen Feldern keinen Sinn, weil ich damit nur die Tabelle dupliziere.

Kann man uneingeschränkt so nicht gelten lassen, finde ich.

Es müsste heißen: Darum ergibt z.B. ein Index mit allen Feldern in identischer Reihenfolge keinen Sinn, weil ich damit nur die Tabelle dupliziere.

Beispiel:

Code:

 Tabelle:
  BelegNr
  BelegJahr
  Kunde
  Artikel

 Index:
  Kunde
  Artikel
  BelegJahr
  BelegNr

Hier würde der Index m.E. durchaus Sinn ergeben, obwohl alle Felder enthalten sind!

Aber auch hier lasse ich mich gern eines Besseren belehren

Viele Grüße und sehr vielen Dank für Eure Beteiligung!
Tim

[mm1256]

Zitat:

Zitat von BlackbirdBerlin (Beitrag 1315280)

...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.

Nun, ich bin ja ein eher praktisch denkender Mensch, und darum würde ich das Problem einfach mal eben auch von der praktischen Seite angehen. Denn, so schön die Diskussion hier mitzulesen ist, und so unterschiedlich die DB's das handhaben, Klarheit wird letztendlich nur ein Test bringen.

Da die Struktur der DB bekannt ist, ist das Anlegen von ein paar Mio Datensätzen in drei Test-Tabellen in allen drei Varianten (Original wie beim Kunden, mit Einzel-Index und mit zusammengesetztem Index) keine Hexerei.

Wenn meine Annahme stimmt, hau ich dem Kunden das Ergebnis um die Ohren und bin der King. Wenn nicht, verkriech ich mich in mein stilles Örtchen und warte bis der Anfall vorüber ist :duck:

[BlackbirdBerlin]

Hi Otto,

das Problem beim Mal-Eben-Testen ist, dass selbst die QS in einer Hochverfügbarkeitsumgebung läuft, weil da viele Projekte parallel laufen und mal eben einen Index für knapp 200 Mio Datensätze anlegen oder drei Testtabellen ins System kippen und damit zig ( oder mehr ) Leute bis zur Behebung des Problems arbeitsunfähig machen, geht leider nicht.
In einer "eigenen" Umgebung solche Tests fahren, ist natürlich kein Problem. Aber da hat man eben nicht die Gewissheit, dass die Systemumgebung und die Datenkonstellation zu dem gleichen Ergebnis führen.

Viele Grüße
Tim

[mm1256]

Zitat:

Zitat von BlackbirdBerlin (Beitrag 1315325)

In einer "eigenen" Umgebung solche Tests fahren, ist natürlich kein Problem. Aber da hat man eben nicht die Gewissheit, dass die Systemumgebung und die Datenkonstellation zu dem gleichen Ergebnis führen.

Ich habe natürlich "eigene Umgebung" gemeint.

Bitte korrigiere mich, wenn ich da etwas falsch sehe:

Systemumgebung: Auch wenn die DB auf deinem eigenen Test-Server mit anderer Hardware läuft, sollten zuverlässige Ergebnisse möglich sein. Dass die Ergebnisse/Zeiten natürlich von der realen Umgebung beim Kunden abweichen, ist mir schon klar. Die Relation der Unterschiede untereinander dürfte jedoch eindeutig zu erkennen sein. Und darum geht es doch letzendlich. Wenn die Query-Zeiten ohne Index und mit Index z.B. im Verhältnis 10:3 wären, dann können die Unterschiede zur Systemumgebung des Kunden nicht ausschlaggebend sein. Denn ob es beim Kunden dann 10:5 oder 10:2 wäre, ist unerheblich. Die Steigerung wäre enorm. Etwas anders sieht es aus, wenn das Verhältnis nahezu gleich wäre. Aber auch dann hast du wenigstens einen Anhaltspunkt, was Sache ist.

Datenkonstellation: Wenn kein Join involviert ist...

Zitat:

Zitat von BlackbirdBerlin

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.

...dürfte das auch kein Problem sein.

[jobo]

Zitat:

Zitat von mm1256 (Beitrag 1315338)

..
Bitte korrigiere mich, wenn ich da etwas falsch sehe:

Systemumgebung: Auch wenn die DB auf deinem eigenen Test-Server mit anderer Hardware läuft, sollten zuverlässige Ergebnisse möglich sein. Dass die Ergebnisse/Zeiten natürlich von der realen Umgebung beim Kunden abweichen, ist mir schon klar. Die Relation der Unterschiede untereinander dürfte jedoch eindeutig zu erkennen sein. Und darum geht es doch letzendlich. ..

Ja, darum geht es primär und das kann man testen, auch in Relation.

Ich würde aber ergänzen:
Der springende Punkt bei solchen Systemen ist, dass sie idR einer Fülle von anderen Anforderungen- von OLTP bis ggF. Reporting- ausgesetzt sind, dass sie über Monate bis Jahre getuned und optimiert wurden und zwar bis in die Formulierung einzelner Abfragen hinein. Und das sehr wahrscheinlich zu einem guten Teil mit einem empirischen Vorgehen, sprich "nicht nachvollziehbar". Status Quo im Produktivsystem ist dann einfach, dass es eben läuft. Und das lässt man nicht gern fahren für einen blöden Index von einem Externen für einen Report, den kein einziger Manager braucht.
Das Risiko ist einfach, dass das System bei irgendwelchen zuvor harmlosen Abfragen kippt und steht.

Das bedeutet am Ende, die Änderung mag für sich Sinn machen und funktionieren und fürchterlich viel Speed bringen für die Abfrage. Wie aber reagiert der Rest des Systems? Das kann ich nur in einem Echtsystem feststellen in dem ein Arbeitstag, eine Arbeitswoche, ein Abschluss, etc pp durchgeorgelt wird.

[Blup]

Variante 1: Ein Index, der die selben Spalten in der gleichen Reihenfolge und Ausrichtung (desc) enthält, wie diese in der Abfragebedingung auftreten.
- optimale Geschwindigkeit bei der Abfrage
- wenn sehr viele Indexe, Zeitaufwand beim Insert/Update
- hohe Entwicklungskosten und Pflegeaufwand

Variante 2: Für jede Spalte die in der Abfragebedingung auftaucht einen eigenen Index.
- Zeitaufwand bei der Abfrage

Meine Erfahrungen decken sich mit dem Zitat von Uwe Raabe:

Bei Interbase ist Variante 1 fast schon zwingend.
Variante 2, erfordert die Abfrage auf dem Server sehr viel Speicher und ist um Größenordnungen langsamer.

Unter Firebird sind Variante 1 und 2 dagegen fast genauso schnell bei der Abfrage.
Deshalb würde ich in der Regel dort Variante 2 einsetzen.

[IBExpert]

kleiner Hinweis in dem Zusammenhang übrigens in Bezug auf Performanceunterschiede bei viel/wenig Indizes
Folgende Werte habe ich vor kurzem mal in einer Kundenschulung ermittelt:

Tabelle

Code:

create table test
(id bigint not null primary key,
txt varchar(80))

Variante 1: auf der Spalte TXT wurde genau 1 Index angelegt
Variante 2: auf der Spalte TXT wurden 100 Indizes angelegt

Test Insert von 10000 Datensätzen

Laufzeit Variante 1 mit einem Index auf TXT: 0,35 Sekunden
Laufzeit Variante 2 mit 100 Indizes auf TXT: 12,31 Sekunden

Zu viele Indizes anlegen, die man eigentlich nicht braucht, ist also ein erheblicher Zeitfaktor beim Schreiben.
Vorteile ergeben sich daher bei Firebird für die Variante single column indizes, weil der Optimierer diese
nach Bedarf kombiniert benutzen kann. Für andere Datenbanken gilt diese Regel aber eben nicht, weil die meisten
eben keine Indizes kombiniert benutzen können.

Ich sehe auch immer wieder Datenbanken beim Kunden, wo gleichartige Indzes mehrfach existieren. Das lässt sich
mit einem SQL in Firebird über RDB$INDICES und RDB$INDEXSEGMENTS relativ einfach finden. Auch non unique Indizes
auf Feldern, wo eh schon unique indizes existieren, ergeben selten Sinn.

Es ist auch hier wie fast immer: Es kommt drauf an, was man braucht und wie die eigene Umgebung damit umgeht.

Ach ja, und ergänzend: Bei Tabellen mit 200 Mio Datensätzen oder noch mehr würde ich auch versuchen, kombinierte
Indizes gemäß der SQL Anforderungen bereitzustellen, weil bei wirklich großen Datenmengen (meiner Meinung nach
ab 10 mio Datensätze) die Kombination von Single Column Indizes nicht unbedingt den gleichen Speed ergibt.
Wenn die Kombinierten Indizes aber nicht passen oder halt was fehlt, wie hier bei der ursprünglichen Kundentabelle,
dann sollte man eben ggf. die verschlechterte Schreibleistung in Kauf nehmen, weil am Ende der Server weniger
Last durch unsinnige Lesevorgänge hat und über alles betrachtet fast immer trotzdem bessere Performance haben wird.


Testgrundlage war hier wieder Firebird 2.5.4

[Dejan Vu]

Es ist grundsätzlich keine gute Idee, in einer zeitkritischen Umgebung irgendwelche Abfragen. Ich würde in solcher einer Umgebung alles auf die Produktivperformance legen und dann über einen Synchronisationsmechanismus (Replikation, Shadow o.ä.) eine 'Reporting-DB' pflegen, die dann eher abfrageoptimiert ist.

[BlackbirdBerlin]

Hi zusammen.
Nochmal der Hinweis, dass es sich hierbei um SAP handelt und da hängt i.d.R. eine Oracle oder DB2 drunter und für Reporting gibt's oft ein extra BI-System. Diese Daten, um die es hier geht, werden aber aus dem ERP gezogen, da nicht bei jeder Gesellschaft die gleichen Voraussetzungen (z.B. BI vorhanden) herrschen. Der gemeinsame Nenner ist das ERP und das Vorhandensein der größtenteils identischen Datenbasis darin.
Viele Grüße
Tim

[Dejan Vu]

Alles klar. Danke für den Hinweis. Hast Du meine Links gelesen?

[BlackbirdBerlin]

Ja, danke. Die hatte ich gesehen und "schnell-gelesen"
Ich habe Bitmapped-Indexes (das sollte ja die technische Basis zur Kombination von Indizes mit einem Feld jeweils) so verstanden, dass diese bei sehr großen Datenmengen und bei hoher Kardinalität wenig sinnvoll sind.
Daher scheint es mir unumgänglich, einen kombinierten Index über alle Felder, die in meiner Abfrage verwendet werden, zu definieren.

Hoffe, ich hab jetzt nix durcheinander gewürfelt...

Viele Grüße
Tim

[jobo]

Zitat:

Zitat von BlackbirdBerlin (Beitrag 1315527)

..Hinweis, dass es sich hierbei um SAP handelt und da hängt i.d.R. eine Oracle oder DB2 drunter..

Rein aus Interesse: Mit welchen Versionsständen wird da gearbeitet?

[BlackbirdBerlin]

Hi Jobo,
welche Versionsstände meinst Du?
SAP (welche Komponente), DB, ...?
Viele Grüße
Tim

[jobo]

Zitat:

Zitat von BlackbirdBerlin (Beitrag 1315540)

welche Versionsstände meinst Du?

Vor allem DB, SAP kenne ich kaum, vlt die Komponenten um die es hier bzw. beim DB Zugriff geht.