【Oracle】仮想列（Virtual Column）完全ガイド｜GENERATED ALWAYS AS・インデックス・パーティションキー・制限事項まで実例で解説

-- 基本構文
CREATE TABLE 表名 (
    列名  データ型  GENERATED ALWAYS AS (式) VIRTUAL,
    -- VIRTUAL は省略可（省略しても仮想列として扱われる）
);

-- 実例: 税込金額・フルネームを仮想列で定義する
CREATE TABLE products (
    product_id     NUMBER          PRIMARY KEY,
    product_name   VARCHAR2(200)   NOT NULL,
    price          NUMBER(10, 2)   NOT NULL,
    tax_rate       NUMBER(5, 4)    DEFAULT 0.10 NOT NULL,

    -- 仮想列: 税込金額（格納しない・SELECT 時に計算される）
    price_with_tax NUMBER(12, 2)
        GENERATED ALWAYS AS (ROUND(price * (1 + tax_rate), 2)) VIRTUAL,

    -- 仮想列: 税額（小数切り捨て）
    tax_amount     NUMBER(10, 2)
        GENERATED ALWAYS AS (ROUND(price * tax_rate, 2)) VIRTUAL
);

-- データ投入: 仮想列（price_with_tax, tax_amount）は指定しない
INSERT INTO products (product_id, product_name, price, tax_rate)
VALUES (1, 'ノートPC', 100000, 0.10);

INSERT INTO products (product_id, product_name, price, tax_rate)
VALUES (2, 'マウス', 3000, 0.10);

COMMIT;

-- SELECT: 仮想列が自動計算される
SELECT product_name, price, tax_amount, price_with_tax
FROM products;
-- ノートPC | 100000 | 10000 | 110000
-- マウス   |   3000 |   300 |   3300

CREATE TABLE employees_v (
    employee_id    NUMBER          PRIMARY KEY,
    first_name     VARCHAR2(50)    NOT NULL,
    last_name      VARCHAR2(50)    NOT NULL,
    hire_date      DATE            NOT NULL,
    salary         NUMBER(10, 2),

    -- フルネーム（姓名の結合）
    full_name      VARCHAR2(101)
        GENERATED ALWAYS AS (last_name || ' ' || first_name) VIRTUAL,

    -- 入社年（数値型）
    hire_year      NUMBER(4)
        GENERATED ALWAYS AS (EXTRACT(YEAR FROM hire_date)) VIRTUAL,

    -- 入社月
    hire_month     NUMBER(2)
        GENERATED ALWAYS AS (EXTRACT(MONTH FROM hire_date)) VIRTUAL
);

-- 使い方例
SELECT full_name, hire_year, hire_month, salary
FROM employees_v
WHERE hire_year >= 2020
ORDER BY full_name;
-- → WHERE や ORDER BY でも仮想列を通常の列と同じように使える

-- 既存テーブルに仮想列を追加する
ALTER TABLE products
ADD (
    discount_price NUMBER(12, 2)
        GENERATED ALWAYS AS (ROUND(price * 0.9, 2)) VIRTUAL   -- 10%割引価格
);

-- 複数の仮想列を一度に追加する
ALTER TABLE employees_v
ADD (
    annual_salary  NUMBER(12, 2)
        GENERATED ALWAYS AS (salary * 12) VIRTUAL,
    monthly_bonus  NUMBER(10, 2)
        GENERATED ALWAYS AS (ROUND(salary * 0.1, 2)) VIRTUAL
);

-- 注意: 仮想列に DEFAULT は指定できない
-- ALTER TABLE products ADD (virt_col NUMBER DEFAULT 0 GENERATED ALWAYS AS (price * 2));
-- → ORA-54012: default value cannot be specified for a virtual column

-- NG: 仮想列に値を INSERT しようとするとエラー
INSERT INTO products (product_id, product_name, price, tax_rate, price_with_tax)
VALUES (3, 'キーボード', 5000, 0.10, 5500);
-- ORA-54013: INSERT operation disallowed on virtual columns

-- NG: 仮想列を UPDATE しようとするとエラー
UPDATE products SET price_with_tax = 6000 WHERE product_id = 3;
-- ORA-54013: INSERT operation disallowed on virtual columns

-- OK: 仮想列を SELECT リストや WHERE で使うのは問題ない
SELECT * FROM products WHERE price_with_tax > 5000;

-- NG: NULL の代入も不可（DEFAULT NULL での定義も不可）
-- INSERT INTO products (..., price_with_tax) VALUES (..., NULL);
-- → ORA-54013

-- 列リストを省略した INSERT も仮想列を含めることになるため注意
-- INSERT INTO products VALUES (4, 'テスト', 1000, 0.10, NULL, NULL, NULL);
-- → ORA-54013（列数が合えばエラーになる）
-- → 列名を明示的に指定して仮想列を除外するのが安全

-- ===== 方法1: 関数ベースインデックス（FBI）=====
-- テーブル定義と独立しており、式を WHERE 句に必ず同じ形で書く必要がある
CREATE INDEX idx_price_with_tax_fbi ON products (ROUND(price * (1 + tax_rate), 2));

-- FBI を使うには WHERE 句の式が完全に一致しなければならない
SELECT * FROM products WHERE ROUND(price * (1 + tax_rate), 2) > 5000;  -- インデックス使用
SELECT * FROM products WHERE price * 1.10 > 5000;  -- → 式が違うためインデックス不使用

-- ===== 方法2: 仮想列にインデックス =====
-- 仮想列 price_with_tax は既に ROUND(price * (1 + tax_rate), 2) として定義済み
CREATE INDEX idx_price_with_tax ON products (price_with_tax);

-- 仮想列名で直接フィルタできる（式を書く必要がない）
SELECT * FROM products WHERE price_with_tax > 5000;  -- インデックス使用
-- → オプティマイザは price_with_tax = ROUND(price*(1+tax_rate),2) を理解してインデックスを選択

-- インデックスの確認
SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name = 'PRODUCTS'
ORDER BY index_name, column_position;

-- 注文日から年月（YYYYMM 形式の数値）を仮想列として定義し、パーティションキーに使う
CREATE TABLE orders_partitioned (
    order_id     NUMBER         PRIMARY KEY,
    order_date   DATE           NOT NULL,
    customer_id  NUMBER,
    amount       NUMBER(12, 2),

    -- 仮想列: YYYYMM 形式の数値（パーティションキーに使う）
    order_yyyymm NUMBER(6)
        GENERATED ALWAYS AS (
            TO_NUMBER(TO_CHAR(order_date, 'YYYYMM'))
        ) VIRTUAL
)
-- 仮想列を RANGE パーティションのキーに指定
PARTITION BY RANGE (order_yyyymm) (
    PARTITION p_202401 VALUES LESS THAN (202402),
    PARTITION p_202402 VALUES LESS THAN (202403),
    PARTITION p_202403 VALUES LESS THAN (202404),
    PARTITION p_maxval VALUES LESS THAN (MAXVALUE)
);

-- INSERT: order_date のみ指定（order_yyyymm は自動計算）
INSERT INTO orders_partitioned (order_id, order_date, customer_id, amount)
VALUES (1001, DATE '2024-01-15', 501, 15000);

INSERT INTO orders_partitioned (order_id, order_date, customer_id, amount)
VALUES (1002, DATE '2024-02-20', 502, 8000);

COMMIT;

-- パーティションプルーニングが効く（2024年1月のデータのみスキャン）
SELECT * FROM orders_partitioned
WHERE order_date BETWEEN DATE '2024-01-01' AND DATE '2024-01-31';
-- → p_202401 パーティションのみアクセスされる（EXPLAIN PLAN で確認可能）

-- USER_TAB_COLS: VIRTUAL_COLUMN 列で仮想列か否かを識別できる
SELECT
    column_name,
    data_type,
    data_length,
    nullable,
    virtual_column,   -- YES = 仮想列、NO = 通常列
    data_default      -- 仮想列の場合は定義式が格納される
FROM user_tab_cols
WHERE table_name = 'PRODUCTS'
ORDER BY column_id;

-- 仮想列だけを抽出
SELECT column_name, data_default AS virtual_expression
FROM user_tab_cols
WHERE table_name = 'PRODUCTS'
  AND virtual_column = 'YES';

-- 全テーブルの仮想列一覧（スキーマ全体）
SELECT table_name, column_name, data_default
FROM user_tab_cols
WHERE virtual_column = 'YES'
ORDER BY table_name, column_id;

-- 仮想列の削除（通常の列削除と同じ構文）
ALTER TABLE products DROP COLUMN discount_price;

-- 仮想列の式を変更する
-- ※ 仮想列の式は直接 ALTER COLUMN で変更できない
-- → 一度削除してから再追加する
ALTER TABLE products DROP COLUMN price_with_tax;

ALTER TABLE products
ADD price_with_tax NUMBER(12, 2)
    GENERATED ALWAYS AS (ROUND(price * (1 + tax_rate) * 0.95, 2)) VIRTUAL;
-- 5% 割引後の税込価格に変更した例

-- 仮想列に NOT NULL 制約を追加する
-- ※ 式が NULL を返さないことが保証されていれば NOT NULL を付けられる
-- ※ NULL が返り得る式に NOT NULL を付けると、INSERT 時に ORA-01400 になる場合がある

-- 仮想列に基づくインデックスがある場合は、列を削除する前にインデックスを先に削除する
DROP INDEX idx_price_with_tax;
ALTER TABLE products DROP COLUMN price_with_tax;

-- NG: LOB 型の仮想列
-- ALTER TABLE t ADD vcol CLOB GENERATED ALWAYS AS (col1 || col2);
-- ORA-54012 / ORA-54003 など型に関するエラー

-- NG: 別仮想列を参照する仮想列
-- （同じ式を別々の列に分けた場合は問題ないが、仮想列Aを仮想列Bの式で参照するのは不可）
-- CREATE TABLE t (
--     price  NUMBER,
--     tax    NUMBER GENERATED ALWAYS AS (price * 0.1),   -- OK
--     total  NUMBER GENERATED ALWAYS AS (price + tax)    -- NG: tax は仮想列
-- );

-- NG: SYSDATE を使う仮想列
-- CREATE TABLE t (price NUMBER, vdate DATE GENERATED ALWAYS AS (SYSDATE));
-- ORA-54002: only pure functions can be specified in virtual column expression

-- OK: 決定論的な組み込み関数（UPPER, LOWER, ROUND, TO_NUMBER, SUBSTR など）
ALTER TABLE employees_v
ADD upper_last_name VARCHAR2(50) GENERATED ALWAYS AS (UPPER(last_name)) VIRTUAL;