당신은 주제를 찾고 있습니까 “오라클 with – (추천SQL강좌/추천SQL교육)오라클 SQL기초 강좌#4, WITH구문 이란?(오라클교육/SQL교육/오라클동영상/SQL동영상/오라클강의/SQL강의/오라클강좌/SQL강좌)“? 다음 카테고리의 웹사이트 you.maxfit.vn 에서 귀하의 모든 질문에 답변해 드립니다: https://you.maxfit.vn/blog. 바로 아래에서 답을 찾을 수 있습니다. 작성자 오라클자바커뮤니티 이(가) 작성한 기사에는 조회수 3,296회 및 좋아요 28개 개의 좋아요가 있습니다.
오라클 with 주제에 대한 동영상 보기
여기에서 이 주제에 대한 비디오를 시청하십시오. 주의 깊게 살펴보고 읽고 있는 내용에 대한 피드백을 제공하세요!
d여기에서 (추천SQL강좌/추천SQL교육)오라클 SQL기초 강좌#4, WITH구문 이란?(오라클교육/SQL교육/오라클동영상/SQL동영상/오라클강의/SQL강의/오라클강좌/SQL강좌) – 오라클 with 주제에 대한 세부정보를 참조하세요
오라클 SQL기초 강좌#4, WITH구문 이란?
– 간단한 이론 및 실습
SQL에서 자주 사용되는 WITH 구문에 대한 간단한 이론 및 실습 영상 입니다.
오라클 with 주제에 대한 자세한 내용은 여기를 참조하세요.
[Oracle] 오라클 WITH절 사용법 & 예제 (임시 테이블 만들기)
WITH절이란 WITH절은 오라클9 이후 버전부터 사용이 가능하며 이름이 부여된 서브쿼리라고 생각하시면 됩니다. 임시테이블을 만든다는 관점에서본다면 …
Source: coding-factory.tistory.com
Date Published: 8/23/2022
View: 7909
[Oracle] 오라클 WITH 절 사용법 및 동작방식 (임시 테이블)
오라클 9i R2부터 WITH 절을 사용할 수 있도록 기능이 추가되었다. WITH 절은 임시 테이블 또는 가상 테이블이라고 생각하면 된다.
Source: gent.tistory.com
Date Published: 7/17/2022
View: 8380
[Oracle]WITH절 사용법 및 예제(임시테이블) – 할매도 코딩한다
WITH절은 다른 DBMS에서도 많이 지원하는 기능이나 오라클에서는 WITH절에 정의된 SQL 문장으로 오라클 공유 메모리에 임시 테이블을 생성하여 반복 …
Source: grandma-coding.tistory.com
Date Published: 3/19/2022
View: 724
Oracle WITH절 기본 사용법 및 응용 1
List · 예제 테이블 생성. 샘플 테이블 생성 · 1. 오라클 WITH 절 · 2. 오라클 WITH 기본사용법. 가. 기본 문법; 나. 사용예제 · 3. WITH절 사용 권고사항. 가 …
Source: engineering-skcc.github.io
Date Published: 12/10/2022
View: 7524
[오라클] WITH문 사용법 및 예제 – Dev. MG
WITH는 오라클9이상 지원. [장점]. 같은 서브쿼리가 여러 번 사용된다면, WITH구문을 사용하는 것이 가독성이 좋다. WITH구문이 여러 번 사용될 때 …
Source: devmg.tistory.com
Date Published: 6/30/2022
View: 9939
[ORACLE] 오라클_WITH 구문 사용하기(feat.서브쿼리)
– 오라클 9버전 이상에서 지원하는 구문이다. – WITH 구문을 사용한 쿼리는 인라인뷰나 임시 테이블로 여긴다. 예제 1) WITH 구문의 일반적인 …
Source: mine-it-record.tistory.com
Date Published: 9/5/2021
View: 9809
[오라클 SQL] 오라클 WITH 구문 사용 방법 및 예제 – 범범조조
– 각 쿼리 Block의 이름을 지정할 수 있습니다. – 오라클 옵티마이저는 WITH 구문으로 선언한 쿼리를 임시 테이블로 봅니다.
Source: afsdzvcx123.tistory.com
Date Published: 7/3/2022
View: 1056
[ORACLE] 오라클 WITH 구문을 사용해 가상테이블 만드는 방법
오라클 쿼리를 작성하다 보면 쿼리문 내에서 동일한 구문의 쿼리문이 반복해서 동일한 테이블에 엑세스하는 경우가 있습니다. WITH 구문은 그런 …
Source: cross-the-line.tistory.com
Date Published: 8/24/2021
View: 8961
[Oracle] With절 사용법 및 예시 (임시 테이블) – 未生
WITH절은 오라클9 이후 사용이 가능한 이름이 부여된 서브쿼리라고 생각하시면 됩니다. 임시테이블을 만든다는 관점에서 본다면 VIEW와 쓰임새가 비슷 …
Source: vanwolha.tistory.com
Date Published: 4/16/2022
View: 1133
오라클 SQL과 PL/SQL을 다루는 기술: 02 WITH 절
02 | WITH 절. 개선된 서브 쿼리. 6장에서 여러 형태의 서브 쿼리를 학습했었는데 서브 쿼리에는 한 가지 제약 사항이있다. 예를 들어, kor_loan_status 테이블에서 …
Source: thebook.io
Date Published: 10/16/2021
View: 6868
주제와 관련된 이미지 오라클 with
주제와 관련된 더 많은 사진을 참조하십시오 (추천SQL강좌/추천SQL교육)오라클 SQL기초 강좌#4, WITH구문 이란?(오라클교육/SQL교육/오라클동영상/SQL동영상/오라클강의/SQL강의/오라클강좌/SQL강좌). 댓글에서 더 많은 관련 이미지를 보거나 필요한 경우 더 많은 관련 기사를 볼 수 있습니다.
주제에 대한 기사 평가 오라클 with
- Author: 오라클자바커뮤니티
- Views: 조회수 3,296회
- Likes: 좋아요 28개
- Date Published: 2018. 12. 18.
- Video Url link: https://www.youtube.com/watch?v=Dtp2BypLwsM
[Oracle] 오라클 WITH절 사용법 & 예제 (임시 테이블 만들기)
WITH절이란
WITH절은 오라클9 이후 버전부터 사용이 가능하며 이름이 부여된 서브쿼리라고 생각하시면 됩니다. 임시테이블을 만든다는 관점에서본다면 VIEW와 쓰임새가 비슷한데 차이점이 있다면 VIEW는 한번 만들어놓으면 DROP할때까지 없어지지 않지만 WITH절의 같은경우 한번 실행할 쿼리문내에 정의되어 있을경우, 그 쿼리문안에서만 실행된다는 차이점이 있습니다.
WITH절을 사용하는 이유
WITH절은 복잡한 SQL에서 동일 블록에 대해 반복적으로 SQL문을 사용하는 경우 그 블록에 이름을 부여하여 재사용 할 수 있게 함으로서 쿼리 성능을 높일 수 있는데 WITH절을 이용하여 미리 이름을 부여해서 Query Block을 만들 수 있습니다. 자주 실행되는 경우 한번만 Parsing되고 Plan 계획이 수립되므로 쿼리의 성능향상에 도움이 됩니다.
WITH절 사용법
WITH EXAMPLE AS ( SELECT ‘WITH절’ AS STR1 FROM DUAL ) SELECT * FROM EXAMPLE
최대한 간단하게 WITH절을 사용해봤습니다. WITH절의 안의 내용을 EXAMPLE이라는 것으로 명칭을 지어 사용하는 개념입니다.
다중 WITH문
WITH EXAMPLE1 AS ( –첫번째 WITH문 SELECT ‘EX1’ A FROM DUAL UNION ALL SELECT ‘EX2’ FROM DUAL ), EXAMPLE2 AS ( –두번째 WITH문 SELECT ‘EX3’ A FROM DUAL UNION ALL SELECT A FROM EXAMPLE1 –첫번째 WITH문 참조 ) SELECT * FROM EXAMPLE2
위와같이 한 쿼리문안에 WITH절을 여러개 쓰는것도 가능하며 WITH절안에서 다른 WITH절을 참조하는것도 가능합니다.
WITH절에 정의된 쿼리는 여러번 사용할수록 효율이 증가합니다.
WITH EX AS ( SELECT * FROM PRODUCTS WHERE STANDARD_COST > 1000 ) SELECT * FROM EX WHERE EX.CATEGORY_ID = ‘1’ UNION ALL SELECT * FROM EX WHERE EX.CATEGORY_ID = ‘2’ UNION ALL SELECT * FROM EX WHERE EX.CATEGORY_ID = ‘3’
WITH절에 정의된 내용을 한번만 사용한다면 서브쿼리를 사용하는것과 크게 성능차이가 나지 않습니다. WITH문의 가장 큰장점은 한번 WITH절의 내용을 한번에 올려놓고 계속 재사용한다는것에 큰 의미가 있기에 WITH절에 구문을 여러번 참조하는 쿼리를 만들수록 그 효과가 배로 증가합니다.
[Oracle] 오라클 WITH 절 사용법 및 동작방식 (임시 테이블)
오라클 9i R2부터 WITH 절을 사용할 수 있도록 기능이 추가되었다. WITH 절은 임시 테이블 또는 가상 테이블이라고 생각하면 된다. 반복되는 서브쿼리 블록을 하나의 WITH 절 블록으로 만들어서 사용하거나, 긴 문장의 서브 쿼리를 WITH 절로 만들어서 SELECT 문의 가독성을 높일 수 있다.
오라클 12c부터 WITH 절에 함수와 프로시저를 선언할 수 있으니 아래를 참고하면 된다.
WITH 절 사용법
WITH emp_w1 AS ( SELECT deptno , SUM(sal) AS sal FROM emp GROUP BY deptno ) SELECT a.deptno , b.dname , a.sal FROM emp_w1 a , dept b WHERE a.deptno = b.deptno
서브쿼리를 WITH 절로 만들어서 테이블을 사용하듯 사용할 수 있다. 특히 여러 번 반복되는 서브쿼리를 WITH 절로 만들어서 사용하면 쿼리의 성능을 높일 수 있다.
오라클 11g R1까지는 WITH 절에 선언된 임시 테이블을 SELECT 문에 사용하지 않으면 오류가 발생하였지만, 11g R2부터는 선언 후 사용하지 않아도 오류가 발생하지 않는다.
다중 WITH 절 사용법 (여러 개)
WITH emp_w1 AS ( SELECT deptno , AVG(sal) AS sal_avg FROM emp GROUP BY deptno ), emp_w2 AS ( SELECT job , AVG(sal) AS sal_avg FROM emp GROUP BY job ) SELECT a.ename , a.job , a.sal , b.sal_avg AS dept_avg , c.sal_avg AS job_avg FROM emp a , emp_w1 b , emp_w2 c WHERE a.empno = 7788 AND a.deptno = b.deptno AND a.job = c.job
WITH 절에 여러 개의 임시 테이블을 만들고 싶을 때는 쉼표(“, “)를 찍고 이어서 선언하면 된다.
WITH 절 함수, 프로시저 선언 방법
WITH FUNCTION fn_get_dept_name(p_deptno IN NUMBER) RETURN VARCHAR2 IS v_dname VARCHAR2(14); BEGIN SELECT dname INTO v_dname FROM dept WHERE deptno = p_deptno; RETURN v_dname; END; SELECT ename , deptno , fn_get_dept_name(deptno) AS dname FROM emp WHERE empno = 7788
오라클 12c부터 WITH 절에 함수를 선언할 수 있으며, 선언된 함수는 SELECT 문에서 사용할 수 있다.
WITH PROCEDURE with_procedure(p_dname IN VARCHAR) IS BEGIN DBMS_OUTPUT.put_line(‘p_dname=’ || p_dname); END; FUNCTION fn_get_dept_name(p_deptno IN NUMBER) RETURN VARCHAR2 IS v_dname VARCHAR2(100); BEGIN SELECT dname INTO v_dname FROM dept WHERE deptno = p_deptno; — with 절에 선언된 프로시저 호출 가능 with_procedure(v_dname); RETURN v_dname; END; SELECT ename , deptno , fn_get_dept_name(deptno) AS dname FROM emp WHERE empno = 7788
프로시저도 선언이 가능하며 프로시저는 SELECT 문에는 사용할 수 없으며, WITH 절에 선언된 함수 내부에서만 사용이 가능하다.
WITH 절 동작 방식 (힌트 사용)
오라클의 WITH 절은 Materialize과 Inline View 2가지의 동작 방식이 있다.
Materialize 방식 : 임시 테이블을 생성 후 WITH 절의 결과를 저장하며, 반복 호출 시 쿼리를 실행하지 않고 임시 테이블에 저장된 결과를 사용한다.
Inline 방식 : 임시 테이블을 생성하지 않고 Inline View 형태로 수행하며, 참조된 횟수만큼 반복적으로 쿼리를 실행한다.
오라클 11g 이전에는 옵티마이저가 동작 방식을 결정하였으며, 11g부터는 직접 힌트를 사용하여 동작 방식을 바꿀 수 있다. (옵티마이저 결정 방식: 2번 이상 수행되면 Materialize, 1회만 수행되면 Inline View)
Materialize 방식은 서브 쿼리의 조회 시간이 오래 걸리고 WITH 절의 임시 테이블을 여러 번 반복해서 사용한다면 성능을 높일 수 있는 방식이지만, 임시 테이블을 Create 하고 Drop 하는 행위를 반복하기 때문에 자주 수행되는 쿼리문에 사용하면 시스템에 부하가 생길 수 있다.
대부분 WITH 절을 사용할 때는 힌트를 사용하지 않고 옵티마이저 결정 방식으로 사용하지만, WITH 절의 동작 방식을 이해했다면 힌트를 사용하여 조금 더 효율 적으로 쿼리문을 작성할 수 있을 것이다.
Materialize 힌트
WITH emp_w1 AS ( SELECT /*+ materialize */ deptno , SUM(sal) AS sal FROM emp GROUP BY deptno ) SELECT a.deptno , b.dname , a.sal FROM emp_w1 a , dept b WHERE a.deptno = b.deptno
Inline 힌트
WITH emp_w1 AS ( SELECT /*+ inline */ deptno , SUM(sal) AS sal FROM emp GROUP BY deptno ) SELECT a.deptno , b.dname , a.sal FROM emp_w1 a , dept b WHERE a.deptno = b.deptno
동일한 WITH 절을 사용한 쿼리문 이지만 힌트에 따라서 쿼리문의 수행 방식이 달라지는 것을 확인할 수 있다.
[Oracle]WITH절 사용법 및 예제(임시테이블)
WITH절 이란?
WITH절은 다른 DBMS에서도 많이 지원하는 기능이나 오라클에서는 WITH절에 정의된 SQL 문장으로 오라클 공유 메모리에 임시 테이블을 생성하여 반복 재사용이 가능하도록 할 수 있다. 그렇게 하면 동일 테이블 접근을 최소화하며 메모리에 생성된 임시 테이블에서 필요한 데이터를 메모리로 접근하기 때문에 디스크 IO로 테이블에 접근하는 것보다 성능을 개선할 수 있다.
WITH절을 완전히 숙지하려면 UNION ALL, DECODE/CASE, WITH, ROLLUP/Grouping Sets 등의 사용법을 알아야 한다. 왜냐하면 위의 문장들은 서로 형태 변환이 가능하여 성능상 매우 유리하게 사용될 수 있기 때문이다.
WITH절을 사용하는 이유?
WITH절은 복잡한 SQL에서 동일 블록에 대해 반복적으로 SQL문을 사용하는 경우 그 블록에 이름을 부여하여 재사용할 수 있게 함으로써 쿼리 성능을 높일 수 있는데 WITH절을 이용하여 미리 이름을 부여해서 Query Block을 만들 수 있다. 자주 실행되는 경우 한 번만 Parsing되고 Plan 계획이 수립되므로 쿼리의 성능 향상에 도움이 된다.
WITH절 사용법
WITH EXAMPLE AS
(
SELECT ‘WITH절’ AS STR1
FROM DUAL
)
SELECT *
FROM EXAMPLE
위 쿼리는 WITH절을 이용한 아주 간단한 예제이다.
서브쿼리를 WITH 절로 만들어서 테이블을 사용하듯 사용할 수 있다.
특히 여러 번 반복되는 서브쿼리를 WITH 절로 만들어서 사용하면 쿼리의 성능을 높일 수 있다.
출처: 젠트의 프로그래밍 세상
오라클 11g R1까지는 WITH 절에 선언된 임시 테이블을 SELECT 문에 사용하지 않으면 오류가 발생하였지만,
11g R2부터는 선언 후 사용하지 않아도 오류가 발생하지 않는다.
WITH절에 정의된 쿼리는 여러번 사용할수록 효율이 증가한다.
WITH EX AS
(
SELECT *
FROM PRODUCTS
WHERE STANDARD_COST > 1000
)
SELECT * FROM EX WHERE EX.CATEGORY_ID = ‘1’
UNION ALL
SELECT * FROM EX WHERE EX.CATEGORY_ID = ‘2’
UNION ALL
SELECT * FROM EX WHERE EX.CATEGORY_ID = ‘3’
WITH절에 정의된 내용을 한번만 사용한다면 서브 쿼리를 사용하는 것과 크게 성능 차이가 나지 않는다. WITH문의 가장 큰 장점은 한 번 WITH절의 내용을 한 번에 올려놓고 계속 재사용한다는 것에 큰 의미가 있기에 WITH절에 구문을 여러 번 참조하는 쿼리를 만들수록 그 효과가 배로 증가한다.
Oracle WITH절 기본 사용법 및 응용 1
– 사용법 : 동일한 서브쿼리의 결과가 반복적으로 사용될때 비효율성을 방지하기위해 사용합니다 가. 단독사용시 – 원문장 SELECT * FROM ( SELECT 1 AS NO, ‘홍길동’ AS NAME FROM DUAL UNION ALL SELECT 2 AS NO, ‘이길동’ AS NAME FROM DUAL ) ; – WITH절 변경 WITH TMP AS ( SELECT 1 AS NO, ‘홍길동’ AS NAME FROM DUAL UNION ALL SELECT 2 AS NO, ‘이길동’ AS NAME FROM DUAL ) SELECT * FROM TMP ; 나. 복수 사용시 WITH TMP_A AS ( SELECT 1 AS NO, ‘홍길동’ AS NAME FROM DUAL UNION ALL SELECT 2 AS NO, ‘이길동’ AS NAME FROM DUAL ) , TMP_B AS ( SELECT 1 AS NO, ‘010-8888-1234’ AS TEL FROM DUAL UNION ALL SELECT 2 AS NO, ‘010-1234-1234’ AS TEL FROM DUAL ) SELECT A.NO, A.NAME, B.TELFROM TMP_A A, TMP_B B WHERE A.NO = B.NO ;
[오라클] WITH문 사용법 및 예제
반응형
WITH절은 서브쿼리가 여러 번 다시 사용될 때 편리하게 사용 (성능면에서도 좋음)
FROM에서 어떠한 조건을 가진 테이블을 불러올 때, 그 조건을 가진 테이블 자체에 이름을 붙여서, 별도의 테이블로 인식하게 한다.
WITH는 오라클9이상 지원
[장점]같은 서브쿼리가 여러 번 사용된다면, WITH구문을 사용하는 것이 가독성이 좋다.
WITH구문이 여러 번 사용될 때 성능적으로도 좋음
[단점]SELECT를 사용할 때는 보기 편한 장점이 있지만 INSERT나 UPDATE, DELETE 처럼
직접 데이터에 변경이 있는 구문에 WITH구문을 ‘사용할 수는 있지만’
WITH 구문을 통째로 넣어야하기 때문에 비효율적 이다. ( WITH를 사용하는 의미가 없다. )
만약 직접 데이터의 변경이 있을 것 같을때는 사용하지 않는 편이 좋음.
[예제]1. 예제를 위한 테이블 생성, 데이터 삽입
CREATE TABLE BBS1 (SEQ NUMBER, TITLE VARCHAR2(20), CONTENTS VARCHAR2(20));
CREATE TABLE BBS2 (SEQ NUMBER, TITLE VARCHAR2(20), CONTENTS VARCHAR2(20));
CREATE TABLE BBS3 (SEQ NUMBER, TITLE VARCHAR2(20), CONTENTS VARCHAR2(20));
commit
INSERT INTO BBS1 (SEQ,TITLE,CONTENTS) VALUES(1,’AAA’,’가’);
INSERT INTO BBS1 (SEQ,TITLE,CONTENTS) VALUES(2,’BBB’,’나’);
INSERT INTO BBS1 (SEQ,TITLE,CONTENTS) VALUES(3,’CCC’,’다’);
INSERT INTO BBS2 (SEQ,TITLE,CONTENTS) VALUES(1,’DDD’,’라’);
INSERT INTO BBS2 (SEQ,TITLE,CONTENTS) VALUES(2,’EEE’,’마’);
INSERT INTO BBS2 (SEQ,TITLE,CONTENTS) VALUES(3,’FFF’,’바’);
INSERT INTO BBS3 (SEQ,TITLE,CONTENTS) VALUES(1,’GGG’,’사’);
INSERT INTO BBS3 (SEQ,TITLE,CONTENTS) VALUES(2,’HHH’,’아’);
INSERT INTO BBS3 (SEQ,TITLE,CONTENTS) VALUES(3,’III’,’자’);
commit
2. WITH구문 – 1개만 있을 때
<사용법> WITH [WITH절 명칭] AS (SELECT [컬럼명] from [테이블명]) SELECT [컬럼명] FROM [WITH절 명칭]
<예제>
WITH WITH_BBS1 AS ( SELECT SEQ, TITLE FROM BBS1 WHERE SEQ = 1 ) select C.SEQ AS BBS1_SEQ , C.TITLE AS BBS1_TITLE , A.TITLE AS BBS2_TITLE , A.CONTENTS AS BBS2_CONTENTS , B.TITLE AS BBS3_TITLE , B.CONTENTS AS BBS3_CONTENTS from BBS2 A, BBS3 B, WITH_BBS1 C where A.SEQ = B.SEQ;
<결과>
*부가설명 : WITH_BBS1에서는 SEQ가 1인 값만 가져왔으므로 1과 AAA만 있지만 BBS2와BBS3 갯수에 의해 카테시안곱으로 표현되었다.
3. WITH구문 – 여러개(2개 이상)
<사용법> WITH [WITH절 명칭-1] AS (SELECT [컬럼명-1] FROM [테이블명-1]), [WITH절 명칭-2] AS (SELECT [컬럼명-2] FROM [테이블명-2]) SELECT ALIAS명-1.[컬럼명-1] , ALIAS명-2.[컬럼명-2] FROM [WITH절 명칭-1] ALIAS명-1 , [WITH절 명칭-2] ALIAS명-2
<예제>
WITH WITH_BBS1 AS ( SELECT SEQ, TITLE, CONTENTS FROM BBS1) , WITH_BBS2 AS ( SELECT SEQ, TITLE, CONTENTS FROM BBS2) , WITH_BBS3 AS ( SELECT SEQ, TITLE, CONTENTS FROM BBS3) select A.SEQ BBS1_SEQ , A.TITLE BBS1_TITLE , A.CONTENTS BBS1_CONTENTS , B.SEQ BBS2_SEQ , B.TITLE BBS2_TITLE , B.CONTENTS BBS2_CONTENTS , C.SEQ BBS3_SEQ , C.TITLE BBS3_TITLE , C.CONTENTS BBS3_CONTENTS from WITH_BBS1 A, WITH_BBS2 B, WITH_BBS3 C where A.SEQ = B.SEQ AND A.SEQ = C.SEQ; *부가설명 : A,B,C의 SEQ가 같은 값끼리 가져온다.
4. INSERT안에 WITH 사용법
INSERT INTO BBS1 SELECT * FROM (WITH WITH_TAB AS ( SELECT SEQ, TITLE, CONTENTS FROM BBS2 WHERE SEQ = 1 ) SELECT * FROM WITH_TAB);
이렇게 사용할 수도 있겠지만 아래의 WITH 없는 쿼리문을 보자.
INSERT INTO BBS1 SELECT * FROM ( SELECT SEQ, TITLE, CONTENTS FROM BBS2 WHERE SEQ = 1 );
WITH를 쓰면 오히려 쿼리문이 더 길어진다. INSERT/UPDATE/DELETE에서는 사용하지 않는 것을 추천한다.
본문의 출처 https://epdl-studio.tistory.com/
반응형
[ORACLE] 오라클_WITH 구문 사용하기(feat.서브쿼리)
반응형
WITH
– WITH 구문은 보통 서브쿼리를 사용할때 많이 사용하고는 한다. (가독성과 성능면에서 우수하다.)
– 일반 서브쿼리는 서로를 참조할 수 없지만 WITH절은 서로 참조가 가능하다.
– 오라클 9버전 이상에서 지원하는 구문이다.
– WITH 구문을 사용한 쿼리는 인라인뷰나 임시 테이블로 여긴다.
예제 1) WITH 구문의 일반적인 사용방법
— 구문 — WITH ALIAS명 AS (SUB 쿼리) — SELECT 컬럼명 FROM ALIAS명; WITH TEST AS (SELECT a.deptno , b.emptno FORM a,b) SELECT * FROM TEST;
예제 2) WITH 구문 안에서 두개의 쿼리문 사용
— 구문 — WITH ALIAS명 AS (SUB 쿼리), — ALIAS명 AS (SUB 쿼리) — SELECT 컬럼명 FROM ALIAS명1, ALIAS명2 WHERE 조인조건; WITH TEST AS (SELECT a.deptno AS deptno1 , b.emptno FORM a,b) , TEST2 AS (SELECT a.deptno AS deptno2, a.salaray , c.voc , c.label FROM a,c) SELECT * FROM TEST, TEST2 WHERE TEST.deptno1 = TEST2.deptno2;
이렇게 두개의 예제를 통해 오라클 WITH 구문에 대해 알아봤는데,
예제 처럼 서브쿼리를 많이 사용할때 사용해주면 가독성 측면에서 매우 좋을것이다.
반응형
[오라클 SQL] 오라클 WITH 구문 사용 방법 및 예제
안녕하세요~~
오늘은 오라클 WITH 구문을 사용하는 방법에 대해서 알려드리고자 합니다!
먼저 오라클에서 WITH 구문에 대해서 간략히 설명을 드릴게요
WITH 구문 이란?
– WITH 구문은 쿼리의 결과가 여러 번 반복해서 사용될 때 유용합니다.
– 각 쿼리 Block의 이름을 지정할 수 있습니다.
– 오라클 옵티마이저는 WITH 구문으로 선언한 쿼리를 임시 테이블로 봅니다.
WITH 구문 선언 방법
WITH ALIAS AS (쿼리)
SELECT 컬럼1, 컬럼2… FROM ALIAS 명
STUDENT Table
단일 WITH 구문 SQL
1 2 3 4 5 6 7 8 9 WITH STU_TMP AS ( SELECT CLASS, NAME, AGE FROM WIN.STUDENT ) SELECT * FROM WIN.STU_TMP cs
실행 결과
위에서 임시테이블로 STU_TMP라고 만들어서 기존의 STUDENT 테이블에서 CLASS, NAME, AGE 컬럼만 뽑는 서브쿼리를 만들고 최종적으로 해당 STU_TMP 테이블을 조회를 해보니 CLASS, NAME, AGE 세 개의 컬럼들만 조회된 것을 확인 하실 수 있습니다!
다음으로는 다중 WITH 구문을 사용하는 방법을 보여드릴게요!
다중 WITH 구문 SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 WITH STU_TMP AS ( SELECT CLASS, NAME, AGE FROM WIN.STUDENT ), STU_SCORE AS ( SELECT NAME, SCORE FROM WIN.STUDENT ), STU_GRADE AS ( SELECT NAME, GRADE FROM WIN.STUDENT ) SELECT tmp.CLASS, tmp.NAME, tmp.AGE , score.SCORE, grade.GRADE FROM WIN.STU_TMP tmp INNER JOIN WIN.STU_SCORE score ON tmp.NAME = score.NAME INNER JOIN WIN.STU_GRADE grade ON score.NAME = grade.NAME cs
실행 결과
위와 같이 다중 WITH 구문을 사용하여 STUDENT 테이블의 데이터를 조회해 보았습니다!
글 읽어 주셔서 감사합니다ㅎㅎ
728×90
[ORACLE] 오라클 WITH 구문을 사용해 가상테이블 만드는 방법
오라클 WITH 구문을 사용해 가상 테이블 만드는 방법 타이틀
오라클 쿼리를 작성하다 보면 쿼리문 내에서 동일한 구문의 쿼리문이 반복해서 동일한 테이블에 엑세스하는 경우가 있습니다. WITH 구문은 그런 동일한 결과를 내는 반복 쿼리문을 줄여서 테이블 엑세스를 줄이고 쿼리문을 단순화하는데 유용합니다. WITH 구문을 이용하면 오라클 내에서 가상 테이블이 생성되어 메모리에 할당됩니다.
오라클에서 WITH 구문을 사용하는 방법
WITH ..가상테이블명.. AS 구문을 사용하여 가상테이블을 구성할 데이터를 조회하는 쿼리를 만듦니다. 이 가상 테이블은 실제 테이블처럼 FROM 구문을 이용하여 외부 쿼리문 내에서 호출하여 사용할 수 있습니다.
WITH TEMP_TABLE AS ( … QUERIES… ) SELECT COLUMNS FROM TEMP_TABLE
예제
WITH 가상 테이블은 아래와 같이 임의의 데이터들을 생성하고 테이블 명으로 외부 쿼리에서 실행할 수 있습니다. 안에 들어가는 쿼리문은 SELECT로 조회되는 어떤 쿼리라도 가능합니다.
WITH TEST_DATA AS ( SELECT ‘김아무개’ AS “이름”,’과장’ AS “직급”, ‘M’ AS “성별”, 38 AS “나이”, 4500000 AS “월급” FROM DUAL UNION ALL SELECT ‘박아무개’,’과장’, ‘F’, 37, 4600000 FROM DUAL UNION ALL SELECT ‘이아무개’,’차장’, ‘F’, 42, 5100000 FROM DUAL UNION ALL SELECT ‘유아무개’,’대리’, ‘M’, 34, 3200000 FROM DUAL UNION ALL SELECT ‘최아무개’,’주임’, ‘M’, 28, 2500000 FROM DUAL UNION ALL SELECT ‘박아무개’,’대리’, ‘M’, 32, 2800000 FROM DUAL UNION ALL SELECT ‘이아무개’,’주임’, ‘F’, 33, 2600000 FROM DUAL UNION ALL SELECT ‘김아무개’,’주임’, ‘F’, 26, 2700000 FROM DUAL UNION ALL SELECT ‘박아무개’,’이사’, ‘M’, 56, 5700000 FROM DUAL UNION ALL SELECT ‘최아무개’,’과장’, ‘M’, 45, 4500000 FROM DUAL UNION ALL SELECT ‘임아무개’,’대리’, ‘M’, 35, 3700000 FROM DUAL ) SELECT * FROM TEST_DATA;
위 쿼리문을 조회한 결과는 아래와 같습니다. 임시ALIAS 로 지정한 ‘이름’, ‘직급’, ‘성별’, ‘나이’, ‘월급’ 컬럼들이 마치 실제 테이블처럼 결과로 조회됩니다.
WITH 예제 쿼리에 대한 결과문
WITH 쿼리를 다중으로 이용하는 방법
WITH 구문은 연속으로 다중 테이블로 구성할 수 있습니다. 다중으로 가상테이블을 생성하여 조인하거나 복합 쿼리문 내에서 활용할 수 있습니다.
WITH TEST_TBL1 AS ( … QUERY… ), TEST_TBL2 AS ( … QUERY… ), TEST_TBL3 AS ( … QUERY… ) SELECT COLUMN1, COLUMN2, … FROM TEST_TBL1 A, TEST_TBL2 B, TEST_TBL3 C WHERE A.KEY = B.KEY AND A.KEY = C.KEY
위 쿼리처럼 동일 외부 쿼리에서 사용할 가상 테이블을 다중으로 생성하여 다양한 쿼리를 활용하여 테이블 데이터를 사용할 수 있습니다. 꼭 조인문이 아니더라도 스칼라 쿼리 등등 실제 테이블처럼 서브쿼리로 사용할 수 있어서 활용도가 높습니다. 무엇보다 상단에 쿼리문 안에서 복잡하게 조회하는 데이터들을 가상테이블로 정렬하여 한 눈에 볼 수 있어서 깔끔합니다.
예제
아래 쿼리문처럼 가상 테이블을 열거식으로 다중테이블로 구성할 수 있습니다. 가상테이블을 활용하는 쿼리문은 실제 테이블을 사용하는 것처럼 여러가지 방법으로 활용가능합니다. 여기서는 조인으로 사용했으나, 일부 서브쿼리로 사용한다든지, GROUP BY 등의 집계함수를 사용하는 등 다양하게 사용할 수 있습니다.
WITH TEST_NAME AS ( SELECT 10021 AS USER_KEY, ‘김아무개’ AS “이름” FROM DUAL UNION ALL SELECT 10022 AS USER_KEY, ‘박아무개’ FROM DUAL UNION ALL SELECT 10023 AS USER_KEY, ‘이아무개’ FROM DUAL UNION ALL SELECT 10024 AS USER_KEY, ‘유아무개’ FROM DUAL UNION ALL SELECT 10025 AS USER_KEY, ‘최아무개’ FROM DUAL UNION ALL SELECT 10026 AS USER_KEY, ‘박아무개’ FROM DUAL UNION ALL SELECT 10027 AS USER_KEY, ‘이아무개’ FROM DUAL UNION ALL SELECT 10028 AS USER_KEY, ‘김아무개’ FROM DUAL UNION ALL SELECT 10029 AS USER_KEY, ‘박아무개’ FROM DUAL UNION ALL SELECT 10030 AS USER_KEY, ‘최아무개’ FROM DUAL UNION ALL SELECT 10031 AS USER_KEY, ‘임아무개’ FROM DUAL ), TEST_PLACE AS ( SELECT 10021 AS USER_KEY, ‘과장’ AS “직급” FROM DUAL UNION ALL SELECT 10022 AS USER_KEY, ‘과장’ FROM DUAL UNION ALL SELECT 10023 AS USER_KEY, ‘차장’ FROM DUAL UNION ALL SELECT 10024 AS USER_KEY, ‘대리’ FROM DUAL UNION ALL SELECT 10025 AS USER_KEY, ‘주임’ FROM DUAL UNION ALL SELECT 10026 AS USER_KEY, ‘대리’ FROM DUAL UNION ALL SELECT 10027 AS USER_KEY, ‘주임’ FROM DUAL UNION ALL SELECT 10028 AS USER_KEY, ‘주임’ FROM DUAL UNION ALL SELECT 10029 AS USER_KEY, ‘이사’ FROM DUAL UNION ALL SELECT 10030 AS USER_KEY, ‘과장’ FROM DUAL UNION ALL SELECT 10031 AS USER_KEY, ‘대리’ FROM DUAL ), TEST_AGE AS ( SELECT 10021 AS USER_KEY, 8 AS “나이” FROM DUAL UNION ALL SELECT 10022 AS USER_KEY, 37 FROM DUAL UNION ALL SELECT 10023 AS USER_KEY, 42 FROM DUAL UNION ALL SELECT 10024 AS USER_KEY, 34 FROM DUAL UNION ALL SELECT 10025 AS USER_KEY, 28 FROM DUAL UNION ALL SELECT 10026 AS USER_KEY, 32 FROM DUAL UNION ALL SELECT 10027 AS USER_KEY, 33 FROM DUAL UNION ALL SELECT 10028 AS USER_KEY, 26 FROM DUAL UNION ALL SELECT 10029 AS USER_KEY, 56 FROM DUAL UNION ALL SELECT 10030 AS USER_KEY, 45 FROM DUAL UNION ALL SELECT 10031 AS USER_KEY, 35 FROM DUAL ), TEST_WAGE AS ( SELECT 10021 AS USER_KEY, 4500000 AS “월급” FROM DUAL UNION ALL SELECT 10022 AS USER_KEY, 4600000 FROM DUAL UNION ALL SELECT 10023 AS USER_KEY, 5100000 FROM DUAL UNION ALL SELECT 10024 AS USER_KEY, 3200000 FROM DUAL UNION ALL SELECT 10025 AS USER_KEY, 2500000 FROM DUAL UNION ALL SELECT 10026 AS USER_KEY, 2800000 FROM DUAL UNION ALL SELECT 10027 AS USER_KEY, 2600000 FROM DUAL UNION ALL SELECT 10028 AS USER_KEY, 2700000 FROM DUAL UNION ALL SELECT 10029 AS USER_KEY, 5700000 FROM DUAL UNION ALL SELECT 10030 AS USER_KEY, 4500000 FROM DUAL UNION ALL SELECT 10031 AS USER_KEY, 3700000 FROM DUAL ) SELECT N.USER_KEY, “이름”, “나이”, “월급” FROM TEST_NAME N, TEST_AGE A, TEST_WAGE W WHERE N.USER_KEY = A.USER_KEY AND N.USER_KEY = W.USER_KEY;
위 쿼리문을 실행한 결과는 아래와 같습니다.
WITH 다중 쿼리 예제에 대한 결과문
마무리
오라클의 WITH 구문은 쿼리문의 가독성을 높이고 빈번하게 동일 SQL구문이 실행되면서 동일블럭에 대한 반복적인 접근이 발생하는 경우의 부하를 줄여준다는 장점이 있습니다. 하지만 다량의 데이터들이 가상테이블로 생성되면 메모리나 성능의 저하를 일으킬 수 있다는 단점이 있습니다. 가상테이블을 활용할때는 데이터 양이 적거나 DISK I/O가 많이 발생하는 쿼리에만 선택적으로 사용하는 것이 괜찮을듯 합니다.
[Oracle] With절 사용법 및 예시 (임시 테이블)
728×90
WITH절 이란
WITH절은 오라클9 이후 사용이 가능한 이름이 부여된 서브쿼리라고 생각하시면 됩니다.
임시테이블을 만든다는 관점에서 본다면 VIEW와 쓰임새가 비슷하지만, VIEW는 한번 만들어 놓으면 DROP 할 때까지 없어지지 않지만 WITH절 같은 경우 한번 실행할 쿼리문내에 정의되어 있을 경우, 크 쿼리문안에서만 실행된다는 차이점이 있습니다.
WITH절을 사용하는 이유
WITH절은 복잡한 SQL에서 동일 블록에 대해 반복적으로 SQL을 사용하는 경우 그 블록에 이름을 부여하여 재사용 할 수 있게 함으로 쿼리 성능을 높일 수 있는데 WITH절을 이용하여 미리 이름을 부여해서 Query Block을 만들 수 있습니다. 자주 실행되는 경우 한번만 Parsing되고 Plan 계획이 수립되므로 쿼리의 성능향상에 도움이 됩니다.
WITH절 사용 예시
WITH EXAMPLE AS ( SELECT ‘WITH절’ AS STR1 FROM DUAL ) SELECT * FROM EXAMPLE
WITH절 안의 내용을 EXAMPLE이라는 것으로 명칭을 지어 사용 하는 개념입니다.
다중 WITH문
WITH EXAMPLE1 AS ( –첫번째 WITH문 SELECT ‘EX1’ A FROM DUAL UNION ALL SELECT ‘EX2’ FROM DUAL ), EXAMPLE2 AS ( –두번째 WITH문 SELECT ‘EX3’ A FROM DUAL UNION ALL SELECT A FROM EXAMPLE1 –첫번째 WITH문 참조 ) SELECT * FROM EXAMPLE2
WITH절에 정의된 쿼리는 여러번 사용할 수록 효율이 증가
WITH EX AS ( SELECT * FROM PRODUCTS WHERE STANDARD_COST > 1000 ) SELECT * FROM EX WHERE EX.CATEGORY_ID = ‘1’ UNION ALL SELECT * FROM EX WHERE EX.CATEGORY_ID = ‘2’ UNION ALL SELECT * FROM EX WHERE EX.CATEGORY_ID = ‘3’
WITH절에 정의된 내용을 한번만 사용한다면 서브쿼리를 사용하는것과 크게 성능차이가 나지 않습니다. WITH문의 가장 큰장점은 한번 WITH절의 내용을 한번에 올려놓고 계속 재사용한다는것에 큰 의미가 있기에 WITH절에 구문을 여러번 참조하는 쿼리를 만들수록 그 효과가 배로 증가합니다.
728×90
SQL을 다루는 기술: 02 WITH 절
0 2 | WITH 절
개선된 서브 쿼리
6장에서 여러 형태의 서브 쿼리를 학습했었는데 서브 쿼리에는 한 가지 제약 사항이있다. 예를 들어, kor_loan_status 테이블에서 연도별 최종월 기준 가장 대출이 많은 도시와 잔액을 구해야 한다고 해보자. 간단히 쿼리를 작성할 수 있을 것 같지만 생각 외로 복잡한 측면이 있으므로 앞에서 배운 “분할해서 정복하라” 기법을 사용해 보자.
ⓐ 연도별 최종: 2011년의 최종년도는 12월이지만 2013년은 11월이므로 연도별 최종월을 알아야 한다.
구현 방안: 그룹 쿼리로 연도별로 가장 큰 월을 구한다. → MAX(period)
ⓑ 연도별 최종월을 대상으로 대출잔액이 가장 큰 금액을 추출해야 한다.
구현 방안: ⓐ와 조인을 해서 연도별로 가장 큰 잔액을 구한다. → MAX(loan_jan_amt)
ⓒ 월별, 지역별 대출잔액과 ⓑ 결과를 비교해 금액이 같은 건을 추출한다.
구현 방안: ⓑ와 조인을 해서 두 금액이 같은 건을 구한다.
위 시나리오대로 작성한 쿼리는 다음과 같다.
입력
SELECT b2.* FROM ( SELECT period, region, sum(loan_jan_amt) jan_amt FROM kor_loan_status GROUP BY period, region ) b2, ( SELECT b.period, MAX(b.jan_amt) max_jan_amt FROM ( SELECT period, region, sum(loan_jan_amt) jan_amt FROM kor_loan_status GROUP BY period, region ) b,ⓑ ( SELECT MAX(PERIOD) max_monthⓒ FROM kor_loan_statusⓐ GROUP BY SUBSTR(PERIOD, 1, 4) ) a WHERE b.period = a.max_month GROUP BY b.period ) c WHERE b2.period = c.period AND b2.jan_amt = c.max_jan_amt ORDER BY 1;
결과
PERIOD REGION JAN_AMT ——– ———- ———- 201112 서울 204275.7 201212 서울 203344.9 201311 서울 205644.3
생각보다는 쿼리가 좀 복잡해졌는데, 자세히 보면 색으로 표시된 부분(연도, 지역별 잔액총액)은 같은 구문이 두 번 사용되었음을 알 수 있다. 동일한 구문을 한 번만 사용할 수 있다면 쿼리가 훨씬 간단해질텐데, 좋은 방법이 없을까? 기존 서브 쿼리로는 해결할 방법이 없지만 WITH 절을 사용하면 가능하다. WITH 구문의 형식은 다음과 같다.
WITH 별칭1 AS (SELECT 문), 별칭2 AS (SELECT 문) … SELECT FROM 별칭1, 별칭2 …
WITH이 맨 앞에 오고 별칭을 앞에 명시한다는 점만 제외하면 기본 형태는 일반 서브 쿼리와 별반 다르지 않다. 하지만 WITH 절은, 별칭으로 사용한 SELECT 문의 FROM 절에 다른 SELECT 구문의 별칭 참조가 가능하다.하다.
먼저 위 쿼리를 그대로 WITH 구문 형태로 변경해 보자.
입력
WITH b2 AS ( SELECT period, region, sum(loan_jan_amt) jan_amt FROM kor_loan_status GROUP BY period, region ), c AS ( SELECT b.period, MAX(b.jan_amt) max_jan_amt FROM ( SELECT period, region, sum(loan_jan_amt) jan_amt FROM kor_loan_status GROUP BY period, region ) b, ( SELECT MAX(PERIOD) max_month FROM kor_loan_status GROUP BY SUBSTR(PERIOD, 1, 4) ) a WHERE b.period = a.max_month GROUP BY b.period ) SELECT b2.* FROM b2, c WHERE b2.period = c.period AND b2.jan_amt = c.max_jan_amt ORDER BY 1;
WITH 절에서는 색으로 표시한 두 번째 SELECT 문인 b대신 바로 직전에 명시한 서브 쿼리인 b2를 참조할 수 있다.
입력
WITH b2 AS ( SELECT period, region, sum(loan_jan_amt) jan_amt FROM kor_loan_status GROUP BY period, region ), c AS ( SELECT b2.period, MAX(b2.jan_amt) max_jan_amt FROM b2, ( SELECT MAX(PERIOD) max_month FROM kor_loan_status GROUP BY SUBSTR(PERIOD, 1, 4) ) a WHERE b2.period = a.max_month GROUP BY b.period ) SELECT b2.* FROM b2, c WHERE b2.period = c.period AND b2.jan_amt = c.max_jan_amt ORDER BY 1;
결과
PERIOD REGION JAN_AMT ——– ———- ———— 201112 서울 204275.7 201212 서울 203344.9 201311 서울 205644.3
쿼리가 훨씬 간단해지면서 동일한 결과가 조회된 것을 확인할 수 있다. 일반 서브 쿼리와 중복 구문이 필요 없는 WITH 절을 그림으로 나타내면 다음과 같다.
▼ 그림 7-13 일반 서브 쿼리와 WITH 절
[일반 서브 쿼리] / [WITH 절]
키워드에 대한 정보 오라클 with
다음은 Bing에서 오라클 with 주제에 대한 검색 결과입니다. 필요한 경우 더 읽을 수 있습니다.
이 기사는 인터넷의 다양한 출처에서 편집되었습니다. 이 기사가 유용했기를 바랍니다. 이 기사가 유용하다고 생각되면 공유하십시오. 매우 감사합니다!
사람들이 주제에 대해 자주 검색하는 키워드 (추천SQL강좌/추천SQL교육)오라클 SQL기초 강좌#4, WITH구문 이란?(오라클교육/SQL교육/오라클동영상/SQL동영상/오라클강의/SQL강의/오라클강좌/SQL강좌)
- 서브쿼리
- 조인
- SQL교육
- SQL학원
- SQL강의
- SQL강좌
- SQL재직자교육
- SQL추천교육
- 추천SQL교육
- SQL추천학원
- SQL학원추천
- SQL
- 오라클SQL
- Oracle교육
- Oracle학원
- 오라클교육
- 오라클학원
- PLSQL
- 오라클힌트
- SQL튜닝
- SQL튜닝학원
- SQL튜닝교육
- SQL튜닝강의
- SQL튜닝강좌
- OracleHint
(추천SQL강좌/추천SQL교육)오라클 #SQL기초 #강좌#4, #WITH구문 #이란?(오라클교육/SQL교육/오라클동영상/SQL동영상/오라클강의/SQL강의/오라클강좌/SQL강좌)
YouTube에서 오라클 with 주제의 다른 동영상 보기
주제에 대한 기사를 시청해 주셔서 감사합니다 (추천SQL강좌/추천SQL교육)오라클 SQL기초 강좌#4, WITH구문 이란?(오라클교육/SQL교육/오라클동영상/SQL동영상/오라클강의/SQL강의/오라클강좌/SQL강좌) | 오라클 with, 이 기사가 유용하다고 생각되면 공유하십시오, 매우 감사합니다.