Partition table 實作筆記 (INV的 MTL_MATERIAL_TRANSACTIONS)

摘自：網路

在 Oracle ERP 裡頭，我們發現 Inventory 的 table 有幾個很大，
以我們的環境，MTL_MATERIAL_TRANSACTIONS 大約是 14GB

因此，有沒有可能透過 partition 來檢驗這樣可以對 performance 有幫助？
如果有幫助，那這樣可以不用花費多餘的成本就能對 Database performance 作有效的控制獲改善。


在作之前，
先跟 Application Team 同事索取一段測試的 SQL statement
作為 performance 檢驗，
為了避免誤差，
我將這段 SQL 寫成 script ，放在 crontab 連續排程多次測試共 80多次，取得平均值後

整理如下：

1. explain plan 的 cost 有些微改變，但 index 的使用對象(MTL_MATERIAL_TRANSACTIONS_N8)仍不變，
這也顯示不影響原本的執行計畫

2.在 index 跟 table partition size 與分割前有滿大的空間不同
以 index MTL_MATERIAL_TRANSACTIONS_N8為例，
分割前空間為 1593MB，
經過分割跟 compress 後，各 partition 空間總和為原先的一半
1 select owner,segment_name,segment_type,extents ,bytes/1024/1024 "MB"from dba_segments
2* where segment_name like 'MTL_MATERIAL_TRANSACTIONS_N8'
SQL> /
e
OWNER SEGMENT_NAME SEGMENT_TYPE EXTENTS MB
------ -------------------------------------- ------------------ ---------- ----------
INV MTL_MATERIAL_TRANSACTIONS_N8 INDEX 1593 1593
GOBO MTL_MATERIAL_TRANSACTIONS_N8 INDEX PARTITION 33 330
GOBO MTL_MATERIAL_TRANSACTIONS_N8 INDEX PARTITION 18 180
GOBO MTL_MATERIAL_TRANSACTIONS_N8 INDEX PARTITION 21 210
GOBO MTL_MATERIAL_TRANSACTIONS_N8 INDEX PARTITION 8 80
GOBO MTL_MATERIAL_TRANSACTIONS_N8 INDEX PARTITION 1 10



3.經過三天定時排程後，將執行時間統計如下表，經過 partition 後，
執行時間約可增進 15~20%的執行效能

select partition "partition", queryrange1, queryrange2,round(avg(Run)/60,2) "Avg Mins" , count(1) "Testing Counts"
from
(select partition, queryrange1, queryrange2,60*to_number(substr(runtime,4,2)) + to_number(substr(runtime,7,2))+ to_number(substr(runtime,9,3)) Run
from inv_parti_perf_chk ) group by partition, queryrange1, queryrange2

partition QUERYRANGE1 QUERYRANGE2 Avg Mins Testing Counts
---------- -------------- -------------- ---------- --------------
n 200701 200706 8.65 5
n 200704 200705 1.99 1
n 200801 200804 4.74 9
y 200701 200706 8.74 4
y 200801 200804 4.13 7




## 建立 partition 的語法
我是把 partition 建立在另外一個 schema
如果真的要在正式環境的話，
那有個方式可以透過 rename table 的方式，
再重新建立 partition table
CREATE TABLE MY.MTL_MATERIAL_TRANSACTIONS
PARTITION BY RANGE (TRANSACTION_DATE)
(
PARTITION TRANSACTION_2005
VALUES LESS THAN ( TO_DATE('01-jan-2006','dd-mon-yyyy')) TABLESPACE GU_PARTITION_TS,
PARTITION TRANSACTION_2006
VALUES LESS THAN ( TO_DATE('01-jan-2007','dd-mon-yyyy')) TABLESPACE GU_PARTITION_TS,
PARTITION TRANSACTION_2007
VALUES LESS THAN ( TO_DATE('01-jan-2008','dd-mon-yyyy')) TABLESPACE GU_PARTITION_TS,
PARTITION TRANSACTION_2008
VALUES LESS THAN ( TO_DATE('01-jan-2009','dd-mon-yyyy')) TABLESPACE GU_PARTITION_TS,
PARTITION TRANSACTION_new
VALUES LESS THAN (MAXVALUE) TABLESPACE GU_PARTITION_TS )
as select * from inv.MTL_MATERIAL_TRANSACTIONS where 1=2;



## 建立 local index 的一例
在 global index 與 local index的選擇上，
因為 global index 還得另外 create index partition
那 local index 剛好有 key 值可以套用在 table partition
所以，我試著用 local index 並 加上 compress 參數

CREATE INDEX GOBO.MTL_MATERIAL_TRANSACTIONS_N8 ON GOBO.MTL_MATERIAL_TRANSACTIONS
(TRANSACTION_SOURCE_TYPE_ID, ORGANIZATION_ID, TRANSACTION_DATE, TRANSACTION_SOURCE_NAME)
NOLOGGING
NOLOGGING local compress;


## Gather Statistics
建立好了 partition table /index 記得跑一次 gather statistics
exec fnd_stats.gather_table_stats('MY','MTL_MATERIAL_TRANSACTIONS',granularity=>'ALL');