레이블이 인라인뷰인 게시물을 표시합니다. 모든 게시물 표시
레이블이 인라인뷰인 게시물을 표시합니다. 모든 게시물 표시

2021년 12월 11일 토요일

오라클TopN쿼리, 상위N명구하기, 오라클12 행제한구,인라인뷰, WITH구문, ROWNUM이용(오라클학원/ORACLE학원교육)

 

오라클TopN쿼리, 상위N명구하기, 오라클12 행제한구,인라인뷰, WITH구문, ROWNUM이용(오라클학원/ORACLE학원교육)

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=703 


오라클TopN쿼리, 상위N명구하기, 오라클12 행제한구,인라인뷰, WITH구문, ROWNUM이용

오라클TopN쿼리, 상위N명구하기, 오라클12 행제한구,인라인뷰, WITH구문, ROWNUM이용ORACLE SQL급여 상위 N명 구하기실습 : ORACLE 19C실습데이터 : http://ojc.asia/bbs/board.php?bo_table=LecHINT&wr_id=117&page=7실습 ; 사

ojc.asia


https://youtu.be/32YaMykHAbc



https://www.youtube.com/watch?v=BVeBkyIWUrU&list=PLxU-iZCqT52Dlz0zGgFiq1mBz21arFUHS&index=33 


ORACLE SQL




급여 상위 N명 구하기


실습 : ORACLE 19C



실습데이터 : http://ojc.asia/bbs/board.php?bo_table=LecHINT&wr_id=117&page=7



실습 ; 사원테이블에서 급여 상위3명을 추출 하세요.


실습테이블 소개

-- 2000만건

SELECT * FROM MYEMP1;

 

-- 급여 컬럼에 생성된 인덱스 확인

SELECT   A.INDEX_NAME, A.COLUMN_NAME, B.VISIBILITY

 FROM     USER_IND_COLUMNS A, USER_INDEXES B

 WHERE  A.TABLE_NAME = 'MYEMP1'

 AND        A.INDEX_NAME = B.INDEX_NAME

 AND        A.COLUMN_NAME = 'SAL';


IDX_MYEMP1_SAL SAL VISIBLE


1. 인라인뷰 & ROWNUM 이용


-- 아래처럼 하지 마세요!
-- ORDER BY는 맨 나중에 실행됩니다.
SELECT ENAME, SAL
FROM   MYEMP1
WHERE  ROWNUM < 8
ORDER BY SAL DESC;

-- 2.5초 SAL 컬럼 인덱스를 이용못하고 MYEMP1 테이블 FULL SCAN함
SELECT *
FROM (
    SELECT ENAME, 
                   SAL
    FROM   MYEMP1
    ORDER BY SAL DESC
    ) T1
WHERE ROWNUM < 8;

-- 0초, WHERE절에 SAL 컬럼이 출현함에 따라 SAL 컬럼 인덱스를 이용함
SELECT *
FROM (
    SELECT ENAME, 
                   SAL
    FROM     MYEMP1
    WHERE  SAL > 0
    ORDER BY SAL DESC
    ) T1
WHERE ROWNUM < 8;

-- 0초, 힌트이용, ORDER BY 생략
-- SAL 컬럼 인덱스가 오름차순으로 생성되어 있어서 역순으로 스캔
SELECT *
FROM (
    SELECT /*+ INDEX_DESC(MYEMP1 IDX_MYEMP1_SAL) */ 
           ENAME, 
           SAL
    FROM   MYEMP1
    WHERE  SAL > 0
    ) T1
WHERE ROWNUM < 8;




2.ORACLE 12C 행제한구 이용



-- 2.5초 SAL 컬럼 인덱스를 이용못하고 MYEMP1 테이블 FULL SCAN함
-- 중복되는 급여가 있더라도 무조건 6개 자름
SELECT ENAME, SAL
FROM   MYEMP1
ORDER BY SAL DESC
FETCH FIRST 7 ROWS ONLY;

-- 0초, SAL 컬럼 인덱스를 이용함
-- 중복되는 급여가 있더라도 무조건 6개 자름
SELECT ENAME, SAL
FROM   MYEMP1
WHERE  SAL > 0
ORDER BY SAL DESC
FETCH FIRST 7 ROWS ONLY;

-- 0초, 오라클 힌트 이용, SAL 컬럼 인덱스를 이용함
-- ORDER BY절 생략(힌트구문 때문에 인덱스 영역에서 역순으로 이미 가져오니 생략됨)
SELECT /*+ INDEX_DESC(MYEMP1 IDX_MYEMP1_SAL) */ 
               ENAME, SAL
FROM   MYEMP1
WHERE  SAL > 0
FETCH FIRST 7 ROWS ONLY;

-- 0초, SAL 컬럼 인덱스를 이용함
-- 중복되는 급여가 있으면 모두 SELECT함
SELECT ENAME, SAL
FROM   MYEMP1
WHERE  SAL > 0
ORDER BY SAL DESC
FETCH FIRST 4 ROWS WITH TIES;



3.WITH구문과 ROWNUM 이용



-- 2.5초 SAL 컬럼 인덱스를 이용못하고 MYEMP1 테이블 FULL SCAN함
WITH A AS
  ( SELECT ENAME, SAL
    FROM   MYEMP1
    ORDER BY SAL DESC)
SELECT ENAME, SAL
FROM   A
WHERE  rownum <= 3;

-- 0초, WHERE절에 SAL 컬럼이 출현함에 따라 SAL 컬럼 인덱스를 이용함
WITH A AS
  ( SELECT ENAME, SAL
    FROM   MYEMP1
    WHERE  SAL > 0
    ORDER BY SAL DESC)
SELECT ENAME, SAL
FROM   A
WHERE  rownum <= 3;

-- 0초, 힌트이용, ORDER BY 생략
-- SAL 컬럼 인덱스가 오름차순으로 생성되어 있어서 역순으로 스캔
WITH A AS
  ( SELECT /*+ INDEX_DESC(MYEMP1 IDX_MYEMP1_SAL) */ 
           ENAME, SAL
    FROM   MYEMP1
    WHERE  SAL > 0 )
SELECT ENAME, SAL
FROM   A
WHERE  rownum <= 3;

 ​

#TopN쿼리, #상위N명쿼리, #오라클교육, #WITH구문, #인라인뷰, #행제한구문, #오라클강의, #오라클학원, #ORACLE교육, #ORACLE동영상, #SQL교육, #SQL학원, TopN쿼리, 상위N명쿼리, 오라클교육, WITH구문, 인라인뷰, 행제한구문, 오라클강의, 오라클학원, ORACLE교육, ORACLE동영상, SQL교육, SQL학원, 

- 댓글 없음:
라벨: , , , , , , ,

2021년 12월 3일 금요일

(오라클동영상학원교육, 오라클학원가보세요)오라클, SQL 동영상강의, 인라인 뷰(Oracle Inline View)

 (오라클동영상학원교육, 오라클학원가보세요)오라클, SQL 동영상강의, 인라인 뷰(Oracle Inline View)

이미지 썸네일 삭제
오라클, SQL 동영상 강의, 인라인 뷰(Oracle Inline View)

오라클, SQL 동영상강의, 인라인 뷰(Oracle Inline View)인라인뷰란?인라인뷰를 이용한 데이터 수정 및 삭제강의 : 오라클자바커뮤니티(http://ojc.asia)실습데이터 생성 http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=654&sfl=wr_subject&stx…

ojc.asia




인라인뷰란?

인라인뷰를 이용한 데이터 수정 및 삭제


강의 : 오라클자바커뮤니티(http://ojc.asia)


실습데이터 생성 http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=654&sfl=wr_subject&stx=%EC%8B%A4%EC%8A%B5&sop=and



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


  • 0열 선택0열 다음에 열 추가
  • 0행 선택0행 다음에 행 추가
셀 전체 선택
열 너비 조절
행 높이 조절
SELECT 컬럼리스트 FROM 테이블;


SELECT 컬럼리스트
FROM
(
SELECT *
FROM 테이블
) t;
  • 셀 병합
  • 행 분할
  • 열 분할
  • 너비 맞춤
  • 삭제



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


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


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

실습


  • 0열 선택0열 다음에 열 추가
  • 0행 선택0행 다음에 행 추가
셀 전체 선택
열 너비 조절
행 높이 조절
실습을 위한 사원(EMP), 부서(DEPT) 테이블 데이터를 확인


인라인뷰를 사용하여 사원(EMP), 부서(DEPT) 테이블에서 부서별로 부서명, 급여의 평균, 급여의 합계를 출력 하세요. (부서명으로 오름차순 정렬, 급여 평균은 소수이하 첫째 자리에서 반올림)
  • 셀 병합
  • 행 분할
  • 열 분할
  • 너비 맞춤
  • 삭제


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


SELECT DNAME, AVG_SAL, SUM_SAL

FROM (

-- EMP 테이블에는 부서명이 없다.

-- 부서코드별 급여의 평균과 합을 구한다.

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

FROM EMP

GROUP BY DEPTNO

) E, DEPT D

WHERE E.DEPTNO = D.DEPTNO --부서명을 가져오기 위해 DEPT 테이블과 조인을 한다.

ORDER BY DNAME;


<실행결과>

  • 0열 선택0열 다음에 열 추가
  • 1열 선택1열 다음에 열 추가
  • 2열 선택2열 다음에 열 추가
  • 3열 선택3열 다음에 열 추가
  • 0행 선택0행 다음에 행 추가
  • 1행 선택1행 다음에 행 추가
  • 2행 선택2행 다음에 행 추가
  • 3행 선택3행 다음에 행 추가
셀 전체 선택
열 너비 조절
행 높이 조절
　
DNAME
AVG_SAL
SUM_SAL
1
ACCOUNTING
1875
3750
2
RESEARCH
2175
10875
3
SALES
1567
9400
  • 셀 병합
  • 행 분할
  • 열 분할
  • 너비 맞춤
  • 삭제


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


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

실습


인라인 뷰를 이용하여 데이터를 수정해 봅니다.


  • 0열 선택0열 다음에 열 추가
  • 0행 선택0행 다음에 행 추가
셀 전체 선택
열 너비 조절
행 높이 조절
인라인뷰를 사용하여 ‘SALES’ 부서원들의 급여를 100 증가 하세요.
  • 셀 병합
  • 행 분할
  • 열 분할
  • 너비 맞춤
  • 삭제


-- SALES 부서원들의 급여를 확인 합니다.

SELECT EMP.ENAME, SAL

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO

AND DEPT.DNAME = 'SALES';


-- 인라인뷰를 이용해서 SALES 부서원들의 급여를 100증가 합니다.

UPDATE

(

SELECT SAL

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO

AND DEPT.DNAME = 'SALES'

)

SET SAL = SAL + 100;


-- SALES 부서원들의 급여가 100 증가됨을 확인 합니다.

SELECT EMP.ENAME, SAL

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO

AND DEPT.DNAME = 'SALES';


다음 실습을 위해 변경 사항을 COMMIT 하지않고 ROLLBACK하여 변경사항을 원복 합니다.


ROLLBACK;


실습


인라인 뷰를 이용하여 데이터를 삭제해 봅니다.


  • 0열 선택0열 다음에 열 추가
  • 0행 선택0행 다음에 행 추가
셀 전체 선택
열 너비 조절
행 높이 조절
인라인뷰를 사용하여 EMP 테이블에서 ‘SALES’ 부서원들 삭제하세요.
  • 셀 병합
  • 행 분할
  • 열 분할
  • 너비 맞춤
  • 삭제


-- SALES 부서원들을 확인 합니다. 6명의 사원이 확인 됩니다.

SELECT EMP.EMPNO, EMP.ENAME, DEPT.DNAME

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO

AND DEPT.DNAME = 'SALES';


-- 인라인뷰를 이용해서 SALES 부서원을 삭제 합니다.

DELETE

(

SELECT EMP.*

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO

AND DEPT.DNAME = 'SALES'

);


-- SALES 부서원들을 확인 합니다. 0명의 사원이 확인 됩니다.

SELECT EMP.EMPNO, EMP.ENAME, DEPT.DNAME

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO

AND DEPT.DNAME = 'SALES';



다음 실습을 위해 변경 사항을 COMMIT 하지않고 ROLLBACK하여 변경사항을 원복 합니다.


ROLLBACK;



#인라인뷰, #오라클인라인뷰, #SQL인라인뷰, #inlineview, #오라클학원, #SQL학원, #SQL교육, #오라클교육, #ORACLE교육, #ORACLE학원, #ORACLE동영상, #ORACLE강좌, #오라클학원동영상, #SQL학원동영상,인라인뷰, 오라클인라인뷰, SQL인라인뷰, inlineview, 오라클학원, SQL학원, SQL교육, 오라클교육, ORACLE교육, ORACLE학원, ORACLE동영상, ORACLE강좌, 오라클학원동영상, SQL학원동영상



- 댓글 없음:
라벨: , , , , , , , , , , ,

2020년 7월 19일 일요일

오라클, SQL, 인라인뷰(IN_LINE VIEW)

오라클, SQL, 인라인뷰(IN_LINE VIEW)

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=617

5.5 인라인뷰(IN_LINE VIEW)

서브 쿼리는 FROM절에서도 가능 한데 이와 같은 것을 뷰((View)는 뷰(View)인데 Create 명령어로 만들지 않고 해당 라인에 직접 기술한다고 해서 하여 인라인 뷰(InLine View)라고 일컫는다. 인라인 뷰는 Create 명령어를 이용하여 만들지 않으므로 필요한 시점에만 사용된다는 특징이 있다. 가령 어떤 테이블의 데이터 건수, 칼럼 개수가 많다고 했을 때 FROM절 다음에 전체 테이블을 기술 하면 쿼리실행시 수행속도에 악 영향을 미칠 수가 있으므로 FROM절 뒤에 테이블 데이터 중 필요한 행과 열만 선택 한다면 좀더 효율적인 쿼리 할 수 있을 것이다. 또한 인라인뷰는 조인 연산을 줄이기 위해 또는 분리된 쿼리를 하나의 쿼리에 모아서 사용하기 위해 사용한다.

--EMP 테이블에서 직무가 “SALESMAN”인 사람들의 이름, 부서명, 직무를 출력하는 예문

SQL> select ename, dname, job from (select ename, job, deptno from emp

2 where job = 'SALESMAN') e, dept d

3 where e.deptno = d.deptno;

ENAME DNAME JOB

---------- -------------- ---------

ALLEN SALES SALESMAN

WARD SALES SALESMAN

MARTIN SALES SALESMAN

TURNER SALES SALESMAN

--아래의 경우 인라인뷰를 이용하지 않은 예문이다.

SQL> select ename, dname, job from emp e, dept d

2 where e.job = 'SALESMAN'

3 and e.deptno = d.deptno;

ENAME DNAME JOB

---------- -------------- ---------

ALLEN SALES SALESMAN

WARD SALES SALESMAN

MARTIN SALES SALESMAN

TURNER SALES SALESMAN

--이번에는 WITH문을 이용해 보자.

SQL> with emp2 as (

2 select ename, job, deptno from emp

3 where job = 'SALESMAN'

4 )

5 select ename, dname, job from emp2 e, dept d

6 where e.deptno = d.deptno;

ENAME DNAME JOB

---------- -------------- ---------------

ALLEN SALES SALESMAN

TURNER SALES SALESMAN

MARTIN SALES SALESMAN

WARD SALES SALESMAN

- 댓글 없음:
라벨:
피드 구독하기: 글 (Atom)

(C#교육동영상)C# ADO.NET 실습 ODP.NET/ODAC 설치 오라클 함수 호출 실습, C#학원, WPF학원, 닷넷학원, 자바학원

  (C#교육동영상)C# ADO.NET 실습  ODP.NET/ODAC 설치  오라클 함수 호출 실습, C#학원, WPF학원, 닷넷학원, 자바학원 https://www.youtube.com/watch?v=qIPU85yAlzc&list=PLxU-i...