10. 서브쿼리(Sub Query)
10장. Sub Query (서브쿼리)를 배웁니다.
1. Sub Query란?
- Sub Query 문법:
|
SELECT select_list
|
: 괄호안에 있는 쿼리를 Sub Query(Inner Query)
, 괄호 밖에 있는 커리를 Main Query(Outer Query)
2. Sub Query의 종류
1)단일행 서브쿼리 : 서브쿼리의 결과가 1개의 행만 나오는 것.
답)
select s.name, d.dname
from student s, department d
where d.deptno = (select deptno1
from student
where name = '이윤나')
and d.deptno=s.deptno1
답)
select p.name, to_char(p.hiredate, 'YY-MM-DD') "입사일", d.dname"학과명"
from professor p, department d
where p.hiredate > ( select hiredate
from professor
where name='송도권')
and p.deptno = d.deptno
답)
select name, weight
from student
where weight > (select avg(weight)
from student
where deptno1=101)
답)
select name, pay, to_char(hiredate,'DD-MON-YY') "입사일"
from professor
where pay<(select pay
from professor
where name='조인형')
and hiredate=(select hiredate
from professor
where name='심슨')
2)다중행 서브쿼리 : 서브쿼리의 결과가 2건 이상 출력되는 것.
답)
select empno, name, deptno
from emp2
where deptno in ( select dcode
from dept2
where area = '서울지사')
(2) 다중행 Sub Query 예2 -Exists 연산자 이용하기.
- Exists연산자 와 IN연산자의 차이점.
답) 다시정리하기
select name, position "직급", to_char(pay,'999,999,999')||'원' "연봉"
from emp2
where pay >any ( select min(pay) ============> any 대신 all써도 같은결과. => any(select pay 써도같은결과
from emp2
where position = '과장')
order by pay desc
답)
select name, grade, weightfrom student
where weight <all (select weight
from student
where grade=4)
답)
from dept2 d, emp2 e
where d.dcode = e.deptno
and e.pay <all (select avg(pay)
from emp2
group by deptno)
order by 3
3) 다중칼럼 Sub Query : SubQuery의 결과가 여러 컬럼인 경우.
답)
select p.profno "교수번호", p.name"교수명", p.hiredate "입사일", d.dname "학과명"
from professor p, department d
where (p.hiredate, d.deptno) in (select min(hiredate), deptno
from professor
group by deptno)
order by p.hiredate
답)
select name"사원명", position "직급", pay"연봉"
from emp2
where (pay, position) in (select max(pay) , position
from emp2
group by position)
order by pay
4) 상호연관 Sub Query (안쓰는게 좋음!!) : MainQuery값을 SubQuery에 주고, SubQuery를 수행 한 후 그 결과를 다시 Main Query로 반환해서 수행하는 SubQuery.
***참고***
Sub Query는 오는 위치에 따라서 이름이 다르다.
|
SELECT ( Sub Query ) <- 1 행만 반환할 경우 Scalar Sub Query(스칼라 서브쿼리)
|
3. 스칼라 서브쿼리 (Scalar Sub Query)
: select절에 오는 서브쿼리로 한번에 결과를 1행씩 반환.(Outer Join과 동일)
(데이터 건수가 별로 없는 경우에 좋다) (엄청 많이 씀!)
|
* scalar sub query 수행 원리 1. Main Query 를 수행한 후 Scalar Sub Query 에 필요한 값을 제공. 그리고 이 결과를 메모리에 입력값 과 출력값으로 메모리 내의 query execution cache 라는 곳에 저장 해 둠. - 입력값: Main Query 에서 주어진 값 - 출력값: Scalar Sub Query 를 수행 후 나온 결과값. 있으면 즉시 결과 값을 출력하고 없으면 다시 블록을 엑세스 해서 해당 값을 찾은 후 다시 메모리에 캐쉬해 둠.
-> 데이터 양이 적은 경우 찾는 데이터가 메모리에 만들어져 있는 값을 찾아오므로 스칼라 서브쿼리가 Join보다 빠르다.
|
1) Scalar Sub Query 테스트
(1) 두 건 이상의 데이터 반환을 요청하는 경우 - 에러 발생.
(2) 2개 이상의 컬럼을 조회할 경우 - 에러 발생함
(3) 일반 join 방법 보다 스칼라 서브 쿼리가 더 느린 경우
4. WITH 절을 활용한 Sub Query
: 원하는 테이블을 메모리에 미리 뷰 처럼 가상의 테이블로 생성시킨 후 데이터를 가져오는 기법.
(현업에서 아주많이 사용됨.)
1) 기본문법
- 단일 가상 테이블 생성
|
WITH a AS
|
- 다중 가상테이블 생성
|
WITH a AS
|
실습 1. 대용량의 테이블을 생성 한 후 최대값과 최소값의 차이를 구하기
|
- 실습 순서 0. 대용량 테이블을 생성하기 위해 관리자로 로그인 후 필요한 파일들의 용량을 증가시킵니다. |
Step 0. 실습 전에 필요한 파일들의 용량을 증가합니다.
(앞 실습에서 하신 분은 이 단계를 건너 뛰세요~)
SCOTT> CONN / AS sysdba;
Connected.
SYS> set line 200
SYS> set timing on <-- SQL 수행 시간을 표시하도록 설정합니다.
SYS> col tablespace_name for a10
SYS> col mb for 999.99
SYS> col file_name for a50
SYS> SELECT tablespace_name , bytes/1024/1024 MB , file_name
2 FROM dba_data_files ;
TABLE SPACE MB FILE_NAME
--------------- --------- --------------------------------------------------
USERS 6.25 /app/oracle/oradata/testdb/users01.dbf
SYSAUX 260.00 /app/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1 35.00 /app/oracle/oradata/testdb/undotbs01.dbf
SYSTEM 450.00 /app/oracle/oradata/testdb/system01.dbf
EXAMPLE 100.00 /app/oracle/oradata/testdb/example01.dbf
5 rows selected.
Elapsed: 00:00:00.02 <-- set timing on 의 결과로 보이는 내용입니다.
SYS> ALTER DATABASE DATAFILE '/app/oracle/oradata/testdb/users01.dbf'
2 AUTOEXTEND ON; <-필요한 만큼 자동 증가되도록 설정을 변경합니다.
Database altered.
Step 1. scott 계정으로 로그인 하여 with_test1 테이블을 생성후 데이터를 500 만 건 입력합니다.
SYS> CONN scott/tiger ;
Connected.
SCOTT> CREATE TABLE with_test1 (
2 no NUMBER ,
3 name VARCHAR(10),
4 pay NUMBER (6) )
5 TABLESPACE USERS ;
table created.
SCOTT> BEGIN
2 FOR i IN 1..5000000 LOOP
3 INSERT INTO with_test1
4 VALUES (i, DBMS_RANDOM.STRING('A',5) ,
5 DBMS_RANDOM.VALUE(6,999999));
6 END LOOP ;
7 COMMIT ;
8 END;
9 /
PL/SQL procedure successfully completed.
SCOTT> SELECT COUNT(*) FROM with_test1 ;
COUNT(*)
----------------
5000000 <---- 500 만 건의 데이터가 입력되었습니다.
1 row selected.
Elapsed: 00:00:00.42
SCOTT> SELECT * FROM with_test1 ;
NO NAME PAY
---------- ---------- ----------
16506 WXERn 480831
16507 FAhPR 235569
16508 vOHla 102066
16509 LaUMo 202489
( 이 하 생 략 )
Step 2. 일반적인 방법인 max 함수와 min 함수를 사용하여 최대값과 최소값의 차이를 구하고 소요시간을 측정합니다.
SCOTT> SELECT max(pay) - min(pay)
2 FROM with_test1 ;
MAX(PAY)-MIN(PAY)
------------------------- 999993
Elapsed: 00:00:02.78 <- 인덱스 없이 조회할 경우 소요 시간.
Step 3. 인덱스를 생성 후 위 step 2 의 작업을 반복합니다.
SCOTT> CREATE INDEX idx_with_pay ON with_test1(pay); <- 인덱스를 생성합니다.
Index created.
SCOTT> SELECT max(pay) - min(pay)
2 FROM with_test1 ;
MAX(PAY)-MIN(PAY)
-----------------------
999993
1 row selected.
Elapsed: 00:00:00.41 <- 인덱스 생성 후 조회 할 경우 소요시간입니다.
Step 4. With 절을 사용하여 동일한 작업을 수행합니다.
SCOTT> WITH a AS (
2 /* 최대값을 구하는 쿼리입니다 */
3 SELECT /*+ index_desc( w idx_with_pay ) */ pay
4 FROM with_test1 w
5 WHERE pay >0
6 AND rownum = 1) ,
7 b AS (
8 /* 최소값을 구하는 쿼리입니다 */
9 SELECT /*+ index( w idx_with_pay ) */ PAY
10 FROM with_test1 w
11 WHERE pay >0
12 AND rownum = 1)
13 SELECT a.pay - b.pay
14 FROM a,b ;
A.PAY-B.PAY
--------------
999993
1 row selected.
Elapsed: 00:00:00.06 <----- 시간이 아주 단축되었습니다!!!
|
원하는 결과값이 제대로 안나옴 1. 이유는?? |
/* */ 안에 +index 바로앞에 띄어쓰기함. (띄어쓰기 안하면 제대로나옴)
|
|
원하는 결과값이 제대로 안나옴 2. 이유는?? |
select 와 pay 사이에 /* */내용 안써줌.
|
|
결과 제대로 나옴. |
|
/*+ index_desc( w idx_with_pay ) */ : SQL힌트. (SQL튜닝관련, 옵티마이져 관련부분 참고)
실습 2. 앞의 실습에서 생성한 with_test1 테이블에서 no 가 120000 번에서 130000 사이인 사람들 중 가장 pay 가 작은 사람을 찾은 후 그 사람보다 pay 가 작은 사람수를 세는 작업입니다.
이 SQL 을 하기 위해 먼저 with_test1 테이블의 no 컬럼에 인덱스를 생성합니다.
SCOTT> CREATE INDEX idx_with_no ON with_test1(no);
Index created.
Elapsed: 00:00:23.55
Step 1. 일반적인 sub query 를 사용하여 데이터를 조회하고 시간을 측정합니다.
SCOTT> SELECT COUNT(*) FROM with_test1
2 WHERE pay <ALL (SELECT /*+ INDEX (w idx_with_no) */ pay
3 FROM with_test1 w
4 WHERE no BETWEEN 120000 AND 130000) ;
COUNT(*)
---------------
230 <--- 230 건의 데이터가 조회되었습니다.
1 row selected.
Elapsed: 00:00:30.42
Step 2. 동일한 작업을 with 절을 사용하여 수행한 후 비교 해 보겠습니다.
( 아래 굵은 표시가 된 부분이 먼저 수행이 됩니다 )
SQL> WITH t AS (
2 SELECT /*+ index (w idx_with_pay ) */ min(pay) min_pay
3 FROM with_test1 w
4 WHERE pay >0
5 AND no between 120000 and 130000
6 AND rownum = 1)
7 SELECT COUNT(*)
8 FROM with_test1 w , t
9 WHERE w.pay < t.min_pay ;
COUNT(W.PAY)
------------------
230 <-- 230 건이 조회 되었습니다.
1 row selected.
Elapsed: 00:00:00.08 <---- 0.08초 만에 수행되었습니다.
- 서브쿼리에서 대량의 데이터를 대상으로 작업을 하여 시간이 오래 걸리는 경우, with 절로 미리 가상의 뷰 처럼 작업을 단축 시킬 수 있는 테이블을 생성 한 후 작업을 수행하면 훨씬 더 빠르고 효과적으로 작업을 할 수 있다.
실습 3: 아래와 같이 500 만 건의 데이터가 들어 있는 테이블의 데이터를 조회하여 성능을 비교합니다. ( 이번 실습은 with 절이 효과가 없는 예를 보여줍니다 )
[실습준비]
Step 1. 실습을 위해 아래와 같이 테이블을 생성하고 데이터를 입력합니다.
SYS> CREATE TABLE t1 (
2 no NUMBER (6),
3 name VARCHAR(10),
4 deptno NUMBER (6))
5 TABLESPACE users;
TABLE created.
Elapsed: 00:00:00.12
SYS> CREATE TABLE t2 (
2 deptno NUMBER (6),
3 dname VARCHAR(10),
4 loc VARCHAR(10))
5 TABLESPACE users;
TABLE created.
Elapsed: 00:00:00.02
SYS> BEGIN
2 FOR i IN 1..5000000 LOOP
3 INSERT INTO t1
4 VALUES (DBMS_RANDOM.VALUE(6,999999),
5 DBMS_RANDOM.STRING('A',9),
6 DBMS_RANDOM.VALUE(6,999999));
7 END LOOP ;
8 COMMIT ;
9 END ;
10 /
PL/SQL procedure successfully completed.
Elapsed: 00:09:38.69
SYS> SELECT COUNT(*) FROM t1 ;
COUNT(*)
----------------
5000000
1 row selected.
Elapsed: 00:00:04.28
SYS> BEGIN
2 FOR i IN 1..5000000 LOOP
3 INSERT INTO t2
4 VALUES (DBMS_RANDOM.VALUE(6,999999),
5 DBMS_RANDOM.STRING('A',9),
6 DBMS_RANDOM.STRING('A',9));
7 END LOOP ;
8 COMMIT ;
9 END;
10 /
PL/SQL procedure successfully completed.
Elapsed: 00:10:31.37
SYS> SELECT COUNT(*) FROM t2;
COUNT(*)
-----------------
5000000
1 row selected.
Elapsed: 00:00:04.71
Step 2. join 이 될 조건 컬럼에 아래와 같이 인덱스도 생성합니다.
SYS> CREATE INDEX idx_t1_deptno ON t1(deptno);
Index created.
Elapsed: 00:00:20.48
SYS> CREATE INDEX idx_t2_deptno ON t2(deptno);
Index created.
Elapsed: 00:00:17.42
[Case 1: 일반적인 join 을 사용하여 데이터를 조회하기 ]
SYS> SELECT t1.no , t1.name , t2.deptno , t2.dname
2 FROM t1 , t2
3 WHERE t1.deptno=t2.deptno
4 AND t1.no < 10000 ;
(중간 출력 결과 생략)
NO NAME DEPTNO DNAME
-------------- ---------------- -------------- -----------------
5200 mRSgZpwlR 591342 yvImWMYgQ
7712 LDkiMtLcJ 101738 SfrjzPhXc
1105 puQvFpMbF 171683 HyZscQzdX
286 XfRLJfFRd 941013 tgyrztMFE
97 BMnEYocpw 301686 jcNbYlKbs
1391 gfZhVGKPr 393626 rgYdGmHTE
4493 agJWUFYrR 660108 jBZDdnCTY
247474 rows selected.
Elapsed: 00:00:07.93
247,474 건의 데이터를 조회하는 데 7.93 초가 소요.
[Case 2: with 구문을 사용하여 임시 테이블 생성 후 join 하기 ]
SYS> WITH a AS (
2 SELECT no , name , deptno
3 FROM t1
4 WHERE no < 10000)
5 SELECT a.no , a.name , a.deptno , b.dname
6 FROM a , t2 b
7 WHERE a.deptno=b.deptno ;
(중간 결과 생략)
NO NAME DEPTNO DNAME
---------- ---------------- ------------ ------------------
5200 mRSgZpwlR 591342 yvImWMYgQ
7712 LDkiMtLcJ 101738 SfrjzPhXc
1105 puQvFpMbF 171683 HyZscQzdX
286 XfRLJfFRd 941013 tgyrztMFE
97 BMnEYocpw 301686 jcNbYlKbs
1391 gfZhVGKPr 393626 rgYdGmHTE
4493 agJWUFYrR 660108 jBZDdnCTY
247474 rows selected.
Elapsed: 00:00:07.40
:Case 1의 경우와 거의 비슷한 시간나옴.
????????
With 절을 활용한 연습문제. with 절을 활용한 대출원리금 균등상환금액 계산하기