11gの仮想列と仮想列ベースのパーティション

1. 仮想列の概念

仮想列は値が一連の式またはファンクションを計算することによって必要に応じて導出される列で、ディスクには格納されない。

1.1 制限事項
  • 仮想列は、リレーショナル・ヒープ表にのみ作成出来る。索引構成表、外部表、オブジェクト表、クラスタ化表または一時表ではサポートされない。
  • 仮想列は別の仮想列を名前で参照出来ない。
  • 仮想列が参照する列は同じ表で定義されている必要がある。
  • 決定的なユーザー定義ファンクションを参照できるが、仮想列をパーティション化キー列として使用できない。
  • 仮想列は、Oracleが提供するデータ型、ユーザー定義型、またはLOBまたはLONG RAWにすることはできない。

2. 仮想列を含める表の作成

PROD(JASON)> CREATE TABLE EMPLOYEE (
        EMP_ID NUMBER PRIMARY KEY,
        SALARY NUMBER (8,2) NOT NULL,
        YEARS_OF_SERVICE NUMBER NOT NULL,
        CURR_RETIREMENT AS (SALARY*.0005 * YEARS_OF_SERVICE)
);

表が作成されました。

PROD(JASON)>

表にDML操作してみる
PROD(JASON)> INSERT INTO EMPLOYEE VALUES (1,100000,5,DEFAULT);

1行が作成されました。

PROD(JASON)> commit;

コミットが完了しました。

PROD(JASON)> select * from EMPLOYEE;

    EMP_ID     SALARY YEARS_OF_SERVICE CURR_RETIREMENT
---------- ---------- ---------------- ---------------
         1     100000                5             250

PROD(JASON)>

仮想列定義情報の照合
PROD(JASON)> SELECT TABLE_NAME, COLUMN_NAME, DATA_DEFAULT FROM DBA_TAB_COLUMNS
        WHERE TABLE_NAME='EMPLOYEE' AND COLUMN_NAME='CURR_RETIREMENT';

TABLE_NAME                               COLUMN_NAME                    DATA_DEFAULT
---------------------------------------- ------------------------------ ----------------------------------------
EMPLOYEE                                 CURR_RETIREMENT                "SALARY"*.0005*"YEARS_OF_SERVICE"

PROD(JASON)>

CASE式の使用してもう少し複雑な仮想列を定義してみる
PROD(JASON)> CREATE TABLE EMPLOYEE (
        EMP_ID NUMBER PRIMARY KEY,
        SALARY NUMBER (8,2) NOT NULL,
        YEARS_OF_SERVICE NUMBER NOT NULL,
        CURR_RETIREMENT AS (SALARY*.0005 * YEARS_OF_SERVICE),
        RETIREMENT_IMPACT VARCHAR2(30) AS (CASE
                WHEN (SALARY*.0005 * YEARS_OF_SERVICE) < 100 THEN 'MINIMAL'
                WHEN (SALARY*.0005 * YEARS_OF_SERVICE) < 500 THEN 'NORMAL'
                ELSE 'WARNING WILL ROBINSON! DANGER!' END)
);

表が作成されました。

PROD(JASON)> INSERT INTO EMPLOYEE VALUES (1,10000,1,DEFAULT,DEFAULT);

1行が作成されました。

PROD(JASON)> INSERT INTO EMPLOYEE VALUES (2,100000,5,DEFAULT,DEFAULT);

1行が作成されました。

PROD(JASON)> INSERT INTO EMPLOYEE VALUES (3,100000,10,DEFAULT,DEFAULT);

1行が作成されました。

PROD(JASON)> select * from EMPLOYEE;

    EMP_ID     SALARY YEARS_OF_SERVICE CURR_RETIREMENT RETIREMENT_IMPACT
---------- ---------- ---------------- --------------- --------------------------------
         1      10000                1               5 MINIMAL
         2     100000                5             250 NORMAL
         3     100000               10             500 WARNING WILL ROBINSON! DANGER!

PROD(JASON)>

3. 仮想列ベースのパーティション表

PROD(JASON)> drop table employee purge;

表が削除されました。

PROD(JASON)> CREATE TABLE EMPLOYEE (
        EMP_ID NUMBER PRIMARY KEY,
        SALARY NUMBER (8,2) NOT NULL,
        BONUS AS (SALARY*0.3) )
PARTITION BY RANGE (BONUS) (
        PARTITION P1 VALUES LESS THAN (10000),
        PARTITION P2 VALUES LESS THAN (30000),
        PARTITION P3 VALUES LESS THAN (MAXVALUE));

表が作成されました。

PROD(JASON)>
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License