9.10 Sequence 與 Synonym

Sequence 就是一組序列號，每次取號後自動增加，一般用來作為需要照數列排序的地方，或者是作為欄位唯一值的設定，例如訂單編號，當接收到一筆訂單之後會賦予這筆訂單一個編號，格式可能是 “PO0001” ，在 “PO” 後面賦予一個數值，此時使用 Sequence 取號就可以代表訂單下單的順序，以及確保每一筆訂單編號都是唯一值。 Sequence 是一個獨立的 Object ，使用 create sequence 來建立，例如建立一個名為 po_seq 的 Sequence :

SQL> create sequence po_seq minvalue 0 maxvalue 1000 increment by 1 start with 1 cache 20 order  cycle;

其中:

• minvalue : 設定此 sequence 的最小值，預設為 nominvalue ，從 1 開始。

• maxvalue : 設定此 sequence 的最大值，預設為 nomaxvalue ，最大可達 10 的 28 次方減 1 。

• increment by : 表示此 sequence 每個序列號增加多少，設定為 1 表示每個序列號增加 1 ，即 1 、 2 、 3 … 如此下去。

• start with : 設定此 sequence 從第幾號開始增加。

• cache : 設定 sequence 事先要把多少個序列號放入 cache 中，設定為 20 表示先將 20 個號碼放入 cache ，從 cache 取號有助於提升效能。對於 RAC 環境來說， cache 的設定是必須的，因為 RAC 透過兩個 Instance 同時取號的話很容易會發生 enq: SQ – contention 的等待，透過 cache 的設定可以避免此等待事件的發生，建議在 RAC 環境下 cache 設定為 100 以上。

• order : 此設定是確保 sequence 序列號是按照數列增加的，在有設定 cache 的 RAC 環境下，照時間的先後所取出來的序列號可能是跳號的，例如 cache 設置為 20 ，那麼第一個 Instance 會先 cache 1 ~ 20 的號碼；而第二個 Instance 會 cache 21 ~ 40 的號碼，如果從 Instance 1 與 Instance 2 輪流來取號的話，所產生的順序可能為 1 、 2 、 21 、 3 、 22 … 產生跳號的情況。使用 order 的設定是確保在這種有 cache 的情況下也不會跳號，即便從不同的 Instance 取號，取出的順序仍然會是照順序的 1 、 2 、 3 … 。雖然 order 的設定可以確保不會跳號，但效能上會比 noorder 還來的差，有些系統的序列號只是要確保此欄位的資料不會重複，並沒有一定要照順序的要求，那麼就可以使用 noorder 來提升效能。這個項目預設是 noorder 。

• cycle : 設定當 sequence 達到 maxvalue 之後，是否要回到從第一個號碼開始，預設為 nocycle 。

Sequence 取號的方式是使用 nextval 函式，例如:

SQL> select po_seq.nextval from dual;         (直接取號) SQL> insert into products values (po_seq.nextval, sysdate);       (insert 的時候帶入 sequence 序列號)

這邊要注意的是，只要進行過 nextval 取號， Sequence 就無法回到上一號。

Sequence 的資訊可以透過 dba_sequences 或是 user_sequences 來查詢。如果有 Sequence 重建的需求時，可以利用 dba_sequences 抓出每個 Sequence 目前的序列號以及相關的屬性，進而組合出 Sequence 建立的語法，例如抓出 HR 與 SCOTT 兩個使用者的所有 Sequence 設定:

SQL> select 'drop sequence '||sequence_owner||'.'||sequence_name||';'||chr(10)||' create sequence '||sequence_owner||'.'||sequence_name||' minvalue '||min_value||' maxvalue '||max_value||' increment  by '||increment_by||' start with '||to_char(last_number)||case when cache_size > 0 then ' cache '||cache_size else ' nocache ' end||case order_flag when 'Y' then 'order ' else ' noorder ' end||case cycle_flag when 'Y' then ' cycle ' else ' nocycle ' end||';' as "seq_create"   from dba_sequences where sequence_owner in ('HR','SCOTT') order by sequence_owner;

Synonym 代表的是同義字，當使用者要存取其他使用者的物件時，必須使用 schema.object_name ，若是建立了同義字，那麼就可以簡化語法，物件前面不需要在加上 “schema.” 。 Synonym 可以分為 Public Synonym 與 Private Synonym ， Public Synonym 表示為公用的同義字，必須具有 DBA 權限才可以建立，所有使用者都可以使用 Public Synonym ， 例如建立一個 Public Synonym emp 來代表 hr.employees 這個 Table :

SQL> create public synonym emp for hr.employees;

Synonym 的定義也可以使用 db link ，例如建立 Public Synonym jobs 代表 HR 資料庫的 open_job 這個 Table :

SQL> create public synonym jobs for open_job@hr;

Private Synonym 表示同義字只有當前的使用者可以使用，也就是使用者將自己的某一個物件設定為另外一個別名而已，例如 hr 這個使用者建立一個 Private Synonym dept 來代表 departments 這個 Table:

SQL> create synonym dept for departments;

查詢 Synonym 相關資訊可以由 dba_synonyms 或 user_synonyms 來得知。