오라클 뷰(ORACLE VIEW)란?

 오라클 뷰(ORACLE VIEW)란?

 

 

뷰는 하나이상의 테이블 또는 다른 뷰를 기반으로 만들어진 가상의 창(WINDOW) 같은 것으로 뷰를 만드는 SQL문장만 오라클 딕셔너리에 저장되고 SQL문에서 테이블 처럼 사용가능한 가상 테이블 입니다.

뷰 종류

하나의 BASE TABLE을 기준으로 단순히 컬럼만 갖고 와서 사용하는 단순 뷰simple view, 두개 이상의 BASE TABLE로 구성되고 조인, GROUP BY, 컬럼 표현식등을 사용할 수 있는 복합 뷰complex view, FROM 절 아래 서브 쿼리 형태로 SQL문 안에서 사용되는 인라인 뷰inline view, 뷰 자체가 실제 데이터도 가지고 있으면서 그룹함수(MIN, MAX, SUM, AVG, COUNT) 튜닝을 위해 사용되는 구체화 뷰materialized view등이 있습니다.

뷰가 꼭 필요한 이유

1. 뷰의 주요 목적은 데이터를 저장하기 위해 테이블을 만들지 않고도 다른 테이블 및 뷰를 결합하여 새로운 데이터 형식을 만들 수 있습니다. 

2. 뷰는 복잡성을 숨길 수 있는데 여러 테이블을 조인해야 하거나 복잡한 논리 또는 계산이 필요한 쿼리가있는 경우 복잡한 부분을 뷰안으로 숨기고 사용자에게는 뷰 만 보여줍니다.

3. 뷰는 보안 메커니즘으로 사용될 수 있는데, 사용자에게 필요한 데이터만 보여주고 나머지는 숨길 수 있습니다. 뷰는 테이블에서 특정 컬럼 및 행을 선택하여 보여줄 수 있습니다. 이를 통해 사용자가 봐야하는 데이터 만 표시 할 수 있는 것이죠.

4. 잘못설계된  테이블을 리팩터링 해야하는 경우 테이블을 동일한 이름의 뷰로 바꿀 수 있습니다. 뷰는 원래 테이블과 똑같은 스키마를 제공하지만 실제 테이블은 아닙니다. 하지만  테이블을 참조하는 레거시 코드가 손상되지 않으므로 남는 시간에 레거시 코드를 변경할 수 있습니다.

13.1. 뷰(View)란?

뷰view는 하나 또는 그 이상의 테이블이나 뷰를 이용하여 생성되는 가상 테이블로 실제 데이터는 저장하지 않고 뷰를 만드는 SELECT 쿼리문만 오라클 딕셔너리에 저장됩니다. 그래서 뷰는 데이터베이스에 저장된 명명 된 쿼리라고 하며, 뷰에서 데이터를 쿼리 할 때 오라클은이 저장된 쿼리를 사용하여 기본 테이블base tables에서 데이터를 검색합니다.

뷰는 데이터 접근을 제한하는 용도로 사용하는데, 테이블 전체 데이터중 일부칼럼, 일부 레코드만 보여줌으로써 데이터의 보안과 사용 편의성을 제공 합니다. EMP 테이블에서 10번 부서만 보는 뷰를 만들수도 있고 empno, ename만 보는 뷰를 만들수도 있습니다. 

뷰의 기본이 되는 테이블을 BASE TABLE 이라고 하며 뷰를 통한 데이터의 입력, 수정, 삭제 가 가능합니다. 뷰의 기본이 되는 BASE TABLE의 무결성 제약조건은 상속됩니다. 또한 뷰자체를 수정할 수는 없습니다.

뷰 생성을 위해서는 CREATE VIEW 시스템 권한이 있어야 합니다.  

[그림13.1 뷰개요]

[기본형식]

CREATE [OR REPLACE] [FORCE|NO FORCE] VIEW  view_name   [(alias[,alias]...)] AS Subquery [WITH READ ONLY] [WITH CHECK OPTION [CONSTRAINT constraint]]; CREATE [OR REPLACE] [FORCE|NO FORCE] VIEW  view_name   [(alias[,alias]...)] AS Subquery [WITH READ ONLY] [WITH CHECK OPTION [CONSTRAINT constraint_name]] OR REPLACE : 이미 뷰가 존재 한다면 RE-CREATE 하라는 의미. FORCE : BASE TABLE 및 컬럼의 존재유무와 관계없이 뷰 생성 가능. NO FROCE : 기본 값이며, BASE TABLE이 존재해야 뷰 생성 가능. ALIAS : BASE TABLE의 컬럼명과 다르게 뷰 컬럼을 생성할 때 사용. WITH READ ONLY : 읽기 전용 뷰 생성 WITH CHECK OPTION : 뷰의 생성 쿼리가 SELECT 할 수 있는 데이터만 INSERT, UPDATE, DELETE 가능. CONSTRAINT constraint_name : 체크 제약 조건의 이름

CREATE VIEW 문을 사용하여 하나 이상의 테이블 또는 뷰를 기반으로하는 논리 테이블 인 뷰를 정의할 수 있습니다. 

일반 뷰와 비슷하지만 XMLType의 XMLSchema기반 테이블에서 데이터를 표시하는 XMLType 뷰를 만들 수도 있지만 자주 사용하지 않으므로 본서에서는 소개하지는 않습니다.

 

#오라클뷰, #부, #뷰란, #ORACLEVIEW, #VIEW, #​ORACLE, #SQL교육, #SQL강좌

오라클 SQL조인, 안티 조인(Anti Join)

 

오라클 SQL조인, 안티 조인(Anti Join)

조인의 대상이 되는 테이블과 일치하지 않는 데이터를 추출하는 조인방식 입니다.
 

서브 쿼리의 테이블에는 없는 메인 쿼리의 테이블의 데이터만 추출하는 조인 방법으로 NOT IN, NOT EXISTS 사용시 발생하는 조인연산이며 오라클에서 중첩루프조인 안티, 머지조인 안티, 해시 조인 안티로 실행 됩니다.
 

NOT IN을 이용한 안티 조인 예문 입니다. EMP 테이블에서 사번, 성명, 부서코드를 추출하는데 부서코드를 가지고 있지않은 사원들만 추출하는 쿼리 문 입니다. “KING” 사원은 최고관리자로 부서코드(deptno) 값을 가지고 있지않지만  NULL값이므로  결과 대상에서 제외 되었습니다.(NULL과 연산하는 결과는 NULL이 됨)

-- NOT IN SELECT empno, ename, deptno FROM emp WHERE deptno NOT IN (   SELECT deptno   FROM dept )

 

<실행결과>

선택된 행 없음

<실행계획(F10)>

오라클19C에서 별다른 힌트구문없이 그냥 실행 했을 때 해시조인 안티로 실행계획을 수립함을 확인할 수 있습니다.

NOT EXISTS을 이용한 안티 조인 예문 입니다. DEPT 테이블에서 부서코드, 부서명을 출력 하는데  EMP 테이블에 부서원들을 가지고 있지 않은 부서코드, 부서명을 출력합니다. 40번 부서는 EMP 테이블에 해당하는 부서원들이 없습니다. HASH_AJ 힌트구문은 안티 해시조인을 하라는 힌트구문으로 사용하지 않으면 오라클19C에서는 머지조인 안티로 쿼리문을 수행 합니다.

-- EXISTS SELECT deptno, dname FROM    dept WHERE NOT EXISTS    (SELECT /*+ HASH_AJ */ 1     FROM   emp     AND      emp.deptno = dept.deptno)

 

<실행결과>

	DEPTNO	ENAME
1	40	OPERATION

<실행계획(F10)>

#SQL조인, #조인이란, #안티조인, #AntiJoin, #SQL교육, #SQL강의, #오라클교육, #오라클, #오라클조인

오라클 조인방법, SQL 세미 조인(Semi Join)

 

오라클 조인방법, SQL 세미 조인(Semi Join)

세미조인은 SQL에서 서브쿼리에 IN, EXISTS를 사용했을 때 주로 나타나는 조인방식으로 서브쿼리에서 메인쿼리와의 연결을 마치 조인처럼 수행하는 것을 의미 합니다. 서브쿼리문을 마치 조인처럼 실행을 하는 것입니다.

중첩루프 세미조인, 해시 세미조인, 소트 머지 세미조인, 필터 세미조인, 부정형 세미조인 등이 있으며 자세한 내용은 오라클 힌트, SQL 튜닝 편에서 확인하세요.

서브쿼리는 메인쿼리에 종속적이므로 메인쿼리의 결과를 변하게 할 수는 없는 것이 특징인데 세미조인의 결과는 항상 메인쿼리의 결과 셋 중의 일부 또는 전체가 됩니다.

사원(EMP) 테이블 입니다.

SELECT empno, ename, deptno FROM emp

 

<실행결과>

	EMPNO	ENAME	DEPTNO
1	7369	SMITH	20
2	7499	ALLEN	30
3	7521	WARD	30
4	7566	JONES	20
5	7654	MARTIN	30
6	7698	BLAKE	30
7	7782	CLARK	10
8	7788	SCOTT	20
9	7839	KING
10	7844	TURNER	30
11	7876	ADAMS	20
12	7900	JAMES	30
13	7902	FORD	20
14	7934	MILLER	10

부서(DEPT) 테이블 입니다.

SELECT * FROM dept

<실행결과>

	DEPTNO	DNAME	LOC
1	10	ACCOUNTING	NEW YORK
2	20	RESEARCH	DALLAS
3	30	SALES	CHICAGO
4	40	OPERATIONS	BOSTON

IN을 이용한 세미조인 예문 입니다. DEPT 테이블에서 부서코드, 부서명을 출력하는데 EMP 테이블에 부서원들이 있는 부서만 출력을 하고 싶습니다. 40번 부서코드를 가진 사원은 EMP 테이블에 존재하지 않으므로 40번 부서는 출력되지 않습니다. HASH_SJ 힌트구문은 해시 세미조인을 하라는 구문으로 기술하지 않으면 오라클 19C에서는 머지 해시조인으로 쿼리문을 실행 합니다.

-- IN SELECT deptno, dname FROM   dept WHERE deptno in     (SELECT /*+ HASH_SJ */ deptno     FROM    emp)

 

<실행결과>

	DEPTNO	DNAME
1	10	ACCOUNTING
2	20	RESEARCH
3	30	SALES

<실행계획(F10)>

-- EXISTS SELECT deptno, dname FROM   dept WHERE EXISTS    (SELECT /*+ HASH_SJ */ 1     FROM   emp     WHERE emp.deptno = dept.deptno)

 

<실행결과>

	DEPTNO	DNAME
1	10	ACCOUNTING
2	20	RESEARCH
3	30	SALES

<실행계획(F10)>

위 EXISTS 쿼리 예문에서 HASH_SJ 대신 중첩루프 세미조인을 하라는 힌트구문인 NL_SJ을 사용하여 다시 실행을 해보겠습니다. 오라클은 서브쿼리로 작성된 구문을 중첩루프조인 형태로 실행 계획을 세우고 실행을 합니다. 머지 세미조인으로 수행하기 위한 MERGE_SJ 힌트 구문도사용하여 테스트 해보시기 바랍니다.   실행계획인 중요한 이유는 대용량의 데이터인 경우 어떤 절차와 어떤 조인 방법으로 실행되느냐에 따라 성능이 좌우 됩니다. 드라이빙 되는(최초에 읽는) 테이블의 데이터가 어마어마하게 많다면 중첩루프조인 으로는 어렵습니다. 한건식 읽으면서 안쪽 Inner 테이블과 처음 읽은 건수 만큼 계속 조인을 해야 하니까요, 이럴 때는 해시조인 형태로 수행을 하는 것이 성능상 대체로 유리 합니다.

-- EXISTS SELECT deptno, dname FROM    dept WHERE EXISTS    (SELECT /*+ NL_SJ */ 1     FROM   emp     WHERE emp.deptno = dept.deptno)

 

<실행결과>

	DEPTNO	DNAME
1	10	ACCOUNTING
2	20	RESEARCH
3	30	SALES

<실행계획(F10)>

#ORACLE, #SQL세미조인, #세미조인, #오라클세미조인, #semijoin, #SQL, #ORACLE교육, #오라클, #sQL교육