Oracle序列和索引

標籤：

序列和索引

一.序列

1.序列的概念：

　　序列（Sequence）是用來產生連續的整數資料的對象。它常常用來作為主鍵的增長列，可以升序，也可以降序。

2.建立序列：

　　文法：建立序列                                              文法解析：

　　CREATE SEQUENCE sequence_name　　　　　

　　[STRAT WITH num]　　　　　　　　　　　　　START WITH:從某一個整數開始，升序預設為1，降序預設為-1.

　　[INCREMENT BY increment]　　　　　　　　　INCREMENT BY:增長數。如果是正數則升序產生，負數則降序產生。升序預設為1，降序預設為-1。

　　[MAXVALUE num | NOMAXVALUE]　　　　　　MAXVALUE:最大值。　　NOMAXVALUE:最大值的預設選項，升序為,10的27次冪，降序預設值為-1。

　　[MINVALUE  num | NOMINVALUE]　　　　　　MINVALUE:最小值。　　　NOMINVALUE:最小值的預設選項，升序為1，降序為-10的26次冪。

　　[CYCLE | NOCYCLE]　　　　　　　　　　　　　CYCLE:表示升序達到最大值的時候，從最小值開始。如果是降序的話，達到最小值後，從最大值開始。

　　　　　　　　　　　　　　　　　　　　　　　　　NOCYCLE：表示達到最大或最小值時，不重新開始。（會報錯）。預設為NOCYCLE。

　　[CACHE num | NOCACHE]　　　　　　　　　  CACHE:使用CACHE選項時，該序列會根據序列規則產生一組序號。保留在記憶體中，當使用下一個序號時，可以更快的響應。

　　　　　　　　　　　　　　　　　　　　　　　　　當記憶體中的序號用完時，系統會再次產生一組序號儲存在緩衝中，這樣可以提高產生序號的效率。ORACLE會預設產生

　　　　　　　　　　　　　　　　　　　　　　　　　20個序號。

　　　　　　　　　　　　　　　　　　　　　　　　　NOCACHE:不預先在記憶體中產生序號。

　　樣本：建立一個從5開始，每次增長3的序列，要求最大值為1000，CYCLE，緩衝區有30個預選序號。

create sequence aastart with 5increment by 3maxvalue 1000cyclecache 30;

3.利用序列：

　　序列建立之後，我們可以通過序列對象的CURRVAL和NEXTVAL兩個‘偽列’來訪問序列的當前值和下一個值。

• 查看下一個值：

select aa.nextval from dual;

• 查看當前值：

select aa.currval from dual;

　　注意：當你剛建立好序列時，不能直接查看currval當前值，會報錯，因為你還沒有從序列中取值，所以currval是不被識別的。先取值（nextval，第一次為5），然後就可以查看當前值了。

• 利用序列插入資料。

　　我先建立一個表:

create table bb(  id int primary key  name varchar2(30);    );

　　利用上個序列插入給id插入資料：

insert into bb values(aa.nextval,‘MIKE‘);

　　插入了id為5，name為MIKE的一條資料。

 4.序列的刪除和修改:

• 修改序列：

alter sequence aa maxvalue 200;

　　修改aa序列的最大值為200。其他屬性照例即可。

• 刪除序列：

drop sequence aa;

二：索引

1.索引的概念：

　　索引是與表關聯的可選結構。可以建立索引加快對錶執行SQL語句的速度。就像書的索引可以協助我們更快的尋找資訊一樣，Oracle中的索引也提供了一種更快的訪問資料的途徑。

2.什麼情況下符合建立索引的條件：

• 在經常需要搜尋的列上，可以加快搜尋的速度。
• 在作為主鍵的列上，強制該列的唯一性和組織表中資料的排列結構。
• 在經常用在串連的列上，這些列主要是一些外鍵，可以加快串連的速度。
• 在經常需要根據範圍進行搜尋的列上建立索引，因為索引已經排序，其指定的範圍是可見的，速度要快。
• 在經常使用where子句的列上面建立索引，加快條件的判斷速度。

3.什麼情況下不應該建立索引：

• 很少查詢的列，只是作為參考的列，不應該建立索引。
• 對於那些數值很少的列。
• 對於那些定義為blob資料類型（大資料）的列。
• 經常增刪改的列，建立索引需謹慎。

4.索引的優點。

• 通過建立唯一性索引，可以保證資料庫表中沒一行資料的唯一性。
• 可以大大的加快資料的檢索速度，也是建立索引的最主要的原因。
• 可以加快表和表的串連，特別是在實現資料的參照完整性方面特別有意義。
• 在使用分組和排序子句進行資料檢索時，同樣可以顯著減少查詢中分組和排序的時間。
• 通過使用檢索，可以在查詢的過程中，使用最佳化隱藏器，提高系統的效能。

5.索引的缺點。

• 建立索引和維護索引要耗費時間，這種時間隨著資料量的增加而增加。
• 索引需要佔物理空間，除了資料表占資料空間之外，每一個索引還要佔一定的物理空間。
• 當對錶中的資料進行增刪改的時候，索引也要動態維護，這樣就降低了資料的維護速度。

6.建立索引。

• 　　建立B-Tree索引：

　　文法：　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　文法分析：

　　CREATE [UNIQUE] INDEX index_name ON　　　　　　　　　　　　　　UNIQUE:意味著索引列中的值必須是唯一的。

　　table_name(column_name,[column_name...])　　　　　　　　　　　　index_name:索引的名稱。

　　TABLESPACE tab_space;　　　　　　　　　　　　　　　　　　　　　　　table_name：需要加索引的表名。　　　

 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　column_name：需要加索引的列（可以加多個列，這樣的索引稱為符合索引。）

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 tab_space:存貯索引的資料表空間。如果不指定的話，那麼索引將會存貯在使用者預設的資料表空間下。（出於效能原因，可以將索引存貯的表

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　空間和實際表存貯的資料表空間分開。管理員應該為表和索引建立不同的資料表空間。）　

 　　樣本：為emp員工表的員工號和薪水建立索引。

create index empno_sal_index on emp(empno,sal);

　　注意：如果你所要建立的索引的列的值有重複的值，那麼就不能建立唯一索引。

• 　　建立函數索引。

　　如果我現在已經建立好了一個ename（員工姓名）列的索引，而現在想實現這樣的一條查詢語句。

select lower(ename) from emp;

　　那麼這條語句雖然可以出來結果，但是卻沒有用到索引。這時我們就需要建立函數索引如下：

create index ename_index on emp(lower(ename));

•     建立位元影像索引。

create bitmap index e_i on emp(ename);

 

　　什麼情況下建立位元影像索引：

1. 列包含較大範圍的值
2. 列包含大量空值
3. 在where子句或者串連條件中頻繁使用一個列或者多個列
4. 表相當的大，但是預計的多數查詢檢索的行數佔總行數的比例較小，如佔百分之十以下。

•     四個索引的總結。

　　B樹

    　　預設的索引類型，平衡樹索引，適用於高基數(不同值的程度高）的列。除非有特殊原因需要使用不同的索引類型或功能，否則用正常的B樹索引即可.

　　唯一索引

     　　B樹索引的一種形式，用於強制執行列值的唯一性.經常與主鍵和唯一鍵約束一起使用，但也可以獨立於約束而建立

　　位元影像索引

     　　對於包含低基數列以及在SQL語句的WHERE子句中使用許多AND或OR運算子的資料倉儲環境，非常適合使用這種索引。

　　函數索引

    　　 適用於應用了SQL函數的列。可與B樹索引類型或位元影像索引類型結合使用

 

7.查看索引資訊。

SELECT index_name, table_name, uniqueness, statusFROM user_indexesWHERE table_name IN (‘EMP‘);

　　這個語句實現了查詢索引名字，索引的表名，是否是唯一索引，索引的狀態（是否可用）。結果如下：

　　INVALID的話就是當前索引不可用。

8.修改、刪除索引。

• 修改索引：

 alter index ename_index rename to ei;

　　更改索引名，其他修改格式都是如此。

• 刪除索引：

drop index ei;

　　

Oracle序列和索引