sequence主键索引单向增长问题

Yong Huang

> 如果middle part限定位数，既然两次sequence肯定不会相同，从内心想，应该不会出现key冲突。

That's what I thought too. There won't be collision initially, although a good math proof is desired.

/* This paragraph is wrong. See corrections in the following two messages.
But remember PK's are going up. Suppose on instance 1, session 1 is SID 55 and the sequence.nextval is 10 (PK is 15510) and session 2 is SID 54 and its nextval is 99 (PK is 15499). It won't take long for the second session to catch up to 15510.
*/

bisal

Yong Huang 发表于 2015-2-25 04:07
> 如果middle part限定位数，既然两次sequence肯定不会相同，从内心想，应该不会出现key冲突。

That's w ...

“That's what I thought too. There won't be collision initially, although a good math proof is desired. But remember PK's are going up. Suppose on instance 1, session 1 is SID 55 and the sequence.nextval is 10 (PK is 15510) and session 2 is SID 54 and its nextval is 99 (PK is 15499). It won't take long for the second session to catch up to 15510.”

一个session产生的主键是15510，另一个session产生的主键是15499，15499－15510之间间隔很短，您的意思是主键是递增的，15499很快追上15510。首先，这两个session针对一张表的操作，取的是同一个sequence对象的nextval，因此无论如何，两个复合主键也不会重复吧？

另外， 因为每张表都会有各自的sequence，都是递增的，只可能会出现不同表的主键重复，例如sessionid＝001的session取表1的sequence.nextval=1，这条记录在表1的主键是10011。
同一个sessionid＝001的session取表2的sequence.nextval＝1，这条记录在表2的主键是10011。
此时两张表的主键有重复的，但这是可以的吧？

不知道您是上面的意思么？

bisal

Yong Huang 发表于 2015-2-25 04:07
> 如果middle part限定位数，既然两次sequence肯定不会相同，从内心想，应该不会出现key冲突。

That's w ...

session1:
SQL> create sequence seq_10 noorder cache 3;
Sequence created.

SQL> select seq_10.nextval from dual;
   NEXTVAL
----------
         1

session2:
SQL> select userenv('sessionid') from dual;
USERENV('SESSIONID')
--------------------
              260041

SQL> select seq_10.nextval from dual;
   NEXTVAL
----------
         2

session1:
SQL> select seq_10.nextval from dual;
   NEXTVAL
----------
         3

session2:
SQL> select seq_10.nextval from dual;
   NEXTVAL
----------
         4

两个session，取同一个sequence，也是按照递增顺序，您说的追的快是什么意思呢？

Yong Huang

You're right. My mistake. I'll mark my msg #21 as wrong. Thank you for your correction.

bisal

Yong Huang 发表于 2015-2-26 04:23
You're right. My mistake. I'll mark my msg #21 as wrong. Thank you for your correction.

谢谢您一贯以来严谨的回复

bisal

Yong Huang 发表于 2015-2-26 04:23
You're right. My mistake. I'll mark my msg #21 as wrong. Thank you for your correction.

但这会不会有另外一个问题：
每个实例不同session产生复合主键，可以说之间都是无序的了相应的主键索引也是无序插入的。索引块和数据块中数据的排列关系可能不能很好地对应，是否会导致clustering factor过大？

Yong Huang

I bet it will. I'm interested in seeing its effect on the clustering factor. Can you test and let us know?

The idea I talked about very early is still a good one: Common practice is that you reserve a large range of sequence values for one instance, but only use sequence, not a combination of it with anything else, as PK.

bisal

Yong Huang 发表于 2015-2-27 00:48
I bet it will. I'm interested in seeing its effect on the clustering factor. Can you test and let us ...

“Common practice is that you reserve a large range of sequence values for one instance, but only use sequence, not a combination of it with anything else, as PK.”

但这样可能也会有个问题，将sequence索引做成reverse，那么原来一次检索只需要一个索引块，现在可能由于索引分散到不同块，那么可能需要将多个块加载到buffer cache中，只有当这个表比较小，索引比较小的时候，可以将所有索引一次性加载到缓存中，才会非常有效，否则如果是一张大表，频繁的SQL操作，很可能需要频繁地将索引从文件加载到缓存，如果缓存不够大，有可能这次已加载到缓存的索引马上就被age out了，但紧接着又需要再次加载。

bisal

Yong Huang 发表于 2015-2-27 00:48
I bet it will. I'm interested in seeing its effect on the clustering factor. Can you test and let us ...

另外，表中很可能有其他索引，实际上可能无法保证所有索引的clustering factor都小吧？

bisal

Yong Huang 发表于 2015-2-27 00:48
I bet it will. I'm interested in seeing its effect on the clustering factor. Can you test and let us ...

如果使用reverse主键，这种情况下，主键索引的clustering factor也会很大。
应用使用连接池，即是有限个和数据库的连接，且都可重用，这样一来，复合主键middle part的部分变化应该有限，相比于reverse主键，是不是clustering factor不会那么大？