[Oracle] Nächstgrößeres Datum mittels Index finden

[Beckerman2609]

Hallo zusammen,

ich habe folgendes Problem:

In einer Tabelle habe ich ein Datumsfeld. Von einem variablen Datum möchte ich nun das nächst größere/kleinere Datum aus der Tabelle haben. Zur Geschwindigkeitsoptimierung möchte ich dafür einen Index nutzen.

Ich bekomme bereits das richtige Ergebnis, jedoch wird dabei nicht der Index angesprochen.

Beispiel:

Select datum from tabelle where datum > '01.01.2010'

Vergleiche ich mit einem Datum wird jedoch der Index angesprochen

Select datum from tabelle where datum = '01.01.2010'

Wie kriege ich es nun hin, dass auch bei einem vergleich mit < oder > der Index benutzt wird? Oder wie komme ich auf eine andere Art an den nächst größeren Datensatz eines bekannten Datums in der Tabelle?

[dr.dimitri]

Hi,

zum einen sollte man immer to_date verwenden wenn man mit Datumswerten arbeitet, denn der 12.03.2010 könnte auch der 3 Dezember sein je nachdem welche Ländereinstellung auf dem Client gesetzt ist.

Des weiteren liefert Deine Abfrage irgendein Datum das größer ist als dein angegegebenes bzw. besser ausgedrückt es liefert alle Datensätze die ein größeres Datum besitzen.

Was Du möchtest geht so:

SELECT * FROM 

    (SELECT datum FROM tabelle WHERE datum >to_date('01.01.2010','DD.MM.YYYY') ORDER BY datum DESC) a

WHERE ROWNUM=1

Beachte auch, dass ein Datum in Oracle immer auch eine Uhrzeit beinhaltet. 01.01.2010 wird also intern zu einem 01.01.2010 00:00.

Dim

[Beckerman2609]

Erstmal danke für die Antwort.

Die Abfrage beinhaltet bereits eine Filterung mit "where rownum =1". Wollte die Abfrage nur vereinfacht darstellen. Ich bekomme ja bereits ein korrektes Ergebnis. Nur leider ist der Zeitaufwand zu groß, da kein Index angesprochen wird. Dabei frage ich mich nur: Warum? Bei einem direkten Vergleich (Bsp: where datum = '01.01.2010') wird der Index angesprochen. Ersetze ich das "=" durch ein ">" oder "<" wird der Index nicht mehr benutzt.

Irgendwie ein komisches Verhalten...

[dr.dimitri]

Sind die Tabellen und der Index mit dbms_stats analysiert? Wenn Du schon eine vereinfachte Variante gepostet hast, sind denn in der echten Abfrage noch weitere Bedingungen, joins etc. drinnen? Wie ist der Index definiert?

Dim

[Beckerman2609]

Das Vorgehen mit dbms_stats ist mir unbekannt. Werde mich aber jetzt erstmal damit befassen.

Die Gesamte Abfrage ist etwas umfangreicher. Jedoch wenn ich diesen Teilausschnitt als einzelne SQL-Abfrage ausführe, komme ich zum gleichen ergebnis.

Bisher lief der Index auf "Upper (to_char('Feldbezeichnung', 'DD.MM.YYYY')) ASC"

Habe Auch schon einen Test-Index nur auf "'Feldbezeichnung' ASC" aufgesetzt.

Werde das gleich mal mit dem Vorgeschlagenen To_Date versuchen.

[dr.dimitri]

Upper (to_char('Feldbezeichnung', 'DD.MM.YYYY')) ASC

Also wenn der Index wirklich als Function Based Index mit dieser Definition angelegt wurde, dann erklärt das ja einiges. Durch das to_char wird aus dem Datum ein String, damit Funktioniert auch die Datumsarithetik natürlich nicht mehr.

Leg den index einfach wie folgt an:

CREATE INDEX index_name ON tabelle (datumsfeld)

Fertig. to_char sollte wirklich nur zur Formatierung in der Ausgabe verwendet werden.

Dim

PS: Ich gehe davon aus, dass das Datumsfeld in der Datenbank auch wirklich als DATE definiert ist.

[Beckerman2609]

Habe nun einen Index mit der TO_DATE Funktion angelegt

(Create Index indexname on tabelle (To_Date(datumsfeld,'DD.MM.YYYY') ASC)

Die ersten Tests verliefen positiv. Das scheint zu funktionieren. Werde das noch intensiver testen.

Das To_Char kam von normalen Textfeldern. Um Groß- und Kleinschreibung zu ignorieren wird also auf einem Textfeld die UPPER-Methode ausgeführt. In der Datenbank wurde dabei automatisch die Zeichenfolge "To_CHAR" angehängt.

Aus diesem Grund wurde obligatorisch für jedes Feld ein UPPER eingesetzt. Eben auch bei den Datumsfeldern. Gibt es denn vielleicht auch eine möglichkeit wie man ohne diesen "UPPER-Trick" die Groß/Kleinschreibung bei einer Abfrage ignorieren kann?

Auf jeden Fall schonmal vielen Dank für diesen Ansatz

[dr.dimitri]

Heißt das, dass ihr das Datum in einem VARCHAR2 Feld speichert? Falls ja, solltet ihr ein DATE verwenden und einen normalen (also keinen Function based Index) Index anlegen.

Gross-/Kleinschreibung sollte bei einem Datum ja nicht relevant sein.

Gibt es denn vielleicht auch eine möglichkeit wie man ohne diesen "UPPER-Trick" die Groß/Kleinschreibung bei einer Abfrage ignorieren kann?

Schon, aber keiner, mit dem auch der Index verwendet wird. Im Index stehts nun mal so drinnen und so ist er auch sortiert.

Dim

[Beckerman2609]

Nein, wir speichern unsere Datumsfelder in DATE-Spalten.

Ich bin nun auf ein paar mir nicht ganz schlüssige Verhalten der Datenbank auf die Schliche gekommen. Kann es sein, dass ein Index nicht auf eine > oder < Vergleichsoperation angewendet werden kann? Habe bemerkt, dass bei einem direkten Vergleich der Index angesprochen wird, bei einem > jedoch nicht.

Um Mit "ROWNUM = 1" den richtigen Datensatz zu bekommen, muss ich die Datenmenge vorher mit "ORDER BY" sortieren. Dabei stellt sich für mich die Frage ob die Sortierung ebenfalls nicht durch einen Index realisierbar ist.

PS Danke für die Hilfe bisher. Habe echt schon mehr über Datenbanken gelernt seit diesem Eintrag

[dr.dimitri]

Kann es sein, dass ein Index nicht auf eine > oder < Vergleichsoperation angewendet werden kann?

Doch natürlich. Voraussetzung für einen Indexzugriff sind folgende Punkte:

1. Der Datentyp stimmt überein bzw. die Konvertierungsrichtlinien von Oracle erlauben eine implicierte Konvertierung in den Datentyp des Index

create table t( col1 number,col2 varchar2(10),col3 varchar2(10));

create index t_ix1 on t (col1);

create index t_ix2 on t (col2);

create index t_ix3 on t (upper(col3));


insert into t select rownum,rownum,rownum from all_objects where rownum <=10000 ;

commit;


begin

 dbms_stats.gather_table_stats(ownname=>'DIMITRI',tabname=>'T');

end;

/

Jetzt ein paar Abfragen:

explain plan for

select * from t where col1='123';

select * from table(dbms_xplan.display);


| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                      

-------------------------------------------------------------------------------------                                                                                                                                                                                                                      

|   0 | SELECT STATEMENT            |       |     1 |    14 |     2   (0)| 00:00:01 |                                                                                                                                                                                                                      

|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    14 |     2   (0)| 00:00:01 |                                                                                                                                                                                                                      

|*  2 |   INDEX RANGE SCAN          | T_IX1 |     1 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                                                      

-------------------------------------------------------------------------------------                                                                                                                                                                                                                      


Predicate Information (identified by operation id):                                                                                                                                                                                                                                                        

---------------------------------------------------                                                                                                                                                                                                                                                        


   2 - access("COL1"=123)  

Beachte die Zeile mit der access Angabe. Da Oracle bei Vergleichen zwischen Strings und Zahlen in Zahlen konvertiert, kann der Index verwendet werden. Schreibst Du statt '123' ein '123a' wird die Abfrage zwar gestartet, aber Du bekommst natürlich einen Fehler, weil '123a' nicht in eine gütige Zahl umgewandelt werden kann. Jetzt das nächste Beispiel:

explain plan for

select * from t where col2=123;

--------------------------------------------------------------------------                                                                                                                                                                                                                                  

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                                  

--------------------------------------------------------------------------                                                                                                                                                                                                                                  

|   0 | SELECT STATEMENT  |      |     1 |    14 |     9   (0)| 00:00:01 |                                                                                                                                                                                                                                  

|*  1 |  TABLE ACCESS FULL| T    |     1 |    14 |     9   (0)| 00:00:01 |                                                                                                                                                                                                                                  

--------------------------------------------------------------------------                                                                                                                                                                                                                                  

Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          

---------------------------------------------------                                                                                                                                                                                                                                                          


   1 - filter(TO_NUMBER("COL2")=123)

Obwohl auf der Spalte col2 ein Index sitzt, kann er nicht verwendet werden, da Oracle wie oben in Zahlen konvertiert. Da ein Index für einen String eine komplett andere Sortierung als für eine Zahl besitzt, kann er hier auch nicht verwendet werden und Oracle durchsucht die komplette Tabelle. Zu guter letzt der Function Based Index auf die Spalte col3:

explain plan for

select * from t where col3='123';

select * from table(dbms_xplan.display);

--------------------------------------------------------------------------                                                                                                                                                                                                                                  

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                                  

--------------------------------------------------------------------------                                                                                                                                                                                                                                  

|   0 | SELECT STATEMENT  |      |     1 |    14 |     9   (0)| 00:00:01 |                                                                                                                                                                                                                                  

|*  1 |  TABLE ACCESS FULL| T    |     1 |    14 |     9   (0)| 00:00:01 |                                                                                                                                                                                                                                  

--------------------------------------------------------------------------                                                                                                                                                                                                                                  

Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          

---------------------------------------------------                                                                                                                                                                                                                                                          


   1 - filter("COL3"='123')

Bei einem FBI muss die WHERE Bedingung immer auch den genauen Funktionsaufruf beinhalten. Eine UPPER('123') würde den Index also verwenden.

Die Konvertierungsrichtlinien von Oracle findest Du hier

Einen FBI sollte man nur verwenden, wenn es wirklich sinvoll ist. Bei einem DATE würde mit kein Fall einfallen bei dem das so wäre. Ich vermute, dass Deine Probleme z.T. auch daher kommen.

2. Die Ergebnismenge darf nicht zu groß sein

Ein Indexzugriff ist nur dann sinnvoll, wenn die Ergebnissmenge nicht zu groß wird. Wie groß die Menge sein darf variiert und hängt von vielen Faktoren ab, die der CBO in seine

Berechnungen mit einbezieht. Als Fausregel gilt, dass bei kleinen Tabellen bis ca. 10%, bei sehr großen Tabellen (Millionen von Einträgen) bis ca. 20-30% der Ergebnismenge ein Indexzugriff verwendet wird.

Wie gesagt nur als Fausregel und es gibt sehr viele Parameter, die das beeinflussen.

3. Die Statistiken müssen aktuell sein

Damit der CBO seine Formeln richtig anwenden kann, müssen die Tabellen über aktuelle Statistiken verfügen. DBMS_STATS wurde ja schon erwähnt.

4. Das SQL muss "verständlich" sein.

SQL ist eine sehr redundante Sprache, dementsprechend gibt es auch viele Möglichkeiten etwas auszudrücken. Je präziser man das macht desto eher hat der CBO eine Chance zu verstehen, was man genau machen möchte.

5. Die Tabelle spiegelt die Daten wieder.

Je genauer die Tabellendefinition zu den Daten passt, desto besser kann der CBO seine Entscheidungen abwägen.

Darf eine Spalte keine NULL Werte enthalten, setze ich einen NOT NULL Constraint, ist eine Spalte Unique, dann verwende ich auch einen Unqiue Index, enthält eine Spalte ein Datum verwende ich ein DATE usw. usw.

Um Mit "ROWNUM = 1" den richtigen Datensatz zu bekommen, muss ich die Datenmenge vorher mit "ORDER BY" sortieren. Dabei stellt sich für mich die Frage ob die Sortierung ebenfalls nicht durch einen Index realisierbar ist.

Um ein sortiertes Ergebnis zu bekommen muss man ORDER BY verwenden. Oracle wird intern natürlich die Sortierung eines Index verwenden falls möglich, aber ohne ORDER BY hast Du keine Gewährleistung wie das Ergebnis letztendlich sortiert wird (auch wenn es jetzt vielleicht passen sollte).

Und last but not least: Full Table Scans sind nicht immer langsamer als ein Indexzugriff. Falls Oracle also den Index nicht verwendet, kann es durchaus sein, dass ein Indexzugriff langsamer wäre.

Umgekehrt ist auch der CBO nicht perfekt und macht Fehler. Sofern die obigen Punkte alle erfüllt sind, kann es in Einzelfällen durchaus sein, dass man ihm mit einem Hint auf die Sprünge helfen muss

Dim

[Beckerman2609]

Also das klingt ja schonmal sehr ... interessant.

Mal von dem ganzen Index und DBMS abgesehen muss ich glaub ich noch einige Basics lernen. So direkt habe ich es immer noch nicht hinbekommen. Hast du vielleicht ein ähnliches Beispiel in dem du einen Vergleich mit > auf ein Datumsfeld absetzt?

Mal von der Datumssortierung abgesehen, gibt es eine Möglichkeit den nächsten Datensatz einer Ergebnismenge zu lesen? Ohne die Sortierung und ausgrenzung mit "Rownum=1"? Sprich: ich habe Artikelnummer 123 und möchte die nächst höhere aus dem Artikelstamm haben.

Hinsichtlich der ganzen Erklärungen -> Kennst du eine gute Tutorialseite für dieses Thema. Suche mir immer für bestimmte Datenbankprobleme eine Lösung, aber eine relative einfache Einführung habe ich für Oracle-Datenbanken noch nicht entdecken können.

[dbwizard]

Mal von der Datumssortierung abgesehen, gibt es eine Möglichkeit den nächsten Datensatz einer Ergebnismenge zu lesen? Ohne die Sortierung und ausgrenzung mit "Rownum=1"? Sprich: ich habe Artikelnummer 123 und möchte die nächst höhere aus dem Artikelstamm haben.

Hallo

Dies kannst du folgendermassen erreichen :

  SELECT id FROM MyTable WHERE id > 123 AND rownum=1 ORDER BY id

Grüsse

[dr.dimitri]

Hallo

Dies kannst du folgendermassen erreichen :

  SELECT id FROM MyTable WHERE id > 123 AND rownum=1 ORDER BY id

Grüsse

Das ist aber nicht die Anforderung. Er möchte den nächst Größeren und nicht irgendeinen der größer ist. Rownum wird vor dem ORDER BY vergeben. Daher muss hier, wie schon beschrieben, mit einem Subselect gearbeitet werden.

Wegen dem Index/Table Scan: Bevor ich hier wieder Pläne poste: Ist Dein Index jetzt mittlerweile schon ohne to_date, to_char oder was auch immer angelegt? Also ein simples create index xxx on tabelle (datum).

Dim

[Beckerman2609]

Also ich habe das mit dem

SELECT id FROM MyTable WHERE id > 123 AND rownum=1 ORDER BY id

mal ausprobiert. Es scheint zu funktionieren. Hatte auch erst bedenken mit der Sortierung. Werde das auch weiter testen.

Zu dem Index:

Habe mittlerweile zwei angelegt. Einen mit To_date und einen ohne alles (also nur 'datumsfeld')