|
Tip of the Week
Tip for Week of November 9, 2003
Fast Reset Sequences
This tip comes from
Ilya Petrenko, Senior DBA at ICT Group, Inc. USA, in Newtown, PA.
Instead of dropping and recreating sequence(s) for existing application, or even changing sign for increment_by to negative and looping till it
reaches Min_Value, you can make fewer calls to the data dictionary by resetting sequence(s) to Min_Value. Oracle does not store the Start_With value anywhere in the data dictionary,so theoretically, the Min_Value value could be even less than the Start_With value.
Fewer calls to data dictionary will provide fewer locks and even faster results.
With this fast_reset technique, stored objects with dependencies to existing sequences do not have to be recompiled.
SQL implementation (user schema):
---------------------------------
def Seq_Name=&My_SEQuence_name
col Max_Value new_value Seq_MaxValue
col Min_Value new_value Seq_MinValue
col Cur_VALUE new_value Seq_CurValue
col CYCLE_FLAG new_value Seq_CYCLE
col CACHE_SIZE new_value Seq_CacheSize
-- Get Info
select
MAX_VALUE,
MIN_VALUE,
DECODE(CACHE_SIZE,'0','NOCACHE','Cache '||CACHE_SIZE)
CACHE_SIZE,
DECODE(CYCLE_FLAG,'Y','CYCLE','NOCYCLE') CYCLE_FLAG
from
user_sequences
where
sequence_name=UPPER('&Seq_Name')
and rownum<2
;
-- No CACHE Values for Next Seq Numbers
alter sequence &Seq_Name NoCACHE;
-- Get Current Value Number
select
LAST_NUMBER - INCREMENT_BY Cur_VALUE
from
user_sequences
where
sequence_name=UPPER('&Seq_Name')
and rownum<2
;
-- Cycle with MaxValue as CurValue
alter sequence &Seq_Name
CYCLE MaxValue &Seq_CurValue
;
-- Reset to Min
select &Seq_Name..NextVal from dual;
-- Return All Rules BACK
alter sequence &Seq_Name
MAXVALUE &Seq_MaxValue &Seq_CacheSize &Seq_Cycle
;
--Check After All manipualtions:
--------------------------------
select * from user_sequences where sequence_name=UPPER('&Seq_Name') and rownum<2;
PL/SQL implementation (schema independent):
-------------------------------------------
create or replace
procedure FAST_RESET_SEQUENCE ( Seq_name Varchar2 default Null,
Seq_Owner varchar2 default USER ) IS
Seq_MaxValue number;
Seq_MinValue number;
Seq_CurValue number;
Seq_CYCLE varchar2(7);
Seq_CacheSize varchar2(30);
Seq_Value number;
BEGIN
If Seq_name is NULL then goto Nothing_To_DO; end if;
-- Get Info
select
MAX_VALUE,
MIN_VALUE,
DECODE(CACHE_SIZE,'0','NOCACHE','Cache '||CACHE_SIZE)
CACHE_SIZE,
DECODE(CYCLE_FLAG,'Y','CYCLE','NOCYCLE')
CYCLE_FLAG
INTO
Seq_MaxValue,
Seq_MinValue,
Seq_CacheSize,
Seq_Cycle
from
all_sequences
where
sequence_owner=UPPER(Seq_Owner)
and sequence_name=UPPER(Seq_Name)
and rownum<2
;
-- No CACHE Values for Next Seq Numbers
execute immediate 'alter sequence '||Seq_Owner||'.'||Seq_Name||'
NoCACHE';
-- Get Current Value Number
select
LAST_NUMBER - INCREMENT_BY Cur_VALUE
Into
Seq_CurValue
from
all_sequences
where
sequence_owner=UPPER(Seq_Owner)
and sequence_name=UPPER(Seq_Name)
and rownum<2
;
-- Cycle with MaxValue as CurValue
execute immediate 'alter sequence '||Seq_Owner||'.'||Seq_name||' CYCLE
MaxValue '||Seq_CurValue ;
-- Reset to Min
execute immediate 'BEGIN select '||Seq_Owner||'.'||Seq_name||'.NextVal
INTO :a from dual; END;' USING OUT Seq_Value;
-- Return All Rules BACK
execute immediate 'alter sequence '||Seq_Owner||'.'||Seq_name
||' MAXVALUE '||Seq_MaxValue||' '||Seq_CacheSize||' '||Seq_Cycle
;
<> Null;
EXCEPTION
WHEN NO_DATA_FOUND
THEN raise_application_error(-20900,'Sequence
'||Seq_Owner||'.'||Seq_Name||' does not exist'); WHEN OTHERS THEN
raise_application_error(-20901,'Error occure with reset of Sequence
'||Seq_Owner||'.'||Seq_Name); END FAST_RESET_SEQUENCE;
/
Usage:
-------
exec FAST_RESET_SEQUENCE('SEQ_TEST3')
P.S. User who executes it has to have rights to change sequence
parameters.
|
Bookmark
