教你如何成為Oracle 10g OCP - 第九章 物件管理(4)

9.4.1.2  索引組織表(IOT: Index Organized Table)

我們知道普通表都是以堆(heap)的方式來組織的，是無序的組織方式，
Oracle還提供了一種有序的表，叫做索引組織表，簡稱IOT表。 IOT表
上必須要有主鍵，IOT本身不對應segment, 表中所有的資料都存放在主
鍵所在索引的葉子節點中。也就是說，在索引葉子節點裡的索引條目裡，
不僅存放了被索引的列的值，還存放了其他列的值。

對於總的透過主鍵訪問資料的表來說，比較適合使用IOT表，因為如果
使用普通表，透過主鍵索引訪問表時，至少要讀取兩個資料塊，一個是
索引塊，一個是表的資料塊。但是透過IOT表，因為表的資料就存放在
索引塊中，只需要讀取一個塊即可。

IOT表是虛擬表，不過它具有普通表所有的特性，儘管IOT表的資料實際
存放在主鍵索引segment中，但是我們還是可以在IOT表的其他列上再次
建立索引，這種索引我們叫做二級索引。

建立IOT表：

CREATE TABLE DFMS.TEST08
(
  OWNER           VARCHAR2(30 BYTE),
  OBJECT_NAME     VARCHAR2(128 BYTE),
  SUBOBJECT_NAME  VARCHAR2(30 BYTE),
  OBJECT_ID       NUMBER,
  DATA_OBJECT_ID  NUMBER,
  OBJECT_TYPE     VARCHAR2(19 BYTE),
  CREATED         DATE,
  LAST_DDL_TIME   DATE,
  TIMESTAMP       VARCHAR2(19 BYTE),
  STATUS          VARCHAR2(7 BYTE),
  TEMPORARY       VARCHAR2(1 BYTE),
  GENERATED       VARCHAR2(1 BYTE),
  SECONDARY       VARCHAR2(1 BYTE),
  CONSTRAINT PK_IOT_TEST PRIMARY KEY (OBJECT_ID))
ORGANIZATION INDEX 
TABLESPACE LOG_IDX
PCTTHRESHOLD 10
INCLUDING  OBJECT_TYPE
OVERFLOW TABLESPACE LOG_DATA ;

假設經常需要返回的列是object_id前面的幾列，則如果將object_id
後面的幾列也放入索引塊中的話，會比較浪費空間，於是我們可以透過
設定pctthreshold及including屬性，從而只在索引塊中存放object_id
及前面的欄位，後面的存在所謂overflow中(overflow是一種segment).

pctthreshold:  object_id及前面的欄位總大小是200 Bytes, 索引塊
大小是8KB, 那麼 200/8192=2.4% ，可以設定為3 .

including object_type 表示從object_type開始後面所有的列都放入
overflow中(包括object_type)。

pctthreshold和including比較，pctthreshold優先順序比較高，如果實際
值已經超過了pctthreshold，那麼沒有指定放入overflow的列也會放入
overflow . 

 

9.4.1.3  簇表(Cluster Table)

對於簇表，總是要先建立簇段(cluster segment),然後將表關聯到cluster
segment, 它也是虛擬表，沒有對應的segment, 簇表對應的是cluster
segment。  型別有兩種： 索引簇表和雜湊簇表 。

索引簇表中，簇上必須有一個索引。它主要用於總是進行主明細表關聯查詢
的表，主表和明細表根據關聯欄位，將資料同時放入同一個簇資料塊中，即
一個資料塊存放了兩個表的記錄，當關聯查詢時只需要掃描一個塊即可，
示例如下：

a. 建立簇段 ：
  create cluster orders(ordno number) ;
b. 建立簇上的索引：
  create index idx_orders on cluster orders ; 
c. 建立表，並關聯到cluster .
   主表：
   create table order_master (ordno number, cstid number,createdate date)
   cluster orders (ordno); 
   明細表：
   create table order_item (ordno number, skuid number, qty number) 
   cluster orders (ordno); 

雜湊簇表中，則是將表的記錄插入簇段的時候，對簇所在的列運用雜湊函式，
從而得到記錄所在的資料塊的位置，不能那個關聯多個表，只能將一個表關聯
到雜湊簇段中。
create cluster phone_cst(phone_no number) hashkeys 1000 size 50 ;
備註： phone_no值最多不超過1000,每個hashkeys能使用的空間大小是50位元組 .
create table phones(phone_no number, name varchar2(10)) cluster phone_cst(phone_no);

 

9.4.1.4  臨時表(Temporary Table)

臨時表存放的資料都是暫時使用的，用完後需要刪除的，臨時表的資料是"session"
專用的，當前session只能看到它自己修改的資料，即使其他session提交了它們自己
的DML操作。臨時表就好像每個session單獨使用一樣。

因為臨時表的資料就像session專用一樣，因此也就不需要消耗DML鎖資源，建立好臨時
表以後，還可以在臨時表上建立索引，約束及觸發器等。

Oracle自動清除臨時表中資料的兩種方式：
a. 退出session時，即使用者終端資料庫連線時，自動清除與該session相關的資料，
透過 on commit preserve rows 設定 。
b. 使用者提交或回滾時，自動清除與該session相關的，位於臨時表中的資料。透過
短語 on commit delete rows 設定。

例子：

CREATE GLOBAL TEMPORARY TABLE DFMS.SYS_T_OTS_TRACE_DETAIL
(
  SYSSERIALNO             VARCHAR2(25 BYTE),
  NEXTEVENT               VARCHAR2(35 BYTE),
  SPAM_FLAG               VARCHAR2(1 BYTE),
  HOLD_STN                VARCHAR2(25 BYTE)
)
ON COMMIT PRESERVE ROWS
NOCACHE;

CREATE INDEX DFMS.IDX1_T_OTS_TRACE_DETAIL ON DFMS.SYS_T_OTS_TRACE_DETAIL
(SYSSERIALNO);

我們可以透過user_tables檢視檢視DURATION欄位，如果是SYS$SESSION說明是
session中斷時刪除資料，而SYS$TRANSACTION 則表示提交或回滾時刪除資料。

 

下一節 ：  索引