Prepare Database Environment

The best way to get this done is to refer installation guide.

Install Oracle Enterprise Linux
Install Oracle Database
Some useful blog:
Oracle Validated RPM
Installing Oracle Database 11g on Linux
Installing Oracle Database 11g Release 1 on Enterprise Linux 5 (32- and 64-bit)
Create Oracle Database Tablespaces
CREATE BIGFILE TABLESPACE FSMS_DATA DATAFILE 'fsms_data.dat' size 10G AUTOEXTEND ON NEXT 1G MAXSIZE 100G;
CREATE BIGFILE TABLESPACE FSMS_IDEX DATAFILE 'fsms_idex.dat' size 10G AUTOEXTEND ON NEXT 1G MAXSIZE 100G;
Create database user and grant required privileges
Create user "fsms" and grant required privileges (ex. create table,create view) and access to tablespace FSMS_DATA,FSMS_IDEX

Use Reference Partitioning to Manage CDA Documents

我們需要對CDA文件進行CDA文件生命週期管理. 依據"最後異動日期", 移動CDA文件於不同的Table Partition. 我們將使用 Oracle Reference Partitioning.

首先, 我們需一個table來記載, 病人的最後到訪日期. 並以最後到訪日期, 做資料表格切割(Partitioning). 病人的ID為Primary Key.

因為, Virtual Column不能成為存在於Primary Key 與 Foreign Key中, 因此CDA_DOCS 表格增加一Patient_ID, 做為Reference Key。CDA欄位為Binary XML, 存放病人的CDA文件。


CREATE TABLE patient_visited_date
(Patient_Id varchar2(10),
Last_Visited_Date date,
CONSTRAINT PK_PATIENT_ID PRIMARY KEY (PATIENT_ID))
PARTITION BY RANGE (Last_Visited_Date)
(
PARTITION Last_visited_date_1990 values less than (TO_DATE('01/01/1991','DD/MM/YYYY')),
PARTITION Last_visited_date_2000 values less than (TO_DATE('01/01/2001','DD/MM/YYYY')),
PARTITION Last_visited_date_2005 values less than (TO_DATE('01/01/2006','DD/MM/YYYY')),
PARTITION Last_visited_date_2010 values less than (TO_DATE('01/01/2011','DD/MM/YYYY')),
PARTITION Last_visited_date_other values less than (MAXVALUE)
);








CREATE TABLE CDA_DOCS
(
  PATIENT_ID VARCHAR2(10) NOT NULL
, CDA XMLTYPE,
 CONSTRAINT FK_PATIENT_ID FOREIGN KEY (PATIENT_ID) REFERENCES PATIENT_VISITED_DATE(PATIENT_ID))
 XMLTYPE COLUMN CDA  store as BINARY XML
PARTITION BY REFERENCE (FK_PATIENT_ID);