實現B樹索引
這裡將介紹使用B樹索引時會遇到的典型任務。典型的任務包括。
.建立索引
.報告索引
.顯示重新建立索引需要的程式碼
.刪除索引

1 建立B樹索引
下面給出的是一個示例指令碼，它建立一個表，並在單獨的表空間建立與之相關的索引。表和索引從表空間繼承儲存屬性，這是因為在create table或create index語句中沒有指定儲存引數。此外，你希望主鍵和唯一鍵約束自動建立B樹索引。

SQL> create table cust1(
  2  cust_id number,
  3  last_name varchar2(30),
  4  first_name varchar2(30)
  5  )
  6  tablespace reporting_data;
Table created.
SQL> alter table cust1 add constraint cust_pk primary key(cust_id) using index tablespace reporting_index;
Table altered.
SQL> alter table cust1 add constraint cust_uk1 unique(last_name,first_name) using index tablespace reporting_index;
Table altered.
SQL> create table address(
  2  address_id number,
  3  cust_id number,
  4  street varchar2(30),
  5  city varchar2(30),
  6  state varchar2(30)
  7  )
  8  tablespace reporting_data;
Table created.
SQL> alter table address add constraint addr_fk1 foreign key(cust_id) references cust1(cust_id);
Table altered.
SQL> create index addr_fk1 on address(cust_id) tablespace reporting_index;
Index created.

此指令碼建立了兩個表。父表是cust1，它的主鍵是cust_id。子表是address，它的主鍵是address_id。在address表中，cust_id列作為外來鍵存在，它對映到cust1表的cust_id列。

此指令碼也建立了三個B樹索引。其中第一個是建立主鍵約束時自動建立的。第二個索引是建立唯一約束時自動建立的。第三個索引是明確建立在address表中的cust_id外來鍵列上的。所有這三個索引都是在reporting_index表空間中建立的，而表是在reporting_data表空間中建立的。

2 報告索引
上面的例子中建立的索引的詳細資訊可以透過查詢資料字典來驗證。

SQL> select index_name,index_type,table_name,tablespace_name,status from user_indexes where table_name in('CUST1','ADDRESS');
INDEX_NAME                                         INDEX_TYPE                  TABLE_NAME                                         TABLESPACE_NAME                                    STATUS
-------------------------------------------------- --------------------------- -------------------------------------------------- -------------------------------------------------- --------
ADDR_FK1                                           NORMAL                      ADDRESS                                            REPORTING_INDEX                                    VALID
CUST_PK                                            NORMAL                      CUST1                                              REPORTING_INDEX                                    VALID
CUST_UK1                                           NORMAL                      CUST1                                              REPORTING_INDEX                                    VALID

執行以下查詢來驗證建立了索引的列:

SQL> select index_name,column_name,column_position from user_ind_columns where table_name in('CUST1','ADDRESS') order by index_name,column_position;
INDEX_NAME                                         COLUMN_NAME                    COLUMN_POSITION
-------------------------------------------------- ------------------------------ ---------------
ADDR_FK1                                           CUST_ID                                      1
CUST_PK                                            CUST_ID                                      1
CUST_UK1                                           LAST_NAME                                    1
CUST_UK1                                           FIRST_NAME                                   2

要顯示區的數目和已使用的空間，可以執行以下查詢:

SQL> select a.segment_name,a.segment_type,a.extents,a.bytes from user_segments a,user_indexes b where a.segment_name=b.index_name and b.table_name in('CUST1','ADDRESS');
no rows selected

請注意，這個例子的輸出結果顯示，沒有為索引分配段，區或空間。

從Oracle 11g第2版開始，在建立表時，如果還沒有往表中插入資料，相關的段(和區)將會初步推遲建立。這意味著直到資料行被插入到相關的表之後，才會為相關的索引建立段。為了說明這一點，給CUST1表插入一行，也給ADDRESS表插入一行，如下所示:

SQL> insert into cust1 values(1,'STARK','JIM');
1 row created.
SQL> insert into address values(100,1,'Vacuum Ave','Portland','OR');
1 row created.
SQL> commit;
Commit complete.

重新執行這個查詢(段的使用報告)產生的輸出如下:

SQL> select a.segment_name,a.segment_type,a.extents,a.bytes from user_segments a,user_indexes b where a.segment_name=b.index_name and b.table_name in('CUST1','ADDRESS');
SEGMENT_NAME                                                                                                                     SEGMENT_TYPE          EXTENTS      BYTES
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ---------- ----------
ADDR_FK1                                                                                                                         INDEX                       1     131072
CUST_PK                                                                                                                          INDEX                       1     131072
CUST_UK1                                                                                                                         INDEX                       1     131072

3 顯示建立索引的程式碼
有時候可能需要刪除一些索引。這些索引可能是由過時的應用程式建立的，也可能是你自己以前建立的，但已經用不到了。在刪除索引之前，建議你首先生成重新建立索引所需的資料定義語言(DDL)。如果刪除索引對效能有不利影響而需要重新建立它，就可以重新建立索引(就像沒有刪除它一樣)。

可以使用dbms_metadata.get_ddl函式來顯示物件的DDL。確保為LONG變數設定適當的值，使用返回的CLOB值能全部顯示出來。例如:

SQL> set long 1000000
SQL> select dbms_metadata.get_ddl('INDEX','ADDR_FK1') from dual;

下面是輸出結果:

DBMS_METADATA.GET_DDL('INDEX','ADDR_FK1')
--------------------------------------------------------------------------------
  CREATE INDEX "JY"."ADDR_FK1" ON "JY"."ADDRESS" ("CUST_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "REPORTING_INDEX"

這段程式碼顯示了重新建立索引需要的所有內容。這些程式碼中的許多值反映了從索引表空間繼承的預設設定或儲存引數。

如果想要顯示當前連線的使用者的所有索引後設資料，可以執行下面的程式碼:

SQL> select dbms_metadata.get_ddl('INDEX',index_name) from user_indexes;
DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME)
--------------------------------------------------------------------------------
  CREATE INDEX "JY"."CUST_IDX1" ON "JY"."CUST" ("LAST_NAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  CREATE INDEX "JY"."CUST_IDX2" ON "JY"."CUST" ("FIRST_NAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  CREATE UNIQUE INDEX "JY"."CUST_PK" ON "JY"."CUST1" ("CUST_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  CREATE UNIQUE INDEX "JY"."CUST_UK1" ON "JY"."CUST1" ("LAST_NAME", "FIRST_NAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "REPORTING_INDEX"
  CREATE INDEX "JY"."ADDR_FK1" ON "JY"."ADDRESS" ("CUST_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "REPORTING_INDEX"

如果當前連線的使用者有很多索引，這個查詢將會產生大量的輸出。

4 刪除B樹索引
如果確定不再使用某個索引了，那麼應該刪除它。在刪除索引之前，應採取必要的預防措施，以確保不會對效能產生不利影響。如果可能的話，最好的辦法是在與生產環境同等條件(在硬體，資料，負載等方面)的測試環境中刪除索引，確定對效能的不利影響。如果不可能進行徹底的測試，那麼在刪除前考慮先做以下工作。
.啟用對索引的監測。
.使用索引不可見。
.使用索引不可用。

這樣做是為了在實際刪除之前，先確定該索引沒有用於任何目的。監控索引會讓你瞭解應用程式的select語句是否使用了它。但索引監控不會告訴你該索引是否被用於其他內部用途，如用來強制執行某個約束或防止鎖定問題。

使用一個索引不可見需要Oracle 11g及以上版本。不可見索引仍然由Oracle維護，但查詢最佳化器確定執行計劃時不考慮它。請注意，不可見的索引仍然可以由Oracle在內部使用，用來避免鎖定問題或強制執行約束。所以，使用索引不可見並不是用來確定該索引是否被使用的完全可靠的方法。

下面是使用索引不可見的一個例子:

SQL> alter index addr_fk1 invisible;
Index altered.

此程式碼使用索引對查詢最佳化器不可見，因此，它不能在查詢中用來檢索行。然而，當修改表中的記錄時，該索引結構仍然由Oracle維護。如果確定該索引對效能非常關鍵，那麼可以透過如下命令很容易地使用它再次對最佳化器可見。

SQL> alter index addr_fk1 visible;
Index altered.

刪除索引之前的另一種選擇是使其不可用。

SQL> alter index addr_fk1 unusable;
Index altered.

此程式碼使得索引不可用，但不會刪除它。不可用表示，不但最佳化器不會使用索引，而且當DML語句操作它的表時，Oracle也不會維護該索引。此外，不可用的索引不能在內部使用，用於強制執行約束或避免鎖定問題。

如果需要重新啟用不可用的索引，那麼就必須重建它。而重建一個大型的索引，會消耗大量的時間和資源。

SQL> alter index addr_fk1 rebuild;
Index altered.

當確信不需要某個索引後，就可以使用drop index語句來刪除它。這個語句將永久刪除該索引，找回該索引的唯一辦法是重新建立它。

SQL> drop index addr_fk1;
Index dropped.

Oracle如何實現B樹索引

相關文章