230717 Oracle SQL 16 [제약, WITH절, subquery factoring, DCL, 권한관리]

권주희·2023년 7월 17일
DCLdisablematerializenovalidaterolesubquery factoringtemp tablewith절객체권한관리권한관리임시테이블제약 중지
0
post-custom-banner

[7월 17일 점심시간 문제] 사원 테이블에 직업 컬럼에 제약을 거세요

  1. 명령 프롬프트창을 열고 emp 테이블과 dept 테이블을 초기화 합니다.

SQL> @init_emp.sql

  1. EMP 테이블에 직업에 check 제약을 거는데 직업이 SALESMAN, ANALYST, CLEKRK, MANAGER, PRESIDENT 만 입력되거나 수정되겠금 check 제약을 거세요.

제약 중지 기능

disable
-> 삭제하는 것은 아니고, 잠시 중지시키는 것

🤔 언제 제약을 중지시키는가?
ex ) 주로 check 제약 관련해서 중지를 많이 시키는데,
예를 들면 사원테이블에 월급을 check 제약을 거는데, 월급이 0-9000 사이의 데이터만 입력 혹은 수정되게끔 check 제약을 걸면 앞으로 월급을 0-9000 사이외의 값으로는 갱신하지 못하게 된다.
그런데 사장인 KING 은 예외로 9500 으로 월급을 갱신하면서 계속해서 다른 사원들의 데이터는 0-9000 사이의 데이터로 제약을 유지하고 싶을때

TEST

  1. emp 와 dept 를 초기화 합니다.
  2. 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'


check 제약 걸려서 변경 불가능!

  1. 체크 제약을 중지 시킵니다.
alter table emp
 disable constraint emp_sal_ck;
select constraint_name, status
 from user_constraints
 where table_name ='EMP';

  1. KING 의 월급을 9500 으로 변경합니다.
update emp
 set sal=9500
 where ename ='KING';

➡️ 2개 행 이(가) 업데이트되었습니다!
5. 다시 check 제약을 활성화 시킵니다.

alter table emp
 enable constraint emp_sal_ck;


➡️ 제약에 위반 데이터인 9500이 있기 때문에 다시 활성화가 안되었다.

⬇️

조건 위반데이터 무시하기

🤔 하지만, 제약에 위반된 데이터가 있지만 제약을 활성화 시키고 싶다면,
novalidate

alter table emp
 enable novalidate constraint emp_sal_ck;

❗novalidate 옵션을 쓰면, 제약에 위반된 데이터가 있어도 제약을 활성화 시킬 수 있다. (예외 옵션)

109. WITH 절 사용하기

(ocp본교재 FUND2->sg1.pdf-> 253p.)

with 절

  • 하나의 SQL 안에서 반복되는 slow 쿼리문이 여러번 나타나서 작성되서 사용되고 있을때,
    성능을 높이기 위해서 사용되는 sql 문법
  • 다른 SQL 과는 다르게 한번 쿼리한 결과를 temporary tablespace (임시테이블) 에 저장해서 temp 테이블로 구성해서 성능을 높인다.

with절로 튜닝

✅ with 절을 사용하지 않았을때의 문장 (sql200제 책의 279p)

select job, sum(sal) as 토탈
 from emp
  group by job
  having sum(sal) > ( select avg( sum(sal) )
                       from emp
                       group by job ) ;

having 절을 사용한 서브쿼리 (=무거운 sql)
-> 직업이 겁나 많아서 메인쿼리가 20분 걸린다면, 서브쿼리도 20분 걸릴 것이다.

✅ with 절을 사용했을 때의 문장 (sqp200제 책의 278p)

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) ;



(5건으로 줄인 초록색 임시테이블을 먼저 임시스페이스에 저장한다음에,
그중에서 뽑아낼 수 있게 하는것!!)

❗ with 절 로 슬로우쿼리의 결과를 temp table 로 구성을 먼저 하고,
이 temp table 에서 데이터를 엑세스 하는 쿼리문을 그 아래에 작성한다.
기존에 40분 걸리던 sql 이 절반으로 수행된다!

문제 639.
아래의 sql 의 실행계획을 확인하시오 (with 절 사용)

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);


temp 테이블을 먼저 생성하고 full scan 했다.

문제 640. 아래의 sql 의 실행계획을 확인하시오 (with절 튜닝 전)

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);

  • filter : 메인쿼리부터 수행한 쿼리문

with절 사용

  • 똑같은 쿼리블럭을 한번 이상 사용 할 때
  • Using the WITH clause, you can use the same query block
    in a SELECT statement when it occurs more than once
    within a complex query.
    • The WITH clause retrieves the results of a query block and
    stores it in the user’s temporary tablespace.
    • The WITH clause may improve performance
    해석해서 정리~

OCP 기출문제.

A : 임시테이블에 저장하기 때문에 큰 쿼리의 퍼포먼스를 높인다(성능을 높인다) ⭕
B : 같은 쿼리블럭을 다시 select 문에서 사용할 수 있다. ⭕
C : permanently ❌ 영구히 x
D : permanently ❌ 영구히 x
-> 답 A,B

문제 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 depno_cnt
where count(*) > (select avg(cnt)
                  from deptno_cnt ) ;

책 예제. (temp table 2개 사용)

⬇️

temp table 을 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;


110. WITH절 사용하기 2 (SUBQUERY FACTORING)

with절에서 subquery factoring 기능 구현하기

(SQL200제 책의 280p.)

subquery factoring

❗앞에서 구현한 temp 테이블을 다른 temp 테이블을 구성하는 쿼리문에서 사용할 수 있는 기능
with절을 이용해서 구현할 수 있다!

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;


두번째 temp table 구성할때, 첫번째 temp table 을 쓰는것
-> subquery factoring
순차적으로 구성되기 때문에 가능. having 절의 서브쿼리로는 불가능


✅ with 절을 이용하지 않고 서브쿼리로만 구현하려고 하면 에러가 난다

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 ;

인라인뷰 2개 사용해서 구현해보려고 했으나 오류남!

문제 642. 아래의 sql을 with 절로 변경하시오 (에러가 나는 sql)

select  a.address, a.평균
 from ( select  telecom, avg(age) as 평균
         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 평균 > (select min(평균)
                                    from t ) )
 select address, 평균
   from a;

✔️ with 절을 사용하는 이유는 자주 반복하는 slow 쿼리문의 결과를 temp 테이블로 구성해서
temp 테이블에서 반복되는 데이터를 읽어와서 쿼리문을 수행하므로 성능을 높일 수 있기 때문이다.

with절 관련한 중요한 힌트 2가지

1. /*+ materialize */
2. /*+ inline */

materialize : temp 테이블을 구성하라고 명령하는 힌트
inline : temp 테이블 구성하지 말고 subquery 로 구현해라 라는 힌트

문제 643.
아래의 with절에 위의 2개의 힌트를 각각 사용해서 실행계획을 각각 확인하시오

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 뒤

with /*+ materialize */ job_sumsal as ( select job, sum(sal) as 토탈
                      from emp
                      group by job )
  select job, 토탈
   from job_sumsal
   where 토탈 > ( select avg(토탈)
                   from job_sumsal );

⬇️ temp 테이블로 구성된 것 확인

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 );

❗❗inline 위치 확인!! 괄호안의 셀렉절에 쓰는것

❓with 절만 사용하면 되는데 굳이 서브쿼리로 구현하라는 힌트가 왜 있음?
-> 전부다 with 동시에 사용하다보면 temp table 이 과부화 되어서 lock 걸리게 된 경우가 있음
dba 에게 with절 사용에 대한 허가를 받고 개발자들이 with 절 사용하게끔 하고 있다.


OCP 기출문제 [OCP SQL 12번 문제] with 절

A : SELECT 절에서 사용할수있다 ⭕
B : 한개 이상의 쿼리를 사용할 수 있다 ⭕
C : 쿼리블록의 이름과 테이블 이름이 동일하다면, 테이블 이름이 우선이다 -> 쿼리블럭이름이 우선이다 ❌
D : with 절의 쿼리 이름은 메인 쿼리 블락에서 수행될 수 있다.? (쿼리팩토링 얘기같음) ⭕

111. DCL 권한관리 (DBA의 업무)

(영어 정교재 FUND2 -> 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가지

  • 시스템 권한 : database 에서 create, alter, drop, truncate 같은 어떤 db에 취할 수 있는 action 의 권한
    -> 개발자들한테는 잘 안넣어준다.
  • 객체 권한 : 어떤 특정 테이블의 data 를 select, insert, update, delete, merge 할 수 있는 권한
    -> 개발 팀이나 직급별로 나눠서 지정해주며, 모든걸 다 볼 수 있는건 dba뿐

❓스키마? 테이블, 뷰 및 같은 객체의 컬렉션 순서들?

시스템권한 확인

문제 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 으로 접속한 상태에서 내가 가지고 있는 시스템 권한이 뭐가 있는지 확인하시오

select *
from session_privs;

🟥 다음중 system 권한이 아닌것은? (시험문제)
1. create table
2. create view
3. create sessin
4. select any table
5. select on emp ❌ (객체권한)
❗select any table 은 모든 테이블을 셀렉할 수 있는 중요한 시스템권한인데, select 로 시작해서 객체권한처럼 느껴질수 있지만 시스템권한 임!!

문제 648. select any table 권한을 c##allen 유저에게 grant 하시오

grant select any table
 to c##allen;

줄수 있는 시스템 권한 (?)

  • CREATE SESSION
  • CREATE TABLE
  • CREATE SEQUENCE
  • CREATE VIEW
  • CREATE PROCEDURE

문제 649. c##scott 유져에서 유저를 생성하는데, c##james 라는 유져를 생성하고 접속할 수 있는 권한을 부여하고 create table 권한을 부여하시오 (pw는 tiger로)


그때그때 새로운 사람에게 새로운 권한들을 주는게 번거로우니까,
역할을 정해놓고 그 역할을 주는!

ROLE

-> 권한의 집합
유저와 시스템 권한 사이에 ROLE이 있으면 권한 관리가 편해진다.

관리자는 관리자에게 필요한 권한들을 가지고 role을 만들면되고,
일반 사원은 일반 사원들에게 필요한 권한들을 가지고 role 을 만들면 됩니다.

그리고 관리자가 입사하면 관리자 role 을 부여하면 되고
일반 사원이 입사하면 일반사원 role 만 부여하면 되니까 권한관리가 수월해진다.

🟥 문법

1. manager 라는 role 을 생성합니다.

create role c##manager;

-> 원래는 c##안붙여도 되는데 오라클보안강화로 생긴거같음!

2. maneger 라는 role 에 create session, create table, create view 권한을 부여합니다.

grant create session, create table, create view to c##manager;

-> 여러개 한번에도 가능하다!

3. c##martin 이라는 유저를 만들고, manager 라는 롤을 부여합니다.

create user c##martin
 identified by tiger;

grant c##manager to c##martin;

4. c##martin 유저로 접속해서 내가 가지고 있는 role 이 무엇인지 확인하시오

connect c##martin/tiger

select * from session_roles;


5. c##manager 롤을 삭제하시오

connect c##scott/tiger

drop role c##manager;

❓도스창에서 어떤건 ; 붙이고 어떤건 안붙이고 기준 먼지?

객체권한 관리

특정 테이블의 데이터를 select, insert, update, delete, merge, alter 할 수 있는 권한

문제 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 할 수 있는 권한을 부여하시오

show user -> c##scott 확인
connect c##scott/tiger
grant select, insert on emp to c##jane;

문제 652. c##jane유저로 접속해서 c##scott 유저의 emp 테이블을 select 할 수 있는지 확인하시오

connect c##jane/tiger

select * from emp;

❓❓

왜이럼..
c##scott.emp 로 검색해야 나옴

select * from c##scott.emp;

문제 653. (오늘의 마지막 문제)
c##scott 유저에서 c##black 유저를 생성하고 connect, create table 권한을 부여하세요.
그리고 c##black 유저로 접속해서 테이블을 생성한 다음에,
만든 테이블에 index 가 만들어지는지 확인하세요.
(create index 라는 권한은 따로 주지 않았는데 index 도 만들 수 있는지 test 하면 됨)

create user c##black
 identified by tiger;

grant connect, create table to c##black;
create table emp_test
 (empno number(10),
  ename varchar2(20) );
create index emp_test_idx on emp(empno);

✔️ create 권한 받으면 create index 권한도 같이 자동으로 들어가게 된다

profile
권주희
열씨미하자
이전 포스트

쿼리문

다음 포스트

DCL 사용 문

post-custom-banner

1개의 댓글

comment-user-thumbnail
이태균
2023년 7월 17일

저도 개발자인데 같이 교류 많이 해봐요 ㅎㅎ! 서로 화이팅합시다!

답글 달기