Oracle中序列(Sequence)示例詳解
一 序列定義
序列(SEQUENCE)是序列號(hào)生成器,可以為表中的行自動(dòng)生成序列號(hào),產(chǎn)生一組等間隔的數(shù)值(類型為數(shù)字)。不占用磁盤空間,占用內(nèi)存。
其主要用途是生成表的主鍵值,可以在插入語(yǔ)句中引用,也可以通過(guò)查詢檢查當(dāng)前值,或使序列增至下一個(gè)值。
二 創(chuàng)建序列
創(chuàng)建序列需要CREATE SEQUENCE系統(tǒng)權(quán)限。序列的創(chuàng)建語(yǔ)法如下:
CREATE SEQUENCE 序列名
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE/ MINVALUE n| NOMAXVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n| NOCACHE}];
其中:
1) INCREMENT BY用于定義序列的步長(zhǎng),如果省略,則默認(rèn)為1,如果出現(xiàn)負(fù)值,則代表Oracle序列的值是按照此步長(zhǎng)遞減的。
2) START WITH 定義序列的初始值(即產(chǎn)生的第一個(gè)值),默認(rèn)為1。
3) MAXVALUE 定義序列生成器能產(chǎn)生的最大值。選項(xiàng)NOMAXVALUE是默認(rèn)選項(xiàng),代表沒有最大值定義,這時(shí)對(duì)于遞增Oracle序列,系統(tǒng)能夠產(chǎn)生的最大值是10的27次方;對(duì)于遞減序列,最大值是-1。
4) MINVALUE定義序列生成器能產(chǎn)生的最小值。選項(xiàng)NOMAXVALUE是默認(rèn)選項(xiàng),代表沒有最小值定義,這時(shí)對(duì)于遞減序列,系統(tǒng)能夠產(chǎn)生的最小值是?10的26次方;對(duì)于遞增序列,最小值是1。
5) CYCLE和NOCYCLE 表示當(dāng)序列生成器的值達(dá)到限制值后是否循環(huán)。CYCLE代表循環(huán),NOCYCLE代表不循環(huán)。如果循環(huán),則當(dāng)遞增序列達(dá)到最大值時(shí),循環(huán)到最小值;對(duì)于遞減序列達(dá)到最小值時(shí),循環(huán)到最大值。如果不循環(huán),達(dá)到限制值后,繼續(xù)產(chǎn)生新值就會(huì)發(fā)生錯(cuò)誤。
6) CACHE(緩沖)定義存放序列的內(nèi)存塊的大小,默認(rèn)為20。NOCACHE表示不對(duì)序列進(jìn)行內(nèi)存緩沖。對(duì)序列進(jìn)行內(nèi)存緩沖,可以改善序列的性能。
大量語(yǔ)句發(fā)生請(qǐng)求,申請(qǐng)序列時(shí),為了避免序列在運(yùn)用層實(shí)現(xiàn)序列而引起的性能瓶頸。Oracle序列允許將序列提前生成 cache x個(gè)先存入內(nèi)存,在發(fā)生大量申請(qǐng)序列語(yǔ)句時(shí),可直接到運(yùn)行最快的內(nèi)存中去得到序列。但cache個(gè)數(shù)也不能設(shè)置太大,因?yàn)樵跀?shù)據(jù)庫(kù)重啟時(shí),會(huì)清空內(nèi)存信息,預(yù)存在內(nèi)存中的序列會(huì)丟失,當(dāng)數(shù)據(jù)庫(kù)再次啟動(dòng)后,序列從上次內(nèi)存中最大的序列號(hào)+1 開始存入cache x個(gè)。這種情況也能會(huì)在數(shù)據(jù)庫(kù)關(guān)閉時(shí)也會(huì)導(dǎo)致序號(hào)不連續(xù)。
7) NEXTVAL 返回序列中下一個(gè)有效的值,任何用戶都可以引用。
8) CURRVAL 中存放序列的當(dāng)前值,NEXTVAL 應(yīng)在 CURRVAL 之前指定 ,二者應(yīng)同時(shí)有效。
例子:
SQL> create sequence t1_seq increment by 1 start with 1; SYS@orclasm > drop sequence t1_seq; Sequence dropped. SYS@orclasm > create sequence t1_seq increment by -1 start with -5; Sequence created. SYS@orclasm > select t1_seq.currval,t1_seq.nextval from dual; CURRVAL NEXTVAL ---------- ---------- -5 -5 SYS@orclasm > select t1_seq.currval,t1_seq.nextval from dual; CURRVAL NEXTVAL ---------- ---------- -6 -6 SYS@orclasm > select t1_seq.currval,t1_seq.nextval from dual; CURRVAL NEXTVAL ---------- ---------- -7 -7
三 使用序列
調(diào)用NEXTVAL將生成序列中的下一個(gè)序列號(hào),調(diào)用時(shí)要指出序列名,即用以下方式調(diào)用: 序
列名.NEXTVAL
CURRVAL用于產(chǎn)生序列的當(dāng)前值,無(wú)論調(diào)用多少次都不會(huì)產(chǎn)生序列的下一個(gè)值。如果序列還
沒有通過(guò)調(diào)用NEXTVAL產(chǎn)生過(guò)序列的下一個(gè)值,先引用CURRVAL沒有意義。調(diào)用CURRVAL
的方法同上,要指出序列名,即用以下方式調(diào)用:序列名.CURRVAL
SQL> create table t1(id number,qq number,ww number); SQL> insert into t1 values(t1_seq.nextval,1,1); 1 row created. SQL> / 1 row created. SQL> / SQL> / SQL> / 1 row created. SQL> select * from t1; ID QQ WW ---------- ---------- ---------- 1 1 1 2 1 1 3 1 1 4 1 1 5 1 1 SQL> select t1_seq.currval from dual; CURRVAL ---------- 5 SQL> select t1_seq.nextval from dual; NEXTVAL ---------- 6 SQL> select t1_seq.nextval from dual; NEXTVAL ---------- 7
四 修改序列
修改序列的注意事項(xiàng):
1 必須是序列的擁有者或?qū)π蛄杏?nbsp;ALTER any sequence權(quán)限
2 只有將來(lái)的序列值會(huì)被改變
3 改變序列的初始值只能通過(guò)刪除序列之后重建序列的方法實(shí)現(xiàn)
Alter sequence示例:
ALTER SEQUENCE emp_sequence INCREMENT BY 10 MAXVALUE 10000 CYCLE -- 到10000后從頭開始 NOCACHE ;
可以影響Sequence的初始化參數(shù):
SEQUENCE_CACHE_ENTRIES =設(shè)置能同時(shí)被cache的sequence數(shù)目。
SQL> alter sequence t1_seq increment by 2 maxvalue 10 cycle; alter sequence t1_seq increment by 2 maxvalue 10 cycle * ERROR at line 1: ORA-04013: number to CACHE must be less than one cycle SQL> alter sequence t1_seq increment by 2 maxvalue 40 cycle; Sequence altered. SQL> insert into t1 values(t1_seq.nextval,1,1); / / ………. / SQL> select * from t1; ID QQ WW ---------- ---------- ---------- 1 1 1 2 1 1 3 1 1 4 1 1 5 1 1 9 1 1 11 1 1 13 1 1 15 1 1 17 1 1 ……………………………………………….. 37 1 1 39 1 1 1 1 1 3 1 1 5 1 1 24 rows selected.
五 查詢序列
1 通過(guò)數(shù)據(jù)字典USER_OBJECTS可以查看用戶擁有的序列。
2 通過(guò)數(shù)據(jù)字典USER_SEQUENCES可以查看序列的設(shè)置。
例:查看用戶的序列:
SELECT SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,LAST_NUMBER FROM USER_SEQUENCES;
執(zhí)行結(jié)果:
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER
SQL> select object_name,object_type from user_objects; OBJECT_NAM OBJECT_TYPE ---------- ------------------- T1_SEQ SEQUENCE T1 TABLE SQL> select sequence_name,increment_by,cycle_flag,cache_size from user_sequences; SEQUENCE_N INCREMENT_BY CYCLE_FLAG CACHE_SIZE ---------- ------------ ---------- ---------- T1_SEQ 2 Y 20
六 刪除序列
SQL> drop sequence t1_seq;
序列作為主鍵使用的原理、優(yōu)缺點(diǎn)討論
這幾天和同事一直在討論關(guān)于表設(shè)計(jì)中主鍵選擇的問(wèn)題,用sequence作為主鍵究竟有什么好處,又有什么缺點(diǎn),尤其是有些事務(wù)場(chǎng)景上下文需要用到創(chuàng)建的序列值,如何用?其實(shí)我想說(shuō)的是,可能只是一個(gè)很簡(jiǎn)單的概念,可能深入理解,還是有很多未知的知識(shí),當(dāng)然也就可能會(huì)有一些容易忽略但又可能很關(guān)鍵的坑,只有碰了才知道。。。以下是總結(jié)摘要,如有疏漏,還請(qǐng)過(guò)路的各位大俠賜教。
1、首先說(shuō)下seq.nextval主要有以下兩種使用場(chǎng)景:
(1). 如果一個(gè)事務(wù)中只是INSERT時(shí)需要序列,其他地方不會(huì)需要這個(gè)序列,那么只需要在INSERT ... VALUES (seq.nextval ...)語(yǔ)句中使用即可。
(2). 如果一個(gè)事務(wù)中INSERT一張表后,還需要插入時(shí)的主鍵ID值,作為外鍵插入其他表,那么就需要在INSERT第一張表前使用select seq.nextval from dual提前獲取可用的ID保存到一個(gè)變量中,為后面使用。
2、其次可以簡(jiǎn)單說(shuō)下調(diào)用序列的原理,只有理解了序列的原理,才能有助于我們知道如何正確使用序列。
使用序列時(shí)Oracle內(nèi)部大體是按照如下步驟進(jìn)行:
(1). 一個(gè)序列會(huì)被定義到Oracle內(nèi)部的一張數(shù)據(jù)字典表(seq$)的一行。
(2). 第一次使用序列,序列的起始值會(huì)加上緩存大小,然后更新回行。
(3). Oracle內(nèi)部會(huì)自動(dòng)跟蹤內(nèi)存中的兩個(gè)值,當(dāng)前值和目標(biāo)值。
(4). 每次有回話調(diào)用seq.nextval,Oracle會(huì)遞增當(dāng)前值,然后檢查是否超過(guò)了目標(biāo)值,再返回結(jié)果。
(5). 如果當(dāng)前值和目標(biāo)值相同,Oracle會(huì)更新數(shù)據(jù)字典表中的行,為目標(biāo)值加上緩存大小,同時(shí)內(nèi)存中產(chǎn)生了一個(gè)新的目標(biāo)值。
例如create sequence seq cache 20;這樣一序列。名稱為seq的序列,緩存大小是20,默認(rèn)初始值是1,步長(zhǎng)默認(rèn)是1。
當(dāng)使用了一次seq.nextval后,可以看HIGHWATER字段值為21,即目標(biāo)值1+緩存大小20=21。
當(dāng)執(zhí)行20次后,seq.nextval值變?yōu)?1,此時(shí)HIGHWATER字段值是41,即目標(biāo)值21+緩存大小20=41。
也就是每調(diào)用seq.nextval值20次,會(huì)更新一次seq$表,那么問(wèn)題來(lái)了,如果cache值較小,且序列使用的頻率較高,那么會(huì)對(duì)seq$表有頻繁的更新操作,日志量會(huì)增加,尤其在RAC下,更新該行的時(shí)候,該數(shù)據(jù)塊會(huì)在節(jié)點(diǎn)間不停的傳送,就會(huì)產(chǎn)生可能的爭(zhēng)用,這種問(wèn)題會(huì)被放大。因此為了減少這種情況,我們可以將cache緩存值設(shè)置大一些,例如1000,減少對(duì)字典表的更新。
序列還有一個(gè)問(wèn)題,就是cache緩存是實(shí)例級(jí)的,對(duì)于RAC,比如第一個(gè)節(jié)點(diǎn)使用序列時(shí)會(huì)分配1-20,第二個(gè)節(jié)點(diǎn)會(huì)被分配21-40,Oracle保證不會(huì)重復(fù),但若節(jié)點(diǎn)crash了,比如節(jié)點(diǎn)1壞了,那么序列就會(huì)出現(xiàn)斷號(hào),節(jié)點(diǎn)1再次使用時(shí),只會(huì)從41-60,由于我們用主鍵只為了標(biāo)示唯一,不關(guān)心段號(hào),也不關(guān)心產(chǎn)生的順序,所以這些可以忽略。
注:最近在討論某系統(tǒng)和一個(gè)外系統(tǒng)做全局事務(wù)的事情,本想用這個(gè)主鍵作為兩系統(tǒng)傳輸?shù)囊徊糠郑糜诳刂迫质聞?wù),且用其作為判斷交易先后順序的依據(jù),這是不太符合要求的,因?yàn)槭荝AC,序列是基于實(shí)例級(jí)cache,那么如果不能保證某一類型的交易總在一個(gè)節(jié)點(diǎn)上執(zhí)行,那么不同次交易產(chǎn)生的主鍵序列值可能不是遞增的,例如節(jié)點(diǎn)1處理一次交易,產(chǎn)生序列是1,節(jié)點(diǎn)2處理一次交易,產(chǎn)生序列是21,此時(shí)節(jié)點(diǎn)1再處理一次交易,產(chǎn)生序列是2;除非設(shè)置序列為order,但這樣在RAC就有可能產(chǎn)生資源爭(zhēng)用的問(wèn)題,因?yàn)闉榱吮WC多節(jié)點(diǎn)間每次產(chǎn)生的序列值是遞增的,每次產(chǎn)生就需要多節(jié)點(diǎn)間判斷當(dāng)前值后,才能知道下一個(gè)值是多少,而且會(huì)有額外的鎖,保證同一時(shí)間只有一個(gè)節(jié)點(diǎn)在做這個(gè)操作,當(dāng)然究竟是否會(huì)產(chǎn)生資源爭(zhēng)用,還是要依據(jù)實(shí)際的業(yè)務(wù)并發(fā)量,或者壓力測(cè)試才能證明,這里只是說(shuō)可能會(huì)這樣的問(wèn)題,不是一定會(huì),否則Oracle就不會(huì)有提供這種order的創(chuàng)建屬性,凡事不絕對(duì)。
3、結(jié)合(1)的場(chǎng)景,
(1). 如果一個(gè)事務(wù)中只是INSERT時(shí)需要序列,其他地方不會(huì)需要這個(gè)序列,那么只需要在INSERT ... VALUES (seq.nextval ...)語(yǔ)句中使用即可。
> 這個(gè)場(chǎng)景下,如果序列cache設(shè)置為1000,調(diào)用100次nextval幾乎沒有影響。
(2). 如果一個(gè)事務(wù)中INSERT一張表后,還需要插入時(shí)的主鍵ID值,作為外鍵插入其他表,那么就需要在INSERT第一張表前使用select seq.nextval from dual提前獲取可用的ID保存到一個(gè)變量中,為后面使用。
> 這個(gè)場(chǎng)景,就有些說(shuō)的了。如果是同一事務(wù)中需要用到之前的序列值,那么就需要提前用select seq.nextval from dual保存到變量中,(當(dāng)然,如果是用PLSQL語(yǔ)句,則可以不用提前保存變量這步,但咱們的應(yīng)用中不適用,就不多說(shuō)了)。
到此這篇關(guān)于Oracle中序列(Sequence)詳解的文章就介紹到這了,更多相關(guān)oracle 序列內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
解決Windows10不能安裝Oracle?11g的問(wèn)題(附詳細(xì)安裝教程)
這篇文章介紹了解決Windows10不能安裝Oracle?11g的方法,對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2022-04-04Oracle游標(biāo)使用參考語(yǔ)句實(shí)例解析
這篇文章主要介紹了Oracle游標(biāo)使用參考語(yǔ)句實(shí)例解析,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-11-11winserver 2012R2 安裝oracle及創(chuàng)建表流程(推薦)
這篇文章主要介紹了winserver 2012R2 安裝oracle及創(chuàng)建表流程,非常不錯(cuò),具有參考借鑒價(jià)值,需要的朋友可以參考下2017-06-06Oracle 輕量級(jí)實(shí)時(shí)監(jiān)控工具 oratop詳解
這篇文章主要介紹了Oracle 輕量級(jí)實(shí)時(shí)監(jiān)控工具 oratop,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2021-03-03