oracle sql 基礎（五）：資料定義語言 (Data Definition Language)（建立和管理序列、索引、同義字）

標籤：lap   更新   設定   for   時間   範圍   dex   最小   pre   

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

      一、建立和管理序列

       序列是使用者建立的資料庫物件，可以被多個使用者共用。 序列的一個典型的用途是建立一個主鍵的值，它對於每一行必須是唯一的。序列由一個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_testINCREMENT BY 2START WITH 3MAXVALUE 1000NOCYCLE  --若使用者產生主鍵，怎不使用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_testINCREMENT BY 10MAXVALUE 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.UniquenessFrom User_Indexes Ix, User_Ind_Columns IcWhere Ic.Index_Name = Ix.Index_NameAnd 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_synFOR scott.dept;

建立同義字執行個體

       3.2 刪除同義字

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

DROP PUBLIC SYNONYM synonym;

       例如：DROP PUBLIC SYNONYM dept_syn;

oracle sql 基礎（五）：資料定義語言 (Data Definition Language)（建立和管理序列、索引、同義字）