Oracle 11G New Feature: Virtual Column(가상 컬럼) 이란[오라클개발자교육/오라클/ORACLE강좌/오라클교육잘하는곳/오라클교육추천/ORACLE실무교육/ORACLE/ORACLE교육/ORACLE학원/오라클실무교육]

Oracle 11G New Feature: Virtual Column 

Oracle 11g에서 새로 소개된 가상 컬럼(Virtual Column)이 있는데 보통의 컬럼과 유사하지만 다음과 같은 차이가 있다. 

- 표현식등에 의해 정의. 
- DB에 저장되지는 않는다.(실행 중에 계산된다.) 
- 가상컬럼을 update 할 수 없다. 
- UPDATE, DELETE의 where절에 나타날 수 있지만 변경은 불가 
-

[문법] 
column_name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL] 


[예제] 

CREATE TABLE MYEMP 
( 
    empno        NUMBER, 
    ename        VARCHAR2(50), 
    sal          NUMBER(10,2), 
    bonus        NUMBER(10,2), 
    total_sal    NUMBER(10,2) GENERATED ALWAYS AS (sal*12 + bonus) 
); 


생성된 가상컬럼을 딕셔너리에서 확인 할 수 있다. 

SELECT column_name, data_type, data_length, data_default, virtual_column 
  FROM user_tab_cols 
 WHERE table_name = 'EMPLOYEE'; 
  
COLUMN_NAME | DATA_TYPE | DATA_LENGTH | DATA_DEFAULT            | VIRTUAL_COLUMN 
EMPNO    | NUMBER    | 22          | null                          | NO          
ENAME    | VARCHAR2  | 50          | null                          | NO          
SAL      | NUMBER    | 22          | null                          | NO          
BONUS    | NUMBER    | 22          | null                          | NO          
TOTAL_SAL | NUMBER    | 22          | "MONTHLY_SAL"*12+"BONUS"|YES ES            


DROP TABLE EMPLOYEE PURGE; 


//연봉을 리턴해주는 함수를 하나 만들자. 
CREATE OR REPLACE FUNCTION get_emp_total_sal ( p_sal  NUMBER, 
                                              p_bonus        NUMBER) 
  RETURN NUMBER 
DETERMINISTIC 
IS 
BEGIN 
  RETURN p_sal * 12 + p_bonus; 
END; 

------------------------------------------------------------- 
DETERMINISTIC  
함수의 입력 값이 같다면 출력 값도 항상 같음을 선언(10gR2에 새롭게 추가된 캐싱 효과) 
FBI(function based index)는 인덱스가 처음 생성 또는 엔트리가 추가되는 시점의 함수 출력 값을 저장해 두는 원리 
오라클은 Deterministic으로 선언하지 않은 함수에 대해서 FBI 생성 거부 
------------------------------------------------------------- 

--이번에는 저장함수를 통해 가상컬럼을 만들자. 

CREATE TABLE MYEMP 
(empno    NUMBER, 
 ename    VARCHAR2(50), 
 sal NUMBER(10,2), 
 bonus      NUMBER(10,2), 
 total_sal  NUMBER(10,2) AS (get_emp_total_sal(sal, bonus)) VIRTUAL 
); 


CREATE INDEX idx_total_sal ON myemp(total_sal); 

SELECT index_name, index_type  
  FROM user_indexes 
 WHERE table_name = 'MYEMP'; 

INDEX_NAME                    INDEX_TYPE                  
------------------------------ --------------------------- 
IDX_TOTAL_SAL                  FUNCTION-BASED NORMAL 


만약 위에서 만든 get_emp_total_sal 함수를 삭제한다면 myemp 테이블은 select 안됨 


Sql> drop function get_emp_total_sal 

SQL> SELECT * FROM employee; 

Error: # 942, ORA-00942: 테이블 또는 뷰가 존재하지 않습니다 
SQL Execution Time > 00:00:00.000 


이번에는 MYEMP 테이블을 생성 후 가상 컬럼을 추가해 보자. 

DROP TABLE MYEMP PURGE; 
  
CREATE TABLE MYEMP 
(EMPNO    NUMBER, 
 ENAME    VARCHAR2(50), 
 SAL      NUMBER(10,2), 
 BONUS      NUMBER(10,2) 
); 
  
ALTER TABLE MYEMP ADD (total_sal AS (sal * 12 + bonus)); 


INSERT INTO MYEMP (EMPNO, ENAME, SAL, BONUS) 
  WITH DATA AS 
        (SELECT 100 empno, 'AAA' ename, 20000 sal, 3000 bonus 
          FROM DUAL 
        UNION 
        SELECT 200, 'BBB', 12000, 2000 
          FROM DUAL 
        UNION 
        SELECT 300, 'CCC', 32100, 1000 
          FROM DUAL 
        UNION 
        SELECT 400, 'DDD', 24300, 5000 
          FROM DUAL 
        UNION 
        SELECT 500, 'EEE', 12300, 8000 
          FROM DUAL) 
  SELECT * 
    FROM DATA; 
  
SQl>SELECT * FROM MYEMP; 

EMPNO      ENAME              SAL      BONUS      TOTAL_SAL          
---------- -------------------- --------- ---------- ------------------------------ 
      100 AAA                20000      3000            243000 
      200 BBB                12000      2000            146000 
      300 CCC                32100      1000            386200 
      400 DDD              24300      5000            296600 
      500 EEE                12300      8000            155600 


이번에는 가상 컬럼에 변경을 가해보자. 


SQL> UPDATE MYEMP 
  SET TOTAL_SAL = 2000; 

Error: # 54017, ORA-54017: UPDATE 작업은 가상 열에서 허용되지 않습니다. 
SQL Execution Time > 00:00:00.015 


이번에는 가상컬럼을 기준으로 파티셔닝을 해보자. 
오라클 이전버전에서는 테이블의 물리적인 컬럼으로만 파티션을 허용했지만 Oracle11g에서는 가상 컬럼도 가능하도록 구성했다. 

예제를 보자. 

DROP TABLE MYEMP PURGE; 

CREATE TABLE MYEMP 
( 
    empno        NUMBER, 
    ename        VARCHAR2(50), 
    sal          NUMBER(10,2), 
    bonus        NUMBER(10,2), 
    total_sal    NUMBER(10,2) GENERATED ALWAYS AS (sal*12 + bonus) 
) 
PARTITION BY RANGE (total_sal) 
    (PARTITION sal_200000 VALUES LESS THAN (200000), 
    PARTITION sal_400000 VALUES LESS THAN (400000), 
    PARTITION sal_600000 VALUES LESS THAN (600000), 
    PARTITION sal_800000 VALUES LESS THAN (800000), 
    PARTITION sal_default VALUES LESS THAN (MAXVALUE)); 


INSERT INTO MYEMP (EMPNO, ENAME, SAL, BONUS) 
  WITH DATA AS 
        (SELECT 100 EMPNO, 'AAA' ENAME, 20000 SAL, 3000 BONUS 
          FROM DUAL 
        UNION 
        SELECT 200, 'BBB', 12000, 2000 
          FROM DUAL 
        UNION 
        SELECT 300, 'CCC', 32100, 1000 
          FROM DUAL 
        UNION 
        SELECT 400, 'DDD', 24300, 5000 
          FROM DUAL 
        UNION 
        SELECT 500, 'EEE', 12300, 8000 
          FROM DUAL) 
  SELECT * 
    FROM DATA; 
    
    COMMIT 
    
EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'MYEMP',granularity => 'PARTITION'); 

SQL> SELECT  table_name, partition_name, num_rows 
    FROM user_tab_partitions 
  WHERE table_name = 'MYEMP' 
ORDER BY partition_name; 

TABLE_NAME                    PARTITION_NAME                NUM_ROWS        
------------------------------ ------------------------------ ---------------- 
MYEMP                          SAL_200000                                    2 
MYEMP                          SAL_400000                                    3 
MYEMP                          SAL_600000                                    0 
MYEMP                          SAL_800000                                    0 
MYEMP                          SAL_DEFAULT                                  0 

5 rows selected. 


이번에는 MYEMP 테이블의 sal 값을 변경해 보자. 

SQL> UPDATE myemp 
  SET sal = 30000 
 WHERE empno = 500; 

Error: # 14402, ORA-14402: 분할영역 키 열을 수정하는것은 분할영역 변경이 생깁니다 
SQL Execution Time > 00:00:00.047 


Sal 값의 변경은 파티션 영역의 total_sal값의 변경을 가져오므로 row movement를 enable 해야 한다. 

ALTER TABLE myemp ENABLE ROW MOVEMENT; 
  
UPDATE myemp 
  SET sal = 80000 

 WHERE empno = 500;

	기업100%환급/오라클/자바/스프링/안드로이드/닷넷C#/웹퍼블리싱…	오라클자바…	12-27	1965
	[채용예정교육]오라클자바개발잘하는신입뽑기2개월과정,교육전취…	오라클자바…	12-11	1445
53	[평일주간]100%환급6건,안드로이드,자바,C#,스프링3.2,SQL,힌트/…	오라클자바…	03-15	1201
52	[주말주간]C#, ASP.NET마스터	오라클자바…	01-31	1365
51	[평일,기업100%환급]SQL기초에서 Schema Object까지	오라클자바…	01-31	1142
50	[평일야간]HTML5, CSS3,Ajax, jQuery마스터과정	오라클자바…	01-31	1048
49	[평일주간,평일야간,주말]Spring,MyBatis,Hibernate개발자과정	오라클자바…	01-19	1362
48	[평일주간,평일야간,주말]안드로이드개발자과정	오라클자바…	01-11	1196
47	[평일야간,주말주간]JAVA,Network&JSP&Spring,MyBatis,Hibernate	오라클자바…	01-03	1697
46	기업100%환급/오라클/자바/스프링/안드로이드/닷넷C#/웹퍼블리싱…	오라클자바…	12-27	1965
45	[평일야간,주말]자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis…	오라클자바…	12-19	1465
44	웹퍼블리싱 마스터(HTML5,CSS3,jQUERY,AJAX,JavaScript)	오라클자바…	12-14	1431
43	[채용예정교육]오라클자바개발잘하는신입뽑기2개월과정,교육전취…	오라클자바…	12-11	1445
42	[평일,기업100%환급]자바기초에서 JDBC, Servlet/JSP까지	오라클자바…	12-09	1162
41	[평일야간]닷넷(C#,Network,ADO.NET,ASP.NET)마스터	오라클자바…	12-01	1367
40	[기업100%환급]C#4.0,WinForm,ADO.NET프로그래밍(평일주간(단기)…	오라클자바…	12-01	1527
39	[평일야간,주말]SQL기초에서실무까지(SQL기초,PLSQL,힌트,튜닝)	오라클자바…	12-01	1025