Friday, September 05, 2008

Efficiently using Oracle sequences which have increment set greater than 1

Some application's create sequences which auto increment by say 200, Map 3d does this for example with the ENTITYID sequence. It means Map 3d reads the sequence once and then waits until it has used 199 values before grabbing another sequence.

This can be inefficient if you loading data via a INSERT into SELECT approach, each row will up the sequence by 200, not really what your after.

Oracle's PL/SQL functions to the rescue

CREATE SEQUENCE SEQ
START WITH 1
INCREMENT BY 200
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;

CREATE OR REPLACE FUNCTION getseq( rn integer, incred integer )
RETURN number IS
calc_seq number;
BEGIN
IF mod(rn, incred) = 0 OR rn = 1 THEN
select seq.nextval into calc_seq from dual;
RETURN calc_seq ;
ELSE
select seq.currval into calc_seq from dual;
RETURN calc_seq + mod(rn, incred);
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN -1;
END;
/

-- populate currval for the session so the next query doesn't error

SELECT SEQ.nextval from dual;

-- handy way to get 30 rows from dual, using 10 rather than 200 to keep it simple

SELECT rownum, zac.getseq(rownum,10) seq, mod(rownum,10) mod_result, seq.currval
FROM dual connect by rownum < 31;


ROWNUM SEQ MOD_RESULT CURRVAL
---------- ---------- ---------- -------
1 201 1 200
2 203 2 200
3 204 3 200
4 205 4 200
5 206 5 200
6 207 6 200
7 208 7 200
8 209 8 200
9 210 9 200
10 401 0 400
11 402 1 400
12 403 2 400
13 404 3 400
14 405 4 400
15 406 5 400
16 407 6 400
17 408 7 400
18 409 8 400
19 410 9 400
20 601 0 600
21 602 1 600
22 603 2 600
23 604 3 600
24 605 4 600
25 606 5 600
26 607 6 600
27 608 7 600
28 609 8 600
29 610 9 600
30 801 0 800
30 rows selected

No comments: