Oracle 的各種表

一、表的型別：

1、 堆組織表：常用的表型別，以堆的方式管理，當增加資料時，將使用段中第一個適合數 據大小的空閒空間。當刪除資料時，留下的空間允許以後的DML操作重用。

2、 索引組織表：表儲存在索引結構中，利用行本身排序儲存。

3、 聚簇表：幾張表物理儲存在一塊，通常是同一個資料塊上。包含相同聚簇碼值的所有數 據在物理上儲存在一起，資料"聚集"在聚簇碼周圍，聚簇碼用索引構建。

4、雜湊聚簇表：把碼雜湊儲存到簇中，來到達資料所在的塊。適用於經常等式訪問的的數 據。

5、巢狀表：OOP擴充套件的一部分，由系統產生，以父子關係維持子表。

6、臨時表：儲存事務或會話中的臨時資料，用以中間結果的計算，分配臨時段做為儲存區 域。

7、物件表：根據物件型別建立，有特殊屬性並且和非物件表不關聯。

一張表最多有1000列；理論上一張表有無限多行；能有和列的排列一樣多的索引，且一次能夠使用32個；擁有表的數量沒有限制。

二、 術語：

1、 高水位標記（HWM）：用以標識曾經包含資料的最後一個塊，全表掃描時資料將檢測至 高水標記所在的塊，不管高水標記下是否有無空閒塊。DELETE不能降低HWM，TRUNCATE可以。

2、 自由列表（FREELIST）：用來跟蹤HWM下有空閒空間的資料塊，並把其放入FREELISTS佇列。在HWM上的塊，只有FREELIST為空才會用到，這樣ORACLE將提 高HWM並把這些塊增加到FREELISTS中。如果預料到有很多並行使用者將對一個物件進行大量的DML操作，可以配置多個FREELIST提高效能。

3、PCTFREE和PCTUSED：這兩個引數用以決定什麼時候資料塊將放置於FREELIST中。PCTFREE表示一個塊中保留多少空間用於UPDATE，當一個塊中的資料達至PCTFREE的高度時，那麼這個塊將從FREELIST中退出。PCTUSED用以 表示當塊中的資料減少至佔用塊空間多少時，將塊放入FREELIST中。 高PCTFREE，低PCTUSED適用於插入許多將要更新的資料，且為經常 增加行的大小；低PCTFREE，高PCTUSED適用於對錶只是進行DELETE和INSERT的操作。

三、 堆組織表： 表中資料在隨機的方式儲存，哪有空間就往哪儲存，不會按值的大小順序儲存。資料表是固有的無序集合。

四、 索引組織表： 資料按主碼儲存和排序，同索引結構一樣，不過資料直接儲存於主碼後面。適用於資訊檢索、空間和OLAP程式。索引組織表的適用情況：
1、 程式碼查詢表。

2、 經常透過主碼訪問的表。

3、 構建自己的索引結構。

4、 加強資料的共同定位，要資料按特定順序物理儲存。

5、 經常用between…and…對主碼或唯一碼進行查詢。資料物理上分類查詢。如一張訂單表，按日期裝載資料，想查單個客戶不同時期的訂貨和統計情況。

索引組織表建立語法：

SQL> create table t2

(x int primary key,

y char(2000) default rpad('a',2000,'d'),

z date )

organization index --表示建立的表型別是IOT

nocompress --同索引的壓縮選項一樣，表示是否對相同索引條目值進行壓縮儲存

pctthreshold 50 --當行的大小超過塊大小的百分比時，超過列資料儲存至溢位段

including y--每行including指定列前邊的列都儲存到索引塊中，其餘列儲存到溢位塊

overflow --IOT中行太大時允許設定另一溢位段來儲存溢位的資料，同行遷移相似

/

Table created.

IOT溢位段管理：如果應用程式基本上總是使用IOT中的前N列，很少訪問後面的列，那麼INCLUDING將會比較適用；如果不能確定經常訪問的列，那麼設定PCTTHRESHOLD會更適合些。

五、 索引聚簇表： 索引聚簇表是表相關的表共享同一資料塊中的相同列，並把相關資料儲存中同一個資料塊上。建立索引聚簇表中最重要的是對SIZE引數有很好的估量，否則聚簇將會降低空間利用，降低效率。

使用索引聚簇表的注意點：

1、 如果表中資料有大量DML操作的話，那麼聚簇將不適用，因為會消極地影響到DML效能。

2、 聚簇中，全表掃描將受到影響。這是因為將掃描聚簇中不同表的資料，額外增加很多無用的資料。

3、 如果經常TRUNCATE表和裝載表的話，聚簇將不適用。聚簇中的表無法被TRUNCATE的，這是因為每個塊中不只是儲存一張表的資料。

4、 如果大部分是讀取操作，且透過聚簇碼索引或聚簇表中其他索引來讀取的話，聚簇將會比較比較適用。

SQL> create cluster emp_dept_cluster 2 (deptno number(2)) --用以標識聚簇列

size 1024 --用來指出大約有多少位元組的資料和每個聚簇碼有關，ORACLE使用這個

/ 來計算每個塊能容納的最大聚簇碼數目，對聚簇的空間分配至關重要

聚簇索引儲存的是每個聚簇碼的值以及包含那個碼的資料塊的塊地址。

索引聚簇載入資料應該是同時裝載同一聚簇碼的所有資料，而不是一次裝載聚簇中不同表的資料。這是因為如果一次裝載單張表資料的話，很有可能單個碼值的資料大於SIZE指定的資料，但是由於聚簇碼已經分配完成，此時將會聚簇碼塊有許多連結塊，影響效能。透過一次裝載每個碼值對應的資料，可以更好地利用聚簇塊的空間。

六、 雜湊（雜湊）聚簇表： 概念上同索引聚簇表一樣，不同的是用雜湊函式代替了索引聚簇碼來進行資料的分配。事實上在雜湊聚簇中資料就是索引，因為資料決定行的物理位置。雜湊聚簇表中ORACLE根據行的碼值，利用內部函式或提供的函式對聚簇碼值進行運算，以決定資料的物理儲存位置。雜湊聚簇通常意味著如果透過聚碼訪問的話，一個IO就能夠提取到所需的資料。 建立雜湊聚簇時，必須指定雜湊碼值的數目。由於使用雜湊函式來確定資料的分佈，對雜湊聚簇表不能使用範圍掃描。全掃描雜湊聚簇表時，不論表是否為空，ORACLE將全掃描所有塊，這是因為雜湊聚簇中資料塊都是預先分配的。雜湊聚簇的初始資料裝載將會比較慢。 自定義的雜湊函式限定只能使用表中可用的列和ORACLE內建函式。

雜湊聚簇要點：

1、 雜湊聚簇透過雜湊碼查詢的時候需要的IO很少。幾乎一個IO就可以提取到所需的資料，除非發生了行溢位。而傳統索引至少需要2個IO才能得到資料。

2、 雜湊聚簇查詢CPU開銷大。雜湊聚簇是CPU密集型的，而索引是IO密集型的。

3、 對錶中資料量比較有把握，如行數，每行佔用空間，有合理的上限，正確設定好HASHKYES和SIZE引數，那麼雜湊聚簇將比較適用。

4、 雜湊聚簇降低DML效能。

5、 總是經常透過HASHKEY等值訪問資料。

SQL> create cluster hash_cluster

(hash_key number)

hashkeys 100 --用以標識有多少個聚簇碼值，不可更改，除非重新構建

size 8192 --每個聚簇碼對應的資料大概有多少位元組

/

Cluster created.

雜湊聚簇表的大小由 hashkeys關鍵字決定，ORACLE將用 hashkeys附近最接近的素數的值（大於該數的最小素數？）為空間分配的塊的數目。雜湊聚簇根據這些東西來預先分配空間，分配的大小為（HASHKES/trunk(blocksize/SIZE)）塊數。

SQL> exec show_space('hash_cluster',user,'cluster');

Total Blocks............................104

Unused Blocks...........................2

PL/SQL procedure successfully completed.

可以看到佔用空間一共為102個塊，一個塊是塊頭用來儲存段的相關資訊，這樣一共分配了101個塊，符合(100/trunc(8192/8192))最近的一個素數的要求。

七、 巢狀表： 巢狀表是ORACLE物件擴充套件的一部分，是ORACLE兩種集合型別中的一種，和關係模式中 傳統的父子表中的子表相似。可以看作父表中每一行都擁有一個自己的表。一般在PL/SQL程式中作為擴充套件PL/SQL語言（多數情況下使用），有就是作為永久儲存機制（很少用）。

八、臨時表：臨時表用來儲存事務或會話期間的中間結果。臨時表中的資料對其他會話是不可見的。 臨時表不用考慮並行性，多個會話並行操作不受影響。臨時表的儲存空間為使用使用者對應的臨時表空間，如果執行會過程等程式的話，那麼將使用建立者的臨時表空間。

臨時表的特點：

1、 不能用作參照完事性約束。不能建立外來鍵。

2、 不能有VARRAY或NESTED TABLE型別的表。

3、 不能是索引組織表，不能是聚簇表，不能分割槽。

4、 不能透過analyze分析產生統計資訊，使用CBO。一種情況是不使用臨時表，而是使用INLINEVIEW，會使用基表的統計資訊，從而讓CBO選擇最佳化的執行方式。還有一種情況就是了解臨時表的應用，知道資料分佈和資料量，DROP掉臨時表，建立一張同名的永久表，載入好資料，然後收集統計資訊並匯出。然後DROP掉永久表，建立回臨時表，匯入統計資訊，這樣就可以使用CBO。

5、 臨時表如果是單純因為查詢需要連線多表而用以消除多表連線的話，那麼這是沒有必要的。ORACLE多表查詢不會成為瓶頸。

[@more@]