오라클 인라인 뷰(Oracle Inline View)

 

오라클 인라인 뷰(Oracle Inline View)

인라인 뷰inline view는 실제 뷰가 아니라 SELECT의 FROM절에 있는 서브 쿼리sub query로 FROM 절에서 테이블 처럼 사용될 데이터를 정의할 때 사용 합니다. FROM절의 테이블을 대체 할 수 있다고 해서 파생 테이블이라고도 부릅니다.

서브 쿼리는 FROM절에서도 사용 가능 한데 이를 뷰(View)인데 CREATE VIEW 명령어로 만들지 않고 SQL문의 해당 라인에 직접 기술한다고 해서 인라인 뷰(InLine View)라고 합니다. SQL문 라인에 직접 기술하니 필요한 시점에만 사용되는 특징이 있습니다.

인라인 뷰를 사용하면 조인 작업을 제거하거나 개별 쿼리를 단일 쿼리로 통합해서 복잡한 쿼리를 단순화 할 수 있습니다. 또한 복잡한 조인 연산시 조인의 타겟 테이블의 데이터 개수를 줄일 수 있습니다.

인라인뷰를 이용한 쿼리 예문을 실습해 보겠습니다.

실습

인라인 뷰를 이용하여 EMP 테이블에서 부서코드별 급여 평균, 합을 구하고 부서코드(deptno)를 조인 키로 DEPT 테이블과 조인하여 부서명을 읽습니다.

인라인뷰를 사용하여 사원(EMP), 부서(DEPT) 테이블에서 부서별로 부서명, 급여의 평균, 급여의 합계를 출력 하세요. (부서명으로 오름차순 정렬, 급여 평균은 소수이하 첫째 자리에서 반올림)

SELECT DNAME, AVG_SAL, SUM_SAL

FROM ( 

              SELECT DEPTNO, ROUND(AVG(SAL)) AVG_SAL, SUM(SAL) SUM_SAL

              FROM    EMP

              GROUP BY DEPTNO

           ) E, DEPT D

WHERE E.DEPTNO = D.DEPTNO

ORDER BY DNAME;

<실행결과>

	DNAME	AVG_SAL	SUM_SAL
1	ACCOUNTING	1875	3750
2	RESEARCH	2175	10875
3	SALES	1567	9400

ROUND 함수에 별다른 파라미터를 사용하지 않으면 소수이하 첫째 자리에서 반올림 합니다.

인라인 뷰의 모양은 FROM절 아래의 서브 쿼리 형태이며 이 부분이 바깥쪽 메인 쿼리보다 먼저 실행되고 DEPT 테이블과 조인 후 최종 결과가 추출 됩니다.

이번에는 인라인 뷰를 이용하지 않고 동일한 결과를 만들어 보겠습니다.

인라인뷰를 사용하지 않고 사원(EMP), 부서(DEPT) 테이블에서 부서별로 부서명, 급여의 평균, 급여의 합계를 출력 하세요. (부서명으로 오름차순 정렬, 급여 평균은 소수이하 첫째 자리에서 반올림)

SELECT DNAME, ROUND(AVG(SAL)) AVG_SAL, SUM(SAL) SUM_SAL

FROM EMP E, DEPT D

WHERE E.DEPTNO = D.DEPTNO

GROUP BY DNAME

ORDER BY DNAME;

<실행결과>

	DNAME	AVG_SAL	SUM_SAL
1	ACCOUNTING	1875	3750
2	RESEARCH	2175	10875
3	SALES	1567	9400

 

#inlineview, #인라인뷰, #오라클인라인뷰, #ORACLE, #오라클, #오라클교육, #ORACLE강좌

 

오라클 복합 뷰(Oracle Complex View)

복합 뷰complex view는 BASE TABLE이 하나 이상이고 조인, 함수, GROUP BY절이 포함될 수 있으며 INSERT/UPDATE/DELETE와 같은 DML 작업은 허용되지 않습니다.

EMP 테이블과 DEPT 테이블을 조인하여 복합 뷰를 생성하고 삭제하는 실습을 해보겠습니다.

뷰를 삭제해 보겠습니다.

실습

EMP, DEPT 두 테이블을 조인한 복합 뷰를 생성합니다.

EMP 테이블과 DEPT 테이블을 조인하여 10번 부서 사원들의 사번(empno), 이름(ename), 직무(job), 부서명(dname)을 정의하는 v_emp10 이라는 이름의 복합뷰를 작성 하시오. (사번, 이름, 직무 컬럼은 EMP 테이블에 있고 부서명 컬럼은 DEPT 테이블에 존재합니다.)

CREATE OR REPLACE VIEW V_EMP10

AS

SELECT EMPNO, ENAME, JOB, DNAME 

FROM    EMP, DEPT

WHERE EMP.DEPTNO = 10

AND       EMP.DEPTNO = DEPT.DEPTNO;

<실행결과>

View V_EMP10이(가) 생성되었습니다.

생성한 V_EMP10 뷰를 조회 합니다.

V_EMP10 뷰의 데이터를 확인 하세요.

SELECT * FROM V_EMP10;

<실행결과>

	EMPNO	ENAME	JOB	DNAME
1	7782	CLARK	MANAGER	ACCOUNTING
2	7934	MILLER	CLERK	ACCOUNTING

EMP  테이블에서 집합함수(SUM, MAX, MIN, AVG, COUNT), GROUP BY를 이용하여 복합 뷰를 생성해 보겠습니다.. 함수를 이용하여 뷰를 생성하는 경우 반드시 칼럼 별명을 사용해야 합니다

EMP 테이블에서 부서별 평균 급여와 급여의 합을 가지는 복합뷰 V_EMP_SUM_AVG를 생성 하세요

CREATE OR REPLACE VIEW V_EMP_SUM_AVG

AS

SELECT DEPTNO, AVG(SAL), SUM(SAL) 

FROM    EMP

GROUP BY DEPTNO;

<실행결과>

ORA-00998: 이 식은 열의 별명과 함께 지정해야 합니다.

AVG, SUM 함수를 사용했으므로 컬럼 별칭column alias을 사용해서 뷰의 컬럼명을 정의해야 합니다.

컬럼 별명을 사용해서 다시 생성해 보겠습니다.

EMP 테이블에서 부서별 평균 급여와 급여의 합을 가지는 복합뷰 V_EMP_SUM_AVG를 생성 하세요

CREATE OR REPLACE VIEW V_EMP_SUM_AVG

AS

SELECT DEPTNO, AVG(SAL) AVG, SUM(SAL) SUM

FROM EMP

GROUP BY DEPTNO;

<실행결과>

View V_EMP_SUM_AVG이(가) 생성되었습니다.

 

#복합뷰, #오라클뷰, #오라클, #ORACLE, #ComplexView, #오라클강의​

오라클ROWID(ORACLE ROWID)

오라클ROWID(ORACLE ROWID)

​

오라클 ROWID란?

- 간단한 이론 및 예문

 

https://www.youtube.com/watch?v=Jf_HnpbSd4g&list=PLxU-iZCqT52Dlz0zGgFiq1mBz21arFUHS&index=2&t=1s 

 

​

#ROWID, #ORACLEROWID, #오라클ROWID, #ORACLE, #오라클

오라클 INDEX, 오름차순/내림차순 인덱스(Ascending/Descending Index)

 

오라클 INDEX, 오름차순/내림차순 인덱스(Ascending/Descending Index)

인덱스index를 생성 할 때 칼럼명 다음에 ASC 또는 DESC를 기술하지 않으면 기본적으로 오름차순(ASCENDING)으로 인덱스가 생성되는데, 컬럼명 다음에 ASC 라고 기술하면 오름차순, DESC 라고 기술하면 내림차순(DESCENDING)으로 인덱스가 생성됩니다.

오름차순, 내림차순 인덱스 구분은 데이터 조회시 정렬(SORT) 이라는 중요한 기능을 수행 합니다.  인덱스가 내림차순으로 생성되어 있다면 인덱스를 경유하여 데이터를 조회하면 ORDER BY를 기술하지 않더라도 데이터는 내림차순 정렬되어 있을 것 입니다.

EMP 테이블의 데이터를 조회할 때 SELECT * FROM EMP 이런식으로 인덱스를 경유하지 않고 원본 테이블에서 조회 한다면 출력되는 데이터는 원본 데이터에서 입력된 순서대로 조회됩니다.

만약 전체 사원목록을 조회할 때 사원명으로 내림차순 조회하고 싶다면 대부분 다음처럼 쿼리 할 것 입니다.

SELECT * FROM EMP ORDER BY ENAME;

이때 EMP 테이블의 ename  컬럼에 인덱스가 있고 이를 경유 한다면 원본데이터를 메모리에 올려서 정렬하는 과정을 거치지 않아 다행이지만, 인덱스가 없다면 위 쿼리 구문의 실행을 위해 오라클은 해당 컬럼 값들을 메모리에 올려 정렬sort해야 하므로 대용량 테이블에서는 최악의 쿼리가 될 것 입니다. 실무에서 대용량 테이블인 경우 인덱스가 없는 컬럼에 대해서 절대 이런식으로 사용하시면 안됩니다. 

만약 인덱스가 생성되어 있다면 EMP 테이블의 데이터를 추출할 때 인덱스를 경유하도록 하면 데이터는 저절로 정렬되어 있다는 사실을 잘 생각해야 합니다. 이런 경우 일부러 ORDER BY를 사용하지 않아도 인덱스를 경유하도록 하면 데이터는 정렬되어 있을 것 입니다. 이장의 뒷부분에서 실습을 해보겠습니다.

실습에서 오름차순, 내림차순 인덱스를 생성하고 ORDER BY 구문을 사용하지 않아도 인덱스 영역을 경유 하도록 쿼리를 잘 사용하면 데이터는 정렬된다는 것을 실습해 보겠습니다.

실습

오름차순 인덱스를 생성해 보겠습니다.

EMP 테이블의 sal 컬럼에 비고유, 오름차순 인덱스를 생성하세요.

CREATE INDEX IDX_EMP_SAL ON EMP(SAL ASC);

<실행결과>

Index IDX_EMP_SAL이(가) 생성되었습니다.

컬럼명 다음에 ASC, DESC를 기술하지 않으면 ASC가 기본값 입니다. 위 구문은 다음 구문과 동일 합니다. CREATE INDEX IDX_EMP_SAL ON EMP(SAL)

실습

복합 컬럼에 오름차순, 내림차순 인덱스를 생성해 보겠습니다.

EMP 테이블에서 job 오름차순, sa 내림차순으로 인덱스를 생성 하세요.

CREATE INDEX IDX_EMP_JOB_SAL ON EMP(JOB, SAL DESC);

<실행결과>

Index IDX_EMP_JOB_SAL이(가) 생성되었습니다.

실습

테이블에 입력되는 순서대로 SELECT 시 조회된다는 사실을 실습을 통해 알아 보겠습니다.

TEST 테이블을 생성 후 3건의 데이터를 입력하고 조회해 보세요.

CREATE TABLE TEST (

A VARCHAR2(100) PRIMARY KEY,

b VARCHAR2(10));

INSERT INTO TEST VALUES ('1','111');

INSERT INTO TEST VALUES ('3','333');

INSERT INTO TEST VALUES ('2','222');

SELECT * FROM TEST;

<실행결과>

	A	B
1	1	111
2	3	333
3	2	222

INSERT 문에 의해 입력되는 순서대로 데이터가 조회 됩니다.

실습

ORDER BY 구문을 사용하지 않고도 정렬된 데이터를 추출해 보겠습니다.

현재  EMP 테이블의 ename 컬럼은 인덱스가 생성되어 있습니다.

EMP 테이블의 모든 데이터를 조회하세요.

SELECT EMPNO, ENAME FROM EMP;

<실행결과>

	EMPNO	ENAME
1	7369	SMITH
...	...	...
13	7902	FORD
14	7934	MILLER

EMP 테이블에 입력된 순서대로 조회 됩니다.

EMP 테이블에서 모든 사원의 empno, ename 컬럼 데이터를 조회하세요.(단 ename 오름차순으로 정렬)

SELECT EMPNO, ENAME FROM EMP ORDER BY ENAME;

<실행결과>

	EMPNO	ENAME
1	7876	ADAMS
...	...	...
13	7844	TURNER
14	7521	WARD

ename 컬럼을 기준으로 오름차순 정렬되어 조회 됩니다.

이번에는 ORDER BY 구문을 이용하지 않고 인덱스를 경유하도록 해서 데이터를 조회해 보겠습니다.

EMP 테이블에서 모든 사원의 empno, ename 컬럼 데이터를 조회하세요.(단 ename 오름차순으로 정렬)

SELECT EMPNO, ENAME FROM EMP WHERE ENAME > 'A';

<실행결과>

	EMPNO	ENAME
1	7876	ADAMS
...	...	...
13	7844	TURNER
14	7521	WARD

ename 컬럼을 기준으로 오름차순 정렬되어 조회 됩니다. 

ORDER BY를 사용하지 않았지만 오라클을 감동시켜 ename 인덱스를 경유 하도록 하기 위해 WHERE 절에 ename 컬럼을 일부러 출현시켰더니 오라클이 ename 인덱스를 경유하여 오름차순으로 데이터가 조회 되었습니다. 여기서 우리는 WHERE 절에 인덱스 컬럼을 출현시키면 오라클이 해당 컬럼의 인덱스 영역에서 데이터를 조회한다는 사실을 알았습니다.

사실 중요한 것은 ORDER BY를 사용했냐 안했냐가 아니라 내가 작성한 쿼리문이 적절한 인덱스를 경유하여 검색 속도에 문제가 없는지 입니다. 

ORDER BY는 인덱스가 없는 컬럼에 대해 사용하게 되면 100% 메모리에 올려 정렬이라는  과정을 거치게 되어 있으므로 대용량 테이블인 경우 조회 속도는 최악 입니다. 정말 주의 해야 합니다. 

물론 ORDER BY를 사용하더라도 컬럼에 인덱스가 있다면 원본 데이터를 정렬하는 것보다 대체로 인덱스 영역에서 데이터를 가지고 오니 큰 무리는 없습니다. 그러므로 ORDER BY를 사용했다면 오라클의 실행계획execution plan(SQL Developer에서 F12)을 반드시 확인하여 ORDER BY에 사용한 컬럼의 인덱스를 경유하는 지를 반드시 확인해야 합니다.

오라클에서 쿼리 실행전 중요한 구성요소중 하나는 오라클 옵티마이저oracle optimizer 입니다. 옵티마이저는 다양한 실행 경로를 생성하고 비용(COST)이 가장 적게드는 최적의 실행경로를 선택합니다.

실행계획은 오라클 옵티마이저(Oracle Optimizer)가 SQL문장의 실행을 위해 테이블의 데이터를 원본에서 하나씩 읽어서 접근할 것인지, 어떤 인덱스에서 랜덤 액세스를 통해 접근할 것인지, 어떤 SQL연산을 사용할 것인지, 어떤 조인순서로 조인을 하고, 어떤 조인을 사용할 것인지 등에 대해 계획을 수립한 것 입니다.

실습

이번에는 ename 컬럼의 인덱스를  내림차순으로 생성한 후 ename 내림차순으로 데이터를 조회해 보겠습니다.

ename 컬럼에 생성되어 있는 IDX_EMP_ENAME 인덱스를 삭제하고 내림차순 인덱스를 다시  생성하세요.

DROP INDEX IDX_EMP_NAME;

CREATE INDEX IDX_EMP_ENAME ON EMP(ENAME DESC);

EMP 테이블에서 모든 사원의 empno, ename 컬럼 데이터를 조회하세요.(단 ename 내림차순으로 정렬)

SELECT EMPNO, ENAME FROM EMP ORDER BY ENAME DESC;

<실행결과>

	EMPNO	ENAME
1	7521	WARD
...	...	...
13	7499	ALLEN
14	7876	ADAMS

ename 컬럼을 기준으로 내림차순 정렬되어 있습니다.

이번에는 ORDER BY 구문을 이용하지 않고 인덱스를 경유하도록 해서 데이터를 조회해 보겠습니다.

EMP 테이블에서 모든 사원의 empno, ename 컬럼 데이터를 조회하세요.(단 ename 내림차순으로 정렬)

SELECT EMPNO, ENAME FROM EMP WHERE ENAME > 'A';

<실행결과>

	EMPNO	ENAME
1	7521	WARD
...	...	...
13	7499	ALLEN
14	7876	ADAMS

ORDER BY를 사용하지 않았지만 WHERE절에 ename 컬럼을 출현시켜 오라클에게 ename 컬럼 인덱스를 경유하여 데이터를 추출하도록 했습니다. WHERE절에 컬럼을 출현시키는 방법이외에 오라클의 힌트 구문을 이용하여 원하는 인덱스를 경유하여 데이터를 추출하도록 할 수 있습니다.

 

#인덱스, #오름차순인덱스, #INDEX, #오라클인덱스, #오라클, #오라클강좌, #오라클교육, #ORACLE​