Oracle 11G New Feature: Virtual Column(가상 컬럼) 이란[ORACLE/SQL/HINT/오라클/교육/강의/강좌/SQL교육/오라클 PLSQL/강의/강좌/오라클교육/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; 오라클자바커뮤니티교육센터, 개발자전문교육, 개인80%환급  www.oraclejavacommunity.com 평일주간(9:30~18:10) 개강 (6/23)[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지  (6/23)[기업100%환급]Spring ,MyBatis,Hibernate실무과정 (6/23)[기업100%환급]SQL기초에서 Schema Object까지 (6/30)[기업100%환급]PL/SQL,ORACLE HINT,TUNING (6/30)[기업100%환급]안드로이드개발자과정 (7/07)[기업100%환급]C#4.0,WinForm,ADO.NET프로그래밍 (7/07)[채용예정교육]오라클자바개발잘하는신입뽑기프로젝트,교육전취업확정 평일야간(19:00~21:50) 개강 (6/19)웹퍼블리싱 마스터 (6/19)Spring3.X, MyBatis, Hibernate실무과정 (6/24)안드로이드개발자과정 (6/24)MyBatis3.X, Hibernate4.X ORM실무과정 (6/26)SQL초보에서실전전문가까지 (7/01)닷넷(C#,Network,ADO.NET,ASP.NET)마스터과정 (7/02)자바웹(JSP,Spring,MyBatis,XPlatform)프로젝트과정 (7/02)JAVA,Network&WEB&Framework(자바기초에서웹스프링까지) (7/22)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지 주말(10:00~17:50) 개강 (6/21)Spring3.X, MyBatis, Hibernate실무과정 (6/21)MyBatis3.X, Hibernate4.X ORM실무과정 (6/21)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지 (6/28)SQL초보에서 Schema Object까지 (6/28)안드로이드개발자과정 (6/28)개발자를위한PLSQL,SQL튜닝,힌트 (6/28)실무예제로 배워보는 jQuery(개발자/디자이너를위한) (6/28)자바웹(JSP,Spring,MyBatis,XPlatform)프로젝트과정 (7/13)C#,ASP.NET마스터 주말저녁(18:30~22:20) 개강 (6/28)JAVA,Network&WEB&Framework (6/28)SQL기초에서실무까지