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%환급]PL/SQL,ORACLE
HINT,TUNING  |
총 | 5일 | 40시간 | 02-17 | |
[기업100%환급]SQL기초에서
Schema Object까지  |
총 | 5일 | 40시간 | 02-24 | |
 | |||||
| SQL초보에서실전전문가까지
|
총 | 18일 | 54시간 | 02-18 | |
 | |||||
| SQL초보에서실전전문가까지
|
총 | 8일 | 56시간 | 02-22 | |
| [주말저녁]SQL기초에서
Schema Object까지 |
총 | 10일 | 40시간 | 03-01 | |
댓글 없음:
댓글 쓰기