Oracle如何建立B樹索引

建立B樹索引之前，為了慎重起起見，有必要從架構層面考慮一些將影響可維護性和可用性的問題。以下是建立索引之前，應該考慮的架構性問題。
.在建立索引之前，首先對它的大小進行估計。
.考慮指定表索引的表空間(與表分離)。這使得分開管理表和索引變得更輕鬆，如備份和恢復任務。
.允許物件從它閃的表空間繼承儲存引數。
.定義建立索引時要使用的命令標準。

1 在建立索引前估計索引的大小
一張大表上建立索引之前，可能需要估計它將會佔用的空間大小。預測索引大小最好的方法是在測試環境中建立它，測試環境中有生產環境的典型資料集。如果不能建立生產資料的完整副本，那麼經常可以用資料的一個子集來推斷在生產中所需索引空間的大小。如果你沒有使用削減的生產資料的奢侈條件，還可以使用dbms_space.create_index_cost儲存過程來估算索引的大小。例如，如下程式碼估算了在cust表的first_name列上建立索引的大小:

SQL> set serverout on
SQL> exec dbms_stats.gather_table_stats(user,'CUST');
PL/SQL procedure successfully completed.
SQL> variable used_bytes number
SQL> variable alloc_bytes number
SQL> exec dbms_space.create_index_cost('create index cust_idx2 on cust(first_name)',:used_bytes,:alloc_bytes);
PL/SQL procedure successfully completed.

下面是這個例子的一些示例輸出:

SQL> print :used_bytes
USED_BYTES
----------
      7490
SQL> print :alloc_bytes
ALLOC_BYTES
-----------
      65536

used_bytes變數給出了索引資料需要多少空間的估計。alloc_bytes變數提供了將在表空間內分配多大空間的估計。

下一步，建立索引。

SQL> create index cust_idx2 on cust(first_name);
Index created.

用如下查詢顯示所佔用的空間的實際數額:

SQL> select bytes from user_segments where segment_name='CUST_IDX2';
     BYTES
----------
     65536

輸出顯示空間分配位元組數的估計量等於實際使用量。

根據記錄數，列數，資料型別和統計資料的準確性，輸出的結果可能會有所不同。除初始大小之外，還要牢記隨著記錄插入到表中，該索引將增大。必須對索引佔用的空間進行監控，並確保有足夠的磁碟空間，以適應未來的增長需求。

2 為索引建立單獨的表空間
對於關鍵的應用程式，必須提前考慮表和索引會消耗多少空間，以及它們增長的速度有多快。空間消耗和物件的增長對資料庫可用性有直接影響。如果空間用盡了，那麼資料庫將變得不可用。最好的管理辦法是，針對空間要求建立表空間，並在建立物件時明確指定表空間名。考慮到這一點，我們建議將表和索引分別儲存到單獨的表空間。考慮以下原因。
.支援採用不同的備份和恢復要求。你可能希望靈活地用與備份表不同的頻率來備份索引。或者可以選擇不備份索引，因為你知道可以重新建立它們。
.如果讓表或索引從表空間繼承它的儲存特性，使用單獨的表空間可以為表空間內建立的物件量身定製儲存屬性。表和索引往往有不同的儲存要求(如區的大小，記錄等)。
.執行維護報告時，如果報告針對不同的表空間具有不同的節(section)，有時管理表和索引會更容易。

如果這些原因出現在你的環境中，那麼可能值得付出額外的努力，對錶和索引採用不同的表空間。如果你沒有前面提到的任何需要，那麼把表和索引儲存在相同的表空間是不錯的選擇。

DBA經常出於效能的原因，考慮把索引放置在單獨的表空間。如果你有從頭開始建立儲存系統的奢侈條件，可以把掛載點(mount point)設定為有自己的磁碟和控制器，那麼可能會看到把表和索引儲存在不同表空間的一些IO上的好處。如今，儲存管理員往往會分配給你的SAN中的一大片儲存，並且無法保證資料和索引將儲存在單獨的磁碟和控制器上。因此，把表和索引儲存在不同表空間的做法，通常對提高效能沒什麼幫助。換句話說，效能獲得提高不是透過將表和索引儲存到不同的表空間實現的，而是由於在所有可用的裝置上均勻地分佈IO實現的。

下面的程式碼顯示的是為表和索引單獨建立表空間的例子。它使用固定大小的區和自動段空間管理(ASSM)建立了本地管理的表空間。

SQL> create tablespace reporting_data datafile '+DATA/JYCS/reporting_data01.dbf' size 1G extent management local uniform size 1M
  2  segment space management auto;
Tablespace created.
SQL> create tablespace reporting_index datafile '+DATA/JYCS/reporting_index01.dbf' size 500M extent management local uniform size 128K
  2  segment space management auto nologging;
Tablespace created.

我們更傾向於使用統一大小的區，因為這確保了表空間記憶體的所有區大小相同，從而減少了物件建立和刪除時的碎片。ASSM的功能允許Oracle自動管理儲存屬性，而以前這需要手動監測和由DBA維護。

3 從表空間繼承儲存引數
建立表或索引時，有幾個與表空間相關的技術細節需要注意。例如，如果建立表和索引時不指定儲存引數，則表和索引會繼承表空間的儲存引數。這是在大多數情況下所需的行為。這樣就可以不必手動指定這些引數。如果需要建立一個具有與表空間不同的儲存引數的物件，那麼用create table/index語句來實現。

此外，請記住，如果不明確指定表儂間，預設情況下，表和索引建立在使用者的預設表空間中。在開發和測試環境中，這是可以接受的。對於生產環境，則應該考慮在create table/index語句中明確命令表空間。

4 命令標準
在建立和管理索引時，制定一些命名標準是非常可取的。考慮以下因素.
.當錯誤訊息中包含表示表，索引型別等的資訊時，簡化了對問題的診斷。
.顯示索引資訊的報告更容易被分組，因此更具可讀性並更容易地發現其中的規律和問題。鑑於這些需求，這裡有一些示例索引命名指南。
.主鍵索引名稱應該包含表名和一個字尾，如_UKN，其中N是一個數字。
.外來鍵列上的索引應包含外來鍵表和一個字尾，如_FKN，其中N是一個數字。
.對於不用於約束的索引，使用表名和一個字尾，如_IDXN，其中N是一個數字。
.基於函式的索引的名稱應包含表名和一個字尾，如_FCN，其中N是一個數字。

一些廠商在命名索引時使用字首。例如，主鍵索引將被命名為PK_CUST(而不是CUST_PK)。所有這些不同的命名標準都是有效的。