仮想列とは既存カラムを使った計算式を新規カラムとして定義したものです。計算結果を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
)
;
