透過指令取得評估版 Windows 作業系統的剩餘時間

將 Oracle 資料表 (table) 的主鍵 (primary key) 建立在不同的表格空間 (tablespace)

Oracle 官方文件中有提到建議將資料表儲存資料的表格空間 (tablespace) 與索引 (index) 的表格空間分開,並放在不同的硬碟達到最佳化 IO 的效果,本文章為紀錄在 Oracle 資料庫中將資料表的主索引鍵與索引放在不同表格空間的作法

官方文件連結
Specify the Tablespace for Each Index

文章將介紹下列三種建立主索引鍵 (primary-key) 的方法

  1. 不指定主索引鍵存放的表格空間(預設)
  2. 建立主索引鍵時存放到儲存索引使用的表格空間
  3. 將唯一索引建立在不同表格空間後再設定為主索引鍵條件限制
範例資訊與資料庫環境

範例 PL/SQL 指令碼可以在下列 Github 連結取得
https://github.com/txstudio/AddPrimaryKeyUsingOtherTableSpaceInOracle

使用之 Oracle Database 為 Docker Hub 中 wnameless/oracle-xe-11g 映像建立的 Oracle Database Express Edition 11g Release 2 資料庫
https://hub.docker.com/r/wnameless/oracle-xe-11g/

建立範例資料內容

範例資料的來源為台灣證券交易所:本國上市證券國際證券辨識號碼一覽表

先取得建立範例資料庫的 PL/SQL 指令碼 init-db.sql

大致步驟如下:

  1. 建立兩個表格空間 STOCK_DATA & STOCK_INDEX
  2. 建立測試資料表 (SYSTEM.STOCK) 到 STOCK_DATA 的表格空間 (tablespace)
  3. 加入測試的資料內容到測試資料表 (SYSTEM.STOCK)
此範例資料表物件並沒有包含主索引鍵 (primary-key) 與索引 (index) 內容

使用下列指定取得表格空間的資料

SELECT * FROM dba_data_files;

建立完成後的表格空間如下

範例資料表 SYSTEM.STOCKS 的資料內容

SYSTEM.STOCKS 資料存放再 STOCK_DATA 表格空間中

STOCK_DATA 與 STOCK_INDEX 表格空間使用如下

架構說明

接下來要設定資料表的主索引鍵 (primary-key) 條件限制與索引 (index),並將資料 (data) 內容與索引 (index) 內容放置到不同的表格空間 (tablespace),預期結果如下圖

主索引鍵欄位為 NO,唯一索引欄位為 SCHEMA

不指定主索引鍵存放的表格空間(預設)

執行 add-primary-key-to-tablespace.sql 指令碼 case 1

此方法會建立主索引鍵與唯一索引,但不指定儲存的表格空間,預設會建立再表格 SCHEMA 的表格空間裡

可以看到 SYSTEM 這個表格空間 (tablespace) 的可使用空間變少了

建立主索引鍵時存放到儲存索引使用的表格空間

執行 add-primary-key-to-tablespace.sql 指令碼 case 2

此方法會建立主索引鍵與索引,並指定儲存的表格空間為 STOCK_INDEX

從表格空間使用量可以看到用 STOCK_INDEX 使用空間變少了

會發現 Oracle 會額外建立一個唯一索引 (unique-index)

主索引鍵 (primary-key) 名稱與該索引 (index) 相同,並儲存到 STOCK_INDEX 的表格空間

將唯一索引建立在不同表格空間後再設定為主索引鍵條件限制

執行 add-primary-key-to-tablespace.sql 指令碼 case 3

此方法會先建立 NO 欄位的唯一索引 (unique-index)

並修改資料表 (system.stock) 加入主索引鍵 (primary-key) 限制條件並指定索引為 NO 使用的唯一索引

表格空間的使用量與前一個步驟是相同的

與前一個方式的差別就在於可以自訂唯一索引的名稱

關聯兩個物件的名稱並不相同

可是 SQL Developer 的 SQL 頁簽沒有顯示唯一索引 (unique-index) 與主索引鍵 (primary-key) 關聯的 PL/SQL 指令碼 ...

總結

透過上述方式可以將主索引鍵 (primary-key) 使用之索引 (unique-index) 儲存到與資料表不同的表格空間 (tablespace),可提升資料庫的在檔案 IO 的效能,當然資料庫的規模要夠大效果才會明顯

參考資料

留言