[7월 12일 점심시간 문제]
부서번호, 부서번호별 평균월급을 출력하는 view를 생성하세요.
view 이름은 dept_avg 로 생성하세요.
create or replace view dept_avg
as
select deptno, round(avg(sal)) as 평균월급
from emp
group by deptno;
select * from dept_avg;✅ 뷰 만드는 문법 다음 select 절에 그룹함수를 쓸 때는 컬럼별칭을 함께 써줘야한다!!
복습(VIEW)
view 를 사용하는 이유?
- 보안상의 이유 (민감한 데이터를 노출하지 않기위해)
- 복잡한 쿼리문을 단순하게 쿼리하기 위해서
view 의 옵션 2가지
- with read only : DML 작업이 아예 안되게 막는 옵션
- with check option : 특정 조건의 행에 데이터를 갱신하지 못하게 막는 옵션
※ view 는 데이터를 저장하지 않고 그냥 테이블을 바라보는 쿼리문이다.
근데 view 에서 바꾸면 기본 테이블에서도 바뀐다.
SQLD 기출 문제
문제. (단답형) 다른 테이블에서 파생된 테이블로, 물리적 데이터가 저장되는 것이 아닌 논리적으로 존재하는것을 무엇이라고 하는가?
답. view
(근데 테이블은 아님!!)
풀어보기!
🟥 참고사항
✔️ group 함수와 group by 절을 써서 만든 복합뷰는 아예 DML이 안된다.
✔️ 조인을 해서 만든 view 는 1쪽 테이블 (더 적은 테이블) 은 수정이 안되는데,
M쪽 테이블 (더 많은 테이블:emp) 은 수정이 된다.
대신 dept 테이블 deptno 에 primary key 제약이 걸려있어서 중복된 부서번호가 없다는 것이 보장이 되어야 수정이 가능하다. (SQLP 객관식, 성능고도화책) ★★★★★
(나중에 다시 복습해보기)
문제 552.
직업, 직업별 토탈월급을 출력하는 view 를 생성하시오 (이름 : job_sumsal)create or replace view job_sumsal as select job, sum(sal) as sumsal from emp group by job;
view 의 insert
문제 553. 위의 뷰 job_sumsal 에 아래의 데이터를 입력하시오
job : enginner sumsal : 5600insert into job_sumsal(job, sumsal) values('engineer',5600);오류
insert into job_sumsal select job, sum(sal) from emp group by job;또 오류
SQL 오류: ORA-01733: 가상 열은 사용할 수 없습니다
즉, inserting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error 는 맞는말이다.▽
OCP 시험문제 에서 보기 E 내용임!
문제 554. job_sumsal 뷰의 데이터에서 job 이 SALESMAN 인 데이터를 지우시오
delete from job_sumsal where job='SALESMAN';SQL 오류: ORA-01732: 뷰에 대한 데이터 조작이 부적합합니다
보기 F도 정답임~
✔️ Group by 절을 포함하는 복합뷰는 insert 와 delete 가 안된다!!
그렇다면 단순뷰는?
문제 555. 아래의 단순뷰를 생성하시오 -> 테이블의 갯수가 한개인..!
create or replace view emp98 as select * from emp where job !='ANALYST';
문제 556. emp98 뷰에 이름이 KING 인 사원을 지우시오 (복합뷰는 안지워졌었음)
delete from emp98 where ename = 'KING';☆ 단순뷰에서는 지워진다! 실제 EMP 테이블에서도 지워졌다.
✔️ 뷰의 데이터를 지우면 실제 뷰 관련 테이블의 데이터를 지우는 것이다!! 잊지말깅
문제 557. emp98 뷰에 아래의 데이터를 입력하시오
사원번호 : 3845 사원이름 : JANE 월급 : 4000 부서번호 : 20insert into emp98 (empno, ename, sal, deptno) values (3845, 'JANE', 4000, 20 );데이터가 생겼다 (insert 성공)
✔️ 뷰에 데이터를 입력하면 뷰와 관련된 테이블에 데이터가 입력된다!! (지우는거, 입력하는거 마찬가지)
✔️ 테이블에 대해서 select, update, delete 에 권한을 주었으면
테이블에 관련된 view 에 대해서는 select, update, delete 권한을 따로 주지 않아도 가능하다.
c##scott c##king
emp테이블
grant select on emp to c##king; -> update emp98
grant update on emp to c##king; set sal=0
grant delete on emp to c##king; where ename=ALLEN';
(EMP 테이블에 대한 권한부여) (EMP98 뷰 도 업데이트 가능)
문제 558. 아래의 보기가 맞는지 틀린지 테스트 하시오
B. Tables in the defining query of a view must always exist in order to create the view (뷰를 만들기위해서는 테이블이 항상 존재해야한다)create view dept_view2 as select * from dept900;ORA-00942: 테이블 또는 뷰가 존재하지 않습니다
-> create force view dept_view2 as select * from dept900;force 옵션 사용하면 테이블이 없어도 view 를 생성할 수 있다. (밑에 옵션 설명 참고)
디폴트는 noforce 임!
View 생성할 때 쓰는 구문 옵션 (오라클 정교재)
: CREATE VIEW 문에 subquery를 포함하여 뷰를 생성할 수 있습니다.
구문 설명:
★ OR REPLACE 뷰가 이미 있는 경우 다시 생성합니다.
★ FORCE 기본 테이블의 존재 여부에 관계없이 뷰를 생성합니다. (위에 문제 증명에 사용된 것)
★ NOFORCE 기본 테이블이 있는 경우에만 뷰를 생성합니다 (기본값 디폴트)
-> force, noforce : 매번 사용하는것은 아님!! 그냥 알고만 있으면 됨.
view 뷰의 이름입니다.
alias 뷰의 query에서 선택한 표현식의 이름을 지정합니다(alias의 수와 뷰에서 선택한 표현식의 수가 일치해야 함).
subquery 완전한 SELECT 문입니다 (SELECT list에서 열의 alias 사용 가능).
WITH CHECK OPTION 뷰에서 액세스할 수 있는 행만 삽입하거나 갱신할 수 있도록 지정합니다.
constraint CHECK OPTION 제약 조건에 할당되는 이름입니다.
WITH READ ONLY 현재 뷰에서 DML 작업을 수행하지 못하도록 합니다.
view 제거
✔️ 뷰는 데이터베이스의 기본 테이블을 기반으로 하기 때문에 뷰를 제거해도 관련된 테이블이 삭제되는 것은 아니다.
✔️ 문법: drop view emp98; -> emp 테이블 그대로 있음
✔️ 그러나 이 뷰를 가지고 만든 procedure 는 무효화 된다.
✔️ 생성자나 DROP ANY VIEW 권한을 가진 유저만 뷰를 제거할 수 있습니다.
문제 559.
내가 가지고 있는 db의 권한이 무엇인지 확인하시오select * from session_privs;100개 이상 권한을 가지고 있다. DBA 권한을 받았기 때문에 이렇게 많은것
문제 560.
session_privs 에서 DROP_ANY_VIEW 가 있는지 확인하시오select * from session_privs where privilege ='DROP ANY VIEW';
있다! 그래서 뷰를 드롭 할 수 있다!
문제 561. 내가 생성한 VIEW 가 뭐가 있는지 확인하시오
select * from user_views;그동안 만들었던 view확인 가능
TEXT 부분이 view 만들때 만든 쿼리문임
문제 562.
가지고있는 view 중에 job_sumsal 뷰를 삭제하시오drop view job_sumsal; (drop view view이름)
VIEW 끝~
책이랑 목차 다르게 인덱스 전에 시퀀스 먼저 하신다구 함
097. 절대로 중복되지 않는 번호 만들기 (SEQUENCE)
: 일련 번호 생성기 (순서대로 번호를 생성하는 DB object)
ex) 쿠팡의 주문 테이블이 있다면 주문번호는 순서대로 부여된다.
은행의 번호표 기계.. 기계가 없이 사람이 일일히 번호를 적어서 나눠준다면 오차가 발생할 것이다.
이런것들이 db에서도 마찬가지다.
✔️ sequence 를 사용하지않고 하드코딩하여 사용하면 실수가 발생할 수 있다.
하지만 오라클의 시퀀스를 생성하면 번호가 순서대로 잘 생성이 된다!
🟥 SEQUENCE
• 고유 번호를 자동으로 생성할 수 있습니다.
• 공유할 수 있는 객체입니다.
• Primary key 값을 생성하는 데 사용할 수 있습니다.
• 응용 프로그램 코드를 대체합니다. (밑의 예시 참고)
• 시퀀스 값이 메모리에서 캐시된 경우 액세스 속도가 향상됩니다.
시퀀스의 응용 프로그램 코드 대체
insert into 주문테이블 (주문번호, 주문상품, 배송지)
values (시퀀스이름.nextval, '노트북' , '서울시');-> 이상적인 시퀀스 활용 insert 문
select max(주문번호) +1 into :v_order -> :v_order 는 변수(빈 컵 상태)
from 주문테이블
insert into 주문테이블 (주문번호, 주문상품, 배송지)
values (:v_order, '노트북' , '서울시'); -> 개선해야 할 개발환경의 insert 문 (요런게 위에 말한 응용프로그램코드)
대용량이 되면 위의 select 문이 느려진다.
max(주문번호) +1 로 번호를 생성하지말고 시퀀스를 사용해서 번호를 생성하길 권장한다.
CREATE SEQUENCE 문: 구문
🟥 구문 설명:
sequence 시퀀스 생성기의 이름입니다.
INCREMENT BY n 시퀀스 번호 사이의 간격을 지정하며, 여기서 n은 정수입 니다(이 절을 생략하면 시퀀스는 1씩 증가함)
START WITH n 생성할 첫번째 시퀀스 번호를 지정합니다(이 절을 생략하면 시퀀스는 1부터 시작함).
MAXVALUE n 시퀀스가 생성할 수 있는 최대값을 지정합니다.
NOMAXVALUE 오름차순 시퀀스의 경우 최대값 10^27을, 내림차순 시퀀스의 경우 –1을 지정합니다(기본 옵션).
MINVALUE n 최소 시퀀스 값을 지정합니다.
NOMINVALUE 오름차순 시퀀스의 경우 최소값 1을, 내림차순 시퀀스의 경우 –(10^26)을 지정합니다(기본 옵션)
CYCLE | NOCYCLE 최대값이나 최소값에 도달한 후에 시퀀스를 계속 생성할지 여부를 지정합니다(NOCYCLE이 기본 옵션임).
CACHE n | NOCACHE Oracle 서버가 메모리에 미리 할당하고 저장하는 값의 개수를 지정합니다(Oracle 서버는 기본적으로 20개의 값을 캐시함).
예제.
1. 시퀀스 생성하기create sequence seq1 increment by 1 -> 증가치 start with 1 -> 시작숫자 maxvalue 100 -> 최대값 nocycle -> 순환여부 cache 20; -> 미리 메모리에 올려놓을 번호의 갯수 (성능을 위해)
주문 테이블을 생선합니다.
create table cuppang_order ( order_num numver(10), order_name varchar2(20), address varchar2(30) );cuppang_order 테이블에 데이터를 입력합니다.
insert into cuppang_order values ( seq1.nextval, '노트북', '서울시 송파구' ); insert into cuppang_order values ( seq1.nextval, '무선 마우스', '서울시 강남구' ); select * from cuppang_order;100번 넘게 입력하면,
오류가 발생한다!
이 오류가 나기전에 DBA 는 지금 현재 몇번까지 번호가 생성되었는지 확인을 하고,
maxvalue 에 도달할 것 같으면, maxvalue 값을 늘려주어야한다. (DBA 역할)
NEXTVAL 및 CURRVAL Pseudocolumn
nextval
NEXTVAL pseudocolumn은 지정된 시퀀스에서 연속적인 시퀀스 번호를 추출하는 데 사용됩니다.
NEXTVAL에 시퀀스 이름을 지정해야 합니다. sequence.NEXTVAL을 참조하면 새 시퀀스
번호가 생성되고 현재 시퀀스 번호가 CURRVAL에 입력됩니다
CURRVAL
현재 시퀀스 값을 확인하는 곳 시퀀스.currval 로 현재 시퀀스 값 확인 가능
문제 563.
현재 seq1 의 현재 시퀀스 값을 확인하시오select seq1.currval from dual;
100번까지 찬 것을 확인 할 수 있다.
문데 564.
seq1 의 설정값이 어떻게 되어 있는지 확인 하시오select * from user_sequences;
max_value 가 100이라는 것을 확인할 수 있다.
문제 565.
seq1 의 maxvalue 값을 200으로 늘리시오alter sequence seq1 maxvalue 200;
200으로 늘어났다!
❗❗ dba가 관리해야 하는 일 중에서 시퀀스 maxvalue 값에 도달해서 에러나지 않게끔해야하는 일을 꼭 챙겨야한다.
시퀀스 수정
문제 566.
seq2 시퀀스를 생성하는데 시작값은 1로 하고 최대값은 100000, 증가치는 1,
cycle 로 하고 cache 는 20으로 줘서 시퀀스를 생성하시오create sequence seq2 increment by 1 start with 1 maxvalue 100000 cycle cache 20;select * from user_sequences; 로 시퀀스 만들어진 것 확인
문제 567.
seq2 시퀀스의 시작값을 1이 아닌 5로 변경하시오alter sequence seq2 start with 5;
✔️ 시작값 (start with) 은 alter 명령어로 수정할 수 없다.
✔️ start with 말고는 다 변경이 가능하다.
🟥 시퀀스 수정 지침
• 시퀀스의 소유자이거나 시퀀스에 대해 ALTER 권한이 있어야 합니다.
• 후속 시퀀스 번호에만 적용됩니다.
• 다른 번호로 시퀀스를 재시작하려면 시퀀스를 삭제하고 다시 생성해야 합니다.
• 일부 유효성 검사가 수행됩니다. 예를 들어, 현재 시퀀스 번호보다 작은 새 MAXVALUE를 적용할 수 없습니다
• 시퀀스를 제거하려면 DROP 문을 사용합니다.
시퀀스 drop
문제 568. seq1 시퀀스를 드롭하시오
drop sequence seq1;cuppang_order 테이블은 그대로 있다.
ex. 번호표 뽑는 기계 삭제했다고 번호가 사라지는것은 아니다!
✔️ 시퀀스를 drop 했어도 이미 생성한 번호들은 그대로 잘 테이블에 입력되어있다.
sequence 관련해서 dba가 알아야할 tip
create sequence seq3
start with 1
increment by 1
maxvalue 100
cahce 20; -> 1번부터 20번까지 미리 메모리에 올려놓겠다 🟥 시퀀스 파라미터 중에 cache 를 사용할 때 주의할 사항
갑자기 db 가 다운되었을 때, cache 까지 미리 만들어놨던 번호는 사라지고
다시 db 열었을 때는 1번이 21번으로 나온다.
보통은 중복 되지만 않으면 상관없다고 하지만,,
반드시 순서대로 나와야한다면 시퀀스 드롭하고 다시 만들어야한다.
-> no cache 로 주면 이런일은 안생기지만, 성능을 위해서 절대 nocahce로 시퀀스를 만들지마라.
- shutdown abort : 급할 때 db 내려보내서 접속 끊기게 하는. oracle 인스턴스가 종료되게하는 (도스)
- startup : db올려주는
-> 얘네 왜 한거임?..
강사님 정리..
시퀀스 번호를 1번,2번 생성한 후에 db가 비정상종료 되었다가 다시 startup 이 되면
시퀀스 번호는 3번이 아니라 21번이 된다.
왜냐하면 메모리에 올렸던 번호들이 다 사라져버렸기 때문입니다.
그래서 번호의 순서가 순차적으로 시퀀스를 통해서 테이블에 입력되어야 한다면
이부분을 신경써야한다.
문제 569.
시퀀스 ocp 시험문제 환경을 내 db에 설정하라create sequence ord_seq increment by 1 ->증가치 start with 1 ->시작숫자 maxvalue 100000 ->최대치 cycle ->순환한다. 1-100000까지 가고 다시 1로~ cache 5000; ->미리 메모리에 1-5000까지 올린다create table ord_items ( ord_no number(4) default ord_seq.NEXTVAL NOT NULL, -> 여기에 아무것도 안넣으면 시퀀스값을 넣겠다는말 (?) item_no number(3), qty number(3), expiry_date DATE, CONSTRAINT it_pk PRIMARY KEY (ord_no, item_no));insert into ord_items( item_no, qty, expriy_date) values (201, 400, sysdate) ;-> 암시적으로 null 넣었음
암시적으로 null 넣으면 디폴트로 설정해준 값이 나온다.명시적으로 null 넣어본다면?
insert into ord_items( ord_no, item_no, qty, expiry_date) values (null, 201, 400, sysdate) ;
PRIMARY KEY 제약이 걸려있어서 null 넣을 수 없음!
❗❗시퀀스 생성시, cycle 옵션을 줬으면 maxvalue 값까지 다 출력된 다음에
다시 시작하는 숫자가 start with 값이 아니라, minvalue 값으로 시작한다.
cycle 썻을때는 minvalue 를 start with 랑 맞춰줘야한다!!
✅ cycle 로 만든다면 maxvalue 까지 갔다가 다시 처음으로 가기떄문에, 무조건 중복되는 것!
(중복 X 보장 된 것 아님!)
098. 데이터 검색 속도를 높이기 (INDEX)
🟥 인덱스
• 스키마 객체입니다.
• Oracle 서버에서 포인터를 사용하여 행 검색 속도를 높이는데 사용할 수 있습니다.
• 신속한 경로 액세스 방식을 사용하여 데이터를 빠르게 찾아 디스크 I/O(입/출력)를 줄일 수 있습니다.
• 인덱스의 대상인 테이블에 독립적입니다. (ex. 책이 테이블이라면 목차가 인덱스)
• Oracle 서버에서 자동으로 사용되고 유지 관리됩니다. (ex. 책의 내용이 바뀌면 목차도 바뀌게)
인덱스의 구조
구조 = 컬럼값 + rowid
✔️AAA, AAB 등등이 rowid (row의 주소 (페이지 번호..?) )
✔️컬럼값은 ascending 하게 정렬되어 있다.
✅ 인덱스를 ename 에 생성하는 쿼리문
create index emp_ename
on emp(ename);✅ ename 의 인덱스만 보는 쿼리문
select ename, rowid
from emp
where ename > ' ' ; 
order by 안썼는데 abc 순으로 정렬 되었다.
✅ 위의 SQL의 결과가 테이블에서 읽어온 데이터가 아니라 emp_ename index에서 읽어온 데이터임을 확인하시오
explain plan for
select ename, rowid
from emp
where ename > ' ' ;
select * from table(dbms_xplan.display); 
문제 570. emp 테이블에 sal 에 인덱스를 생성하시오. 인덱스 이름은 emp_sal 이라고 하시오
create index emp_sal on emp(sal);
문제 571. emp_sal 인덱스의 구조를 확인하시오
select sal, rowid from emp where sal >= 0;❗where 절이 있어야 테이블에서 읽어온게 아니라 인덱스에서 읽어온거라는게 된다!
문제 572. emp 테이블에 hiredate 에 인덱스를 생성하시오
create index emp_hiredate on emp(hiredate);
문제 573. emp 테이블에 hiredate의 인덱스의 구조를 확인하시오
select hiredate, rowid from emp where hiredate < to_date('9999/12/31','RRRR/MM/DD');
index 에서 where 절 정렬
- 문자 : > ' '
- 숫자 : >= 0
- 날짜 : < to_date('9999/12/31','RRRR/MM/DD') ★날짜 쓰는거 참고!!★
✅ rowid 는 로우(행) 의 주소인데, 인덱스에도 있고 테이블에도 있습니다. (행마다 유니크함)
select rowid, empno, ename, sal
from emp;문제 574. 이름이 SCOTT 인 사원의 이름과 월급을 출력하시오
select ename, sal from emp where ename='SCOTT';ename 에 인덱스를 생성해놓았기 때문에 이 데이터를 찾는 방법이 두가지로 나뉨
인덱스 생성된 컬럼에서 데이터 찾는 방법 두가지
- full table scan : scott 데이터를 찾기위해서 테이블을 처음부터 끝까지 모두 스캔한것
- index range scan : scott 데이터를 찾기위해서 목차(index) 를 통해서 테이블을 엑세스 한것
✅ 당연히 index range scan 이 좋은 성능을 보인다!
문제 575.
여러분들의 옵티마이저는 아래의 SQL을 실행할 때 full table scan을 했는지
index range scan 을 했는지 실행계획을 확인하시오explain plan for select ename, sal from emp where ename = 'SCOTT'; select * from table(dbms_xplan.display);
✅ index 인덱스를 통해서 테이블을 엑세스 한다는것은
Oracle 서버에서 포인터를 사용하여 행 검색 속도를 높이는데 사용할 수 있습니다
딱딱 찍어서 찾는것!
다 버리면 쭉 메모리를 많이 올린다.
풀테이블 스캔 힌트
/*+ full(테이블명)*/문제 576.
아래의 SQL의 실행계획이 'full table scan' 이 되게 하시오select /*+ full(emp) */ ename, sal from emp where ename='SCOTT' select * from table(dbms_xplan.display);
문제 577. 월급이 1250인 사원의 이름과 월급을 출력하는 쿼리문을 작성하는데,
full table scan 이 되게 하시오select /*+ full(emp)*/ ename, sal from emp where sal=1250;
인덱스 스캔 힌트
/*+ index(테이블명 인덱스이름)*/문제 578.
위의 sql 이 emp_sal 인덱스를 통해서 테이블을 엑세스 할 수 있도록 힌트를 주시오select /*+ index(emp emp_sal)*/ ename, sal from emp where sal=1250;설명
/*+ index(테이블명 인덱스이름)*/
❗힌트? 실행계획을 제어하는 명령어 -> 얼토당토않는걸 주면 안되고, 합리적인걸 줘야함!
/*+ 어쩌구저쩌구 */index SQL 튜닝방법 첫번째
- where 절에 좌변을 가공하지 말아라 -> full scan 해버림
✔️튜닝전
select ename, sal
from emp
where sal*12=36000;
✔️튜닝후
select ename, sal
from emp
where sal=36000/12; 
-> 인덱스를 탄다. (?) 탄다가 뭐임
문제 579. 사원 테이블에 job 에 인덱스를 생성하시오
(인덱스 이름은 emp_job 으로 하세요)create index emp_job on emp(job);
문제 580. 오늘의 마지막문제
아래의 sql을 튜닝하시오
- 튜닝전 (full table scan)
select ename, sal, job from emp where substr(job, 1, 5)='SALES';
- 튜닝후 (index range scan, 좌변을 가공하지 않게하기)
select ename, sal, job from emp where job like 'SALES%';
