Oracle Query Tuning

[BaseBallBatBoy]

Ich habe folgende Query, welche bei mir ca. 17h braucht.
Dies dauert leider zu lange, daher muss ich nun hier optimieren.

Query:

SELECT 
       b.LINE_A
     , b.LINE_B
     , b.LINE_C
     , b.LINE_D
     , b.LINE_E
     , e.LINE_X
     , a.LINE_Y
  FROM tableA a
  INNER JOIN tableB b
    ON a.LINE_A = b.LINE_A
  INNER JOIN tableC c
    ON b.LINE_A = c.LINE_A
  LEFT OUTER JOIN tableD d
    ON c.LINE_K = d.LINE_K 
  LEFT OUTER JOIN tableE e
    ON d.LINE_S=e.LINE_S
    AND LTRIM(RTRIM(e.LINE_S)) IS NOT NULL
AND LTRIM(RTRIM(e.LINE_X)) IS NOT NULL
AND a.LINE_V = 0;

Noch einige Angaben zu den Daten:
tableA hat 50mio rows. Ca. 2.5mio (5%) haben LINE_V = 0, der Rest hat LINE_V = 1
tableB hat 250mio rows.
tableC hat 80mio rows.
tableD hat 200k rows
tableE hat 200k rows.


Und die Indexe:
tableA: PK Index auf LINE_A, Bitmap Index auf LINE_V
tableB: B*Tree Index auf LINE_A
tableC: B*Tree Index auf LINE_A, B*Tree Index auf LINE_K,
tableD: B*Tree Index auf LINE_K, B*Tree Index auf LINE_S
tableE: B*Tree Index auf LINE_S
(die Tabellen haben auch noch weitere Indexe, welche aber in diesem Zusammenhang keine Rolle spielen sollten, da sie auf anderen Spalten liegen)


Trotz dieser Indexe verwendet der Optimizer keinen einzigen Index und macht immer Full Table Scan.
Auch wenn vorher ANALYZE TABLE x ESTIMATE STATISTICS; für jede der Tabellen gemacht wurde.

Der /*+ RULE */ Hint hat auch keine Veränderung gebracht. Mit einem Index Hint kann ich erzwingen, dass der Bitmap Index verwendet wird.


Hat mir jemand eine Idee was ich noch versuchen könnte?

 

[MPr]

ein paar ungeordnete Fragen/Bemerkungen dazu:
- welche Oracle-Version ist im Einsatz?
- wäre es möglich den Plan anzuhängen? Ohne die Details ist es schwer, etwas über das Costing zu sagen.
- gibt es Histogramme für table_a.line_v? Wenn nicht, dann nimmt der Optimizer eine Gleichverteilung der Werte an und rechnet demnach mit 25M rows aus tableA.
- Mit der korrekten Annahme von 2,5M rows könnte der Optimizer zum Ergebnis kommen, den Index zu verwenden, aber ob das effizienter wäre, ist eine andere Frage: wenn die Daten stark geclustert sind - also line_v = 0 in relativ wenigen Blocks der Tabelle auftritt, dann kann der Index-Zugriff günstig sein. Aber wenn fast jeder Block einen zugehörigen Eintrag enthält, ist es sinnvoller die Tabelle über full table scan zu lesen.
- der RULE-Hint weist das System an, den (seit langer Zeit nicht mehr weiterentwickelten) regelbasierten Optimizer zu verwenden. Das mag zwar in Einzelfällen einen brauchbaren Plan hervorrufen, ist aber eher Zufall als eine brauchbare Strategie.
- auch das ANALYZE-Kommando ist ein Relikt aus vergangenen Zeiten: für halbwegs aktuelle Releases (>= 9) sollten die Prozeduren des DBMS_STATS-Packages verwendet werden.

Grundsätzlich gilt: wenn es keine selektiven Einschränkungen gibt, dann bringen die schönsten Indizes nichts. Und angesichts der relativ großen Datenmengen ist ohne entsprechende Filter eine hohe Laufzeit wahrscheinlich kaum zu vermeiden. Unter Umständen könnte man durch Partitionierung etwas erreichen (list partitioning für line_v in tableA, sofern dieses Statusfeld ein regelmäßig verwendetes Einschränkungskriterium ist), je nach Hardware-Ausstattung und sonstiger Nutzung der DB könnte man auch über einen parallelisierten Zugriff nachdenken. Aber ich halte es für nicht besonders wahrscheinlich, dass ein indizierter Zugriff für den gegebenen Fall zu einer niedrigeren Laufzeit führt als eine Reihe von HASH JOINs mit Full Table Scans.

Gruß

Martin

 

[BaseBallBatBoy]

Danke für deine Antwort und sorry, dass ich erst jetzt schreibe. Ich hatte und habe gerade viel um die Ohren und kann daher dir noch nicht alle Fragen beantworten. Aber ich wollte einfach schon mal danke sagen. DBMS ist Oracle 11g EE 11.2.0.3.0. Ich schreibe wieder sobald ich kann.