È necessario reimpostare il valore della sequenza in Oracle

Sto lavorando con Spring e Hibernate per sviluppare applicazioni web in Java. Supponiamo di avere un tavolo. Quando elimino alcuni record da questa tabella, a volte ho bisogno di reimpostare il valore del campo chiave primaria.

Diciamo che ho 10 record in una tabella e cancello gli ultimi 5 record. Ora, quando inserisco nuovi record, il valore del campo chiave primaria deve essere avviato a 6 ma inizierà a 11 .

Se ho bisogno di avviare il valore della chiave primaria a 6 ( maximum +1 ) in MySql, ho solo bisogno di eseguire la seguente dichiarazione SQL.

 alter table table_name auto_increment=1; 

Questo ripristinerà automaticamente il valore di auto_increment al maximum + 1 valore di quel campo (può essere concettualmente non corretto ma funziona).

In Oracle (10g), sto usando la sequence con la chiave primaria. C’è un modo in Oracle per ripristinare il valore della sequence al maximum + 1 valore quando alcuni record vengono cancellati dal database?

Motivi per cui non dovresti reimpostare il valore se è in uso:

Cosa succede se hai 20 record e cancelli i record 5-10? Hai un gap nel mezzo che re-impostare la sequenza non risolverà. Le sequenze non genereranno mai una sequenza di numeri senza spazi , un perfetto 1, 2 .. n .

Se chiami .nextval e non usi il valore è andato . Hai intenzione di abbandonare e ricreare la sequenza? Se avvii un inserto e lo annulli e Oracle ripristina ciò che hai fatto, quei valori sono spariti . Se imposti nocache , avrai meno spazi vuoti ma al costo di un successo in termini di prestazioni; ne vale la pena?

La cache deve essere impostata sul numero di inserti che si prevede di eseguire in qualsiasi momento su tutte le sessioni per evitare problemi di prestazioni. Le sequenze sono progettate per fornire un modo molto rapido e scalabile di creare una chiave surrogata senza blocchi ecc. Per non rigenerare il set di numeri interi positivi.

Alla fine della giornata non dovrebbe importare il minimo. Se ti affidi a una sequenza ininterrotta come chiave del tuo tavolo, allora hai un problema con i tuoi dati piuttosto che con le sequenze.


Rispondere alla domanda:

Per rispondere effettivamente alla tua domanda dovresti:

  1. In primo luogo, scopri qual è il valore di id (sequenza) massimo nella tua tabella.
  2. Quindi rilasciare e ricreare la sequenza .

Trovare il valore massimo significa che dovrai ricreare la sequenza in modo dinamico al costo di un altro colpo alle prestazioni.

Se provi ad inserire qualcosa nella tua tabella mentre questo sta accadendo, fallirà, e potrebbe invalidare qualsiasi trigger o altri oggetti che usano la sequenza:

 declare l_max_value number; begin select max(id) into l_max_value from my_table; execute immediate 'drop sequence my_sequence_name'; -- nocache is not recommended if you are inserting more than -- one row at a time, or inserting with any speed at all. execute immediate 'create sequence my_sequence_name start with ' || l_max_value || ' increment by 1 nomaxvalue nocycle nocache'; end; / 

Come ho detto questo non è raccomandato e dovresti semplicemente ignorare eventuali lacune.


Aggiornamento – alias Una risposta migliore Grazie a Jeffrey Kemp :

Contrariamente alla raccomandazione della documentazione, come suggerito da Jeffrey Kemp nei commenti, c’è un modo per farlo senza perdere e ricreare la sequenza.

Vale a dire, da:

  1. Elaborare la differenza tra l’ id massimo nella tabella e il valore corrente della sequenza.
  2. Modifica della sequenza da incrementare con questo numero negativo
  3. Modifica della sequenza per incrementare di 1 ancora.

I vantaggi di questo sono che l’object esiste ancora così e trigger, concessioni ecc sono ancora mantenuti. Il lato negativo, a quanto vedo, è che se un’altra sessione incrementa con questo numero negativo contemporaneamente al tuo, puoi tornare indietro.

Ecco una dimostrazione:

Configura il test:

 SQL> create sequence test_seq 2 start with 1 3 increment by 1 4 nomaxvalue 5 nocycle 6 nocache; Sequence created. SQL> SQL> create table tmp_test ( id number(16) ); Table created. SQL> SQL> declare 2 l_nextval number; 3 begin 4 5 for i in 1 .. 20 loop 6 insert into tmp_test values ( test_seq.nextval ); 7 end loop; 8 9 end; 10 / PL/SQL procedure successfully completed. SQL> SQL> select test_seq.currval from dual; CURRVAL ---------- 20 SQL> SQL> delete from tmp_test where id > 15; 5 rows deleted. SQL> commit; Commit complete. 

Ripristina la sequenza

 SQL> SQL> declare 2 3 l_max_id number; 4 l_max_seq number; 5 6 begin 7 8 -- Get the maximum ID 9 select max(id) into l_max_id 10 from tmp_test; 11 12 -- Get the current sequence value; 13 select test_seq.currval into l_max_seq 14 from dual; 15 16 -- Alter the sequence to increment by the difference ( -5 in this case ) . 17 execute immediate 'alter sequence test_seq 18 increment by ' || ( l_max_id - l_max_seq ); 19 20 -- 'increment' by -5 21 select test_seq.nextval into l_max_seq 22 from dual; 23 24 -- Change the sequence back to normal 25 execute immediate 'alter sequence test_seq 26 increment by 1'; 27 28 end; 29 / PL/SQL procedure successfully completed. SQL> SQL> select test_seq.currval from dual; CURRVAL ---------- 15 SQL> 

Quanto segue funziona anche (aumenta il valore della sequenza di 100):

 select my_sequence.nextval from dual connect by level <= 100; 

Con riferimento alla documentazione di Oracle ALTER SEQUENCE ,

  • Per riavviare la sequenza con un numero diverso, è necessario eliminarla e ricrearla.

È necessario passare il valore massimo corrente della colonna della chiave primaria per generare il successivo numero di sequenza.
Se si continua a cancellare alcuni record più recenti e si desidera riutilizzare i valori di sequenza già generati, potrebbe essere necessario riavviare questa sequenza più e più volte. L’eliminazione di una sequenza può portare a SQLException se il server riceve una richiesta per il valore successivo prima che la sequenza sia pronta per essere pubblicata.

Puoi anche trovare alcune note utili su Oracle Sequences qui .

Questo trucco funziona per me. Nella mia sequenza (USERSUTPL_USERUTPL_SQ) l’ultimo numero era 53. L’ultimo id nella mia tabella era 83

 SELECT MAX(ID) FROM USERSUTPL_USERUTPL 

Quindi 83 – 53 = 31. Quindi:

 ALTER SEQUENCE USERSUTPL_USERUTPL_SQ INCREMENT BY +31; SELECT USERSUTPL_USERUTPL_SQ.NEXTVAL FROM dual; ALTER SEQUENCE USERSUTPL_USERUTPL_SQ INCREMENT BY 1; 

E i suoi cambiamenti l’ultimo numero. : D