이 글을 작성하기 전, 두 개의 아티클(About Oracle Virtual Column, About Oracle Identity Column)에서 Oracle ORDBMS(이하 오라클)에서 제공하는 컬럼에 대해 간략히 알아봤습니다. 오라클에서 제공하는 Virtual Column은 테이블에서 정의한 하나 이상의 컬럼을 활용하는 계산 식의 결과 또는 함수의 리턴 값 등을 활용할 때 사용하고, Identity Column은 대체 키(Surrogate Key)를 효과적으로 구성하기 위해 도입된 도구입니다.
이번 시간에는 Oracle ORDBMS(이하 오라클) 12c에서 새롭게 도입한 새로운 컬럼에 대해 알아보겠습니다. Oracle Invisible Column은 생성한 컬럼 목록 중에서 Invisible로 설정한 컬럼을 볼 수 없도록 제한1할 수 있는 기능입니다. 개인적으로 Invisible Column은 관리용 컬럼 등에 효과적으로 적용할 수 있을 것 같습니다.
Invisible Column & Features
오라클 12c에서 도입된 Invisible Column은 Invisible 속성으로 생성했을 때, 컬럼을 보이지 않게 설정할 수 기능으로 다음 작업을 처리할 때 컬럼이 보이지 않습니다.
- SELECT * FROM tab_name 등과 같은 와일드카드(*) 문자를 사용한 문장
- SQL*PLUS에서 DESC 명령
- PL/SQL &rowtype 선언
- INSERT INTO SELECT * 문장
하지만 명시적으로 컬럼을 정의하여 참조하는 모든 SQL 문장에서는 여전히 컬럼에 접근이 가능하며, 직접 조작할 수 있습니다. Invisible Column은 파티션 키, 인덱스의 키, 그리고 Virtual Column으로도 생성할 수 있습니다. Invisible Column은 Create Table, Create View, Alter Table, Alter View 명령을 이용하여 컬럼을 Invisible로 생성하거나 변경할 수 있습니다.
하지만 다음 테이블 유형에서는 Invisible Column으로 생성할 수 없습니다.
- 외부 테이블(External Tables)
- 클러스터 테이블(Cluster Tables)
- 임시 테이블(Temporary Tables)
- 사용자 정의 유형의 속성 목록
Invisible Column에 대해 보다 더 자세한 내용은 다음 링크에서 확인할 수 있습니다.
Example
이 절에서는 Invisible Column을 생성하고, 이를 활용하는 방법을 간단하게 살펴보겠습니다.
먼저 Invisible Column을 갖는 테이블을 생성하겠습니다.
DROP TABLE INVISIBLECOL_TAB PURGE;
CREATE TABLE INVISIBLECOL_TAB (
REG_NO NUMBER,
MESSAGE NVARCHAR2(100),
USERID NVARCHAR2(10) INVISIBLE
);
DESCRIBE 명령을 이용하여 생성한 테이블의 구조를 확인해보겠습니다.
DESC INVISIBLECOL_TAB;
앞 단락에서 설명한 내용과 같이 DESCRIBE 명령을 이용하여 테이블 구조를 확인해보면 Invisible 속성으로 생성된 컬럼은 보이지 않음을 확인할 수 있습니다.
INVISIBLECOL_TAB에 데이터를 삽입해보겠습니다.
INSERT INTO INVISIBLECOL_TAB VALUES (1, '안녕하세요.');
INSERT INTO INVISIBLECOL_TAB VALUES (2, '반갑습니다.');
COMMIT;
SELECT * 구문을 이용하여 생성한 테이블의 내용을 확인해보겠습니다.
SELECT * FROM INVISIBLECOL_TAB;
등록한 내용이 정확하게 입력된 것을 확인할 수 있습니다. 그럼 USERID 컬럼에 값을 등록하려면 어떻게 해야 할까요? 다음 질의문이 성공적으로 수행이 될까요?
INSERT INTO INVISIBLECOL_TAB VALUES (3, '테스트입니다.', 'SCOTT');
COMMIT;
예상했던대로 정상적으로 등록되지 않음을 확인할 수 있습니다. 그럼 정상적으로 등록하려면 어떻게 해야 할까요?
INSERT INTO INVISIBLECOL_TAB(REG_NO, MESSAGE, USERID)
VALUES (3, '테스트입니다.', 'SCOTT');
COMMIT;
이제 정상적으로 등록이 됩니다. 그럼 USERID를 확인하고 싶으면 어떻게 해야 할까요?
SELECT * FROM INVISIBLECOL_TAB; (1)
SELECT REG_NO, MESSAGE, USERID FROM INVISIBLECOL_TAB; (2)
(1)번 문장으로 조회하면 Invisible Column의 값은 추출되지 않습니다. 앞에서 언급했듯이 Invisible Column의 컬럼명을 정확하게 입력하는 질의에서는 결과물을 얻을 수 있습니다.
Invisible Columns and Column Ordering
Invisible Column으로 설정하는 경우, 컬럼의 순서가 변경됩니다. 이 특성은 매우 중요하다고 생각되어 별도의 단락으로 구분하여 정리합니다. Invisible로 설정된 컬럼을 Visible 컬럼으로 변경하면, 가장 오른쪽으로 컬럼의 위치가 변경됩니다. 그러므로 컬럼의 위치를 재배정하고 싶을 때, 과거처럼 테이블의 재생성을 하지 않아도 Invisible 컬럼을 사용하여 해결할 수 있습니다.
여기에서는 앞의 예제를 활용하여 MESSAGE 컬럼과 USERID 컬럼의 위치를 변경해보겠습니다.
ALTER TABLE INVISIBLECOL_TAB MODIFY (MESSAGE INVISIBLE);
ALTER TABLE INVISIBLECOL_TAB MODIFY (USERID VISIBLE);
ALTER TABLE INVISIBLECOL_TAB MODIFY (MESSAGE VISIBLE);
DESC INVISIBLECOL_TAB;
정확하게 컬럼의 위치가 변경된 것을 확인할 수 있습니다. 필자가 질의 결과를 함께 기록하지 않은 이유는 직접 수행해보고 확인을 해보셨으면 하는 바람이 있기 때문입니다.
Conclusion
지금까지 오라클 12c에서 제공하는 특수한 컬럼인 Invisible Column에 대해 간단하게 알아봤습니다. 관리용 컬럼이나 반드시 보이지 않아도 되는 속성인 상황이 필요할 때 유용하게 활용할 수 있으며, 컬럼의 위치를 변경해야 하는 상황에서 유연하게 대처할 수 있습니다.
References
1. Oracle, oracle Database Document 12c Release 1
2. Oracle-Base, Invisible Columns in Oracle Database 12c Release 1 [관련링크]
- 단, 명시적으로 컬럼명을 지정하면 해당 컬럼을 볼 수 있습니다. [본문으로]