Oracle 官方文件中有提到建議將資料表儲存資料的表格空間 (tablespace) 與索引 (index) 的表格空間分開，並放在不同的硬碟達到最佳化 IO 的效果，本文章為紀錄在 Oracle 資料庫中將資料表的主索引鍵與索引放在不同表格空間的作法
官方文件連結
Specify the Tablespace for Each Index
文章將介紹下列三種建立主索引鍵 (primary-key) 的方法
- 不指定主索引鍵存放的表格空間(預設)
- 建立主索引鍵時存放到儲存索引使用的表格空間
- 將唯一索引建立在不同表格空間後再設定為主索引鍵條件限制
範例資訊與資料庫環境
範例 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
大致步驟如下：
- 建立兩個表格空間 STOCK_DATA & STOCK_INDEX
- 建立測試資料表 (SYSTEM.STOCK) 到 STOCK_DATA 的表格空間 (tablespace)
- 加入測試的資料內容到測試資料表 (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 的效能，當然資料庫的規模要夠大效果才會明顯
留言
張貼留言