oracle

Oracle 仮想列の作成、削除、確認

仮想列とは既存カラムを使った計算式を新規カラムとして定義したものです。計算結果をDB内部で保持するため検索パフォーマンスを上げる一面もありますが注意点もあります。仮想列をインデックスに利用できますしヒストグラムの取得もできますが、仮想列に対し直接INSERTやUPDATEをする事はできません。

仮想列の追加

以下例はADDRESS_CODEの6桁目から7桁をOAZA_TOWN_CHOME_CODEとして新規に項目追加しています。

-- 仮想列の作成 
ALTER TABLE KOKUDO_ADDRESS_CHOME ADD OAZA_TOWN_CHOME_CODE AS (substr(ADDRESS_CODE,6,7)) 
;
SQL> set lin 100
SQL> COLUMN ADDRESS_CODE FORMAT A12
SQL> COLUMN PREFECTURES_CODE HEADING 'PREFECTURES|CODE'
SQL> COLUMN PREFECTURES_CODE FORMAT A11
SQL> COLUMN PREFECTURES_NAME HEADING 'PREFECTURES|NAME'
SQL> COLUMN PREFECTURES_NAME FORMAT A11
SQL> COLUMN CITY_CODE FORMAT A5
SQL> COLUMN CITY_NAME FORMAT A20
SQL> COLUMN OAZA_TOWN_CHOME_NAME FORMAT A24
SQL> select t.ADDRESS_CODE,t.PREFECTURES_CODE,t.PREFECTURES_NAME,t.CITY_CODE,t.CITY_NAME,t.OAZA_TOWN_CHOME_NAME
  2  from KOKUDO_ADDRESS_CHOME t
  3  where t.CITY_CODE='26108'
  4  order by t.ADDRESS_CODE fetch first 5 rows only
  5  ;

             PREFECTURES PREFECTURES
ADDRESS_CODE CODE        NAME        CITY_ CITY_NAME            OAZA_TOWN_CHOME_NAME
------------ ----------- ----------- ----- -------------------- ------------------------
261080001000 26          京都府      26108 京都市右京区         太秦青木ケ原町
261080002000 26          京都府      26108 京都市右京区         太秦青木元町
261080003000 26          京都府      26108 京都市右京区         太秦荒木町
261080004000 26          京都府      26108 京都市右京区         太秦石垣町
261080005000 26          京都府      26108 京都市右京区         太秦和泉式部町

SQL>
SQL> -- 仮想列の作成
SQL> ALTER TABLE KOKUDO_ADDRESS_CHOME ADD OAZA_TOWN_CHOME_CODE AS (substr(ADDRESS_CODE,6,7))
  2  ;

表が変更されました。

-- 追加された事を確認する
SQL> COLUMN OAZA_TOWN_CHOME_CODE HEADING 'OAZA_TOWN|CHOME_CODE'
SQL> COLUMN OAZA_TOWN_CHOME_CODE FORMAT A10
SQL> select t.ADDRESS_CODE,t.PREFECTURES_CODE,t.PREFECTURES_NAME,t.CITY_CODE,t.CITY_NAME
  2  ,t.OAZA_TOWN_CHOME_CODE,t.OAZA_TOWN_CHOME_NAME
  3  from KOKUDO_ADDRESS_CHOME t
  4  where t.CITY_CODE='26108'
  5  order by t.ADDRESS_CODE fetch first 5 rows only
  6  ;

             PREFECTURES PREFECTURES                            OAZA_TOWN
ADDRESS_CODE CODE        NAME        CITY_ CITY_NAME            CHOME_CODE OAZA_TOWN_CHOME_NAME
------------ ----------- ----------- ----- -------------------- ---------- --------------------
261080001000 26          京都府      26108 京都市右京区         0001000    太秦青木ケ原町
261080002000 26          京都府      26108 京都市右京区         0002000    太秦青木元町
261080003000 26          京都府      26108 京都市右京区         0003000    太秦荒木町
261080004000 26          京都府      26108 京都市右京区         0004000    太秦石垣町
261080005000 26          京都府      26108 京都市右京区         0005000    太秦和泉式部町

SQL>
仮想列の確認

DBA_TAB_COLSのVIRTUAL_COLUMNにて仮想列か否か確認できます。

select
 c.INTERNAL_COLUMN_ID  -- 内部の保持順番
,c.COLUMN_NAME         -- カラム名
,c.DATA_TYPE           -- 型
,c.VIRTUAL_COLUMN      -- 仮想列かどうか
from dba_tab_cols c where c.TABLE_NAME='KOKUDO_ADDRESS_CHOME'
and c.OWNER ='SOONI'
order by c.INTERNAL_COLUMN_ID
;
SQL> set lin 100
SQL> set pages 100
SQL> COLUMN COLUMN_NAME FORMAT A20
SQL> COLUMN DATA_TYPE FORMAT A12
SQL> select
  2   c.INTERNAL_COLUMN_ID  -- 内部の保持順番
  3  ,c.COLUMN_NAME         -- カラム名
  4  ,c.DATA_TYPE           -- 型
  5  ,c.VIRTUAL_COLUMN      -- 仮想列かどうか
  6  from dba_tab_cols c where c.TABLE_NAME='KOKUDO_ADDRESS_CHOME'
  7  and c.OWNER ='SOONI'
  8  order by c.INTERNAL_COLUMN_ID
  9  ;

INTERNAL_COLUMN_ID COLUMN_NAME          DATA_TYPE    VIRTUA
------------------ -------------------- ------------ ------
                 1 LNO                  NUMBER       NO
                 2 ADDRESS_CODE         VARCHAR2     NO
                 3 PREFECTURES_CODE     VARCHAR2     NO
                 4 PREFECTURES_NAME     VARCHAR2     NO
                 5 CITY_CODE            VARCHAR2     NO
                 6 CITY_NAME            VARCHAR2     NO
                 7 AZA_TOWN_CHOME_CODE  VARCHAR2     YES
                 8 OAZA_TOWN_CHOME_NAME VARCHAR2     NO
                 9 LATITUDE             NUMBER       NO
                10 LONGITUDE            NUMBER       NO
                11 DOCUMENT_CODE        NUMBER       NO
                12 CHOME_KUBUN_CODE     NUMBER       NO

12行が選択されました。
仮想列の制限

仮想列に対してUPDATEや直接値のINSERTはできません。 ORA-54017 が発生します。

SQL> insert into KOKUDO_ADDRESS_CHOME (LNO,ADDRESS_CODE,PREFECTURES_CODE,PREFECTURES_NAME,CITY_CODE,CITY_NAME,OAZA_TOWN_CHOME_NAME,LATITUDE,LONGITUDE,DOCUMENT_CODE,CHOME_KUBUN_CODE,OAZA_TOWN_CHOME_CODE)
  2  values ('7640','261080006000','26','京都府','26108','京都市右京区','追加町','35.017289','135.713627','0','1','0006000')
  3  ;
insert into KOKUDO_ADDRESS_CHOME (LNO,ADDRESS_CODE,PREFECTURES_CODE,PREFECTURES_NAME,CITY_CODE,CITY_NAME,OAZA_TOWN_CHOME_NAME,LATITUDE,LONGITUDE,DOCUMENT_CODE,CHOME_KUBUN_CODE,OAZA_TOWN_CHOME_CODE)
                                                                                                                                                                                *
行1でエラーが発生しました。:
ORA-54013: 仮想列ではINSERT操作は許可されていません


SQL> update KOKUDO_ADDRESS_CHOME set OAZA_TOWN_CHOME_CODE= '0005001' where ADDRESS_CODE='261080005000'
  2  ;
update KOKUDO_ADDRESS_CHOME set OAZA_TOWN_CHOME_CODE= '0005001' where ADDRESS_CODE='261080005000'
                                *
行1でエラーが発生しました。:
ORA-54017: 仮想列ではUPDATE操作は許可されていません


SQL>
仮想列の削除

通常のカラム削除の要領で可能です。

alter table KOKUDO_ADDRESS_CHOME drop column OAZA_TOWN_CHOME_CODE
;
CREATE TABLEのタイミングで仮想列を定義するには

GENERATED ALWAYS AS XXXXX VIRTUAL にて定義できます。

CREATE TABLE SOONI.KOKUDO_ADDRESS_CHOME ( 
    LNO                         NUMBER,
    ADDRESS_CODE                VARCHAR2(12 CHAR),
    PREFECTURES_CODE            VARCHAR2(2 CHAR),
    PREFECTURES_NAME            VARCHAR2(32 CHAR),
    CITY_CODE                   VARCHAR2(5 CHAR),
    CITY_NAME                   VARCHAR2(64 CHAR),
    AZA_TOWN_CHOME_CODE         VARCHAR2(7 CHAR) GENERATED ALWAYS AS (SUBSTR("ADDRESS_CODE",6,7)) VIRTUAL,
    OAZA_TOWN_CHOME_NAME        VARCHAR2(32 CHAR),
    LATITUDE                    NUMBER(11,8),
    LONGITUDE                   NUMBER(11,8),
    DOCUMENT_CODE               NUMBER,
    CHOME_KUBUN_CODE            NUMBER
) 
;

スポンサーリンク