重新编号pimary-key [英] re-numbering pimary-key
问题描述
我有一系列具有主键/外键
关系的表，其中主键错误地增加了20
而不是1。默认缓存值设置为20.已经将
重新设置为1.
我想重新编号为主键然后重新设置序列然后
回到正确的数字来增加。
任何人都有任何课程倾向于我需要注意这样做？
建议编号更改示例：
如果我有1,4,8,12,18
我会改变：
18到99，
12，到98.
然后更改
4到2，
8到3，
98到4，
99到5.
然后将下一个序列号更改为6.
任何人都看到有任何问题吗？
Mike
I have a series of tables that have primary-key / foreign key
relationships where the primary key was being incremented in error by 20
instead of by 1. The default cache value was set to 20. It has since
been re-set to 1.
I''d like to re-number the primary key and then re-set the sequence then
back to the correct number to increment.
Anyone have any lessons leaned that I need to watch in doing this?
Example of proposed numbering change:
If I have 1,4,8,12,18
I would change:
18 to 99,
12, to 98.
Then change
4 to 2,
8 to 3,
98 to 4,
99 to 5.
Then change the next sequence number to 6.
Anyone see any problem with this?
Mike
推荐答案
Michael Hill< hi **** @ ram。 lmtas.lmco.com>在消息新闻中写道：< 3F *************** @ ram.lmtas.lmco.com> ...
Michael Hill <hi****@ram.lmtas.lmco.com> wrote in message news:<3F***************@ram.lmtas.lmco.com>...
我有一系列的表格有主键/外键关系，其中主键错误增加20
而不是1.默认缓存值设置为20.它已经重新开始了 - 设置为1.
我想重新编号主键，然后重新设置序列然后
返回正确的数字来增加。
<任何人都有任何课程倾向于我需要注意这样做吗？
提议的编号更改示例：
如果我有1,4,8,12,18
我会改变：
18到99，
12，到98.
然后改变
4到2，
8到3，
98到4，
99到5.
然后将下一个序列号更改为6.
任何人都看到有任何问题吗？
Mike
I have a series of tables that have primary-key / foreign key
relationships where the primary key was being incremented in error by 20
instead of by 1. The default cache value was set to 20. It has since
been re-set to 1.
I''d like to re-number the primary key and then re-set the sequence then
back to the correct number to increment.
Anyone have any lessons leaned that I need to watch in doing this?
Example of proposed numbering change:
If I have 1,4,8,12,18
I would change:
18 to 99,
12, to 98.
Then change
4 to 2,
8 to 3,
98 to 4,
99 to 5.
Then change the next sequence number to 6.
Anyone see any problem with this?
Mike
迈克，我实际上已经完成并迁移了
应用程序，其中父表键是一个序列值。
从逻辑上讲，编写我以前使用的pl / sql代码并不困难
执行处理但是从Oracle必须完成的工作的角度来看这是一个相当昂贵的过程，索引
更新，重做生成等。 ...
这是我们今天要做的另一种选择。由于将要回收的序列
（由于外部系统的大小限制，我们需要
feed）以及我们必须首先删除现有数据以允许重用
的序列值，但客户不希望任何数据被删除直到年底之后，因为这将扭曲他们的
年 - - 日期信息，是替换我们自己种植的
序列。
我所做的是写一个函数作为匿名事务，读取
来自IOT的一行，其中填充了缺失（跳过）
序列号，使用select for update，delete，commit，返回
选择的值。
似乎测试得很好。只要您使用的是Oracle版本8i
且该函数未作为分布式事务的一部分调用
这可能是一条更简单的路径。对于版本9，应删除分发的
交易限制。我们只是用序列替换了我们的
函数名，重新编译了调用
序列的四个程序，并进行了测试。
HTH - Mark D Powell -
Mike, I have actually gone through and migrated all related rows in an
application where the parent table key was a sequence value.
Logically it was not that hard to write the pl/sql code I used to
perform the processing but it was a fairly expensive process from the
point of view of the work that has to be done by Oracle, indexes
updated, redo generated, etc....
Here is an alternate that we are about to do today. Due to a sequence
that is about to recycle (due to size limitation of external system we
feed) and where we must first remove existing data to allow the reuse
of the sequence value, but where the customer does not want any data
removed until after year-end since this will distort their
year-to-date information, is to substitute our own home-grown
sequence.
What I did was write a function as an anonymous transaction that reads
one row from an IOT that was populated with the missing (skipped)
sequence numbers using select for update, delete, commit, return the
selected value.
It seems to test just fine. As long as you are on Oracle version 8i
and the function is not called as part of a distributed transaction
this may be a simplier route to take. With version 9 the distributed
transaction restriction should be removed. We just substituted our
function name for the sequence, recompiled the four programs that call
the sequence, and tested.
HTH -- Mark D Powell --
Michael Hill< hi **** @ ram.lmtas.lmco.com>在消息新闻中写道：< 3F *************** @ ram.lmtas.lmco.com> ...
Michael Hill <hi****@ram.lmtas.lmco.com> wrote in message news:<3F***************@ram.lmtas.lmco.com>...
我有一系列的表格有主键/外键关系，其中主键错误增加20
而不是1.默认缓存值设置为20.它已经重新开始了 - 设置为1.
我想重新编号主键，然后重新设置序列然后
返回正确的数字来增加。
<任何人都有任何课程倾向于我需要注意这样做吗？
提议的编号更改示例：
如果我有1,4,8,12,18
我会改变：
18到99，
12，到98.
然后改变
4到2，
8到3，
98到4，
99到5.
然后将下一个序列号更改为6.
任何人都看到有任何问题吗？
Mike
I have a series of tables that have primary-key / foreign key
relationships where the primary key was being incremented in error by 20
instead of by 1. The default cache value was set to 20. It has since
been re-set to 1.
I''d like to re-number the primary key and then re-set the sequence then
back to the correct number to increment.
Anyone have any lessons leaned that I need to watch in doing this?
Example of proposed numbering change:
If I have 1,4,8,12,18
I would change:
18 to 99,
12, to 98.
Then change
4 to 2,
8 to 3,
98 to 4,
99 to 5.
Then change the next sequence number to 6.
Anyone see any problem with this?
Mike
迈克，我实际上已经完成并迁移了
应用程序，其中父表键是一个序列值。
从逻辑上讲，编写我以前使用的pl / sql代码并不困难
执行处理但是从Oracle必须完成的工作的角度来看这是一个相当昂贵的过程，索引
更新，重做生成等。 ...
这是我们今天要做的另一种选择。由于将要回收的序列
（由于外部系统的大小限制，我们需要
feed）以及我们必须首先删除现有数据以允许重用
的序列值，但客户不希望任何数据被删除直到年底之后，因为这将扭曲他们的
年 - - 日期信息，是替换我们自己种植的
序列。
我所做的是写一个函数作为匿名事务，读取
来自IOT的一行，其中填充了缺失（跳过）
序列号，使用select for update，delete，commit，返回
选择的值。
似乎测试得很好。只要您使用的是Oracle版本8i
且该函数未作为分布式事务的一部分调用
这可能是一条更简单的路径。对于版本9，应删除分发的
交易限制。我们只是用序列替换了我们的
函数名，重新编译了调用
序列的四个程序，并进行了测试。
HTH - Mark D Powell -
Mike, I have actually gone through and migrated all related rows in an
application where the parent table key was a sequence value.
Logically it was not that hard to write the pl/sql code I used to
perform the processing but it was a fairly expensive process from the
point of view of the work that has to be done by Oracle, indexes
updated, redo generated, etc....
Here is an alternate that we are about to do today. Due to a sequence
that is about to recycle (due to size limitation of external system we
feed) and where we must first remove existing data to allow the reuse
of the sequence value, but where the customer does not want any data
removed until after year-end since this will distort their
year-to-date information, is to substitute our own home-grown
sequence.
What I did was write a function as an anonymous transaction that reads
one row from an IOT that was populated with the missing (skipped)
sequence numbers using select for update, delete, commit, return the
selected value.
It seems to test just fine. As long as you are on Oracle version 8i
and the function is not called as part of a distributed transaction
this may be a simplier route to take. With version 9 the distributed
transaction restriction should be removed. We just substituted our
function name for the sequence, recompiled the four programs that call
the sequence, and tested.
HTH -- Mark D Powell --
>我只是试图在测试中改变一些，当然，由于
> I just tried to change some in test and of course it wouldn''t let me change any because of the
约束，它不会让我改变。我以前很怕那个。
Mike，我实际上已经完成并迁移了一个
应用程序中的所有相关行，其中父表键是一个序列值。
逻辑上写起来并不难pl / sql代码我以前用来执行处理，但从工作的角度来看这是一个相当昂贵的过程，必须由Oracle完成，索引更新，重做生成等等....
这是我们今天要做的另一种选择。由于即将回收的序列（由于外部系统的大小限制，我们需要进料）以及我们必须首先删除现有数据以允许重复使用序列值，但是客户不希望在年底之前删除任何数据，因为这会扭曲他们今年的最新信息，取而代之的是用我们自己种植的
序列。
所以你所做的就是重新使用存在的漏洞。你有这个代码的例子吗？这可能是一个更好的主意。
我所做的是将一个函数写成一个匿名事务，从一个填充了IOT的IOT读取一行。缺少（跳过）
序列号使用select进行更新，删除，提交，返回
选中的值。
似乎测试就好了。只要您使用的是Oracle版本8i
并且该功能未作为分布式事务的一部分调用
这可能是一条更简单的路径。对于版本9，应删除分布式的
事务限制。我们只是用序列替换了我们的
函数名，重新编译了调用序列的四个程序，并进行了测试。
HTH - Mark D Powell -
constraints. I was afraid of that.
Mike, I have actually gone through and migrated all related rows in an
application where the parent table key was a sequence value.
Logically it was not that hard to write the pl/sql code I used to
perform the processing but it was a fairly expensive process from the
point of view of the work that has to be done by Oracle, indexes
updated, redo generated, etc....
Here is an alternate that we are about to do today. Due to a sequence
that is about to recycle (due to size limitation of external system we
feed) and where we must first remove existing data to allow the reuse
of the sequence value, but where the customer does not want any data
removed until after year-end since this will distort their
year-to-date information, is to substitute our own home-grown
sequence.
So all you are doing is re-using the holes that exist. Do you have an example of that code. This may be a
better idea.
What I did was write a function as an anonymous transaction that reads
one row from an IOT that was populated with the missing (skipped)
sequence numbers using select for update, delete, commit, return the
selected value.
It seems to test just fine. As long as you are on Oracle version 8i
and the function is not called as part of a distributed transaction
this may be a simplier route to take. With version 9 the distributed
transaction restriction should be removed. We just substituted our
function name for the sequence, recompiled the four programs that call
the sequence, and tested.
HTH -- Mark D Powell --
这篇关于重新编号pimary-key的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
