[OCP SQL 5번 문제] 제약(constaint) 부분2
교재: FUN1 폴더에 D49996GC20_sg2.pdf ---> 페이지번호 90번
1. on delete cascade : 부모 테이블의 데이터가 지워질때 자식 테이블의
데이터도 지워지는 옵션
2. on delete set null : 부모 테이블의 데이터가 지워질때 자식 테이블의
foreign key 데이터가 null 로 변경되는것문제637. on delete cascade 옵션을 테스트 하시오 !
-
emp 와 dept 를 초기화 합니다.
SQL> @init_emp.sql
-
on delete cascade 옵션을 써서 부모-자식 관계를 형성합니다.
alter table dept
add constraint dept_deptno_pk primary key(deptno);alter table emp
add constraint emp_deptno_fk foreign key(deptno)
references dept(deptno) on delete cascade;
- dept 테이블의 10 번 데이터를 지우면 emp 테이블의 10번 사원들의 데이터도
지워지는지 확인하시오 !delete from dept where deptno = 10; select * from dept; select * from emp; 설명: https://cafe.daum.net/oracleoracle/SmzH/6 <-- 시험 문제 보기 E 는 맞습니다.
문제638. 이번에는 다시 on delete set null 을 실험하시오 !
-
emp 와 dept 다시 초기화 합니다.
-
on delete set null 옵션을 써서 dept 와 emp 를 부모 자식 관계로 형성하시오
alter table dept
add constraint dept_deptno_pk primary key(deptno);alter table emp
add constraint emp_deptno_fk foreign key(deptno)
references dept(deptno) on delete set null ; -
dept 테이블의 deptno 10번을 지우시오
delete from dept
where deptno = 10 ;select * from emp;
설명: dept 테이블의 10번을 지웠지만 emp 테이블의 10번행이 지워지지는 않았고 deptno 만 null 로 변경 되었습니다.
▣ 제약 중지 기능 테스트
"제약을 삭제하는것은 아니고 중지시키는 것입니다. "
언제 제약을 중지시키는가 ? 주로 CHECK 제약관련해서 중지를 많이 시키는데
예를 들면 사원 테이블에 월급에 CHECK 제약을 거는데 월급이 0~9000 사이의 데이터만 입력 또는 수정되겠금 CHECK 제약을 걸면 앞으로 월급을 0~9000 사이외의 값으로는 갱신하지 못하게 됩니다. 그런데 사장인 KING 은 예외로 9500으로 월급을 갱신하면서 계속해서 다른 사원들의 데이터는 0~9000 사이의 데이터로 제약을 유지하고 싶을 때 중지 기능을 쓰면 됩니다.
테스트 순서:
-
emp 와 dept 를 초기화 합니다.
-
emp 테이블의 월급을 0~9000 사이의 데이터만 입력 또는 수정되게끔 check 제약을 겁니다.
alter table emp
add constraint emp_sal_ck check(sal between 0 and 9000);update emp
set sal = 9500
where ename='KING'; -
체크 제약을 중지 시킵니다.
select constraint_name, status
from user_constraints
where table_name='EMP'; alter table emp **disable constraint** emp_sal_ck; select constraint_name, status from user_constraints where table_name='EMP';  -
KING 의 월급을 9500 으로 변경합니다.
update emp
set sal = 9500
where ename='KING'; -
다시 check 제약을 활성화 시킵니다.
alter table emp
enable constraint emp_sal_ck;ORA-02293: (C##SCOTT.EMP_SAL_CK)을 검증할 수 없습니다.
설명: 제약에 위반 데이터인 9500 이 있기 때문에 활성화가 안됩니다.
제약에 위반된 데이터가 있지만 제약을 활성화 시키고 싶다면
아래와 같이 하면 됩니다.alter table emp
enable novalidate constraint emp_sal_ck;※ novalidate 옵션을 쓰면 제약에 위반된 데이터가 있어도 제약을 활성화 시킬수 있습니다.
▣ 109 WITH절 사용하기 1(WITH ~ AS)
교재: FUN2 --> D49994GC20_sg1.pdf ---> 253 페이지
-
하나의 SQL 안에서 반복되는 slow 쿼리문이 여러번 작성돼서 사용되고 있을때 성능을 높이기 위해서 사용되는 SQL문법
-
다른 SQL과는 다르게 한번 쿼리한 결과를 temporary tablespace 에 저장해서 temp 테이블로 구성해서 성능을 높입니다.
-
with 절을 사용안했을때의 문장 (SQL200제 책의 279페이지)
select job, sum(sal) as 토탈
from emp
group by job
having sum(sal) > ( select avg( sum(sal) )
from emp
group by job ) ; -
with 절을 사용했을때의 문장 ( SQL200제의 278 페이지 )
with job_sumsal as ( select job, sum(sal) as 토탈
from emp
group by job )select job, 토탈
from job_sumsal
where 토탈 > ( select avg(토탈)
from job_sumsal ) ;설명: with 절로 slow Query 의 결과를 temp table 로 구성을 하고
이 temp table 에서 데이터를 엑세스하는 쿼리문을 그 아래에 작성합니다. 기존에는 40분 걸리던 SQL이 20분으로 수행되는것입니다.
문제639. 아래의 SQL의 실행계획을 확인하시오
explain plan for
with job_sumsal as ( select job, sum(sal) as 토탈
from emp
group by job )
select job, 토탈
from job_sumsal
where 토탈 > ( select avg(토탈)
from job_sumsal ) ;
select * from table(dbms_xplan.display);
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 266 | 8 (13)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D670C_2AC0D1 | | | | |
| 3 | HASH GROUP BY | | 14 | 266 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 14 | 266 | 3 (0)| 00:00:01 |
|* 5 | VIEW | | 14 | 266 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D670C_2AC0D1 | 14 | 266 | 2 (0)| 00:00:01 |
| 7 | SORT AGGREGATE | | 1 | 13 | | |
| 8 | VIEW | | 14 | 182 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D670C_2AC0D1 | 14 | 266 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
-> SYS_TEMP_0FD9D670C_2AC0D1, temp table 이름 (우리는 job_sumsal 로 정함 4 -> 3 -> 2 -> 1 순으로 읽음)
id 1 ~ 4까지 먼저 수행한다! (temp table 구성하는 부분, WITH )
5 ~ 9 아래의 쿼리를 나타내는 것.
select job, 토탈
from job_sumsal
where 토탈 > ( select avg(토탈)
from job_sumsal ) ;문제640. 아래의 SQL의 실행계획을 확인하시오 !
explain plan for
select job, sum(sal) as 토탈
from emp
group by job
having sum(sal) > ( select avg( sum(sal) )
from emp
group by job ) ;
select * from table(dbms_xplan.display);
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 266 | 4 (25)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 14 | 266 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 266 | 3 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 19 | 4 (25)| 00:00:01 |
| 5 | SORT GROUP BY | | 1 | 19 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL| EMP | 14 | 266 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
FILTER는 메인쿼리 부터 수행하는 쿼리문이다. id 2 ~ 3
서브쿼리를 수행하였다. id 4 ~ 6temp tablespace -> 임시 테이블, 영구히 저장되는 것이아님!
1. order by
2. temp table
3. with 절 -> with 절이 끝나면 사라짐.문제641. 아래의 SQL을 with 절로 변경하시오 !
select deptno, count()
from emp
group by deptno
having count() > ( select avg(count())
from emp
group by deptno );
답:
with deptno_cnt as ( select deptno, count(*) as cnt
from emp
group by deptno )
select deptno, cnt
from deptno_cnt
where cnt > ( select avg(cnt)
from deptno_cnt );
[7월 17일 점심시간 문제] 사원 테이블에 직업 컬럼에 제약을 거세요
1. 명령 프롬프트창을 열고 emp 테이블과 dept 테이블을 초기화 합니다.
SQL> @init_emp.sql
2. EMP 테이블에 직업에 check 제약을 거는데 직업이 SALESMAN, ANALYST, CLEKRK, MANAGER, PRESIDENT 만 입력되거나 수정되겠금 check 제약을 거세요.
alter table emp
add constraint emp_job_ck
check ( job in ('SALESMAN', 'ANALYST', 'CLERK','MANAGER', 'PRESIDENT') );
select table_name, constraint_name, constraint_type
from user_constraints
where table_name = 'EMP';▣ 110 WITH절 사용하기 2(SUBQUERY FACTORING)
- temp 테이블을 2개 이상 작성하여 with 절을 구성하는 방법 with job_sumsal as ( select job, sum(sal) as sumsal
from emp
group by job ),
deptno_sumsal as ( select deptno, sum(sal) as sumsal
from emp
group by deptno )
select job, sumsal
from job_sumsal
union all
select to_char(deptno), sumsal
from deptno_sumsal;
- with 절에서의 subquery factoring 기능 구현하기(SQL200제의 280 페이지) with job_sumsal as ( select job, sum(sal) 토탈
from emp
group by job ) ,
deptno_sumsal as ( select deptno, sum(sal) 토탈
from emp
group by deptno
having sum(sal) > ( select avg(토탈) + 3000
from job_sumsal ) ) select deptno, 토탈
from deptno_sumsal ;
설명: subquery factoring 이란 앞에서 구현한 temp 테이블을 다른 temp 테이블을
구성하는 쿼리문에서 사용할 수 있는 기능을 말합니다.
with 절을 이용해서 구현할 수 있고 다음과 같이 with 절을 이용하지 않고
그냥 서브쿼리로만 하려 하면 에러 납니다. (SQL200제 281페이지)
select deptno, sum(sal)
from ( select job, sum(sal) 토탈
from emp
group by job ) as job_sumsal,
( select deptno, sum(sal) 토탈
from emp
group by deptno
having sum(sal) > ( select avg(토탈) + 3000
from job_sumsal )
) as deptno_sumsal ;
책에 나온 WITH 절 :
WITH dept_costs AS ( SELECT d.department_name, SUM(e.salary) AS dept_total
FROM employees e JOIN departments d
ON e.department_id = d.department_id
GROUP BY d.department_name),
avg_cost AS ( SELECT SUM(dept_total) / COUNT(*) AS dept_avg
FROM **dept_costs** )SELECT *
FROM dept_costs
WHERE dept_total > ( SELECT dept_avg
FROM avg_cost )
ORDER BY department_name;
문제642. 아래의 SQL 을 with 절로 변경하시오 ! (아래의 SQL은 에러가 나므로
WITH 절로 구현하시오 )
select a.address, a.평균
from ( select telecom, avg(age) 평균
from emp17
group by telecom ) t ,
( select substr(address, 1, 3) address, avg(age) 평균
from emp17
group by substr(address, 1, 3 )
having avg(age) > ( select min(평균)
from t ) ) a;답: with t as ( select telecom, avg(age) 평균
from emp17
group by telecom ),
a as ( select substr(address, 1, 3) as address, avg(age) 평균
from emp17
group by substr(address, 1, 3 )
having avg(age) > ( select min(평균)
from t ) )
select address , 평균
from a;
with 절을 사용하는 이유는 자주 반복되는 slow 쿼리문의 결과를 temp 테이블로 구성해서 temp 테이블에서 반복되는 데이터를 읽어와서 쿼리문을 수행하므로 성능을 높일 수 있기 때문입니다.
▣ WITH 절 관련해서 중요한 힌트 2가지 ?
1. /*+ materialize */ : temp 테이블을 구성하라고 명령하는 힌트
2. /*+ inline */ : temp 테이블 구성하지 말고 subquery 로 구현해라라는 힌트문제643. 아래의 WITH절에 위의 2개의 힌트를 각각 사용해서 실행계획을
각각 확인하시오 !
explain plan for
with job_sumsal as ( select /+ inline / job, sum(sal) as 토탈
from emp
group by job )
select job, 토탈
from job_sumsal
where 토탈 > ( select avg(토탈)
from job_sumsal );
select * from table(dbms_xplan.display);
sk 텔레콤의 요금 청구 빌링 시스템 SQL튜닝 프로젝트 :
TOP 10 SQL튜너 --> WITH 절로 컨설팅 수행
개발자들에게 WITH 절을 쓰라고 권장을 하고 프로젝트
개발자 A, 개발자 B, 개발자 C, 개발자 D, ..............................개발자 Z
WITH WITH WITH WITH ....... WITH
※ dba 에게 with 절 사용에 대한 허가를 받고 개발자들이 with 절 사용하게끔 하고 있습니다. ****
여러 개발자가 여러개의 WITH 절을 쓰면,
데이터의 양이 많으면(temp tablespace의 temp table이 너무 커서 공간을 다쓰고 있다),
WITH 절 내부의 쿼리 테이블이 많을 경우(temp tablespace의 temp table이 너무 많아서 공간을 다쓰고 있다), LOCK 이 걸릴 수 있음.
이럴 경우 /+ inline / 을 사용해서 temp 테이블을 구성하지 말고 서브쿼리로 구현해라!
WITH 절이 느리다고 서브쿼리로 바꾸는 경우가 많다. 쌩으로 바꾸는거 보다 /+ inline / 힌트를 알면 바꾸기 얼마나 중요한지 알게 될꺼야
▣ 111. DCL 권한관리 ( 완전히 DBA 만이 할 수 있는 업무 )
FUN2 ---> D49994GC20_sg1.pdf --> 37 페이지
- SQL의 종류
1. DML (데이터 조작 언어): insert, update, delete, merge, select
2. DDL (데이터 정의 언어): create, alter, drop, truncate, rename
3. DCL ( 데이터 제어 언어) : grant, revoke
4. TCL ( 트랜잭션 제어 언어) : commit, rollback, savepoint
- 오라클 db 권한의 종류 2가지
1. 시스템 권한 : database 에서 create, alter, drop, truncate 같은 어떤 db에
취할 수 있는 action 의 권한
2. 객체 권한 : 어떤 특정 테이블의 data 를 select, insert, update, delete, merge
할 수 있는 권한문제644. 내가 현재 소유하고 있는 시스템 권한이 뭐가 있는지 확인하시오 !
select *
from session_privs;
설명: 첫날 dba 권한을 c##scott 에게 부여했기 때문에 200개가 넘는 시스템 권한을
가지게 된것 입니다.
문제645. c##scott 유져에서 c##allen 이라는 유져를 생성하시오 !
create user c##allen
identified by tiger;
문제646 c##allen 유져에게 db 에 접속할 수 있는 권한을 부여하시오 !
grant connect to c##allen;
connect c##allen/tiger
show user;
문제647. c##allen 으로 접속한 상태에서 내가 가지고 있는 시스템 권한이
뭐가 있는지 확인하시오 !
show user
select * from session_privs;
PRIVILEGE
--------------------------------------------------------------------------------
CREATE SESSION --------> 접속할 수 있는 권한
SET CONTAINER※ 다음중 system 권한이 아닌것은 ? 답: 5
- create table
- create view
- create session
- select any table
- select on emp ----------> 객체 권한
문제648. select any table 권한을 c##allen 유져에게 grant 하시오 !
c##scott ------------------------------> c##allen
↓
grant select any table to c##allen ;문제649. c##scott 유져에서 유져를 생성하는데 c##james 라는 유져를 생성하고
접속할 수 있는 권한을 부여하고 create table 권한을 부여하시오.
( 패스워드는 tiger 로 만드세요 )
create user c##james
identified by tiger;
grant connect, create table to c##james;
▣ role 이란 ? 권한의 집합입니다.
유져와 시스템 권한 사이에 role 이 있으면 권한 관리가 편해집니다.
관리자는 관리자에게 필요한 권한들을 가지고 role 을 만들면 되고
일반 사원은 일반 사원들에게 필요한 권한들을 가지고 role 을 만들면 됩니다.
그리고 관리자가 입사하면 관리자 role 을 부여하면 되고 일반 사원이 입사하면
일반사원 role 만 부여되니까 권한 관리가 수월해집니다.
문법:
-
c##manager 라는 role 을 생성합니다.
create role c##manager; -
c##manager 라는 role 에 create session , create table, create view 권한을
부여합니다.```sql grant create session, create table, create view to c##manager; ``` -
c##martin 이라는 유져를 만들고 c##manager 라는 롤을 부여 합니다.
create user c##martin identified by tiger; grant c##manager to c##martin; -
c##martin 유져로 접속해서 내가 가지고 있는 role 이 무엇인지 확인하시오
connect c##martin/tiger select * from session_roles; -
c##manager 롤을 삭제하시오!
connect c##scott/tiger drop role c##manager;
▣ 객체권한 관리
특정 테이블의 데이터를 select, insert, update, delete, merge, alter 할 수 있는 권한
→ 그림, [OCP 문제] alter 도 객체 권한 이다 (DDL 임에도)
문제650. c##scott 유져에서 c##jane 유져를 생성하고 connect 할 수 있는
권한을 부여하시오 !
create user c##jane
identified by tiger;
grant connect to c##jane;
문제651. c##scott 유져가 c##jane 유져에게 c##scott 유져의 emp 테이블을
select , insert 할 수 있는 권한을 부여하시오
connect c##scott/tiger
grant select, insert on emp to c##jane;
문제652. c##jane 유져로 접속해서 c##scott 유져의 emp 테이블을 select 할 수
있는지 확인하시오 !
connect c##jane/tiger
selelct * from c##scott.emp;
문제653. (오늘의 마지막 문제) c##scott 유져에서 c##black 유져를 생성하고
connect 와 create table 권한을 부여하세요. 그리고 c##black 유져로 접속해서 테이블을 생성한 다음에 만든 테이블에 인덱스가 만들어지는지 확인하세요.
( create table 권한만 받았는데 create index 권한 없이 인덱스가 만들어
지는지 테스트 하시오 )
SQL> create user c##black identified by tiger;
사용자가 생성되었습니다.
SQL> grant connect, create table to c##black;
권한이 부여되었습니다.
SQL> connect c##black/tiger;
연결되었습니다.[OCP 문제] create table 권한을 받으면 create index 권한도 같이 자동으로 들어가게 됩니다.
