对oracle了解还很初级皮毛，希望通过这里能伴随自己在数据库方面慢慢的成长！也希望在这里能多多认识一些数据库方面的朋友，一起学习，互相交流。

最近要学习oracle里面可以起到自增长唯一标识作用的sequence序列和其他方式组成的唯一标识主键在性能上差异如何。

向身边一些朋友了解了一下，说是sequence序列组成主键是数值型的肯定比用字符串方式组成的唯一标识查询快，因为查询数值型比字符串型快。

为此，最近网上查询了一些这方面的一些资料，贴到这里做个学习笔记，方便自己日后总结整理：

从网上查阅的一些资料来看有两种方法可以设置主键，一种是自增长主键，另一种就是生成唯一序列。

一、自增长主键

--首先建一个表TEST

create table TEST

(

  NID int PRIMARY KEY,

  test1 varchar2(20),

  test2 varchar2(20),

  test3 varchar2(20),

  test4 varchar2(20),

  test5 varchar2(20)

)

-- 再建一个序列SEQ_TEST

create sequence SEQ_TEST

minvalue 1        --最小值

nomaxvalue        --不设置最大值

start with 1      --从1开始计数

increment by 1    --每次加1个

nocycle           --一直累加，不循环

nocache;          --不建缓冲区

以上代码完成了一个序列(sequence)的建立过程,名称为SEQ_TEST,范围是从1开始到无限大(无限大的程度是由你机器决定的),nocycle 是决定不循环,如果你设置了最大值那么你可以用cycle 会使seq到最大之后循环.对于nocache顺便说一下如果你给出了cache值那么系统将自动读取你的cache值大小个seq,这样在反复操作时会加快运行速度,但如果遭遇意外情况如当机了或oracle死了,则下次取出的seq值将和上次的不连贯.(如果连不连贯无所谓建议用cache,因为时间就是金钱呀!)

你只有了表和序列还不够，最好再建一个触发器来执行它！代码如下：

CREATE OR REPLACE TRIGGER tg_test 

BEFORE INSERT ON test FOR EACH ROW WHEN (new.nid is null)

begin

select seq_test.nextval into:new.nid from dual;

end;

 

下面是测试

select * from test

insert into test(nid,test1) values(6,'aaa')

insert into test(test1) values('bbb') 

二、唯一序列

        SYS_GUID() 生成32位的唯一编码。

        序列生成器所生成的数字只能保证在单个实例里是唯一的，这就不适合将它用作并行或者远程环境里的主关键字，因为各自环境里的序列可能会生成相同的数字，从而导致冲突的发生。SYS_GUID会保证它创建的标识符在每个数据库里都是唯一的。

　　此外，序列必须是DML陈述式的一部分，因此它需要一个到数据库的往返过程（否则它就不能保证其值是唯一的）。SYS_GUID源自不需要对数据库进行访问的时间戳和机器标识符，这就节省了查询的消耗。

        很多应用程序都依靠序列生成器来创建数据行的主关键字，这些数据行没有一个明显的主值，这也就是说，在这样的数据集里一条记录的创建就会让数据列发生改变。因此，管理员可能会对在表格中将SYS_GUID用作主关键字而不使用序列数感兴趣。这在对象在不同机器的不同数据库里生成以及需要在后来合并到一起的情况下很有用。

        使用SYS_GUID或者序列会在数据库使用周期里的某些地方造成性能上的消耗；问题就是在哪里。对于SYS_GUID而言，性能上的影响在查询时间和创建时间上（在表格里要创建更多的块和索引以容纳数据）。对序列而言，性能上的影响在查询期间，在这个时候，SGA序列的缓冲区被用光。在缺省情况下，一个序列一次会缓冲20个值。如果数据库没有使用这些值就关闭了，它们就会被丢失。

        SYS_GUID生成的值的另一个显著的不足之处是，管理这些值会变得困难得多。你必须（手动）输入它们或者通过脚本来填充它们，或者将它们作为Web参数来传递。出于这些原因，将SYS_GUID作为一个主关键字不是一个很好主意，除非是在一个并行的环境里或者希望避免使用管理序列生成器的情况下。

网上查了sys_guid()函数的概念，如下：

SYS_GUID (),是Oracle 8i 后提供的函数。SYS_GUID产生并返回一个全球唯一的标识符(原始值)由16个字节组成。在大多数平台,生成的标识符由主机标符，执行函数的进程或者线程标识符，和进程或线程的一个非重复的值(字节序列)组成。

sys_guid()和传统的序列（sequence）

　　Oracle8i引入了SYS_GUID这个概念，它同Oracle管理员所使用的传统的序列（sequence）相比具有诸多优势。一个序列生成器只是简单地创建从给定的起点开始的一系列整数值，而且它被用在选择陈述式的时候自动地递增该系列。　

 

　　序列生成器所生成的数字只能保证在单个实例里是唯一的，这就不适合将它用作并行或者远程环境里的主关键字，因为各自环境里的序列可能会生成相同的数字，从而导致冲突的发生。SYS_GUID会保证它创建的标识符在每个数据库里都是唯一的。

 

　　序列必须是DML陈述式的一部分，因此它需要一个到数据库的往返过程（否则它就不能保证其值是唯一的）。SYS_GUID源自不需要对数据库进行访问的时间戳和机器标识符，这就节省了查询的消耗。　

 

　　很多应用程序都依靠序列生成器来创建数据行的主关键字，这些数据行没有一个明显的主值，这也就是说，在这样的数据集里一条记录的创建就会让数据列发生改变。因此，管理员可能会对在表格中将SYS_GUID用作主关键字而不使用序列数感兴趣。这在对象在不同机器的不同数据库里生成以及需要在后来合并到一起的情况下很有用。

 

　　但是，SYS_GUID所生成的值是一个16个字节的原始值。序列所生成的整数不会使用16字节（的值），除非它达到了10的30次方（每个字节两个１６进制显示位），而且数字是相当独特的：

 

　　SQL> select dump(123456789012345678901234567890) from dual;

 

　　DUMP(123456789012345678901234567890)

 

　　--------------------------------------------------------------

 

　　Typ=2 Len=16: 207,13,35,57,79,91,13,35,57,79,91,13,35,57,79,91

 

　　使用SYS_GUID或者序列会在数据库使用周期里的某些地方造成性能上的消耗；问题就是在那里。对于SYS_GUID而言，性能上的影响在查询时间和创建时间上（在表格里要创建更多的块和索引以容纳数据）。对序列而言，性能上的影响在查询期间，在这个时候，SGA序列的缓冲区被用光。在缺省情况下，一个序列一次会缓冲20个值。如果数据库没有使用这些值就关闭了，它们就会被丢失。　

 

　　SYS_GUID生成的值的另一个显著的不足之处是，管理这些值会变得困难得多。你必须（手动）输入它们或者通过脚本来填充它们，或者将它们作为Web参数来传递。

 

性能比较

　　创建下列对象：

 

　　create table tsg as select RAWTOHEX(sys_guid()) sgid,a.* from all_objects a;

 

　　create SEQUENCE seq_tsg;

 

　　create table tsg2 as select seq_tsg.nextval,a.* from all_objects a;

空间比较

　　现在这两个表：tsg和tsg2拥有的行数相同，但大小不同：

 

　　

表	行数	Number Extents	Size in bytes	索引大小
TSG（SYS_GUID主键）	50231	23	8388608	3145728
TSG2（Sequence主键）	50231	21	6291456	917504

　换言之，相同条件下，使用SYS_GUID做主键比用Sequence做主键，表多消耗了空间2097152 byte,索引多消耗2228224 byte,平均每行多消耗86.1 byte.

 

　　考虑到生产环境下,每天5万条记录,则一年365*50000=18250000条记录,则理论上需要多耗费空间约合 1.43GB 存储空间.这些空间对磁盘消耗而言可以忽略不计，对内存仍然是有一定影响的，但就当前的服务器能力而言，影响有限，如果对表进行合理分区后，这种影响可以降低至极低。

执行计划比较

　　比较唯一查询时的执行计划:

 

　　对TSG执行:

 

　　select owner

 

　　from tsg

 

　　where sgid = 'F36C09B7A7A84297995352D2409EB40E'

 

　　对TSG2执行:

 

　　select owner

 

　　from tsg2

 

　　where sgid = 99

 

　　统计信息对比:从以上统计信息看,执行计划相同。

 

　　可以预料到的是,由于使用SYS_GUID做主键,比较的是字符串,故耗费CPU要高些,因此,logical reads要高些,至于Physical Readers居然低一些,就不知道原因了（实际上二者基本都没有产生大量的物理读）,估计是我的测试环境Db Cache太小的缘故.

 

　　对于响应时间,这应该是计算机环境产生的影响,不能说明问题，这两条语句响应都很快,小于0.02秒.

小结

　　从实践来看,使用SYS_GUID()做主键的优点多于负面影响。特别是在多个数据库数据集成时,GUID的优点显而易见.A项目最终没有采用客户定义的“货单唯一序号”作为主键，也是出于关系数据库设计的法则约定：“主键不要代表任何意义”。

 

自我小结：

从以上找的这些资料来看，觉得是选用sequence做主键还是唯一编码做主键各有利弊，sys_guid ()生成的序列号过长，这会消耗数据库存储空间，且管理不方便。但是对数据集成和并行环境的应用数据的唯一性比sequence好，减少冲突发生。那是不是可以通过什么办法产生位数比较短的唯一序号就解决了guid产生的问了就好了呢？不知道还有没有更好的标识办法，希望这方面有研究的朋友给一些指点，回头有空自己参照网上的举例也再亲自做一些简单的试验验证一下。