oracle sql 基礎（五）：數據定義語言（創建和管理序列、索引、同義詞）

       許多應用程式要求使用唯一的數字作為主鍵的值，你即可以在應用程式中構建代碼來處理這種需求，也可以用一個序列來產生唯一的數字。如果你想要增進某些查詢的性能，你應該考慮創建一個索引，你也可以用索引在列或列的集合上強制唯一性。你可以用同義詞為對象提供可替代的名字。下麵我們來介紹序列、索引和同義詞三個資料庫對象。

      一、創建和管理序列

       序列是用戶創建的資料庫對象，可以被多個用戶共用。 序列的一個典型的用途是創建一個主鍵的值，它對於每一行必須是唯一的。序列由一個Oracle內部程式產生並增加或減少。 序列是一個節省時間的對象，因為它可以減少應用程式中產生序列程式的代碼量。 序列號獨立於表被存儲和產生，因此，相同的序列可以被多個表使用。

       1.1 定義和查詢序列

       用CREATE SEQUENCE語句自動產生序列數。語法：

CREATE SEQUENCE sequence   --sequence 是序列發生器的名字
[INCREMENT BY n]           --序列號之間的間隔，在這兒n是一個整數，
[START WITH n]             --產生的第一個序列數，預設值1。
[{MAXVALUE n| NOMAXVALUE}] --序列能產生的最大值，預設NOMAXVALUE（升序：10^27；降序：-1）
[{MINVALUE n| NOMINVALUE}] --最小序列值，預設NOMINVALUE（升序：1；降序-（10^26））
[{CYCLE | NOCYCLE}]        --指定序列在達到它的最大或最小值之後，是否繼續產生，預設NOCYCLE。
[{CACHE n| NOCACHE}];      --指定Oracle伺服器預先分配多少值，並且保持在記憶體中，預設緩衝20個值。

       如果INCREMENT BY值是負數，序列是降序。另外，ORDER | NOORDER選項可用，ORDER選項保證序列值按順序產生，如果你將序列用於產生主鍵值它是不重要的，該選項僅與Parallel Server(並行服務)選項有關。如果序列值被高速緩衝，系統故障它們將被丟失。

       一旦創建了序列，它就被文本化在數據字典中。因為序列是一個資料庫對象，你可以在USER_OBJECTS數據字典表中識別它。你也可以從USER_SEQUENCES數據字典視圖中用選擇確認序列的設置。下麵是一個定義和查詢序列的實例。

--創建序列
CREATE SEQUENCE seq_test
INCREMENT BY 2
START WITH 3
MAXVALUE 1000
NOCYCLE  --若用戶產生主鍵，怎不使用CYCLE選項。
NOCACHE  --不允許高速緩衝;
--查詢序列
SELECT object_name,object_id,object_type 
FROM USER_OBJECTS 
WHERE object_name = 'SEQ_TEST';
SELECT * FROM USER_SEQUENCES 
WHERE sequence_name = 'SEQ_TEST';

       1.2 使用序列

       在創建序列後，它產生連續的數給你在表中使用。用NEXTVAL和CURRVAL偽列引用序列值。NEXTVAL偽列用於從指定的序列中取回連續的序列數的下一個值。你必須用序列名限定NEXTVAL，當你引用sequence.NEXTVAL時，一個新的序列數被產生並且當前的序列數被放入CURRVAL。CURRVAL偽列被用於查閱當前當前用戶剛纔產生的序列數，NEXTVAL必須被在 CURRVAL可以被引用之前用於在當前用戶的會話中產生一個序列數，你必須用序列名限定CURRVAL，當sequence.CURRVAL被引用時，最後返回給用戶程式的值被顯示。

       如果序列用帶 NOCACHE 的選項創建的，就可以在序列值不增加的情況下用查詢 USER_SEQUENCES表的方法，查看下一個可用的序列值。經常使用的序列用帶緩存創建將增進效率，對於緩存的序列，沒有辦法找出下一個可用的序列將是什麼，該值不實際獲得和使用，因此，建議用戶不要查找下一個序列值，而相信每次一個序列被用於一個 INSERT 語句時系統會提供一個唯一值。

--在表中插入序列號
INSERT INTO dept (deptno,dname,loc)
VALUES (SEQ_TEST.NEXTVAL,'TEST','TEST');
--查看當前序列值
SELECT SEQ_TEST.CURRVAL FROM dual;
--查看下一個序列值
SELECT SEQ_TEST.NEXTVAL FROM dual;

       1.3 修改序列

       可以用ALTER SEQUENCE語句修改該序列。語法：

ALTER SEQUENCE sequence
[INCREMENT BY n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];

       用ALTER SEQUENCE語句，只有以後的序列數會受影響；START WITH選項不能被改變。為了以不同的數重新開始一個序列，該序列必須被刪除和重新創建；一些驗證被執行，例如，一個新MAXVALUE如果小於當前的序列值就不能用。

ALTER SEQUENCE seq_test
INCREMENT BY 10
MAXVALUE 10000;

       1.4 刪除序列

       使用DROP SEQUENCE語句從數據字典中刪除一個序列。你必須是被刪除序列的所有者或者有DROP ANY SEQUENCE許可權來刪除它。語法：

DROP SEQUENCE sequence;

       例如：DROP SEQUENCE seq_test;

      二、創建和維護索引

       索引是Oracle伺服器的一個方案對象，索引能用指針加速行的取回，索引可以被顯式創建，也可以被自動創建，如果你在列上沒有索引，那麼將發生全表掃描。索引提供對錶中行的直接和快速訪問，它的目的是用已索引的路徑快速定位數據以減少磁碟I/O。索引由Oracle伺服器自動使用和維護，一旦一個索引被創建，它就不再需要用戶直接管理。

       2.1 創建索引

       可以創建兩種類型的索引，一種是唯一性索引：當你在一個表中定義一個列為主鍵，或者定義一個唯一鍵約束時Oracle伺服器自動創建該索引，索引的名字習慣上是約束的名字。 另一種索引類型是非唯一索引，它可以由用戶創建，例如，你可以創建一個FOREIGN KEY列索引用於一個查詢中的連接來改進數據取回的速度。創建索引語法：

CREATE INDEX index ON table(column[,column]...);

       例如：CREATE Index dept_dname_idx ON dept(dname);
       在表上建立更多的索引並不意味者更快地查詢，在帶索引的表上被提交的每個 DML操作意味者索引必須更新；與表聯繫的索引越多，對Oracle伺服器的影響越大。因此，你只需要在以下情況下創建索引：
       1.列包含一個大範圍的值、列包含大量的空值、一個或多個列在WHERE子句或連接條件中被頻繁使用、表很大並且大多數查詢所期望的返回行數少於總行數的2–4%。
       2.如果你想要強制非唯一，你應該在表中定義一個唯一的約束，然後唯一索引被自動創建。
       3.複合索引 (也稱為連接索引) 是在一個表中的多個列上創建的索引，在複合索引中的列可以任何順序出現，並且不需要與在表中的列相一致。

       下麵的情況通常不值得創建索引：

       表很小，不經常在查詢中作為條件被使用的列，大多數查詢期望取回多於表中百分之2到4的行，表經常被更新，被索引的列作為表達式的的一部分被引用。

       2.2 查詢索引

       從USER_INDEXES數據字典視圖可以確認索引的存在。你也可以查詢 USER_IND_COLUMNS視圖，檢查與索引有關的列。下麵的例子查詢EMPLOYEES表上所有已創建的索引，包括受影響的列的名字和索引的唯一性。

Select Ic.Index_Name,
       Ic.Column_Name,
       Ic.Column_Position Col_Pos,
       Ix.Uniqueness
From User_Indexes Ix, User_Ind_Columns Ic
Where Ic.Index_Name = Ix.Index_Name
And Ic.Table_Name = 'DEPT';

       2.3 刪除索引

       你不能修改索引，為了改變索引，你必須先刪除它，然後重新創建它。用DROP INDEX 語句從數據字典中刪除索引，為了刪除索引，你必須是索引的所有者，或者有 DROP ANY INDEX許可權。如果你刪除一個表，相關的索引和約束將被自動刪除，但視圖和序列將保留。刪除索引語法：

DROP INDEX index;

       例如：DROP INDEX dept_dname_idx;

      三、創建和管理同義詞

       為了查閱另一個用戶所擁有的表，你需要將創建該表的用戶名加句點作為首碼加在表名前面。創建一個同義詞可以除去對象名必須帶的方案限制，並提供給你一個可替換表名、視圖名、序列名和過程名或其它對象名。該方法對具有特別長的對象的名字很有用。

       3.1 創建同義詞

CREATE [PUBLIC] SYNONYM synonym FOR object;
--PUBLIC 創建一個可以被所有用戶訪問的同義詞
--synonym 是要被創建的同義詞的名字
--object 指出要創建同義詞的對象

       資料庫管理員可以創建一個可為所有用戶訪問的公共同義詞。下麵的例子為scott的dept表創建一個公共同義詞：

CREATE PUBLIC SYNONYM dept_syn
FOR scott.dept;

       3.2 刪除同義詞

       用DROP SYNONYM語句刪除一個同義詞，只有資料庫管理員可以刪除一個公共同義詞。

DROP PUBLIC SYNONYM synonym;

       例如：DROP PUBLIC SYNONYM dept_syn;