Verständnisfrage zum Indexieren?

[Martin R.]

Hallo Zusammen,

habe da mal folgende Frage.

Ich habe eine Index auf Spalte xy (varchar2) gesetzt.

Bei einem Statement

select * from Tabelle where xy=1000
wird der Index nicht genutzt

bei
select * from Tabelle where xy='1000'wird er genutzt.

Wie geht oracle da vor

Ergänzung

Und noch eine Frage
Durch welche Konstellation ignoriert der Optimizer hints, in diesem Fall den, den Index zu nutzen(unabhängig von der Fragestellung oben)?
Ich habe zwar gelesen, dass der Optimizer diese Möglichkeit hat, aber nicht, wann er diese nutzt.

Vielen Dank schon mal!

Viele Grüße

Martin

[Bernhard Geyer]

Mich wundert das er beim ersten Statement nicht einen Fehler über falschen Spaltentyp meldet.
Sieht für mich danach aus als hätte deine Oracle-Installation hier einen Bug.

[Martin R.]

Hallo,

einen Fehler hätte ich im nachhinein auch erwartet.

Die Frage resultiert aus einem Zufall.
Früher war der Wert numerisch, daher hatte ich dieHochkommata vergessen.
Ich wunderte mich dann aber, dass kein Index verwendet wurde auch nicht, nach dem ich einen hint nahm.

Oracle(11g) scheint wohl impliziet zu konvertieren.

Meine Fragen bleiben aber trotzdem bestehen, halt Interessehalber

Danke Dir!

Gruß

Martin

[Bernhard Geyer]

Ob man für deine Frage eine allgemeine Antwort finden kann?

Wir hatten schon einige Fälle (von Joins) wo jede andere DB (MS SQL-SErver, MySQL) schön ihren Index verwendet hat aber Oracle zu dumm war zu erkennen das es diesen verwenden kann.

[defo]

Ich glaub nicht, dass das ein Bug ist.

Es geht um implizite Konvertierung. Die ist generell nicht erstrebenswert, weil nicht so gut vorhersehbar.

Die Frage ist, was Oracle hier tut. Konvertiert es die Zahl in einen Text, bevor die Abfrage tatsächlich bearbeitet wird oder konvertiert es vielleicht die Spalte in eine Zahl?

Da der Index nicht greift, vermute ich letzteres (zumindest bei der bestimmung des ausführungspfades).

Du solltest es lieber korrekt abfragen (keine implizite Konvertierung). Falls das nicht möglich ist (legacy Anwendung), könnte ein funktion based index helfen, der die to_number Konvertierung enthält. Das macht natürlich nur Sinn, wenn die Spalte entgegen der Definition nur Zahlen enthält.

[Wernfried]

Für mich stimmt der Plan, die indizierte Spalte ist vom Type VARCHAR2.

Wenn du mit xy = 1000 kommst kann der Index nicht verwendet werden weil im Index ja keine Zahlen stehen, sondern VARCHAR2.

Versuche mal WHERE xy = TO_CHAR(1000), dann sollte der Index genommen werden.

Gruss

[Martin R.]

Für mich stimmt der Plan, die indizierte Spalte ist vom Type VARCHAR2.

Wenn du mit xy = 1000 kommst kann der Index nicht verwendet werden weil im Index ja keine Zahlen stehen, sondern VARCHAR2.

Versuche mal WHERE xy = TO_CHAR(1000), dann sollte der Index genommen werden.

Gruss

aber die DB konvertiert die Zahl in einen String, sonst kann nicht selektiert werden.

Daher die Frage, wie oracle intern vorgeht.

[Martin R.]

Ob man für deine Frage eine allgemeine Antwort finden kann?

Wir hatten schon einige Fälle (von Joins) wo jede andere DB (MS SQL-SErver, MySQL) schön ihren Index verwendet hat aber Oracle zu dumm war zu erkennen das es diesen verwenden kann.

DA gehe ich mal davon aus, dass die db die Statistiken falsch interpretiet.'Dumm' aber ok.

Aber warum nutz oracle manchmal Indexes nicht, obwohl der DB expiziet mitgeteilt wird, dass sie den Index nutzen soll?

Gruß

Martin

[Falk Prüfer]

Hallo,

...Aber warum nutz oracle manchmal Indexes nicht, obwohl der DB expiziet mitgeteilt wird, dass sie den Index nutzen soll?

Das dürften eigentlich nur Fälle sein, in denen der Index einfach nicht benutzt werden KANN. Eben z.B. die numerische Suche auf einem alphanumerischen Index oder anderen Kriterien, die explizit die Verwendung eines Index (egal welchen) unmöglich machen.
Wenn du Brot schneiden musst, dann kann dir deine Mutter dreimal sagen du sollst eine Gabel verwenden - du wirst sie nicht nehmen, weil Brot schneiden mit einer Gabel eben nicht geht.

Gruß Falk

[Wernfried]

Wenn ein Select Statement ausgeführt werden soll, wird es zunächst analysiert und der Optimizer versucht abzuschätzen ob der Zugriff über einen Index vorteilhaft wäre.

Das der Index und die Bedingung unterschiedliche Datentypen sind, kann die Abschätzung nicht gemacht werden.

Weshalb Oracle nicht zuerst versucht die Bedingung in den Datentyp des Index zu konvertieren und dann die Analyse macht, kann ich dir auch nicht sagen. In deinem Fall wäre es wohl von Vorteil.

Zu deiner anderen Frage: Oracle verwendet Hints wenn sie möglich sind, andernfalls ignoriert er sie (andere DB's erzeugen eine Exception)
Was passiert wenn du einen Hint angibst?

Gruss

[Martin R.]

Das oracle die Option hat hints nicht zu nutzen, habe ich gelesen.
Leider stand nicht dabei, wann die DB bzw optimizer das macht.

Bisher hatte ich das noch nicht, zumindest nicht bemerkt.
Bisher wurden hints immer befolgt und wenn sie auch sich als performance verschlechternd zeigten.

O.a. Bsp bewegte mich nur dazu mir diese Gedanken zu machen.


Danke und Gruss

Martin

[Wernfried]

Hints sind in den meisten Fällen nur Symtombekämpfung und keine richtige Lösung - natürlich gibt es Ausnahmen, z.B. im Data Warehouse Bereich.

Eine "richtige" Lösung wäre entweder den Datentyp mit dem Index auf NUMBER zu ändern oder in der Abfrage explizit ein TO_CHAR machen.

Als allererstes solltest du jedoch die Statistiken für diese Tabelle neu berechnen lassen.

Gruss

[Martin R.]

Hallo,

meine Abfrage hat mit der Frage nichts zu tun.
Der Index ist richtig gesetzt, der optimizer nutzt ihn und der hint funktioniert auch.

s. erster Post

Die Fragestellung hat mich einfach interessiert.

Indexes per hints anfordern kann durchaus produktiv sein,
dann z.b. wenn du Wissen vom Ergebnis hast, welche der Verteilungswahrscheinlichkeit widerspricht.

Oder sehe ich das doch falsch?

[defo]

Ich bin etwas verwirrt, welche Frage hat mit der Abfrage nichts zu tun?

Die Aussage "index per hint anfordern, kann produktiv sein " ist viel zu allgemein formuliert, um etwas dagegen zu sagen.

Die Nutzung von Hints wird von Oracle nur "sparsam" oder so empfohlen. Es gibt unter 10 ca 60 Stück, unter 11 noch mehr. Die Oracle Version ist hier schon mal eine grundlegende Frage. Der Optimizer ist m.E. ist unter V10xy sehr gut geworden. Hints spare ich mir bzw. der DB idR.

Angenommen:
- Du hast saubere Statistiken
- Du verwendest keine Hints
dann hat der Optimizer jedenfalls immer die Chance, den besten Pfad zu finden (auch wenn es ungewöhnliche Verteilungen gibt).
Sobald irgendein Hint eingesetzt wird, kann der je nach Zusammenhang/Indexierung - wie Du richtig bemerkt hast- eine Verschlimmbesserung bedeuten. Bei verschachtelten Views ist u.U. gar nicht transparent, wo ein solcher Hint sitzt, das macht es dann auch nicht besser.

Nur wenn Oracle glaubt, die Anwendung des Hints sei technisch nicht möglich, wird das (natürlich) nicht gemacht.

Ich würde im Zweifel den Hint lieber weglassen und vor allem so codieren, dass in meinen Routinen keine implizite Typkonvertierung durch Oracle vorgenommen wird.

[Martin R.]

Ich bin etwas verwirrt, welche Frage hat mit der Abfrage nichts zu tun?

Die Aussage "index per hint anfordern, kann produktiv sein " ist viel zu allgemein formuliert, um etwas dagegen zu sagen.

Die Nutzung von Hints wird von Oracle nur "sparsam" oder so empfohlen. Es gibt unter 10 ca 60 Stück, unter 11 noch mehr. Die Oracle Version ist hier schon mal eine grundlegende Frage. Der Optimizer ist m.E. ist unter V10xy sehr gut geworden. Hints spare ich mir bzw. der DB idR.

Angenommen:
- Du hast saubere Statistiken
- Du verwendest keine Hints
dann hat der Optimizer jedenfalls immer die Chance, den besten Pfad zu finden (auch wenn es ungewöhnliche Verteilungen gibt).
Sobald irgendein Hint eingesetzt wird, kann der je nach Zusammenhang/Indexierung - wie Du richtig bemerkt hast- eine Verschlimmbesserung bedeuten. Bei verschachtelten Views ist u.U. gar nicht transparent, wo ein solcher Hint sitzt, das macht es dann auch nicht besser.

Nur wenn Oracle glaubt, die Anwendung des Hints sei technisch nicht möglich, wird das (natürlich) nicht gemacht.

Ich würde im Zweifel den Hint lieber weglassen und vor allem so codieren, dass in meinen Routinen keine implizite Typkonvertierung durch Oracle vorgenommen wird.

Die Abfrage im ersten Post war nur zurErklärung der Fragestellung.
Es war nur ein versehen so zu codieren.

Dies brachte mich dann dazu zu überlegen, welche Schritte, und vorallem in welcher Reihenfolge den Code abarbeitet.

Gruss

Martin