This post explains how one can re-sequence a number column in Oracle. Let’s take a hypothetical example and walk through it. Suppose that a big departmental store has its own stocking facility and each shelf in the facility is identified as a location (STORE_LOCN_ID) in which more than one item can be stored. Whenever a new item is added to the shelf, a new record is created in the table by increasing the value in the locn_seq column by 1. Whenever actual quantity (actl_qty) for the item decreases to 0, record is deleted from the table. Following is the table structure to hold the data of the items in the facility.
CREATE TABLE STORE_STOCK
(
STORE_STOCK_ID NUMBER(9) NOT NULL,
STORE_LOCN_ID VARCHAR2(10) NOT NULL,
LOCN_SEQ NUMBER(3) NOT NULL,
ITEM_NO VARCHAR2(10) NOT NULL,
ACTL_QTY NUMBER(10) DEFAULT 0 NOT NULL,
MAX_QTY NUMBER(10) DEFAULT 0 NOT NULL,
CONSTRAINT PK_STORE_STOCK PRIMARY KEY(STORE_STOCK_ID)
)
/
There is also a unique index on STORE_LOCN_ID + LOCN_SEQ column combination since that forms the business key to this table. Please note that the LOCN_SEQ column has been defined as NUMBER(3) in this example to demonstrate how to re-sequence the numbers. In an actual design, you may want to increase the length of that column. The example is so chosen to illustrate the req-sequencing using analytic function in Oracle.
And for this example, this is how a set of data looks like in this table:
STORE_STOCK_ID STORE_LOCN LOCN_SEQ
————– ———- ———-
1 A100000001 5
4 A100000001 11
2 A100000001 18
6 A100000001 35
3 A100000001 99
5 A100000001 245
7 A100000001 803
8 A100000001 922
9 A100000001 945
10 A100000001 999
11 A100000002 1
12 A100000002 2
13 A100000002 15
14 A100000002 411
15 A100000002 999
16 A100000003 1
17 A100000004 1
18 A100000004 3
19 A100000004 5
20 A100000005 999
Now, suppose that because of frequent movement of items, we reached the maximum limit (999) of locn_seq for a particular store_locn_id. But, there are few more items that need to be stocked. Since the max value for LOCN_SEQ has been reached, any attempt to insert another record with a LOCN_SEQ value of 1000 will result into ORA-1401 error. We can thus re-sequence the values so that for a given STORE_LOCN_ID, the LOCN_SEQ values are sequenced and any gaps that resulted via deletion of records could be re-used again. Let us see how we can resolve this using analytical function.
We will create temporary table using CTAS (Create Table AS) to store the data. A key thing to note is that we are using the Row_Number() and the partition clauses of the analytic functions,. Since we want to initialize locn_seq to 1 for each store_locn_id, we will partition by store_locn_id and order by locn_seq.
SQL>create table temp_store_stock
as select store_stock_id, store_locn_id,locn_seq,
Row_number() over(partition by store_locn_id order by locn_seq) new_seq
from store_stock
order by store_locn_id, locn_seq;
The data in this table is shown below: You will notice that new_seq number is in the continuous order without any gap.
SQL> select * from temp_store_stock;
STORE_STOCK_ID STORE_LOCN LOCN_SEQ NEW_SEQ
————– ———- ———- ———-
1 A100000001 5 1
4 A100000001 11 2
2 A100000001 18 3
6 A100000001 35 4
3 A100000001 99 5
5 A100000001 245 6
7 A100000001 803 7
8 A100000001 922 8
9 A100000001 945 9
10 A100000001 999 10
11 A100000002 1 1
12 A100000002 2 2
13 A100000002 15 3
14 A100000002 411 4
15 A100000002 999 5
16 A100000003 1 1
17 A100000004 1 1
18 A100000004 3 2
19 A100000004 5 3
20 A100000005 999 1
Now we will update original table with new_seq_nbr from temp_store_distro table.
SQL>update store_stock
set locn_seq = (select new_seq
from temp_store_Stock
where temp_store_stock.store_stock_id = store_stock.store_stock_id)
where exists (select 1
from temp_store_stock
where temp_store_stock.store_stock_id = store_stock.store_stock_id);
SQL> commit;
The results of the above update are shown below: You can see that, locn_seq is now re-aligned and we can add some more records.
SQL> select store_stock_id,store_locn_id,locn_seq from store_stock
2 order by store_locn_id,locn_seq;
STORE_STOCK_ID STORE_LOCN LOCN_SEQ
————– ———- ———-
1 A100000001 1
4 A100000001 2
2 A100000001 3
6 A100000001 4
3 A100000001 5
5 A100000001 6
7 A100000001 7
8 A100000001 8
9 A100000001 9
10 A100000001 10
11 A100000002 1
12 A100000002 2
13 A100000002 3
14 A100000002 4
15 A100000002 5
16 A100000003 1
17 A100000004 1
18 A100000004 2
19 A100000004 3
20 A100000005 1
We can now drop the temporary table.
SQL> drop table temp_store_stock;
If you want to dig into the Row_Number() and the partition by logic, you can search this web-site for more information on those functions or look up Oracle docs. Oracle has a very rich set of analytic functions which can be very useful for development as well as troubleshooting purposes. We had also covered one of those in a post few days ago – you can access it here.
Systems Engineering and RDBMS 