sequence主键索引单向增长问题

bisal

Yong Huang 发表于 2015-2-20 01:48
> 您的意思是“select instance_number || session_id || sequence.nextval  from v$instance”这条SQL是错 ...

1. “select instance_number + userenv('sessionid') + yoursequence.nextval  from v$instance;”
我尝试了下这种写法相当于instance_number + userenv('sessionid') + your sequence.nextval三者之和，应该是：
select instance_number || userenv('sessionid') || yoursequence.nextval  from v$instance;
才是我们需要的。

2. 您说的instance 1设置sequence的起始值是1，instance 2设置sequence的起始值时10000。
是可以让不同实例的sequence区分开，但仍旧有sequence做主键时的单向增长问题。

3. If you combined session_id into the sequence value, that would cause the sequence to jump around too much.
之所以需要将”session_id“+sequence，就是为了破除sequence作为主键的单向增长问题，将sequence序列打散，减少主键索引单向增长导致的索引块争用。
另外，为了RAC下减少索引块在多实例间的传递，拼接主键用的是"instance_id || session_id || sequence.nextval"，添加了instance_id，以使本实例需要使用的索引块减少传递到其他实例的可能，所有以上的操作，都是为了做到这种拼接的主键不会受到索引单向增长的影响。
不知道这样是否说清楚了呢？

bisal

Yong Huang 发表于 2015-2-20 01:48
> 您的意思是“select instance_number || session_id || sequence.nextval  from v$instance”这条SQL是错 ...

4. It's 38. 22 is the limit in storage. (I think 21 bytes for the actual storage plus 1 byte to store length.)
22是存储限制，这个22指的是什么单位呢？怎么计算出来的？NUMBER长度默认是38，就是38个数字。
另外，还有个问题，Oracle中存储的数据类型中最小是什么呢？1个bit？1个byte？

bisal

Yong Huang 发表于 2015-2-20 01:48
> 您的意思是“select instance_number || session_id || sequence.nextval  from v$instance”这条SQL是错 ...

忘了和您说一句，去年在论坛中没少得到您的帮助，也从您的每次耐心解答中学到了不少东西，由衷地感谢您，也看了您的著作，敬佩您，希望能以您为榜样，在这条自己感兴趣的道路上继续努力前进。

再次感谢，祝您新春快乐，心想事成。

@bisal
2015.2.20

Yong Huang

> 之所以需要将”session_id“+sequence，就是为了破除sequence作为主键的单向增长问题，将sequence序列打散，减少主键索引单向增长导致的索引块争用。

So your intention is to assign an almost random number as the primary key. But after running the application for a while, won't you have more and more frequent number collision? Suppose on instance 1, one session has SID 71 and the sequence nextval is 1. So the PK it uses is 1711. Later another session logs in and has SID 7 and starts the nextval of the sequenc at 21 (assume cache of 20 for the sequence). So PK starts with 1721. And suppose the first session keeps inserting new rows using more and more sequence values. It won't take long for it to bump into 1721 and get a unique key violation.

If you have a reference to an article using your strategy, I'd love to know.

Storage of data can be checked with vsize function. Dump function can also show it. Oracle needs at least 1 byte to store a number (excluding length).

Thanks for your compliment. We're all learning from each other, on this forum and elsewhere as well.

bisal

Yong Huang 发表于 2015-2-21 00:50
> 之所以需要将”session_id“+sequence，就是为了破除sequence作为主键的单向增长问题，将sequence序列打散 ...

您能给我一个邮箱么？我将您说的资料发给您。

bisal

Yong Huang 发表于 2015-2-21 00:50
> 之所以需要将”session_id“+sequence，就是为了破除sequence作为主键的单向增长问题，将sequence序列打散 ...

会用例如cache＝1000/2000的sequence，即：
实例1：1(instance_name=1) + 170(SID=170) + 1(实例1的sequence第一个nextval=1)
即11701
实例2：2(instance_name=2) + 200(SID=200) + 1001(实例2的sequence第一个nextval=1001)
即220010001
两个记录的主键分别为11701和220010001。
确实看起来相差比较大，但应用场景用这些主键的，基本就是表连接，所以是否这种情况场景可以这么用呢？

另外，相关的PPT已经发到您的邮箱yong321@yahoo.com，请您查收

谢谢您。

Yong Huang

Thank you. Now I realize this is something I've read before. See the first "and we said" by Tom Kyte

https://asktom.oracle.com/pls/ap ... 6077843300346542181

He suggested using

"mod(session/process_id,some_small_number) so you get things like 23, 56, 89 and so on - just 1 or 2 or three digits"

as the middle part instead of simply using userenv('sessionid'), in the 3-part primary key (instance number || middle part || sequence value). That's a very crucial modification, and needs one caveat I'll explain below.

No doubt there will never be collision (in the sense of unique key violation) between RAC instances. But what about on the same instance? That's the scenario I described in msg #14. Let's say your sequence has cache 1000 as you said. Suppose the first session on instance 1 has SID 7100 and is the first session to use the sequence i.e. nextval is 1. So the session constructs the PK as 171001. Now suppose the second session happens to be on instance 1 too and its SID is 7 and uses sequence value 1001 (remember the cache is 1000). So its constructed PK is 171001. That's a collision!

Tom's suggestion is to use a mod() function to generate a "random" number instead of SID. One important rule not to be violated is that these numbers must be of the same number of digits. The result of this mod() cannot be e.g. 7100 and then 7. If the mod() is supposed to generate 4-digit numbers (i.e. at most 9999) and the result is 7, it should be padded to 0007; alternatively, limit the range to between 1000 and 9999.

Actually, I'm not sure if the rule that the middle part of the PK has a known number of digits is a guarantee that the resultant PK is collision-free. Here's a hypothesis I propose to be proved or disproved by people good at math:

1. M and N are two strings composed of digits only (0 through 9), and N does not begin with 0.
2. The number of digits (or characters) in M is always the same.
3. All instances of N are unique, i.e. no two instance of N are the same (Ni != Nj, where i != j for any i and j).
4. A string S is the concatenation of M and N in that order (S = M || N).
5. Hypothesis: There do not exist two instances of S, S1=M1||N1 and S2=M2||N2, such that S1 = S2.

For now, the hypothesis does not take into account of the initial difference between N1 and N2 (i.e. cache value of the Oracle sequence).

bisal

Yong Huang 发表于 2015-2-24 01:08
Thank you. Now I realize this is something I've read before. See the first "and we said" by Tom Kyte ...

1. 确实如您所说，如果是用sessionid，可能存在两个session产生的key一致的场景。如何证明例如4位数字能保证组成的复合key不会重复是能否使用这个问题的关键。
我的理解：
(1) (instance number || middle part || sequence value)之所以可能存在两个session产生的key冲突的原因，可以分开看：
首先同一个instance的instance number肯定一样。
其次，sequence值，肯定不会重复。
问题就出在middle part，原来的方式，之所以会重复，是因为middle part不限定位数，不同位数的middle part＋sequence才可能会有相同的值。换句话说，就是“小位数的middle part＋大位数的sequence”，可能会和“大位数的middle part＋小位数的sequence”相同。
(2) 因此，如果middle part限定位数，既然两次sequence肯定不会相同，从内心想，应该不会出现key冲突。
进一步说，middle part只需要限定1位即可，也未必需要限定2位、3位。
不知道以上理解是否准确？


2. 如果是限定middle part一位，不知道如下方式是否准确？
select instance_number || lpad(mod(userenv('sessionid'), 10), 1, 0) || sequence.nextval from dual;


3. 看到tom的说法，建议设置三个字段，来拼接key，如果这能确定表中最多保存几千万的数据，那就设置为一个字段，其中
instance_number是1位。
middle part是1位。
sequence最大好像是28位。
一共30位，几千万的范围应该是可以？
另外，那设置key的类型是NUMBER(28)，或者设置为NUMBER，是否就可以了呢？如果是NUMBER，默认是38位，是否像他说的，会浪费空间呢？

bisal

Yong Huang 发表于 2015-2-24 01:08
Thank you. Now I realize this is something I've read before. See the first "and we said" by Tom Kyte ...

另外，您说的“Now suppose the second session happens to be on instance 1 too and its SID is 7 and uses sequence value 1001 (remember the cache is 1000).”，我感觉这和cache是1000没有关系，因为无论cache是多少，都可能会有sequence值是1001，这样和SID为7的session，组成的key就是171001，对于SID是7100的sequence值是1的组合key相同，即171001。

bisal

bisal 发表于 2015-2-24 20:03
1. 确实如您所说，如果是用sessionid，可能存在两个session产生的key一致的场景。如何证明例如4位数字能保 ...

"select instance_number || lpad(mod(userenv('sessionid'), 10), 1, 0) || sequence.nextval from dual;"
写错了，应是：
"select instance_number || lpad(mod(userenv('sessionid'), 10), 1, 0) || sequence.nextval from v$instance;"