Re: "Re-sequencing" a pseudo-key
- From: Kim Berg Hansen <kibeha@xxxxxxxxx>
- To: Sergey Kosourikhin <kosour@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 11 Oct 2014 22:02:23 +0200
Hi, Serqey
Thanks for the ideas - I'm copying this to the list for the benefit of
others.
Unfortunately the idea won't work for us, I cannot change how the
application use sequences.
You see, the application is written in an old legacy programming language.
The compiler translates into legacy "executable" with embedded SQL (which
we cannot touch.) Then the runtime engine executes the legacy compiled
executable code with the SQL in it.
Part of that is that the runtime engine whenever a RECID is needed (no
matter which table) the engine will get the nextval from the *one* master
sequence (which is defined as INCREMENT BY 10) and it will use that value
for the INSERT it executes. Then the engine will use the next 9 values for
the next 9 INSERTS (no matter which table) and only then will it get
sequence.nextval again.
And the engine uses 32-bit signed integers for the RECIDs and there will
never be a 64-bit updated version, thus our deadline in about a year to 1½.
The numbers are "running out" faster than the amount of rows we have,
partly because (as Iggy pointed out) numbers will be "lost" when the engine
calls nextval but then only inserts a couple rows before closing the
session (similar to how a sequence cache in Oracle may "lose" numbers when
the cache is aged out of shared pool or at database restart.)
But mostly because the application inserts rows to tables like SalesHeader
and SalesLine, and then when the order is invoiced the data is inserted to
tables like InvoiceHeader and InvoiceLine, after which SalesHeader and
SalesLine is deleted. So the application as such "wastes" numbers that is
deleted and reinserted in new tables. Again it is unfortunately not
possible to change this - only option to avoid it would be to replace the
system completely...
I definitely wish I could give each table a separate sequence, but I cannot
change how this runtime engine works when it executes our compiled legacy
code :-(
Regards
Kim Berg Hansen
http://dspsd.blogspot.com
kibeha@xxxxxxxxx
@kibeha
On Sat, Oct 11, 2014 at 3:41 AM, Sergey Kosourikhin <kosour@xxxxxxxxx>
wrote:
> Hi Kim,
>
> If we look at this task from slightly different angle, I would say this
> task is not about re-sequencing, but rather it’s de-sequencing.
>
> I mean, your main issue is that application uses the only sequence which
> hit the limit too fast. And the solution would be to have separate
> sequences for every table. And it’s not a one-off operation. Actually, it
> should be like nightly job during outage period. (well, I don’t know your
> application - but it may be even possible to run it without outage under
> some conditions )
>
> So, if we want to provide solution for de-sequencing, one of the
> implementatios would be:
>
> 1. Create individual sequences for every table.
> 2. Create mapping table (every time you when you will reset main sequence,
> we can call it “incarnation”. Currently you have incarnation 1. every time
> you reset main sequence, incarnation will be incremented by 1). In every
> incarnation all your parent recid’s are unique across database - as they
> come from the same sequence. That means to re-sequence child rec_id you
> don’t need to know from which table it came from - just understand if it’s
> current incarnation or past incarnations). So, in simplest way you can use
> structure of mapping table :
> - table_name
> - incarnation_id
> - old_rec_id
> - new_rec_id -> populated from table related sequence
> - is_processed (default 0)
>
> Once outage started, you
> 1. scan your tables and take all recid’s newer than maximum from previous
> incarnation.
> 2. populate mapping table with this rec_id’s and tables related sequences
> 3. You don’t need to worry about FK - as rec_id is unique across
> incarnation - just update them correctly (if it’s bigger than latest from
> previous incarnation - update it, if less - skip it) using mapping table.
> 4. Once you populated mapping table, you can start updating
> (re-sequencing) rec_id in your tables by using new_rec_id column
> 5. I would suggest do it in batches by, say, 1000 rec_ids in 1
> transaction. before commit, you update is_processed = 1 for these
> old_rec_id’s. You can create stored proc metadata driven which will update
> all tables for given rec_id.
> 6. to speed up process you can create mapping tables as list partitioned
> by table name and sub partitioned by is_processed columns and enable row
> movement. So, you will have partition with non-processed rows which will
> decrease in size as long as you re-sequencing source tables.
> 7. once you processed all rows, you can reset your main sequence to the
> greatest of all table related sequences + 1.
>
> You can run this procedure during the outage every day and during
> weekends/holidays. It’s not a big problem if you couldn’t de-sequenced all
> rows for particular outage - that means you will just skip 1 sequence reset
> and you can continue next outage.
>
>
> Think yourself if you need all processed rows in mapping table (it looks
> to me at this moment that only the last row you need to start next
> de-sequencing run).
>
> If I didn’t explain something clear enough - feel free to ask me for
> details.
>
> P.S. Sorry that I can’t reply to oracle-list - I have some issues with
> writing there - I am more like reader :)
>
> Regards,
> Sergey.
>
>
>
Other related posts:
