Re: "Re-sequencing" a pseudo-key

Thanks, now I understand. With that idea I could make it an ongoing process to be run periodically that keeps the database fairly "re-sequenced" so I won't have to do it again in 10-12 years.

But the idea resets the master application sequence in every "incarnation", so the first initial re-sequencing (the big job) either will have to work in one go, or I'd combine something like Iggys idea for splitting the first big re-sequencing up into smaller parts, and then move on to your idea afterwards for "future continuos resequencing."

Might be worth considering some way to keep doing this regularly rather than waiting to do another big batch in the future... Hmm... I'll think about that one ;-)

Regards

Kim Berg Hansen

http://dspsd.blogspot.com
kibeha_at_gmail.com
_at_kibeha

On Sat, Oct 11, 2014 at 11:12 PM, Sergey Kosourikhin <kosour_at_gmail.com> wrote:

> Hi Kim,
>
> Looks like I explained my idea not clear enough :
> You don't need to change your application at all. I understand that you
> don't have acccess to source code of application. it's a black box for you.
>
> You will use personal table sequences only in you de-sequencing process.
> Let's have a look how my idea can work in details.
> For example,
>
> First incarnation :
> App#SEQ.nextval = 250 <<<< sequence which application uses.
>
> TableA
> Row 1 : rec_id = 200
> Row 2: rec_id = 210
>
> Table B
> Row 1 : rec_id = 201
> Row 2 : rec_id = 202
> Row 3: rec_id = 203
> Row 4: rec_id = 211
>
> De-sequence starts:
>
> <<<< your table-level sequences (application does not know about them)
> >>>>>
>
> Table_A_Seq. nextval = 3
> Table_B_seq.nextval = 5
>
> insert into Mapping table:
>
> table old_rec new_rec incarnation
> ------------------------------------------------------------------
> TableA 200 1 1
> TableA 210 2 1
>
> TableB 201 1 1
> TableB 202 2 1
> TableB 203 3 1
> TableB 211 4 1
>
> Now you are ready to update your source tables
> update tableA t
> set rec_id = (select m.new_rec from map m where m.table = 'TABLEA' and
> m.old_rec = t.rec_id)
>
> update tableB t
> set rec_id = (select m.new_rec from map m where m.table = 'TABLEB' and
> m.old_rec = t.rec_id)
>
>
> at this stage you know, that even App#SEQ.nextval = 250, you don't have
> in source tables any rec_id bigger than
>
> select max(new_rec) from map.
>
> So, you can reset App#SEQ to
> select max(new_rec)+1 from map.
> ==========================
> 5
>
> Reset your application's sequence:
>
> App#SEQ.nextval = 5
>
>
> Incarnation 1 is closed.
>
>
> >>>>>>>>>>>>>>>>>Incarnation 2:
>
>
> application inserts records into source tables using App#SEQ
>
> Table A :
> row3: rec_id = 5
> row4: rec_id = 15
>
> table b:
> row5: rec_id = 6
> row6: rec_id = 7
> row7: rec_id = 8
> row8: rec_id = 16
>
> App#SEQ.nextval = 17
>
> de-sequence starts:
>
> add new rows to map table:
>
> table old_rec new_rec incarnation
> TableA 5 3 2
> TableA 15 4 2
>
> TableB 6 5 2
> TableB 7 6 2
> TableB 8 7 2
> TableB 16 8 2
>
> Table_A_Seq. nextval = 5
> Table_B_seq.nextval = 9
>
> Now you can re-sequence your source tables again
>
> update tableA t
> set rec_id = (select m.new_rec from map m where m.table = 'TABLEA' and
> m.old_rec = t.rec_id and incarnation = 2)
> where rec_id > (select max(rec_id) from map where m.table = 'TABLEA' and
> incarnation = 1)
>
> update tableA t
> set rec_id = (select m.new_rec from map m where m.table = 'TABLEA' and
> m.old_rec = t.rec_id and incarnation = 2)
> where rec_id > (select max(rec_id) from map where m.table = 'TABLEA' and
> incarnation = 1)
>
>
> at this stage you know, that even App#SEQ.nextval = 17, you don't have in
> source tables any rec_id bigger than
>
> select max(new_rec) from map.
> So, you can reset App#SEQ to
> select max(new_rec)+1 from map.
> --------------------------
> 9
>
> reset application sequence :
> App#SEQ.nextval = 9
>
> incarnation 2 is closed.
>
> incarnation 3 is open.
>
> and your application can start inserting data using that common sequence =
> 9.
> And so on.
>
> BTW, As you can see at this stage you don't need incarnation = 1 to be in
> map table - so if you don't need them - delete them (drop partition...)
>
> Does it makes sense ?
>
> Regards,
> Sergey.
>

--
http://www.freelists.org/webpage/oracle-l