Re: "Re-sequencing" a pseudo-key

  • From: Kim Berg Hansen <kibeha@xxxxxxxxx>
  • To: Sergey Kosourikhin <kosour@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 13 Oct 2014 08:57:21 +0200

Hi, Sergey

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@xxxxxxxxx
@kibeha


On Sat, Oct 11, 2014 at 11:12 PM, Sergey Kosourikhin <kosour@xxxxxxxxx>
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.
>

Other related posts: