Oracle學習總結--基礎部分（儲存與索引）

Oracle學習
block、extent、segment的概念
一般的block，現在都是8K的，但也有的系統有16K的，少的4K的我也碰到過。
在4K的環境下執行一些大檔案的匯入及大表的操作是非常令人痛苦的。
很多情況下，大資料量的dmp檔案匯入，最終都會以失敗而告終。
block是oracle的基本儲存單位，是最小的儲存單元。
一般一個block可以儲存若干條記錄（具體多少隨欄位的多少及每個欄位的長度而不同）。
所以在建立表空間、表或索引等物件的時候會有這麼幾個引數與block相關的：
pctfree N: 指的是在指定的這個段(segment)的區(extent)中，控制每個block中保留用於update的空間，
    當block中的free space小於pctfree時，將該block從freelist中去除。
pctused N: 這個引數在索引中是沒有的，指的是對object做過DML操作後，free space的值大於pctused值
    時，將該block加回到free list中。

舉例說明：
Oracle 資料庫的讀取單位是資料塊（Block），而一個Block 是否允許被寫入資料
是基於一定的空閒度，這就是大家知道的pctfree 和pctused 儲存引數設定。
假設pctfree=10, pctused=40 ， 這就表明當一個Block 的空間使用率達到了90%
（10-pctfree）時，這個block 就不再允許被用於新增資料（insert），而保留下來的這10%
的空間則被預留為行更新（update）所可能需要的空間擴充套件，我們說此時這個block 就從
freelist 上被摘走了（實際上還有另外一種情況，就是當塊剩餘空間不足以插入一條記錄並
且該塊的使用率已經超過了pctused 定義的值並且該塊位於freelist header 處時，該塊也會
從freelist 上被摘走，術語稱為UNLINK）。當有資料刪除（delete）的時候，只有該block
中的資料被刪除到一定的程度，該塊才會重新被加入到freelists 中，而這個程度就是pctused
引數定義的數值，如我們這個例子中，只有塊中的資料降低到40%以下的時候，該塊才被
重新允許用於新增資料。

free list是指Oracle中的一個可使用的block的列表，當某個block達到指定條件不再可以插入或更新資料時
將從這個列表中去除。當某個block由於DML操作而可以插入或更新資料時，又會被新增到該列表中。

比如說要建立一個表：
create table abc(id integer)
tablespace dvbcetus --指定表空間為dvbcetus
pctfree 5  --預設值為5
pctused 40  --預設值為40
mintrans 1  --最小可併發查詢數, default 1
maxtrans 255  --最大可併發查詢數，default 255
storage(  --儲存引數
  initial 10m  --初始獲取的
  next 10m  --下一次擴充套件的大小
  minextent 1 --可獲得的最小的區的數量
  maxextent unlimited  --可獲得的最大的區的資料，unlimited是指無限制
  pctincrease 0  --指定每次next增長的係數，預設為0，建議為0，演算法： next = next(1+n)
)

如何查詢：
使用dbms_rowid來實現block的查詢, 輸出如下：
SQL> select dbms_rowid.rowid_object(sub.rowid) "data_object_id",
  2         dbms_rowid.rowid_relative_fno(sub.rowid) "rfile#",
  3         dbms_rowid.rowid_block_number(sub.rowid) "block_number",
  4         dbms_rowid.rowid_row_number(sub.rowid) "row_number"
  5  from ow_subscriber sub
  6  where rownum <= 10
  7  ;
 
data_object_id     rfile# block_number row_number
-------------- ---------- ------------ ----------
          7468          7        13317         78
          7468          7        13328         69
          7468         12        10610          3
          7468         12        10610          0
          7468         12        10610          1
          7468         12        10610          2
          7468         12        10610          4
          7468         12        10610          5
          7468         12        10610          6
          7468         12        10610          7
 
10 rows selected
其中對於rowid的簡單說明，rowid是一個由18個64進位制的字元組成的字串（8i以後），共10個位元組（32bit + 10bit rfile# + 22bit + 10bit），表示形式為：
OOOOOO FFF BBBBBB RRR
O: 指data_object_id，一個object可能跨越多個資料檔案, 與下面的rfile#合起來就能對應一個block的所在資料檔案，32bit；
F: 指相對file_id, 在非cluster(RAC)模式下，rfile#與file_id是相同的，10bit,即檔案可以顯示的最大的個數為2^10-1=1023個；
B: 指block id, 即某個資料塊，22bit，即一個檔案下最大可容納的block為2^22-1個，如果一個block為8k，則檔案的大小最大隻能為：(2^22-1)*8/1024/1024=32G；
R: 指的是每個行的標號，即row number， 16bit，如果一行的長度為200byte，則一個block可容納的row為：8k*1024/20=40個row。

注意：
rowid的位元組長度跟字串的長度不是同一個概念
rowid的位元組長度是指這個rowid字串可容納的位元組數，而字串長度則是指所有字元加起來的總長度，在這裡即為18；

如何dump某個資料塊的內容：
alter system dump datafile 39 block 4106;
alter system dump datafile [file_id] min block [block_id] max block [block_id];

 

索引的概念
一、二叉樹索引
通常的索引都是指B*樹索引，即常說的二叉樹索引
二叉樹索引是將不同的資料排序後，以樹形結構的方式儲存在資料庫中，查詢的時候可根據一層層節點最終找到葉子節點。
二叉樹索引是使用的最廣泛的索引，是Oracle中預設的。對於高基數（high-即重複的資料很少）且非空的或為空的資料很少的列來說比較適合；
建立方式如下：
create index [idx_name] on [table_name]([column_name]) tablespace [index_tablespace] [nologging/logging] [local]
說明：
1、透過指定nologging引數，有如下好處：減少重做日誌空間大小；比預設建索引方式快；；
2、local引數是在分割槽表的時候使用的，指定該引數用來實現本地分割槽索引；
3、tablespace [index_tablespace]引數可以不指定，預設情況下會建在使用者的預設表空間下。

二、點陣圖索引
點陣圖索引適合於基數較小的列，也就是重複資料很多。假如有學位這個欄位，有學士、碩士、博士、壯士這四個內容組成，這樣的欄位就適於點陣圖索引。
但是點陣圖索引對DML操作的代價比價大，需要花很多的時間來維護索引，因此點陣圖索引通常適合於查詢系統，
而不是業務執行系統那種資料DML操作很多的情況。