Oracle: nächste freie ID finden
Christoph Hager
Ich habe eine Tabelle (Oracle) in der jeder Datensatz eine eindeutige
ID hat. Dieses ID-Feld wird bisher einfach immer um eins erhöht.
Leider läuft dieser Zähler bald über die 100.000 drüber was er aber
nicht machen darf (die Applikation ist nicht dafür ausgelegt worden).
Es sind aber nicht 100.000 Datensätze in dieser Tabelle sondern nur so
ca. 13.000.
Als Problemlösung dachte ich mir einfach einen ID Wert der nicht in
der Tabelle vorkommt und zwischen 10.000 und 99.999 liegt wieder zu
verwenden.
Gibt es einen eleganten SQL String um den nächsten freien Wert der >
10.000 ist zu finden?
Danke
Christoph
Andrey Behrens
Hager) said:
> Ich habe eine Tabelle (Oracle) in der jeder Datensatz eine eindeutige
> ID hat. Dieses ID-Feld wird bisher einfach immer um eins erhöht.
> Leider läuft dieser Zähler bald über die 100.000 drüber was er aber
> nicht machen darf (die Applikation ist nicht dafür ausgelegt worden).
Was ist einfacher: Die Beschränkung der Applikation zu ändern oder
irgendwelche Trigger einzubauen, die auf gültige IDs testen?
Und erzähle doch mal, warum die ID nicht über 100.000 laufen kann.
> Es sind aber nicht 100.000 Datensätze in dieser Tabelle sondern nur so
> ca. 13.000.
Wenn die ID trotzdem über 100.000 zu laufen droht, wurden die Ids wohl
in der Vergangenheit mal belegt. Wie willst Du absichern, dass nicht
irgenwelche Leichen auf alte (ungültige) Ids verweisen und durch eine
neue Vergabe reaktiviert werden?
Ansonsten. Unter Postgres kann man sich mit folgendem Kommando die
nächste Id holen:
select nextval('foo_id_seq');
und mit diesem Befehl kann man die Sequence auf einen bestimmten Wert setzen:
SELECT setval ('foo_id_seq', 666);
Mit diesem Befehl bekommst Du die höchste vergebene Id
select max(id) from foo;
und die kleinste, benutzte Id:
select min(id) from foo;
So kannst Du testen ob eine Id vergeben ist:
select id from foo where id=666;
Dann gibt es noch die Möglichkeit sql funktionen zu erzeugen, die
automatisch die nächste Id holen und prüfen ob die Id schon vergeben
ist.
Syntax irgendwas wie
CREATE FUNCTION getnextfreeid () RETURNS integer
AS 'select nextval('foo_id_seq');'
LANGUAGE sql IMMUTABLE;
So, jetzt hast Du sicherlich einiges zu spielen.
Aber nochmal: Nicht Deine Datenbank hat ein Problem, sondern Deine
Software. Und mich würde ernsthaft interessieren, warum eine
Beschränkung auf < 100.000 eingebaut wurde.
Mfg
Marc Blum
wrote:
Solltest Du nicht die Möglichkeit haben, wie bereits vorgeschlagen die
Beschränkung auf 100.000 zu beseitigen, geht das so:
CREATE TABLE t
(id number primary key);
INSERT INTO t VALUES (1);
INSERT INTO t VALUES (10);
INSERT INTO t VALUES (10001);
COMMIT;
CREATE OR REPLACE FORCE VIEW v10
AS
SELECT dummy FROM DUAL
UNION ALL
SELECT dummy FROM DUAL
UNION ALL
SELECT dummy FROM DUAL
UNION ALL
SELECT dummy FROM DUAL
UNION ALL
SELECT dummy FROM DUAL
UNION ALL
SELECT dummy FROM DUAL
UNION ALL
SELECT dummy FROM DUAL
UNION ALL
SELECT dummy FROM DUAL
UNION ALL
SELECT dummy FROM DUAL
UNION ALL
SELECT dummy FROM DUAL
/
SELECT MIN(id) AS new_id
FROM (
SELECT rownum AS id
FROM v10,v10,v10,v10,v10
MINUS
SELECT id
FROM t) v
WHERE id > 10000
/
--
Marc Blum
mailto:blumN...@marcblum.de
http://www.marcblum.de
Robert Riesenberger
> Als Problemlösung dachte ich mir einfach einen ID Wert der nicht in
> der Tabelle vorkommt und zwischen 10.000 und 99.999 liegt wieder zu
> verwenden.
> Gibt es einen eleganten SQL String um den nächsten freien Wert der >
> 10.000 ist zu finden?
Vielleicht bin ich da jetzt am Holzweg, aber was spricht gegen eine Tabelle
ID_TEST, die die Spalte ID mit den Werten 10000-99999 enthält, und dann via
Trigger die kleinste Zahl, die zwar in ID.ID_TEST aber nicht in der Spalte
ID Deiner angesprochenen (z.B. ID_TEST2) Tabelle liegt, einsetzen.
So zum Beispiel:
create table id_test (id number (5 ));
create sequence test_insert_id_seq minvalue 10000 maxvalue 99999;
begin
for i in 1..10000 loop
insert into id_test (id) values (test_insert_id_seq.nextval);
end loop;
end;
select min(id) from (select id from id_test minus select id from id_test2);
Doppel-R
--
|-Suzuki GSX-R 750 '01-Kawasaki ZR 750 C '91-Gilera FXR 1xx Runner '00-|
|-------"DEPPATA!... Des gheat so!" -- W.(C). afm-ST am 24.10.98-------|
|-PIP #853 --- -- -- - http://doppel-r.is-ur.org/ - -- -- --- RRR #853-|
|--- Die afm-Reifen-FAQ - http://doppel-r.is-ur.org/reifen-faq.html ---|
Michael Rauscher
Christoph Hager schrieb:
probier mal:
SELECT min(id)+1 FROM tabelle
WHERE id+1 between 10000 AND 99999
AND id+1 NOT IN (SELECT id FROM tabelle);
>
> Danke
> Christoph
Gruß
Michael
Christoph Hager
Erstmal danke für die ganzen Tipps. Ich bin kurz nach dem Posten auf
die Idee mit der zweiten Tabelle mit den Zahlen 10000-99999 gekommen
und habs so gelöst.
Die Applikation ist ein CMS das 1997/98 genau für diese Firma gebaut
wurde (und diese Leute die das gebaut haben gibts natürlich nicht
mehr). Und dort werden erstellte Beiträge als statische HTML Seiten
erzeugt welche in einer Verzeichnisstruktur abgespeichert werden
welche die ID abbildet. Also der Beitrag mit der ID 12345 liegt dann
so vor: 1/2/3/4/5.html
Ich habe keine Ahnung was passieren würde wenn der Zähler auf 100.000
umspringt. Evtl. macht er ja richtig weiter und legt dann eben die
Files 1/0/0/0/0/0.html an. Evtl. macht aber der Rest der
Transferskripten nicht mehr mit. Evtl. geht dann irgendwas anderes in
diesem Riesensystem schief.
Also hab ich mir die Lösung mit dem Recyclen der IDS überlegt und
jetzt läuft es gerade im Testbetrieb. Soweit ich das überblicken kann
dürfte es auch keine Probleme mit alten Datenleichen in anderen
Tabellen geben da es zumeist 1-1 Zuordnungen sind.
Christoph
Timo Nentwig
<christo...@hotmail.com> wrote:
> Ich habe eine Tabelle (Oracle) in der jeder Datensatz eine eindeutige
> ID hat. Dieses ID-Feld wird bisher einfach immer um eins erhöht.
Nimmt die Systemzeit (long) oder UUID.
Marc Blum
Und wie dichtest Du das gegen die Sommer/Winterzeitumstellung ab? Da gibt es die
Stunde von 2:00 bis 3:00 zweimal.
Wie verhinderst Du, dass Datensätze, die innerhalb der Ganularität Deiner
Systemzeit eintrudeln, den gleichen Wert zugewiesen bekommen? Bei einem System
mit mehreren hundert Usern nicht unwahrscheinlich. Erst recht bei Masseninserts.
Robert Riesenberger
>>> Ich habe eine Tabelle (Oracle) in der jeder Datensatz eine eindeutige
>>> ID hat. Dieses ID-Feld wird bisher einfach immer um eins erhöht.
>>Nimmt die Systemzeit (long) oder UUID.
> Und wie dichtest Du das gegen die Sommer/Winterzeitumstellung ab?
Garnicht. Wozu auch?
> Da gibt es die Stunde von 2:00 bis 3:00 zweimal.
Aber nicht in der (Oracle)Datenbank.
> Wie verhinderst Du, dass Datensätze, die innerhalb der Ganularität Deiner
> Systemzeit eintrudeln, den gleichen Wert zugewiesen bekommen?
Unique.
> Bei einem System mit mehreren hundert Usern nicht unwahrscheinlich. Erst
> recht bei Masseninserts.
sql> select current_timestamp(9) from dual
CURRENT_TIMESTAMP(9)
---------------------------------------------------------------------------
01-APR-04 08.17.07.585564000 PM +02:00
SQL>
Ich denke, die Wahrscheinlichkeit wird eher gering sein.
Marc Blum
>Marc Blum schrieb:
>>"Timo Nentwig" <t...@spamgourmet.org> wrote:
>>> Christoph Hager <christo...@hotmail.com> wrote:
>
>>>> Ich habe eine Tabelle (Oracle) in der jeder Datensatz eine eindeutige
>>>> ID hat. Dieses ID-Feld wird bisher einfach immer um eins erhöht.
>>>Nimmt die Systemzeit (long) oder UUID.
>> Und wie dichtest Du das gegen die Sommer/Winterzeitumstellung ab?
>
>Garnicht. Wozu auch?
>
>> Da gibt es die Stunde von 2:00 bis 3:00 zweimal.
>
>Aber nicht in der (Oracle)Datenbank.
In Oracle 8.x gibt es nur DATE, der ist nur sekundengenau und kennt keine
Zeitzonen.
Ab Oracle 9 stehen dann TIMESTAMP-Attribute zur Verfügung, die
Zeitzonen-sensibel sind
>
>> Wie verhinderst Du, dass Datensätze, die innerhalb der Ganularität Deiner
>> Systemzeit eintrudeln, den gleichen Wert zugewiesen bekommen?
>
>Unique.
Danke für den Hinweis, aber das Unique ergibt sich schon aus den Anforderungen
(s.o.).Wenn neue Datensätze mit der gleichen Systemzeit angelegt werden, kracht
ebenjener Unique Constraint und ein Teil der Datensätze kann nicht angelegt
werden. Damit funktioniert der Workaround "Systemzeit als eindeutige ID" nicht.
.
>
>> Bei einem System mit mehreren hundert Usern nicht unwahrscheinlich. Erst
>> recht bei Masseninserts.
>
>sql> select current_timestamp(9) from dual
>
>CURRENT_TIMESTAMP(9)
>---------------------------------------------------------------------------
>01-APR-04 08.17.07.585564000 PM +02:00
>
>SQL>
>
>Ich denke, die Wahrscheinlichkeit wird eher gering sein.
>
Nicht denken, annehmen oder meinen. So 'nem Rechner ist Deine *Meinung* ziemlich
egal. Programmier mal 'n Loop in PLSQL, der nur INSERTs macht. Schon
erstaunlich, wieviele Datensätze den gleichen Zeitstempel erhalten. Und von
einem Mehrbenutzersystem brauchen wir gar nicht erst zu reden.
Und "die Wahrscheinlichkeit wird eher gering sein" bedeutet ganz einfach: über
kurz oder lang wird das schiefgehen. Hoffentlich erst, wenn ich aus dem Projekt
raus bin.
Robert Riesenberger
> On Thu, 01 Apr 2004 20:19:16 +0200, Robert Riesenberger <dev-...@aon.at>
> wrote:
>>Aber nicht in der (Oracle)Datenbank.
> In Oracle 8.x gibt es nur DATE, der ist nur sekundengenau und kennt keine
> Zeitzonen.
Ups, das dürft ich schon mal übersehen haben.
>>Ich denke, die Wahrscheinlichkeit wird eher gering sein.
> Nicht denken, annehmen oder meinen.
Na, das sollte wohl noch erlaubt sein.
> So 'nem Rechner ist Deine *Meinung* ziemlich egal.
Das wohl.
> Programmier mal 'n Loop in PLSQL, der nur INSERTs macht.
Also bei mir @home (9ir2) schaut das dann so aus:
SQL> create table test (id varchar2(40), text varchar2(10));
Table created.
SQL> alter table test add primary key (id);
Table altered.
SQL> ed
Wrote file afiedt.buf
1 declare v_id varchar2(40);
2 begin
3 for i in 1..10000 loop
4 select current_timestamp(9) into v_id from dual;
5 insert into test (id, text) values (v_id, 'blafasl');
6 end loop;
7* end;
SQL> /
PL/SQL procedure successfully completed.
SQL>
> Schon erstaunlich, wieviele Datensätze den gleichen Zeitstempel erhalten.
Naja, im Moment erstaunt mich das nicht so sehr.
> Und von einem Mehrbenutzersystem brauchen wir gar nicht erst zu reden.
Daran wirds vermutlich liegen.
> Und "die Wahrscheinlichkeit wird eher gering sein" bedeutet ganz einfach:
> über kurz oder lang wird das schiefgehen. Hoffentlich erst, wenn ich aus
> dem Projekt raus bin.
Tja, ich hab sicher was übersehen. Aber also huschpfusch würds
funktionieren.
> --
Ich glaub, da hats was.
Timo Nentwig
Ja, aus diesem Grund bin ich auch gerade erst auf UUID umgestiegen :)
Wobei ich mich frage, wieso Datenbanken das nicht selbst anbieten...
Frank Zaum
> In Oracle 8.x gibt es nur DATE, der ist nur sekundengenau und kennt keine
> Zeitzonen.
Eine Krücke gibt es schon: dbms_utility.get_time liefert sogar
sekundenbruchteile zurück, allerdings als zahl.
> Und "die Wahrscheinlichkeit wird eher gering sein" bedeutet ganz einfach: über
> kurz oder lang wird das schiefgehen. Hoffentlich erst, wenn ich aus dem Projekt
> raus bin.
Diesen Satz muss ich mir UNBEGDINGT merken.
Gruß, Frank