7월5일
유명한 SQL 튜닝 공식을 스스로 알아내보세요.
SQLP주관식) 이름, 월급, 직업이 SALESMAN인 사원들의 토탈월급,
직업이 SALESMAN인 사원들의 최대월급,
직업이 SALESMAN인 사원들의 최소월급
을 출력하시오 !
튜닝전: select ename, sal,
( select sum(sal) from emp where job='SALESMAN' ) 토월급,
( select max(sal) from emp where job='SALESMAN' ) 최대월급,
( select min(sal) from emp where job='SALESMAN' ) 최소월급
from emp;
--> emp 테이블을 4번이나 접근한다.
튜닝후: ???
select ename, sal,
sum(decode(job, 'SALESMAN', sal)) over () 토탈월급,
max(decode(job, 'SALESMAN', sal)) over () 최대월급,
min(decode(job, 'SALESMAN', sal)) over () 최소월급
from emp;
select ename,sal,substr(급여,1,4)as 합계,substr(급여,5,4)AS 최대급여,substr(급여,9,4)AS 최소월급
from(
select ename,sal,(select sum(sal)||max(sal)||min(sal) from emp where job='SALESMAN' )AS 급여
FROM EMP
);
SELECT e.ename, e.sal, s.최대월급, s.최소월급, s.토탈월급
FROM emp e, (
SELECT max(sal) 최대월급, min(sal) 최소월급, sum(sal) 토탈월급
FROM emp
WHERE job = 'SALESMAN') s;[유명한 SQL튜닝 기법] 튜닝전 SQL
튜닝전 : select deptno, ename, sal,
( select sum(sal) from emp s where s.deptno = e.deptno ) 토탈월급,
( select max(sal) from emp s where s.deptno = e.deptno ) 최대월급,
( select min(sal) from emp s where s.deptno = e.deptno ) 최소월급
from emp e;
튜닝후 : ???
※ select 절의 서브쿼리를 용어로 ? 스칼라(scalar) 서브쿼리
메인 쿼리의 컬럼이 서브쿼리에 들어가져 있으면 ? 상호관련 서브쿼리- merge 문은 특정 테이블에 insert, update, delete 를 한번에 수행하는 명령어
문제466. 운영db와 테스트db 가 여러분들 컴퓨터에 하나의 db 에 있다고
가정하고 하기 위해서 두개의 테이블을 아래와 같이 생성하세요.
- 운영 db 테이블
create table operate_emp
as
select * from emp;
- 테스트 db 테이블
create table test_emp
as
select * from emp;
문제467. test_emp 테이블에서 직업이 SAELSMAN 과 MANAGER 인 사원들의
데이터를 지우시오 !
delete from test_emp
where job in ('SALESMAN', 'MANAGER');
commit;
문제468. test_emp 테이블에서 직업이 ANALYST 와 CLERK 인 사원들의
월급을 0 으로 변경하시오 !
update test_emp
set sal = 0
where job in ('ANALYST', 'CLERK') ;
commit;
문제469. test_emp 테이블로 operate_emp 테이블을 merge 하는데
operate_emp 테이블에는 존재하는 데이터인데 test_emp 테이블에는
존재하지 않는 데이터는 test_emp 테이블에 입력하고
양쪽다 존재하는데 데이터가 틀리면 operate_emp 의 데이터로
test_emp 의 데이터를 update 하시오 !
merge into test_emp t
using operate_emp o
on ( t.empno = o.empno )
when matched then
update set t.sal = o.sal, t.ename=o.ename
when not matched then
insert (t.empno, t.ename, t.job, t.sal, t.comm, t.hiredate, t.mgr, t.deptno )
values ( o.empno, o.ename, o.job, o.sal, o.comm, o.hiredate, o.mgr, o.deptno);
다소 문법이 복잡해보이지만 성능이 좋아서 merge 문을 많이 사용합니다.
문제470. 우리반 테이블을 emp17_backup 으로 백업하세요!
create table emp17_backup
as
select * from emp17;
문제471. emp17 테이블의 데이터를 아래와 같이 지우시오 !
delete from emp17
where rownum < 10;
commit;
문제472. emp17_backup 의 내용으로 emp17 의 데이터를 merge 하시오 !
merge into emp17 e
using emp17_backup b
on ( e.empno=b.empno)
when matched then
update set e.ename=b.ename,
e.gender = b.gender,
e.birth = b.birth,
e.age = b.age,
e.telecom = b.telecom,
e.email = b.email,
e.major = b.major,
e.address = b.addresswhen not matched then
insert ( e.empno, e.ename, e.gender, e.birth, e.age, e.telecom, e.email, e.major, e.address) values( b.empno, b.ename, b.gender, b.birth, b.age, b.telecom, b.email, b.major, b.address) ;
문제473. 이번에는 emp17 테이블을 truncate 시키고 emp17_backup 으로
데이터를 입력하시오 !
truncate table emp17;
insert into emp17
select *
from emp17_backup;
commit;
※ merge 하려는 2개의 테이블 사이의 데이터의 변경의 차이가 심하고
둘다 대용량 테이블이 아니면 위의 방법이 더 간단합니다.
truncate 해야한다는 부담감이 있습니다.
문제474. market_2022 테이블을 백업하세요 !
create table market_2022_backup
as
select *
from market_2022;
문제475. market_2022 테이블의 데이터를 일부 지우시오 !
delete from market_2022
where rownum < 156405;
commit;
문제476. market_2022 테이블의 상호명을 전부 null 로 변경하시오 !
update market_2022
set 상호명 = null;
commit;
문제477. market_2022 테이블의 데이터를 market_2022_backup 테이블의
데이터로 merge 하시오 !
merge into market_2022 m
using market_2022_backup b
on ( m.상가업소번호 = b.상가업소번호)
when matched then
update set
m.상호명=b.상호명,
m.지점명=b.지점명,
m.상권업종대분류코드=b.상권업종대분류코드,
m.상권업종대분류명=b.상권업종대분류명,
m.상권업종중분류코드=b.상권업종중분류코드,
m.상권업종중분류명=b.상권업종중분류명,
m.상권업종소분류코드=b.상권업종소분류코드,
m.상권업종소분류명=b.상권업종소분류명,
m.표준산업분류코드=b.표준산업분류코드,
m.표준산업분류명=b.표준산업분류명,
m.시도코드=b.시도코드,
m.시도명=b.시도명,
m.시군구코드=b.시군구코드,
m.시군구명=b.시군구명,
m.행정동코드=b.행정동코드,
m.행정동명=b.행정동명,
m.법정동코드=b.법정동코드,
m.법정동명=b.법정동명,
m.지번코드=b.지번코드,
m.대지구분코드=b.대지구분코드,
m.대지구분명=b.대지구분명,
m.지번본번지=b.지번본번지,
m.지번부번지=b.지번부번지,
m.지번주소=b.지번주소,
m.도로명코드=b.도로명코드,
m.도로명=b.도로명,
m.건물본번지=b.건물본번지,
m.건물부번지=b.건물부번지,
m.건물관리번호=b.건물관리번호,
m.건물명=b.건물명,
m.도로명주소=b.도로명주소,
m.구우편번호=b.구우편번호,
m.신우편번호=b.신우편번호,
m.동정보=b.동정보,
m.층정보=b.층정보,
m.호정보=b.호정보,
m.경도=b.경도,
m.위도=b.위도
when not matched then
insert (m.상가업소번호,m.상호명,m.지점명,m.상권업종대분류코드,m.상권업종대분류명,m.상권업종중분류코드,m.상권업종중분류명,m.상권업종소분류코드,m.상권업종소분류명,m.표준산업분류코드,m.표준산업분류명,m.시도코드,m.시도명,m.시군구코드,m.시군구명,m.행정동코드,m.행정동명,m.법정동코드,m.법정동명,m.지번코드,m.대지구분코드,m.대지구분명,m.지번본번지,m.지번부번지,m.지번주소,m.도로명코드,m.도로명,m.건물본번지,m.건물부번지,m.건물관리번호,m.건물명,m.도로명주소,m.구우편번호,m.신우편번호,m.동정보,m.층정보,m.호정보,m.경도,m.위도 )
values (b.상가업소번호,b.상호명,b.지점명,b.상권업종대분류코드,b.상권업종대분류명,b.상권업종중분류코드,b.상권업종중분류명,b.상권업종소분류코드,b.상권업종소분류명,b.표준산업분류코드,b.표준산업분류명,b.시도코드,b.시도명,b.시군구코드,b.시군구명,b.행정동코드,b.행정동명,b.법정동코드,b.법정동명,b.지번코드,b.대지구분코드,b.대지구분명,b.지번본번지,b.지번부번지,b.지번주소,b.도로명코드,b.도로명,b.건물본번지,b.건물부번지,b.건물관리번호,b.건물명,b.도로명주소,b.구우편번호,b.신우편번호,b.동정보,b.층정보,b.호정보,b.경도,b.위도);
select 'm.'|| column_name || '=' || 'b.' || column_name || ', '
from dba_tab_columns
where table_name = 'MARKET_2022';
select listagg('b.'|| column_name,', ')
from dba_tab_columns
where table_name = 'MARKET_2022';
SELECT listagg( 'm.'||column_name ,',')
from dba_tab_columns
where table_name='MARKET_2022';
위와 같이 dba_ 로 시작하는 테이블들이 바로 데이터 사전 관련한 테이블들
입니다.
문제478. 내가 가지고 있는 테이블 리스트를 확인하고 싶다면 ?
select *
from user_tables;
※ 1. user_tables : c##scott 유져가 소유하고 있는 테이블 리스트를 확인
2. all_tables : c##scott 유져가 소유하고 있는 테이블들 + 엑세스 권한 받은 테이블들
3. dba_tables : db 에 있는 모든 테이블 리스트를 다 확인
※ 1. user_tab_columns : c##scott 유져가 소유하고 있는 테이블들의 컬럼 정보를 확인하는 사전
2. all_tab_columns : c##scott 유져가 소유하고 있는 테이블들 + 엑세스 권한 받은 테이블들의 컬럼정보를 확인하는 사전
3. dba_tab_columns : db 에 있는 모든 테이블들의 컬럼 정보를 확인하는 사전위에 데이터 사전들이 뭐가 있는지 외우는 dba 는 없고 다 아래의 쿼리를
조회해서 필요한 사전들을 찾아냅니다.
select *
from dictionary
where table_name like '%보고싶은키워드를 대문자로%';
select *
from dictionary
where table_name like '%DB%TAB%COLUMN%';
설명: DB 를 포함하고 TAB 을 포함하고 COLUMN 을 포함하는 데이터 사전을
보여달라 !※ 위와 같이 merge 문을 이용하지 않는다면 다른 방법으로 두 테이블 사이에
데이터의 일치를 맞춰주는 SQL이 뭐가 있는가 ?
상호관련 서브쿼리를 사용한 update 문 (대체적으로 성능이 안좋습니다.)
▣ [7월 7일 점심시간 문제]
hr 계정의 employees 테이블을 다음과 같이 백업합니다.
create table employees_backup
as
select * from employees;
그리고 update 문을 이용해서 employees_backup 테이블의 데이터 일부를 수정합니다.
수정은 여러분들이 마음껏 수정하시면 됩니다.
그리고 **짝꿍의 employees_backup 테이블과 나의 employees_backup 테이블의 데이터의 차이**를 확인합니다.
차이가 나는 데이터가 무엇인지 확인하세요. **양쪽으로 다 확인하세요 !**
select * from employees_backup;
update employees_backup
set salary = 6000
where first_name = 'Donald';
select * from dba_db_links;
select * from employees_backup
where first_name = 'Donald';
select * from employees_backup
minus
select * from employees_backup@dblink_shm;
select * from employees_backup@dblink_shm
minus
select * from employees_backup;
commit;
describe employees_backup;▣ 083 락(LOCK) 이해하기
lock ? 세션(유져)이 특정 데이터를 수정하게 되면 commit 하기 전까지 수정한 행(row) 에 lock 을 겁니다.
특정 세션이 변경한 데이터를 다른 세션이 변경하지 못하도록 막는 기능.
만약 lock 이 없다면 데이터의 일관성이 깨집니다.
A session B session
( c##scott ) ( c##scott )
1. update emp
set sal = 9000
where ename='KING';
2. update emp
set sal = 0
where ename='KING';
commit;
select ename, sal
from emp;- 지금 현재 db 에서 발생하고 있는 lock 홀더 세션과 lock waiter 세션을 찾는
쿼리문```sql select decode(status,'INACTIVE',username || ' ' || sid || ',' || serial#,'lock') as Holder, decode(status,'ACTIVE', username || ' ' || sid || ',' || serial#,'lock') as waiter, sid, serial#, status from( select level as le, NVL(s.username,'(oracle)') AS username, s.osuser, s.sid, s.serial#, s.lockwait, s.module, s.machine, s.status, s.program, to_char(s.logon_TIME, 'DD-MON-YYYY HH24:MI:SS') as logon_time from v$session s where level>1 or EXISTS( select 1 from v$session where blocking_session = s.sid) CONNECT by PRIOR s.sid = s.blocking_session START WITH s.blocking_session is null); ``` alter system kill session '1347,16491';
우리가 테이블의 데이터를 update 하면 lock 이 행단위로 걸립니다.
그리고 반드시 db 에 데이터 수정 작업을 했으면 꼭 commit 을 해서
마무리를 잘 해야합니다. 안그러면 다른 세션들이 lock 에 걸리게 됩니다.
문제479. 아래와 같이 c##scott 세션을 2개를 열고 update 를 수행하시오 !
c##scott (A session) c##scott (B session)
1. update emp
set sal = 9000
where job='SALESMAN'; 2. update emp
set sal = 7000
where ename='MARTIN';문제480. 이번에는 holder 를 죽이지 마시고 waiter 를 죽이세요 ~
lock 은 행단위로 걸립니다.
문제481. emp 테이블 전체에 lock 을 겁니다.
lock table emp in exclusive mode;
※ lock 이 왜 필요한지 ? 데이터의 일관성이 깨지지 않기 위해서 필요합니다.
문제482. c##scott 유져로 3개의 세션을 열고 다음과 같이 update 하시오 !
▣ 084 SELECT FOR UPDATE절 이해하기
lock 은 update 문을 수행할 때 update 를 하려는 행들에 자동으로
lock 이 걸리게 되는데 select 를 할 때도 lock 을 걸 수 있습니다.
보통은 select 를 할 때는 lock 이 안걸립니다.
A 세션 B 세션
1. select ename, sal
from emp 2. select ename, sal
where ename='KING'; from emp
where ename='KING';
select는 lock 이 걸리지 않습니다.
지금부터 특정 테이블의 데이터를 집계를 해야하는데
그 누구도 내가 select 한 데이터를 수정못하게 해야한다면 ?**select ... for update ;**
A B
1. select ename, sal
from emp 2. update emp
where job='SALESMAN' set sal = 0
for update ; where ename='MARTIN';
3. commit ; <--- commit 을 해야 lock 이 풀립니다.문제483. 위의 작업을 다시 한번 수행하고 SQLDEVELOPER 창에서
Lock holder 와 lock waiter 를 확인하시오
▣ 085 서브 쿼리를 사용하여 데이터 입력하기
예제1. 아래의 테이블을 생성하시오 !
create table emp900
( empno number(10),
ename varchar2(10),
sal number(10) );
예제2. emp900 테이블에 아래의 데이터를 입력하시오 !
empno : 9384
ename : jack
sal : 3000
insert into emp900(empno, ename, sal )
values( 9384, 'jack', 3000 );
예제3. 서브쿼리를 사용한 insert 문으로 사원 테이블의 직업이
SALESMAN인 사원들의 사원번호, 이름, 월급을 emp900 에
입력하시오!
insert into emp900(empno, ename, sal )
select empno, ename, sal
from emp
where job='SALESMAN';
select * from emp900;
※ 위의 서브쿼리를 사용한 insert 문은 data 이행할 때 사용하는 SQL 입니다.
문제484. 짝꿍의 data 를 나의 db 에 이행 합니다. 짝꿍의 sales 를 나의 db 에 이행하기 위해서 먼저
sales 테이블의 구조를 생성합니다.
create table my_sales
as
select *
from sh.sales
where 1 = 2;
설명: 1 = 2 때문에 데이터는 못가져오고 테이블 구조만 생성합니다.
select * from my_sales;
문제485. 짝꿍의 sales 테이블의 데이터를 나의 my_sales 테이블에
이행합니다.
insert into my_sales
select *
from sh.sales@dblink21;
select count(*) from my_sales;
▣ 086 서브 쿼리를 사용하여 데이터 수정하기
**※ update 문에서 서브쿼리를 쓸 수 있는 절**
update ---> 서브쿼리 가능
set ---> 서브쿼리 가능
where ----> 서브쿼리 가능문제486. 사원 테이블에 ALLEN 보다 늦게 입사한 사원들의 월급을
9000 으로 변경하시오
update emp
set sal = 9000
where hiredate > ( select hiredate
from emp
where ename='ALLEN' );
문제487. SCOTT 과 같은 부서번호에서 일하는 사원들의 월급을 8000
으로 수정하시오 !
update emp
set sal = 8000
where deptno = ( select deptno
from emp
where ename='SCOTT' );
문제488. KING 의 월급을 ALLEN 의 월급으로 변경하시오 !
update emp
set sal = ( select sal
from emp
where ename='ALLEN' )
where ename='KING';
문제489. KING 의 부서번호를 JAMES 의 부서번호로 변경하시오 !
update emp
set deptno = ( select deptno
from emp
where ename='JAMES' )
where ename='KING';
emp 와 dept 를 깔끔하게 새로 생성하세요 !
문제490. emp 테이블에 loc 컬럼을 추가하시오 !
alter table emp
add loc varchar2(10) ;
문제491. 지금 추가한 loc 컬럼에 해당 사원의 부서위치로 값을 갱신하시오
( merge 문 말고 상호관련 서브쿼리를 이용한 update 문으로 수행)
update emp e
set loc = ( select loc
from dept d
where d.deptno = e.deptno );
select ename, loc from emp;
※ emp 테이블의 부서번호가 set 절의 서브쿼리에 하나씩 들어가면서
하나씩 갱신하기 때문에 시간이 많이 걸립니다.
alter table emp
drop column loc;
문제492. 사원 테이블에 deptavg(부서평균) 라는 컬럼을 추가하시오
alter table emp
drop column deptavg;
alter table emp
add deptavg number(10,2);
select ename, deptavg from emp;
문제493. deptavg 컬럼에 값을 갱신하는데 해당 사원이 속한 부서번호의
평균월급으로 값을 갱신하시오 !
update emp e
set deptavg = ( select avg(sal)
from emp s
where s.deptno = e.deptno );
rollback;
문제494. 위의 SQL을 튜닝하시오 ! ( merge 문 )
merge into emp e
using ( select deptno , avg(sal) as 부서평균
from emp
group by deptno ) v
on ( e.deptno = v.deptno )
when matched then
update set e.deptavg = v.부서평균 ;
문제495. emp 테이블에 grade 컬럼을 추가하시오 !
alter table emp
add grade number(10) ;
문제496. salgrade 테이블을 사용하여 emp 테이블의 grade 컬럼을
merge 하는데 해당 사원의 급여등급으로 값을 merge
하시오 !
튜닝후:
merge into emp e
using salgrade s
on ( e.sal between s.losal and s.hisal )
when matched then
update set e.grade = s.grade;
rollback;
문제497. 위의 merge 문을 상호관련 서브쿼리를 사용한 update 문으로
작성하시오 !
튜닝전:
update emp e
set grade = ( select grade
from salgrade s
where e.sal between s.losal and s.hisal );
튜닝후:
merge into emp e
using salgrade s
on ( e.sal between s.losal and s.hisal )
when matched then
update set e.grade = s.grade;
문제498. merge 문이 튜닝후 SQL임을 증명하기 위해서 대용량
테스트 테이블을 다음과 같이 생성하시오 !
drop table sales100;
-- 실습 환경만들기
create table sales100
as
select *
from sh.sales;
create table sales200
as
select rownum rn, prod_id, cust_id, time_id, channel_id,
promo_id, quantity_sold, amount_sold
from sales100;
alter table sales200
add date_id date;
create table time2
( rn number(10),
date_id date );
begin
for i in 1 .. 918843 loop
insert into time2
values( i , to_date('1961/01/02','YYYY/MM/DD')+ i );
end loop;
end;
/
commit;문제499. time2 테이블을 사용하여 sales200 테이블을 merge 하는데
time2 의 date_id 로 sales200 의 date_id 를 갱신하시오
SQL> merge into sales200 s
using time2 t
on ( s.rn = t.rn )
when matched then
update set s.date_id = t.date_id;
경 과: 00:00:06.27
SQL> rollback;
문제500. (오늘의 마지막 문제) 위의 SQL의 튜닝전 SQL을 작성하시오
상호관련 서브쿼리를 이용한 update 문으로 작성하시오 !
update sales200 s
set s.date_id = (select t.date_id
from time2 t
where t.rn = s.rn);
세시간이 넘게 걸려도 안된다..
