n Oracle 11g에서 새로 소개된 가상 컬럼(Virtual Column)은 다른 칼럼의 값들을 이용하여 새로운 값을 만들어 내는 경우에 유용하게 사용될 수 있다.
n 일반 컬럼과 유사하지만 다음과 같은 차이가 있다.
표현식등에 의해 정의.(사원 테이블에서 급여와 수당 칼럼을 이용하여 년봉을 계산)
DB에 저장되지는 않으며 실행 중에 계산된다.
UPDATE, DELETE의 WHERE절에 나타날 수 있지만 칼럼값 자체에 대한 변경은 불가능 하다.
[형식]
column_name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL] |
SQL>DROP TABLE emp21 PURGE; SQL>DROP TABLE emp22 PURGE; SQL>DROP TABLE emp23 PURGE;
SQL> CREATE TABLE EMP21 ( SABUN NUMBER, IRUM VARCHAR2(50), SAL NUMBER, BONUS NUMBER, TOTAL_SAL NUMBER GENERATED ALWAYS AS (SAL * 12 + BONUS) );
테이블이 생성되었습니다.
SQL> SELECT column_name, data_type, data_length, data_default, virtual_column FROM user_tab_cols WHERE table_name = 'EMP21';
COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_DEFAULT VIRTUAL_COLUMN -------------------------------------------------------------------------------------------------------------- SABUN NUMBER 22 NO IRUM VARCHAR2 50 NO SAL NUMBER 22 NO BONUS NUMBER 22 NO TOTAL_SAL NUMBER 22 "SAL"*12+"BONUS" YES
-- 간단히 급여와 보너스를 입력받으면 연봉을 리턴해 주는 함수를 하나 만들자. -- DETERMINISTIC : 함수의 입력 값이 같다면 출력 값도 항상 같음을 선언(10gR2에 새롭게 추가된 캐싱 효과), FBI(function based index)는 인덱스가 처음 생성 또는 엔트리가 추가되는 시점의 함수 출력 값을 저장해 두는 원리인데 오라클은 Deterministic으로 선언하지 않은 함수에 대해서 FBI 생성 거부한다. 즉 DETERMINISTIC으로 함수를 선언해야지 함수기반 인덱스에서 이용될 수 있다.
SQL> CREATE OR REPLACE FUNCTION get_annual( p_sal NUMBER, p_bonus NUMBER) RETURN NUMBER DETERMINISTIC IS BEGIN RETURN p_sal * 12 + p_bonus; END; /
함수가 생성되었습니다.
-- 위에서 만든 get_annual 함수를 이용하여 가상칼럼을 만들자. SQL> CREATE TABLE EMP22 ( SABUN NUMBER, IRUM VARCHAR2(50), SAL NUMBER, BONUS NUMBER, TOTAL_SAL NUMBER AS (get_annual(sal, bonus)) virtual ); 테이블이 생성되었습니다.
-- 테이블의 total_sal 칼럼이 get_annual 함수에 의존해서 만들어 졌으므로 get_annual 함수가 DETERMINISTIC 으로 선언되어야 함수기반 인덱스가 생성가능 하다. 아래 인덱스는 함수기반 인덱스로 만들어 진다. SQL> CREATE INDEX idx_emp22_total_sal ON emp22(total_sal); 인덱스가 생성되었습니다.
-- 아래에서 생성된 인덱스의 타입을 확인하자.
SQL> SELECT index_name, index_type FROM user_indexes WHERE table_name = 'EMP22';
INDEX_NAME INDEX_TYPE ------------------------------ ------------------------------------ IDX_EMP22_TOTAL_SAL FUNCTION-BASED NORMAL
-- total_sal 칼럼에는 직접값을 INSERT하지 않아도 자동으로 값이 만들어진다.
SQL> INSERT INTO EMP22 (sabun, irum, sal, bonus) WITH DATA AS (SELECT 100 sabun, 'AAA' irum, 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> COMMIT;
SQL> SELECT * FROM EMP22;
SABUN IRUM 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 emp22 SET total_sal = 0; UPDATE emp10 SET total_sal = 0 * 1행에 오류: ORA-54017: UPDATE 작업은 가상 열에서 허용되지 않습니다.
-- 이번에는 가상칼럼의 값을 기준으로 파티셔닝을 해 보자. 오라클 이전버전에서는 테이블의 물리적인 컬럼으로만 파티션을 허용했지만 Oracle11g 이후 에서는 가상 컬럼도 가능하도록 했다.
SQL> CREATE TABLE EMP23 ( SABUN NUMBER, IRUM VARCHAR2(50), SAL NUMBER, BONUS NUMBER, TOTAL_SAL NUMBER 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));
테이블이 생성되었습니다.
SQL> INSERT INTO EMP23 (SABUN, IRUM, SAL, BONUS) WITH DATA AS (SELECT 100 SABUN, 'AAA' IRUM, 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;
5 개의 행이 만들어졌습니다.
SQL> COMMIT;
커밋이 완료되었습니다.
SQL> SELECT * FROM emp23;
SABUN IRUM SAL BONUS TOTAL_SAL ---------- -------------------------------------------------- ---------- ---------- ---------- 200 BBB 12000 2000 146000 500 EEE 12300 8000 155600 100 AAA 20000 3000 243000 300 CCC 32100 1000 386200 400 DDD 24300 5000 296600
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'EMP23',granularity => 'PARTITION'); PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> SELECT table_name, partition_name, num_rows FROM user_tab_partitions WHERE table_name = 'EMP23' ORDER BY partition_name;
TABLE_NAME PARTITION_NAME NUM_ROWS ------------------------------ ------------------------------ ---------- EMP23 SAL_200000 2 EMP23 SAL_400000 3 EMP23 SAL_600000 0 EMP23 SAL_800000 0 EMP23 SAL_DEFAULT 0
SQL> UPDATE emp23 SET sal = 30000 WHERE sabun = 500; UPDATE emp23 * 1행에 오류: ORA-14402: 분할영역 키 열을 수정하는것은 분할영역 변경이 생깁니다
-- sal 칼럼에 대한 값 변경은 파티션영역의 total_sal값의 변경이므로 row movement를 enable 해야 한다. SQL> ALTER TABLE emp23 ENABLE ROW MOVEMENT; 테이블이 변경되었습니다.
SQL> UPDATE emp23 SET sal = 30000 WHERE sabun = 500; 1 행이 갱신되었습니다.
SQL> COMMIT; 커밋이 완료되었습니다.
|
댓글 없음:
댓글 쓰기