Hilfe bei Index-Erstellung!

[danyball]

Hi Leute!

Ich arbeite auf einer ORACLE-Datenbank. Mein Explain-Plan sagt, dass er für folgende Abfragen bei allen beteiligten Tabellen ein "TABLE ACCESS FULL" durchführt.

Es interessiert mich erstmal nur die Tabelle "vorplanung". Es exisitiert dort nur 1 Index auf die Spalte "ID" mit Sortierung ASC.

--> Was für Indizes bzw. welchen kombinierten Index sollte ich für Tabelle "Vorplanung" erstellen? (ich kapier nicht wie ich einen index erstellen soll, wenn die Tabelle in mehreren joins UND in der where vorkommt)

select
                                    v.id, v.von, v.bis, v.einheit_bezeichnung_kurz as bezeichnung_kurz, v.beschreibung,
                                    va.BEZEICHNUNG_LANG as name_kalender, va.farbe_hintergrund, va.farbe_text, va.BEZEICHNUNG_KALENDER as name_kurz,
                                    eh.BEZEICHNUNG_LANG as einheit_lang
                              from vorplanung v
                              left join vorplanung_art va ON v.VORPLANUNG_ART = va.BEZEICHNUNG_KURZ
                              left join einheit eh ON eh.BEZEICHNUNG_KURZ = v.einheit_bezeichnung_kurz
                              WHERE
                              NOT
                                ((v.bis <= '') OR (v.von >= ''))

DANKE!
Daniel

[akretschmer]

leg mal Indexe auf von und bis an. Was sind das für Spalten? Also welcher Datentyp?

Außerdem solltest Du Indexe auf die Join-Spalten der zwei anderen Tabellen legen.

[danyball]

von und bis sind VARCHAR. Beinhalten aber ein Datum im amerikanischen Format.

Ich habe schon einen kombinierten Index auf von und bis gelegt. Laut explain plan hat er den index aber nicht genommen??! Ist es unrelevant, dass ich in der gleichen Abfrage mit dem Feld "v.einheit_bezeichnung_kurz" joine? Muss ich dann nicht z.b. einen kombinierten index mit "von, bis, einheit_bezeichnung_kurz" erstellen?

[akretschmer]

von und bis sind VARCHAR. Beinhalten aber ein Datum im amerikanischen Format.

Das ist strunzdumm.

Ich habe schon einen kombinierten Index auf von und bis gelegt. Laut explain plan hat er den index aber nicht genommen??!

Die Wege des Optimizers sind oft unergründlich. Verteilung der Daten und so, ...

Ist es unrelevant, dass ich in der gleichen Abfrage mit dem Feld "v.einheit_bezeichnung_kurz" joine?

Ja.

Muss ich dann nicht z.b. einen kombinierten index mit "von, bis, einheit_bezeichnung_kurz" erstellen?

Nein.

[Perry Staltic]

Ich arbeite auf einer ORACLE-Datenbank.

Welche Version?

--> Was für Indizes bzw. welchen kombinierten Index sollte ich für Tabelle "Vorplanung" erstellen? (ich kapier nicht wie ich einen index erstellen soll, wenn die Tabelle in mehreren joins UND in der where vorkommt)

Es ist egal, wie oft die Tabelle vorkommt. Es geht immer um die gejointen Spalten.
Versuch es mit einzelnen Indizes auf den Spalten, die bereits empfohlen wurden.

Ggf wäre auch zu klären, ob Statistiken für die Tabelle vorliegen, falls nicht, muss der Optimizer raten, falls es eine halbwegs aktuelle Version ist.

Außerdem:
Was soll kleiner als '' sein? Reicht nicht das leer? Oder ggF. eine Prüfung auf 'is Null'.
Je präziser Du die Bedingung formulierst, desto eher wird ein Index eingesetzt.

[danyball]

ORACLE 11g.

Ok, mein Code ist nicht ganz vollständig. Die WHERE-Klausel schaut so aus:

((v.bis <= '2015-06-01T00:00:00') OR (v.von >= '2015-05-01T00:00:00'))

[Perry Staltic]

ORACLE 11g.

Ok, mein Code ist nicht ganz vollständig. Die WHERE-Klausel schaut so aus:

((v.bis <= '2015-06-01T00:00:00') OR (v.von >= '2015-05-01T00:00:00'))

11g sollte eigentlich gut optimieren.
Was ist mit den Statistiken? Werden die automatisch erstellt?
Hast Du schon die Indizes erzeugt?

[danyball]

Ja, Statistiken werden wohl autom. gesammelt:

auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED

Indizis auf den Join-Spalten sind schon erstellt, da das Key-Spalten sind.

Und Indizis auf "bis" und "von" werden nicht verwendet. Evtl. weil das VARCHAR sind?! (werd ich ändern).

[Perry Staltic]

Ja, Statistiken werden wohl autom. gesammelt:

auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED

Indizis auf den Join-Spalten sind schon erstellt, da das Key-Spalten sind.

Und Indizis auf "bis" und "von" werden nicht verwendet. Evtl. weil das VARCHAR sind?! (werd ich ändern).

Wie sind denn die Mengengerüste, hast Du mal in DBA_Tables geschaut, wie die Sample Size ist?
Die Definition/Existenz eines Keyconstraints bedeudet nicht zwangsläufig, dass ein Index da ist.
Der Feldtyp sollte weitgehend unabhängig für die Verwendung eines Index sein, außer es ist sowas wie Boolean oder so.
Dennoch(!) ist es sehr ratsam, daraus eine Datespalte zu machen.

Insgesamt ist irgendwas faul, hast Du vielleicht noch weitere Dinge unterschlagen?
Und was ist eigentlich das Problem? Ist es langsam?
Bspw. wenn nur 2 Datensätze oder 10 oder 1000 in den Tabellen sind, macht er halt n Fulltablescan, ist bequemer und schneller.

[akretschmer]

ORACLE 11g.

Ok, mein Code ist nicht ganz vollständig. Die WHERE-Klausel schaut so aus:

((v.bis <= '2015-06-01T00:00:00') OR (v.von >= '2015-05-01T00:00:00'))

test=# create table danyball (von date, bis date);
CREATE TABLE
Time: 170,074 ms
test=*# set enable_seqscan to off;
SET
Time: 0,078 ms
test=*# explain select * from danyball v where not ((v.bis <= '2015-06-01T00:00:00') OR (v.von >= '2015-05-01T00:00:00'));
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Seq Scan on danyball v  (cost=10000000000.00..10000000042.10 rows=238 width=8)
   Filter: ((bis > '2015-06-01'::date) AND (von < '2015-05-01'::date))
(2 rows)

Time: 0,797 ms
test=*# create index idx_von on danyball (von);
CREATE INDEX
Time: 19,651 ms
test=*# create index idx_bis on danyball (bis);
CREATE INDEX
Time: 1,946 ms
test=*# explain select * from danyball v where not ((v.bis <= '2015-06-01T00:00:00') OR (v.von >= '2015-05-01T00:00:00'));
                               QUERY PLAN
------------------------------------------------------------------------
 Bitmap Heap Scan on danyball v  (cost=9.56..30.26 rows=238 width=8)
   Recheck Cond: (bis > '2015-06-01'::date)
   Filter: (von < '2015-05-01'::date)
   ->  Bitmap Index Scan on idx_bis  (cost=0.00..9.50 rows=713 width=0)
         Index Cond: (bis > '2015-06-01'::date)
(5 rows)

Ich glaube nicht, daß Oraggle hier dümmer als PG ist.

test=*# drop index idx_bis;
DROP INDEX
Time: 21,474 ms
test=*# explain select * from danyball v where not ((v.bis <= '2015-06-01T00:00:00') OR (v.von >= '2015-05-01T00:00:00'));
                               QUERY PLAN
------------------------------------------------------------------------
 Bitmap Heap Scan on danyball v  (cost=9.56..30.26 rows=238 width=8)
   Recheck Cond: (von < '2015-05-01'::date)
   Filter: (bis > '2015-06-01'::date)
   ->  Bitmap Index Scan on idx_von  (cost=0.00..9.50 rows=713 width=0)
         Index Cond: (von < '2015-05-01'::date)
(5 rows)

test=*# rollback;
ROLLBACK
Time: 0,963 ms
test=#