使用序列的錯誤ORA-02287

使用序列的錯誤ORA-02287

今天一個開發的同事問我一個問題，說在執行一條sql語句的時候報了ORA錯誤，腦海中刪除了各種許可權的問題之後，他提供給我的錯誤還是在我預料之外。
ERROR at line 1:
 ORA-02287: sequence number not allowed here

這個問題看錯誤資訊是很明顯和sequence有關的。但是為什麼會報出這個錯誤呢，在幾封郵件交流之後，問題就明朗起來，語句是類似下面這樣的結構，insert into customer(xxxxx,xxxxx,xxx...............)
 select distinct xxxxx,seq_value.nextval,xxxx,xxxx,xxx... from new_table group by xxx,xxx,xxx,xxx;
真實的指令碼洋洋洒洒一大頁，各種子查詢，表關聯，函數處理，看起來還是需要些耐心的。簡縮之後就是上面的結構，
 這個Insert採用了子查詢來插入資料，根據和開發的溝通，是需要提供一個補丁，做修複資料所用，所以會有大量的資料過濾操作。
 插入資料的時候使用seq_value.nextval也還是很常見的，怎麼會報出錯誤了呢，按照這個語句的結構發現還是最開頭的distinct和group操作導致的，這種資料的統計分析操作讓本來就不確定的sequence值更加不確定，所以斟酌再三還是建議他們先建立一個暫存資料表，不涉及序列的操作，只對序列之外的資料通過distinct,group by過濾之後，在insert的時候對剛建立的暫存資料表和序列結合起來，一次插入。

虛擬碼類似下面的形式，
create table temp_tab as select distinct xxxxxx  from xxxx  group by xxxxxx;
 insert into customer(xxxxx,xxxx) select xxxx,seq_vvalue.nextval from temp_tab;

我們來簡答類比一下這個問題。
 首先為了不影響原有的sequence，我們可以建立一個類似的sequence,然後在指令碼中替換即可，這樣也不會對原有環境的sequence值造成影響。
CREATE SEQUENCE  "NEW_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1012852 CACHE 20 NOORDER  NOCYCLE
然後我們建立一個表
create table new_test( id1 number,id2 number,name varchar2(30));
然後嘗試distinct和group by 操作，發現都不可以。
n1@TEST11G> select new_seq.nextval,id1,id2,name from new_test group by id1,id2,name;
 select new_seq.nextval,id1,id2,name from new_test group by id1,id2,name
                *
 ERROR at line 1:
 ORA-02287: sequence number not allowed here

n1@TEST11G> select distinct new_seq.nextval,id1,id2,name from new_test;
 select distinct new_seq.nextval,id1,id2,name from new_test
                        *
 ERROR at line 1:
 ORA-02287: sequence number not allowed here
其實這個問題的原因還是很容易理解的，這種sequence值的動態不確定性，很容易出問題。其實不光使用distinct，group by 會有問題，很多相關的操作都是如此。
 比如union,union all
 select new_seq.nextval,id1,id2,name from new_test
 union all
 select new_seq.nextval,id1,id2,name from new_test

 minus操作。
select new_seq.nextval,id1,id2,name from new_test
 minus
 select new_seq.nextval,id1,id2,name from new_test

使用In子查詢
select new_seq.nextval id1,id2,name from new_test  where id1 in (select new_seq.nextval from new_test )

 order by操作
select new_seq.nextval,id1,id2,name from new_test order by id2;

換個角度來看，對於這類問題，也可以使用暫存資料表來處理，也可以使用pl/sql來處理，都能達到比較目的，另外一個角度來說，對於sequence的使用，大家一般都認為是取之不盡，用之不竭，感覺大量使用時應該的，在很多時候還是需要好好斟酌一下，有些補丁或者臨時的處理是否一定需要使用到序列，序列資源也是很寶貴的資源，如果在測試指令碼中做了大量的自增處理也是很大的浪費。