select rpad(' ', level*2) || ename, sal, connect_by_isleaf as isleaf
from emp
startwith ename ='KING'connectby prior empno = mgr;
설명
#말단 사원이면 1로 출력되고, 아니면 0으로 출력됨
089 계층형 질의문으로 서열을 주고 데이터 출력하기 1
📖
계층형 질의문으로 서열을 주고 데이터 출력하기 1
데이터에서 서열을 발견해서 출력하는 쿼리문
사원 테이블에는 사원간의 서열이 있음
서열에 관련한 컬럼이 mgr임
LEVEL이라는 컬럼을 볼 수 있게 해주는 것
<문법>select level, empno, ename, mgr
from emp
where [조건]
startwith [제일 첫번째 행에 나올 사원을 결정하는 절]
connectby prior [첫번째행의 사원의 부하직원들을 출력하기 위한 연결조건어줌]
#첫번째 행이 무조건 서열 1위로 출력됨.
#CONNECTBY 절에 첫번째행의 사원의 부하직원들을 출력하기 위한 연결조건 적음
#EMPNO = MGR 순서 외우기! 변경되면 안됨
#LEVEL이라는 컬럼 추가!STARTWITH 와 CONNECTBY 는 짝꿍
<실행순서>1.from2.startwith3.connectby4.where5.select
계층형 질의문도 where절을 사용할 수 있는데,
where절을 사용하면 계층형 질의문이 먼저 돌고나서 where절로 데이터를 필터링함
계층형 질의문 데이터 정렬
계층형 질의문에서 서열의 틀을 깨뜨리지 않으면서 데이터를 정렬하려면, order by할 때 특별한 키워드를 하나 기술해야한다.
order SIBLINGS by [컬럼명] [옵션];
=>order 과 by 사이에 SIBLINGS라는 명령어를 기입해야함!
예제1)
다음과 같이 서열을 출력하시오.
select rpad(' ', level*3) || ename as employee,
level, ename, sal, mgr
from emp
startwith ename='KING'connectby prior empno = mgr
예제2)
위의 결과를 다시 출력하는데, 월급이 높은 순서대로 출력하시오.
select rpad(' ', level*3) || ename as employee,
level, ename, sal, mgr
from emp
startwith ename='KING'connectby prior empno = mgr
orderby sal desc;
!ERROR!
서열의 틀이 깨져버림. BLAKE 아래의 팀원들이 깨져버림.
누가 팀장이고 누가 팀원인지가 사라짐.
예제3) (SQLD, OCP등 KEYWORD 넣는 문제로 자주 출제됨)
위의 결과를 다시 출력하는데, 서열의 틀을 잘 유지하면서 출력하시오.
select rpad(' ', level*3) || ename as employee,
level, ename, sal, mgr
from emp
startwith ename='KING'connectby prior empno = mgr
order SIBLINGS by sal desc;
#SIBLINGS를 ORDERBY 사이에 기입하면 서열의 틀을 유지하며 출력됨.
문제풀며 익히기
Q1.
위의 결과를 다시 출력하는데, 이름과 LEVEL과 입사일을 출력하고, 최근에 입사한 사원순으로 출력하되 서열의 틀을 깨뜨리지 않으면서 출력하시오.
A. select rpad(' ', level*3) || ename as employee,
level, hiredate
from emp
startwith ename='KING'connectby prior empno = mgr
order SIBLINGS by hiredate desc;
092 계층형 질의문으로 서열을 주고 데이터 출력하기 4 (★)
📖
계층형 질의문의 짝꿍 함수 SYS_CONNECT_BY_PATH
: 계층형 질의문에서 가로로 데이터를 출력하는 함수
(LISTAGG는 계층형에서 불가)
sys_connect_by_path([컬럼명],'[구분자]')
예제1)
다음과 같이 SYS_CONNECT_BY_PATH함수를 이용한 SQL을 작서하시오.
select ename, SYS_CONNECT_BY_PATH(ename,'/')
from emp
startwith ename ='KING'connectby prior empno = mgr;
#설명
#/KING/JONES/SCOTT => 서열 그대로 유지하면서 이름을 바로 출력해줌
##즉, SCOTT은 KING아래 JONES 아래가 SCOTT임을 알 수 있음
CREATE TABLE [테이블명]
([컬럼명] [데이터유형],
[컬럼명] [데이터유형]);
<DATATYPE>1. 문자형 : VARCHAR2([길이]), char(), long(), clob(), blob()
2. 숫자형 : NUMBER([길이]) ---최대 길이 38byte3. 날짜형 : DATE<CHAR vs VARCHAR2> #한글 한글자 2byte
-CHAR(10) : 고정형 --최대 길이 2000byte-VARCHAR(10) : 가변형 --최대 길이 4000byte- long() : 큰 텍스트 데이터를 입력할 때 사용하는 데이터 유형
ex) 잡코리아의 컬럼 중 자기소개서 컬럼
-clob() : long또는 clob을 사용해도 됨
-blob() : 이미지나 동영상도 오라클에 저장할 수 있는데
그 때 사용하는 데이터 유형이 blob이다
<CHAR vs VARCHAR2>
CHAR(10) : 고정형
M
I
R
A
C
L
ㅤ
ㅤ
ㅤ
10byte일 때 10자리 중 6자리만 써도 4개의 공간 남아있음
만약 UPDATE를 할 일이 많을 것 같은 컬럼이면, 처음부터 테이블을 설계할 때 VARCHAR2가 아니라 CHAR로 생성해야함.(VARCHAR2는 ROW MIGRATION현상이 발생할 수 있기 때문에)
VARCHAR2(10) : 가변형
M
I
R
A
C
L
ㅤ
ㅤ
ㅤ
ㅤ
M
I
R
A
C
L
6자리만 사용시 4개의 공간 회수함
ROW MIGRATION 현상 발생
현장에서 ERD를 보면 CHAR보다는 VARCHAR2가 거의 다임
왜 그런지 이유는 다음과 같음(결론: 조인안되는 경우가 발생해서)
<TEST SCRIPT>create table emp600
( ename varchar2(10),
sal number(10),
deptno varchar2(10) );
create table dept600
( deptno char(10),
loc varchar2(10) );
insert into emp600 (ename, sal, deptno)
select ename, sal, to_char(deptno)
from emp;
insert into dept600(deptno, loc )
select to_char(deptno), loc
from dept;
commit;
<조인해보기>select e.ename, d.loc
from emp600 e, dept600 d
where e.deptno = d.deptno;
!!ERROR!!
선택된 레코드가 없음!=> emp.deptno는 VARCHAR2(10) dept.deptno는 CHAR(10)이기 때문
=> VARCHAR2는 남은 공간을 회수해버리기 때문에 다른 값이 됨
=> 즉 10이라는 값이 들어갔을 때 CHAR(10), VARCHAR(2)가 되어버리기 때문
create table emp907
( empno number(10), <--- 숫자의 길이를 10개까지 입력 가능
ename varchar2(20), <-----영문 문자의 길이를 20자까지 입력 가능
hiredate date); <---- ~9999/12/31까지 가능
문제풀며 익히기
Q1.
아래의 데이터를 저장 할 수 있는 테이블을 emp301이라는 이름으로 생성하시오.
empno : 7566
ename : scott
hiredate : 81/11/17
sal : 3000
comm : 200
deptno : 30
empno : 1111
ename : 김인호
age : 34
gender : 남
address : 서울시 강남구 역삼동
email : sk1234@naver.com
create table emp99
(empno number(10),
ename varchar2(10),
age number(10),
gender varchar2(10),
address varchar2(100),
email varchar2(100));
insert into emp99
VALUES(1111, '김인호', 34, '남자', '서울시 강남구 역삼동', 'sk1234@naver.com');
Q3.
emp99테이블의 address를 varchar2(200)으로 늘리시오.
alter table emp99
modify address varchar2(200);
094 임시 테이블 생성하기(CREATE TEMPORAY TABLE) - DBA만 가능
📖
영구히 데이터를 저장할 수 있는 테이블 : HEAP TABLE
임시로 데이터를 저장할 수 있는 테이블 : TEMPORARY TABLE
CREATEGLOBAL TEMPORARY TABLE
잠깐만 필요할 때
DB에 큰 공간을 사용하는 테이블을 생성할 때 부담스러워 할 때
<테이블 스페이스 사용량 조회 쿼리>
<테이블 스페이스 사용량 조회 쿼리>select t.tablespace_name, free_size,
round( ((t.total_size - f.free_size) / t.total_size) *100) usedspace
from (select tablespace_name, sum(bytes)/1024/1024 total_size
from dba_data_files
groupby tablespace_name) t,
(select tablespace_name, sum(bytes)/1024/1024 free_size
from dba_free_space
groupby tablespace_name) f
where t.tablespace_name = f.tablespace_name(+);
현업의 예 : 통신사에서 통신요금 계산할 때
이번 달 요금 청구를 위해 사용할 테이터를 저장할 때 주로 사용
임시 테이블 옵션 2가지
ON COMMIT DELETE ROWS
: commit 할 때 까지 데이터를 유지
ON COMMIT PRESERVE ROWS
: 세션이 종료될 때 까지 데이터를 유지
예제1)
#1. on commit delete rows 옵션으로 임시 테이블을 생성하기
createglobal temporary table emp_temp3
( empno number(10),
ename varchar2(10),
sal number(10))
ONCOMMITDELETEROWS;
insert into emp_temp3 values(1111, 'scott', 3000);
insert into emp_temp3 values(2222, 'allen', 2000);
select*from emp_temp3;
commit;
select*from emp_temp3;
#즉, 커밋할 때 까지만 데이터가 존재하고
#커밋 후 데이터 사라짐
#2. ON commit preserve rows 옵션으로 임시 테이블 생성하기
createglobal temporary table emp_temp4
( empno number(10),
ename varchar2(10),
sal number(10) )
oncommit preserve rows ;
insert into emp_temp4 values(1111,'scott', 3000);
insert into emp_temp4 values(2222,'allen', 2000);
select*from emp_temp4;
commit;
select*from emp_temp4;
#commit이 아니라 세션이 종료되어야 데이터 사라짐
문제풀며 익히기
Q1.
(cmd창에서 @demo.sql로 테이블 초기 세팅한다음 시작)
dept 테이블과 똑같은 구조로 dept_temp 테이블을 만드는데, commit하면 사라지는 임시 테이블로 생성하시오.
describe dept;
A. createglobal temporary table dept_temp
( deptno number(10),
dname varchar2(14),
loc varchar2(13))
oncommitdeleterows;
Q2.
서브쿼리를 사용한 insert문을 이용해서 dept테이블의 모든 데이터를 dept_temp 테이블에 입력하시오.
A. insert into dept_temp
select*from dept;
select*from dept_temp;
commit;
select*from dept_temp;
Q3.
짝꿍과 나와 누가 운영서버이고, 누가 테스트 서버인지 정했었다.
테스트 서버인 학생이 아래의 테이블을 생성하시오.
create table emp_test7
asselect*from emp;
deletefrom emp_test7
where deptno in(10,20);
update emp_test7
set sal =0;
commit;
Q4.
운영서버 쪽에서 테스트 서버의 테이블을 엑세스 하기 위한 DB 링크를 생성하기
create database link test_link
connectto c##scott
identified by tiger
using'192.168.19.26:1521/xe';
select*from emp_test7@test_link;
Q5.
운영에서 merge문을 이용해서 운영의 emp 테이블로 테스트의 emp_test7을 똑같이 맞추시오.
mergeinto emp_test7@test_link t
using emp e
on ( t.empno = e.empno )
when matched thenupdateset t.sal = e.sal
whennot matched theninsert ( t.empno, t.ename, t.sal, t.job, t.mgr, t.hiredate, t.comm, t.deptno)
values(e.empno, e.ename, e.sal, e.job, e.mgr, e.hiredate, e.comm, e.deptno);
commit;
select*from emp_test7@test_link;
095 복잡한 쿼리를 단순하게 하기 1(VIEW)
📖
오라클의 DB 의 오브젝트 5가지
TABLE : 행(ROW)과 컬럼(COLUMN)으로 이루어진 기본 데이터 저장 구조
VIEW : 데이터를 저장하지 않고 테이블의 데이터를 볼 수 있게 해주는 객체
사용하는 이유
1) 보안상의 이유 때문. => 특정 컬럼의 데이터를 노출하지 않으려고
2) 복잡한 쿼리문을 심플하게 작성하기 위해서
view의 값을 갱신하면 실제 테이블의 데이터가 바뀜
INDEX :
SEQUENCE :
SYNONYM :
view 실습)
#1. 다음의 VIEW를 생성하시오.
createview emp_view
asselect empno, ename, job, mgr, hiredate, deptno
from emp;
#민감할 수 있는 컬럼을 제외하고 생성한 것
#뷰는 데이터를 저장하지 않고 그냥 테이블을 바라보는 것
select*from emp_view;
select*from emp;
!!변경됨!!VIEW를 갱신한다는 것은 실제 테이블의 데이터를 변경하는 것
왜? VIEW는 데이터를 저장하고 있지 않기 때문에. 원래의 테이블의 값이 변경
문제풀며 익히기
Q1.
emp 체이블에서 직업이 ANALYST, SALESMAN, CLERK인 사원들의 모든 컬럼을 보이게 하는 VIEW를 emp_view2로 생성하시오.
createOR REPLACE view emp_view2 --> emp_view2가 없으면 만들고 있으면 대체해라asselect*from emp
where job in ('ANALYST', 'SALESMAN', 'CLERK');
select*from emp_view2;
Q2.
emp_view2의 데이터를 변경하는데 FORD의 월급을 0으로 변경하시오.
update emp_view2
set sal =0where ename ='FORD';
select ename, sal from emp_view2;
select ename, sal from emp;
emp_view2조회
emp조회
095 복잡한 쿼리를 단순하게 하기 1(VIEW)
📖
오라클의 DB 의 오브젝트 5가지
TABLE : 행(ROW)과 컬럼(COLUMN)으로 이루어진 기본 데이터 저장 구조
VIEW : 데이터를 저장하지 않고 테이블의 데이터를 볼 수 있게 해주는 객체
사용하는 이유
1. 보안상의 이유 때문. => 특정 컬럼의 데이터를 노출하지 않으려고
2. 복잡한 쿼리문을 심플하게 작성하기 위해서
1) view 를 사용하면 ?
select ename, loc from emp_dept where loc='DALLAS';
2) view 를 사용안하면 ?
select e.ename, d.loc
from emp e, dept d
where e.deptno = d.depton and d.loc='DALLAS';
3. 특정 DATA를 갱신하지 못하게 할 때
또는 전체 DATA의 DML 여부를 막게 하고 싶을때
EX) 신세계 이마트 백화점 시스템 개발 프로젝트 DBA시절
PM이 포인트를 쌓아주는 적립률 테이블을 절대로
누구도 갱신하지 못하도록 막으라는 요청을 받음
view의 값을 갱신하면 실제 테이블의 데이터가 바뀜
사용이유 3번 연관 내용
VIEW의 옵션 2가지
with check option : 특정 데이터를 갱신 못하게 할 때
뷰를 생성할 때 WHERE절에 기술했던 컬럼의 조건을
추후에 절대로 변경하지 못하게끔 해줘야할 때
사용하는 옵션
CREATE~ASSELECT~FROM~WHERE~WITHCHECK OPTION;
(다음 단원 문제익히기에서 배움)
with read only : 모든 데이터에 대해서 DML 을 막고 싶을때
이마트 신세계 백화점 카드 개발할때 PM 이 요청한 내용 ?
백화점 카드 포인트 적립 기준 테이블에 2%, 5% 적립율이 입력되어있는데
이 테이블에 절대 DML 못하게 막아라 !> lock table emp in exclusive mode;
>COMMIT;하면 LOCK이 풀림
!문제 발생!=> 매일 컴퓨터를 켜고있어야함.
이렇게 하면 안되고 with read only 로 view 로 만들어 주면 됩니다.
createview emp77
asselect*from emp
with read only;
select*from emp77;
INDEX :
SEQUENCE :
SYNONYM :
view 실습)
#1. 다음의 VIEW를 생성하시오.
createview emp_view
asselect empno, ename, job, mgr, hiredate, deptno
from emp;
#민감할 수 있는 컬럼을 제외하고 생성한 것
#뷰는 데이터를 저장하지 않고 그냥 테이블을 바라보는 것
select*from emp_view;
VIEW
내가 만든 뷰 리스트 확인하기
SELECGT *FROM user_views;
VIEW 삭제 및 테이블이 없어도 뷰 생성하기
DROPVIEW [VIEW이름];
<FROCE함수>create FORCE view [view명]
asselect [컬럼명]
from [없는 테이블];
A.
DROPTABLE EMP;
createview emp77
asselect empno, ename, sal
from emp;
!!ERROR!!
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다
Q8.
위의 view 강제로 생성하기
DROPVIEW EMP77;
create FORCE view emp77
asselect empno, ename, sal
from emp;
select*from emp77;
#생성은 되는데 select이 안
096 복잡한 쿼리를 단순하게 하기 2(VIEW)
📖
VIEW의 종류
주의사항
뷰를 생성할 때 그룹함수를 사용했으면 반드시 컬럼 별칭을 사용해야함. 안그럼 생성되지 않음
group by 절을 사용해서 만든 위의 복합뷰는 data 를 update 할 수 없음
group by 절을 사용해서 만든 위의 복합뷰는 data를 delete할 수 없음(19c, 21c 둘 다 delete안됨)
조인 VIEW
Oracl 19C
: 조인으로 만든 view 는 update 가 m 쪽에 해당하는 테이블의 데이터는
update 가 되고 1쪽에 해당하는 테이블의 데이터는 update 가 안됨
dept --------- emp 의 관계는 서로 1:m(다) 관계1 m
dept 테이블의 경우 부서번호가 중복되지 않고 10, 20, 30, 40 각각
하나씩 있음 .하지만 emp 테이블은 부서번호가 여러개임.
Oracl 21c
: 1쪽이든 m쪽이든 다 변경
예제 0) (단순 view)
이름, 월급, 직업을 출력하는 sales_view를 만드는데, 직업이 SALESMAN인 사람은 제외하고 출력하시오.
createor replace view sales_view
asselect ename, sal, job
from emp
where job ='SALESMAN';
select*from sales_view;
예제1) (복합 VIEW)
이름, 월급, 부서위치를 출력하는 view 를 emp_dept 로 생성하시오
createor replace view emp_dept
asselect e.ename, e.sal, d.loc
from emp e, dept d
where e.deptno = d.deptno;
select*from emp_dept;
예제2) (갱신 설명)
emp_dept 에서 KING의 부서위치를 필라델피아로 변경하세요
update emp_dept
set loc ='philadelpia'where ename ='KING';
select*from emp_dept;
!주의!19버전이 아닌 21버전에서는 update가 되긴 하지만 king이 아닌 다른 사원들도
변경되는 오류가 발생함. 19버전에서는 아예 update가 되지 않지만(ORA-01779ERROR)
21버전에서는 주의해야함.
아래 예제3 참
예제3)
emp_dept 뷰에서 KING 의 월급을 9000 으로 변경하시오
<21c 버전>update emp_dept
set sal =9000where ename ='KING';
select ename, sal
from emp_dept;
----------------------------<19c 버전>alter table dept
add constraint dept_deptno_pk primary key(deptno);
update emp_dept
set sal =9000where ename ='KING';
select ename, sal
from emp_dept;
=>19c는 deptno 테이블에 deptno에 primary key 제약을 걸어주어야 갱신 가능
문제풀며 익히기
Q1. (복합뷰 ⇒ 그룹함수를 포함하고 있기 때문)
직업, 직업별 토탈월급을 출력하시오
A. select job, sum(sal)
from emp
groupby job;
Q2. (주의사항 포함)
위의 결과를 출력하는 view 를 job_sum 이라는 이름으로 생성하시오
A. createview job_sum
asselect job, sum(sal) as sumsal =>!!반드시 컬럼 별칭을 써줘야함!!from emp
groupby job;
#뷰를 생성할 때 그룹함수를 사용했으면 반드시 컬럼별칭을 사용해야한다.
##별칭 사용하지 않을 경우 생성되지 않음
select*from job_sum;
Q3.
job_sum 뷰에서 직업이 CLERK 의 sumsal 을 7000 으로 변경하시오
!ERROR!update job_sum
set sumsal =7000where job ='CLERK';
!!ERROR!!SQL 오류: ORA-01732: 뷰에 대한 데이터 조작이 부적합합니다
group by 절을 사용해서 만든 위의 복합뷰는 data 를 update 할 수 없음
Q4.
예제7. 위의 job_sum 뷰를 delete 로 지우시오
!ERROR!deletefrom job_sum;
!!ERROR!!SQL 오류: ORA-01732: 뷰에 대한 데이터 조작이 부적합합니다
group by 절을 사용해서 만든 위의 복합뷰는 data를 delete할 수 없음(19c, 21c 둘 다 delete안됨)
Q5. (단순 뷰)
부서번호가 10번, 20번인 사원들의 모든 컬럼을 가져오는 view 를 생성하시오
(emp 547로 뷰를 생성하시오)
A. createor replace view emp547
asselect*from emp
where deptno in(10, 20);
select*from emp547;
Q6.
emp547 뷰를 수정하는데 KING 의 월급을 8400으로 수정하시오
A. update emp547
set sal =8400where ename ='KING';
select*from emp547;
select*from emp; => emp가 변경된 것
Q7.
emp547 뷰를 수정하는데 KING 의 부서번호를 30번으로 수정하시오
update emp547
set deptno =40where ename ='KING';
select*from emp547;
#view에서 KING이 사라짐
##왜? emp547 view를 만들 때 부서번호 10,20만 포함하여 만들었기 때문
###뷰를 생성할 때 이 where절에 기술한 컬럼의 조건에 대해서는 절대로 변경하지
####못하게 해야하는데 그 옵션은 WITHCHECK OPTION임
rollback;
Q8.
emp547 뷰를 다시 만드는데 deptno 만큼은 갱신되지 못하겠금 뷰를 생성하시오 !
(뷰생성시 where 절 조건에 위배되겠금 뷰를 갱신하지 못하게 하시오!)
A.
createor replace view emp547
asselect*from emp
where deptno in (10,20)
withcheck option;
----------------------------------update emp547
set deptno =30where ename ='FORD';
!!ERROR!!SQL 오류: ORA-01402: 뷰의 WITHCHECK OPTION의 조건에 위배 됩니다
Q9.
월급이 4000 이하인 사원들의 모든 컬럼을 담는 VIEW 를 emp551 로 생성하는데
emp551에서 월급을 4000 보다 크게 갱신하지 못하도록 view 를 생성하시오
A.
createview emp551
asselect*from emp
where sal <=4000withcheck option;
select*from emp551;
-------------------------------update emp551
set sal =8000where ename ='SMITH';
!!ERROR!!SQL 오류: ORA-01402: 뷰의 WITHCHECK OPTION의 조건에 위배 됩니다
097 데이터 검색 속도를 높이기(INDEX)
📖
데이터 검색 속도를 높이기(INDEX)
쿼리문의 검색속도를 높이는 db object (책 앞의 목차 같은 느낌)
<테이블에 있는 확인방법>select rowid, empno, ename, sal, hiredate
from emp;
테이블의 rowid는 책의 페이지번호 느낌
내가 만든 INDEX LIST 확인하기
<전체 INDEX LIST>select index_name
from user_indexes; #USER_INDEXS = 데이터 사전
<특정 테이블에 대한 INDEX LIST>select index_name
from user_indexes
where table_name ='EMP';
<특정 테이블에 대한 INDEX LIST SQLDEVELOPER로 확인하기>
SQLdeveloper ---> 테이블 탐색기 ---> emp ----> 인덱스 클릭
해보기
1. INDEX를 ENAME에 생성
create index emp_ename
on emp(ename);
#회사마다 index 만드는 규칙이 있음
2. ename 의 인덱스만 보는 쿼리문으로 emp_ename의 인덱스가 어떻게 생겼는 확인
(구조 확인하는 방법)
select ename, rowid
from emp
where ename >' ';
!#이름 전체를 출력해야하기 때문에 이름이 공백문자보다 크다고 조건 작성
!#where절에다가 ename을 써줘야 index에 access할 수 있음
#orderby 절을 작성하지 않아도 ename이 abcd 순서대로 나오고 있음
3. 위의 SQL의 결과가 테이블에서 읽어온 데이터가 아니라
EMP_ENAME 인덱스에서 읽어온 데이터임을 확인하시오
=> 실행계획을 보면 알 수 있음
explain plan forselect ename, rowid
from emp
where ename >' ';
select*fromtable(dbms_xplan.display);
=>FULLTABLE SCANE이 아니라 INDEX RANGE SCAN을 한 것을 확인할 수 있음
문제풀며 익히기
Q1.
emp 테이블에 sal 에 인덱스를 생성하시오 ! 인덱스 이름은 emp_sal 이라고 하시오
A. create index emp_sal
on emp(sal);
------------------------------
#index로 인하여 아래와 같은 sql의 성능이 향상됨
select ename, sal
from emp
where sal =1250;
Q2.
emp_sal 인덱스의 구조를 확인하시오
A. select sal, rowid
from emp
where sal >0; #알아서 설정하시옹
#ascending하게 나옴. 왜? index에서 읽어왔기 때
Q3.
emp 테이블에 hiredate 에 인덱스를 생성하시오
A. create index emp_hiredate
on emp(hiredate);
Q4.
emp 테이블에 hiredate 의 인덱스의 구조를 확인하시오
A. select hiredate, rowid
from emp
where hiredate < to_date('9999/12/31', 'rrrr/mm/dd');
#날짜는 ~보다 작다가 나음
#index에서 가져왔기 때문에 정렬되어져서 보
Q5.
이름이 CLARK 인 사원의 이름과 월급을 출력하시오
A. select ename, sal
from emp
where ename ='CLARK';
INDEX가 없다면? FULLTABLE SCAN 사용해서 오래걸림
INDEX가 있다면?
ASCENDING하게 정렬된 이름 중에 찾은 후
↓
ROWID를 확인하여 해당 TBLAE 데이터의 ROWID로 찾아감
Q6.
여러분들의 옵티마이져는 아래의 SQL을 실행할 때 full table scan을
했는지 index range scan을 했는지 실행계획을 확인하세요
A. explain plan forselect ename, sal
from emp
where ename ='CLARK';
select*fromtable(dbms_xplan.display);
Q7.
아래의 SQL의 실행계획이 full table scan 이 되게 하시오
select/*+ 힌트 */ ename, sal
from emp
where ename ='CLARK';
#힌트 = 실행계획을 제어하는 명령 (힌트는 옵티마이저 프로세스가 만드는 것)
##즉, 바둑에서 훈수를 두는 것 처럼. 옵티마이저가 제안하는 것
---------------------------------------<적용>select/*+ full(emp) */ ename, sal
from emp
where ename ='CLARK';
#full scan하는 법
explain plan forselect/*+ full(emp) */ ename, sal
from emp
where ename ='CLARK';
select*fromtable(dbms_xplan.display);
Q8.
월급이 1250 인 사원의 이름과 월급을 출력하는 쿼리문을 작성하는데
full table scan 이 되게 하시오
A. select/*+ full(emp) */ ename, sal
from emp
where sal =1250;
----------------------------------<실행계획 보며 확인하기>
explain plan forselect/*+ full(emp) */ ename, sal
from emp
where sal =1250;
select*fromtable(dbms_xplan.display);
Q9.
위의 SQL이 emp_sal 인덱스를 통해서 테이블을 엑세스 할 수 있도록 힌트를 주시오.
<인덱스 힌트 주는 문법>/* index([테이블명] [인덱스이름] */-------------------------------------<적용>select/*+ index(emp emp_sal) */ ename, sal
from emp
where sal =1250;
explain plan forselect/*+ index(emp emp_sal) */ ename, sal
from emp
where sal =1250;
select*fromtable(dbms_xplan.display);
----------------------------------------<읽는 방법>1. INDEX RANGE SCAN 를 함 => EMP_SAL 즉 월급의 INDEX를 통해서 TABLE ACCESS
2.TABLE ACCESS BY INDEX ROWID BATCHED
3.SELECT STATEMENT
Q10.
사원 테이블에 job 에 인덱스를 생성하시오( 인덱스 이름은 emp_job 으로 하세요 ! )
A. create index emp_job
on emp(job);
Q11.
아래의 SQL을 튜닝하시오.
<튜닝 전>select ename, sal, job
from emp
where substr( job, 1, 5 ) ='SALES';
explain plan forselect ename, sal, job
from emp
where substr( job, 1, 5 ) ='SALES';
select*fromtable(dbms_xplan.display);
<튜닝 후>select ename, sal, job
from emp
where job like'SALES%';
EXPLAIN PLAN FORselect ename, sal, job
from emp
where job like'SALES%';
select*fromtable(dbms_xplan.display);
Q12.
c##scott 유져가 소유하고 있는 인덱스 리스트를 확인하시오
A. select index_name
from user_indexes
where table_name ='EMP';
Q13.
아래의 SQL을 튜닝하시오.
<튜닝전>
explain plan forselect ename, hiredate
from emp
where to_char(hiredate,'rr/mm/dd') ='81/12/11';
select*fromtable(dbms_xplan.display);
#where 절에 인덱스 컬럼을 가공하면 인덱스를 사용할 수 없고
#fulltable scan 을 하게 됩니다.
<튜닝후>
explain plan forselect ename, hiredate
from emp
where hiredate = to_date('81/12/11','rr/mm/dd');
#hiredate가 날짜형이기 때문에, 우변도 날짜형으로 고정. 어느나라에 가서도 실행 가
select*fromtable(dbms_xplan.display);
Q14. (인덱스의 구조를 이해하기 위한 문제)
아래의 SQL을 튜닝하시오.
<튜닝전>select ename, sal
from emp
orderby sal asc ;
<튜닝후>select/*+ index_asc(emp emp_sal) */ ename, sal
from emp
where sal >=0;
#emp_sal이라는 index를 ascending하게 정렬하라
!!반드시 where절에 관련 컬럼 써야 index가 작동함!!
이미 인덱스는 데이터를 정렬해서 저장하고 있으므로 인덱스에서
정렬된 데이터를 읽어오는게 더 성능이 좋습니다.
orderby 를 수행하는것보다 더 성능이 좋습니다.
Q15.
아래의 sql을 튜닝하시오.
<튜닝전>select ename, job, sal
from emp
where job='SALESMAN'orderby sal desc ;
<튜닝후>select/*+ index_dsc(emp emp_sal) */ ename, job, sal
from emp
where job='SALESMAN'and sal >=0;
(추가) INDEX 2
📖
인덱스 생성(오라클 본교재 11-37p)
인덱스의 데이터를 스캔하기 위해 WHERE 절에 해당 인덱스 컬럼이 있어야하는데 인덱스의 데이터를 읽어오기 위한 조건들
문자 > ' '
숫자 >= 0
날짜 < to_date('9999/12/31', 'RRRR/MM/DD')
인덱스의 구조를 위한 SQL튜닝 방법 :
1. where 절의 좌변을 가공하지 말아라
2. order by 절 대신에 인덱스를 이용해라 !
인덱스가 생성되는 방식 2가지 ? p11-36
수동으로 :
create index emp_deptno
on emp(deptno);
자동으로(OCP 시험) : primary key 제약이나 unique 제약을 걸면
제약(contraint) 은 테이블의 데이터의 품질을 높이기 위해서
반드시 필요한 db 기능입니다. primarykey 제약을 생성한 컬럼은
중복된 데이터와 null 값이 입력안됩니다.
데이터의 값이 unique 한지 unique 하지 않은지에 따라 2가지
UNIQUE INDEX
primary key 제약 또는 unique 제약을 걸었을때
또는
createunique index emp17_empno
on emp17(empno);
NON UNIQUE INDEX
create index emp_job
on emp(job);
INDEX RANGE SCAN (NON UNIQUE INDEX일때) vs IDEX UNIQUE SCAN
IDEX UNIQUE SCAN
: unique 인덱스는 이름의 중복된 데이터가 없음이 확실히 보장된 인덱스이므로 김호일 하나의 인덱스 행만 검색하고 바로 테이블 엑세스를 하러 가고 끝남. 즉, 속도가 더 빠름
INDEX RANGE SCAN (NON UNIQUE INDEX
: 그러나 non unique 인덱스이면 그 다음행인 나윤호 행을 엑세스 합니다.
<NON UNIQUE INDEX 확인 방법>select index_name, uniqueness
from user_indexes #USER_INDEXES 데이터 사전에서 찾는 것임
where table_name ='[확인하고 싶은 테이블 명]'; #테이블 명 대문자로!
인덱스를 구성하려는 컬럼의 개수에 따라 (6번 부터) - SQLD 하는 사람에게 중요 ✅
단일 컬럼 인덱스 : INDEX를 구성하는 컬럼의 개수 1개
create index emp_sal
on emp(sal);
결함 컬럼 인덱스 : INDEX를 구성하는 컬럼의 개수가 2개 이상 (더 좋은 인덱스)
-결합컬럼 인덱스 사용시 where절에 첫번째 컬럼이 있으면 됨(Q11)
create index emp_job_sal
on emp(job, sal) ;
-----------------------------
결합컬럼 인덱스의 첫번째 컬럼이 where절에 있느냐 없느냐가 중요함.
있으면, 힌트를 이용해서 INDEX RANGE SXAN으로 유도할 수 있음
덕분에 INDEX_DESC 힌트 또는 INDEX_ASC 힌트를 쓸 수 있음
만약, 결합컬럼 인덱스의 첫번째 컬럼이 WHERE절에 없으면
FULLTABLE SCAN을 하게되거나 다른게 됨
문제풀며 익히기
Q1. (INDEX RANGE SCAN)
우리반 테이블에서 이름이 김호일 학생의 이름과 나이와 주소를 출력하고 그 쿼리문의 실행계획을 확인하시오
A. select ename, age, address
from emp18
where ename ='김호일';
-----------------------------------<실행계획 확인>
explain plan forselect ename, age, address
from emp18
where ename ='김호일';
select*fromtable(dbms_xplan.display);
-------------------------------------
#emp18의 emp에 index를 걸지 않았기 때문에 FULLTABLE SCAN
--------------------------------------<인덱스 생성 후 실행계획 확인>create index emp18_ename
on emp18(ename);
explain plan forselect ename, age, address
from emp18
where ename ='김호일';
select*fromtable(dbms_xplan.display);
-인덱스 생선 전 실행계획
-인덱스 생성 후
<emp18의 ename의 인덱스 구조 확인>select ename, rowid
from emp18
where ename >' ';
============================================
Q. 어떻게 RANGE SCAN 을 했는가?
1. 김호일을 index에서 찾는데, 그 다음 나윤호까지 읽음.
왜? 김호일 아니네! 라고 인식 후 빠져나옴
2. index에서 김호일의 rowid확인
3. 찾은 rowid를 emp18테이블의 rowid와 매칭함
=>unique 인덱스는 이름의 중복된 데이터가 없음이 확실히 보장된
인덱스이므로 김호일 하나의 인덱스 행만 검색하고 바로 테이블 엑세스를
하러 가고 끝납니다. 그러나 non unique 인덱스이면 그 다음행인 나윤호
행을 엑세스 합니다.
A. select index_name, uniqueness
from user_indexes
where table_name ='EMP18';
#즉, NON UNIQUE 인덱스 이므로 인덱스를 스캔할 때 INDEX RANGE SCAN을 함
Q3.
EMP18 테이블에 ENAME에 걸린 인덱스를 삭제하고 다시 UNIQUE인덱스로 생성하시오.
A.
drop index emp18_ename;
createunique index emp18_ename
on emp18(ename);
------------------------------<확인>select index_name, uniqueness
from user_indexes
where table_name='EMP18';
Q4.
다시 이름이 김호일 학생의 이름과 나이와 주소를 출력하는 SELECT 문의 실행계획을 확인하시오.
A.
explain plan forselect ename, age, address
from emp18
where ename ='김호일';
select*fromtable(dbms_xplan.display);
Q5.
emp18 테이블의 age컬럼에 non unique 인덱스를 생성하고, 이름이 김호일이고 나이가 27인 학생의 이름과 나이와 주소를 출력하는 쿼리문의 인덱스는 아래의 둘중에 어떤 인덱스를 옵티마이져가 선택할까?
create index emp18_age on emp18(age);
explain plan forselect ename, age, address
from emp18
where age =27and ename ='김호일';
select*fromtable(dbms_xplan.display);
#나이는 중복값이 존재하므로 non nuique index
#이름에는 unique index
-----------------------------------------------
정답 : UNIQUE INDEX를 선택함.
=> 똑똑하지 않은 옵티마이져는 UNIQUE INDEX를 선택하지 않을 수 있음.
**여러개의 인덱스 중에 특정 인덱스를 사용하게 하고 싶을 때
아래의 INDEX 힌트를 이용함 **/*+ index( [테이블명] [인덱스명]) */
<적용해보기>
explain plan forselect/*+ index(emp18 emp18_age) */ ename, age, address
from emp18
where age =27and ename ='김호일';
select*fromtable(dbms_xplan.display);
Q6.
emp 테이블의 job 컬럼에 인덱스를 생성하시오( 인덱스 이름은 emp_job 으로 하세요 )
A. create index emp_job
on emp(job);
Q7.
직업이 CLERK 인 사원들의 직업과 월급을 출력하는 쿼리문의 실행계획을 확인하시오
A. explain plan forselect job, sal
from emp
where job ='CLERK';
select*fromtable(dbms_xplan.display);
--------------------------------------------
위의 SQL의 실행계획은 JOB 에 대한 DATA 는 emp_job라는 인덱스에 있지만
select 절에서 요구하는 sal 에 대한 data 는 emp_job라는 인덱스에 없으므로
emp 테이블을 엑세스 하러 가야 하는 실행계획이 나왔습니다.
만약 인덱스에 sal 까지도 다 구성했으면 테이블 엑세스하러 갈 필요가 없었을 것
Q8. (결합 컬럼 인덱스)
emp 테이블에 job 과 sal 에 다음과 같이 결합 컬럼 인덱스를 생성하시오
create index emp_job_sal
on emp(job,sal);
------------------------- <emp_job_sal 인덱스의 구조 살펴보기>select job, sal, rowid
from emp
where job >' ';
=> JOB은 A,B,C 순으로 ascending하게 정렬
=> SAL은 각 직업 별로 월급이 ascending하게 정렬되어있음
Q9.
아래의 SQL의 실행계획을 다시 확인하시오
explain plan forselect job, sal
from emp
where job ='CLERK';
select*fromtable(dbms_xplan.display);
---------------------------------------
emp_job_sal 결합 컬럼 인덱스에서 job 과 sal 에 대한 data 를
다 얻어냈기 때문에 emp 테이블을 엑세스 하지 않는 실행계획이 나옴
즉, 결합컬럼 인덱스를 엑세스함.
Q10.
emp 테이블과 관련된 인덱스를 조회하시오.
select index_name
from user_indexes
where table_name ='EMP'; #테이블명 대문자로 써야함
Q11.
아래의 SQL을 튜닝하시오
<튜닝 전>select ename, job, sal
from emp
where job='CLERK'orderby sal desc;
#ORDERBY 절이 문제
#직업이 cleark인 사원들의 월급을 decending하게 읽어야
---------------------
explain plan forselect ename, job, sal
from emp
where job='CLERK'orderby sal desc;
select*fromtable(dbms_xplan.display);
<튜닝 후>select/*+ index_desc(emp emp_job_sal) */ ename, job, sal
from emp
where job='CLERK';
#결합컬럼 인덱스를 사용하려면 where절에 첫번째 컬럼이 있으면 됨.
---------------------------
explain plan forselect/*+ index_desc(emp emp_job_sal) */ ename, job, sal
from emp
where job='CLERK';
select*fromtable(dbms_xplan.display);
===============================
결합컬럼 인덱스의 첫번째 컬럼이 where절에 있느냐 없느냐가 중요함.
있으면, 위와 같이 힌트를 이용해서 INDEX RANGE SXAN으로 유도할 수 있음
덕분에 INDEX_DESC 힌트 또는 INDEX_ASC 힌트를 쓸 수 있음
만약, 결합컬럼 인덱스의 첫번째 컬럼이 WHERE절에 없으면
FULLTABLE SCAN을 하게되거나 다른게 됨
Q12.
deptno 와 sal 을 결합 컬럼 인덱스로 생성하시오( 인덱스 이름은 emp_deptno_sal 로 하시오 )
A. create index emp_deptno_sal
on emp(deptno, sal);
Q13.
emp_deptno_sal 인덱스의 구조를 확인하시오
A. select deptno, sal, rowid
from emp
where deptno >0;
#deptno ascending하게 정렬되었으며
##sal도 ascending하게 정렬됨
Q14.
아래의 SQL을 튜닝하시오 ! ( order by 절을 사용하지 말고 수행하시오)
(emp_deptno_sal 인덱스를 활용)
<튜닝전>select ename, deptno, sal
from emp
where deptno =20orderby sal desc;
<튜닝 후>
A. select/*+ index_desc(emp emp_deptno_sal) */ ename, deptno, sal
from emp
where deptno =20;
Q15.
emp 테이블에 관련된 인덱스가 무엇인지 조회하시오
A.
select index_name
from user_indexes
where table_name ='EMP';
Q16.
emp 테이블과 관련된 모든 인덱스를 다 삭제하시오
A.
drop index emp_job;
drop index emp_job_sal;
drop index emp_deptno_sal;
----------------------<확인>select index_name
from user_indexes
where table_name ='EMP';
-----------------------<추가 설명>
emp 테이블을 삭제하면 emp 테이블과 관련된 인덱스들도 다 삭제 됩니다
(추가) 동의어(SYNONYM)
📖
DB 오브젝트 5가지
TABLE : 행과 열로 이루어진 데이터를 저장하는 기본 저장 구조
VIEW : 데이터를 저장하지 않는 그냥 테이블을 바라보는 db object
SEQUENCE(SI형 DBA를 하면 자주 만들게 됨) : 일련번호 생성기
SYNONYM : 기존 테이블을 명명하기 위한 다른 이름
INDEX : 데이터 검색속도를 향상시키기 위한 db object
SYNONYM(동의어)
특정 개체에 대한 다른 이름
개발 DBA로 일할 때 시너님 생성 TIP
hr계정이 가지고 있는 모든 테이블들을 c##smith 유저가 select 할 수 있게 하시오
1. hr 계정이 소유하고 있는 테이블 리스트를 확인하시오
SQL>connect c##scott/tiger
연결되었습니다.
SQL>select table_name
from dba_tables ----> table 리스트를 보는 사전where owner='HR'; ----> HR은 USER명! 대문자로 작성해야함
TABLE_NAME
------------
COUNTRIES
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
7 행이 선택되었습니다.
2. hr계정이 소유하고 있는 employees테이블 시너님 생성과정
C:\Users\itwill> sqlplus sys/oracle_4U as sysdba
#dba의 최고 권한 계정으로 접속
SQL>showuserUSER은 "SYS"입니다
SQL>alteruser hr
identified by hr; #hr계정 비밀번호 변
사용자가 변경되었습니다.
SQL>connect hr/hr
연결되었습니다.
#잠겼을때
#alteruser hr account unlock;
#후에 접속
SQL>connect c##scott/tiger
연결되었습니다.
SQL>grant dba to hr;
권한이 부여되었습니다.
SQL>connect hr/hr
연결되었습니다.
SQL>create public synonym jobs for hr.jobs;
동의어가 생성되었습니다.
SQL>create public synonym departments for hr.departments;
동의어가 생성되었습니다.
SQL>create public synonym locations for hr.locations;
동의어가 생성되었습니다.
SQL>create public synonym employees for hr.employees;
동의어가 생성되었습니다.
3. c##scott 유저로 접속해서 hr user의 테이블인 jobs를
아래와 같이 조회하시오.
SQL>select*from jobs;
1. hr유저가 생성한 synonym을 전부 삭제하시오
SQL>connect hr/hr
연결되었습니다.
SQL>select synonym_name
2from all_synonyms
3where table_owner='HR';
SYNONYM_NAME
----------------
COUNTRIES
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
EMPLOYEES
DEPARTMENTS
JOBS
LOCATIONS
10 행이 선택되었습니다.
2. 삭제하기
SQL>drop public synonym employees;
동의어가 삭제되었습니다.
synonym 이름만 바꿔서 drop해주면 됨
예제1)
c##scott 유져에서 c##smith 유져를 만들고 접속할 수 있는 권한을 부여하고 c##smith 유져로 접속하시오 (c##붙이는 이유는 관리수업에서 진행)
createuser c##smith
identified by tiger;
grant dba to c##smith;
==========================createuser [유저명]
identified by [암호];
==========================<cmd로 접속하기>> sqlplus c##smith/tiger
>showuser
c##smith 테이블에서 emp 테이블 조회해보기
SQL>showuserUSER은 "C##SMITH"입니다
SQL>select*from emp;
select*from emp
*1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다
# c##scott user의 emp table 확인하기
>select*from c##scott.emp;
-----------------------------------
emp테이블의 소유자 c##scott유져이므로 emp 테이블 앞에 유져이름을 붙여줘야함
=====================================
개발 dba 가 emp 테이블을 생성했으면 위와 같이 synonym 도 생성합니다.
synonym 을 만들어야 c##smith 유져가 emp 테이블 select 할때
테이블명 앞에 c##scott 을 접두어로 안붙여도 되기 때문입니다
#sqlplus에서 새롭게 다른 유저로 접속할 때
>connect c##scott/tiger
#cmd인 DOS창에서 새롭게 창을 띄우며 다른 유저로 접속할 때
> sqlplus c##scott/tiger
------------------------------------
다시 c##scott 창으로 가서 다음과 같이 sysnonym 생성하기
SQL>create public synonym emp
2for c##scott.emp;
동의어가 생성되었습니다.
c##scott user 외에 다른 유저가 emp테이블에 접근할 때 시너님 사
문제풀며 익히기
Q1.
c##smith 유저가 다음과 같이 dept테이블과 salgrade 테이블을 조회할 수 있게 하시오.(c##scott 유저에서 dept와 salgrade를 위한 시너님을 생성하면 됨)
select*from dept;
select*from salgrade;
SQL>connect c##scott/tiger
연결되었습니다.
SQL>showuserUSER은 "C##SCOTT"입니다
SQL>create public synonym dept
2for c##scott.dept;
동의어가 생성되었습니다.
SQL>create public synonym salgrade
2for c##scott.salgrade;
동의어가 생성되었습니다.
------------------------------------SQL>connect c##smith/tiger
연결되었습니다.
SQL>select*from dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>select*from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------17001200212011400314012000420013000530019999
098 절대로 중복되지 않는 번호 만들기(SEQUENE)
📖
DB 오브젝트 5가지
TABLE
VIEW
SEQUENCE(SI형 DBA를 하면 자주 만들게 됨)
SYNONYM
INDEX
시퀀스 :
일련 번호 생성기 (순서대로 번호를 생성하는 db object)
예: 쿠팡의 주문 테이블이 있다면 주문번호는 순서대로 부여됩니다.
은행의 번호표 기계를 연상하면 되는데 기계가 없이 사람이 일일히
번호를 적어서 나눠주면 분명히 실수할 가능성이 있습니다.
오라클의 시퀀스를 생성하면 번호가 순서대로 잘 생성이 됩니다.
주문 테이블
주문번호 주문 상품 배송지
1 마우스 서울시 ...
2 노트북 경기도 ...
3 키보드 서울시 ...
직접 하게된다면,
insert into 주문
values(1, '마우스', '서울시...');
=> 이렇게 하다보면 실수할 가능성 큼
=> 테이블에 지금 몇번까지 입력되어져서 다음 번호가 몇번인지 매번
확인하는 작업이 필요한데,
=> 시퀀스를 이용하면 이런 불편함이 사라짐
문법
<SEQUENCE 문법>create sequence [시퀀스 이름]
increment by [증가치 즉, 숫자]
startwith [시작숫자]
(minvalue [cycle 시작할 숫자]) #쓰지 않으면 기본값 1
maxvalue [시퀀스의 최댓값]
nocycle/cycle--> 즉 순환 여부 지정
nocache/cache [미리 메모리에 올려놓을 번호 수 지정] ;
#--> nocyle순환하지 않겠다는 것. 시작부터 최대까지
#cycle은 순환 : minvalue는 cycle을 설정했을 때 사용되는 옵션
#minvalue가 -1이면 번호가 -1부터 -1,0,1,...로 가는것
#시퀀스 생성시 cycle 옵션을 줬으면 maxvalue 값 다 출력된 다음 다시 시작하는
#숫자가 startwith 값이 아니라 minvalue 값입니다. 그래서 cycle 을 썼으면
#startwith 와 minvalue 의 값을 동일하게 맞춰줘야 합니다.
#시퀀스 생성시 minvalue를 안썼으면, 기본값이 1임
#cache 20 : 20개의 번호를 미리 오라클의 메모리에 올려놓겠다는 것.
##cahce는 dba입장에서 매우 중요
<SEQUENCE들의 상태 확인 법>select*from user_sequences;
*주의사항
1.startwith 변경할 수 없음
2.DROP해도 이미 생성한 번호들은 그대로 테이블에 잘 입력되어있음(19c 버전 기준)
시퀀스 번호를 1번, 2번 생성한 후에 db 가 비정상 종료 되었다가 다시
startup 이 되면 시퀀스 번호는 3번이 아니라 21번이 됨.
왜냐하면 메모리에 올렸던 번호들이 다 사라져버렸기 때문
그래서 번호의 순서가 순차적으로 시퀀스를 통해서 테이블에 입력되어야 한다면, 이 부분을 신경써야 합니다. 시퀀스 삭제하고 다시 시작숫자 맞춰서 생성해줘야함
BUT 성능을 위해서 절대 nocache 로 시퀀스를 만들지는 마세요
실험 1.
#seq7생성
create sequence seq7
startwith1
increment by1
maxvalue 100
nocycle
cache 20;
#두 번 삽입
select seq7.nextval from dual;
select seq7.nextval from dual;
# DOS 창에 다음과 같이 입력
> sqlplus sys/oracle_4U as sysdba #sql 접속
> shutdown abort
> startup
>connect 본인 계정/ 비밀번호
>select seq7.nextval from dual;
시퀀스를 사용했을 때의 개발환경과 사용하지 않을때의 개발환경
좋은 개발환경인 사용할 때의 개발환경 INSERT문
insert into 주문테이블(주문번호, 주문상품, 배송지)
values( 시퀀스이름.nextval, '노트북', '서울시');
#시퀀스이름.NEXTVAL
개선해야할 사용하지 않을때의 개발환경 INSERT문
selectmax(주문번호) +1into:v_order
from 주문테이블; #매번 주문테이블에 SELEC문 실행
#위의 쿼리에서의 V_ORDER을 INSERT 문에 보냄
insert into 주문테이블(주문번호, 주문상품, 배송지)
values( :v_order ,'노트북', '서울시' );
#매번 INSERT할때마다 첫번째 쿼리 반복.
#시간 소모 큼
max(주문번호) + 1 로 번호를 생성하지 말고 시퀀스를 사용해서 번호를
생성하기를 권장( 그래야 성능이 좋아짐)
예제 1) 시퀀스 생성하기
#1. 시퀀스 생성
create sequence seq1
increment by1startwith1
maxvalue 100
nocycle
cache 20 ;
----------------------------------create sequence seq1
increment by1startwith1
minvalue -1
maxvalue 100cycle
cache 20 ;
#위 시퀀스는 1번부터 시작해서 100까지 출력된 다음에 다시 시작할 때 -1부터 나옴
#시퀀스 생성시 cycle 옵션을 줬으면 maxvalue 값 다 출력된 다음 다시 시작하는
#숫자가 startwith 값이 아니라 minvalue 값입니다. 그래서 cycle 을 썼으면
#startwith 와 minvalue 의 값을 동일하게 맞춰줘야 합니다.
시퀀스 생성시 minvalue를 안썼으면, 기본값이 1임
#2. 주문 테이블을 생성합니다.(1번 중 위에 것 기반)
create table cuppang_order
( order_num number(10),
order_name varchar2(20),
address varchar2(30) );
#3. cuppang_order 테이블에 데이터를 입력합니다.
insert into cuppang_order
values( seq1.nextval, '노트북', '서울시 송파구');
insert into cuppang_order
values( seq1.nextval, '무선 마우스', '서울시 강남구');
=> 증가치를 1로 했기 때문에 번호가 1,2,3,..순서대로 될것임
select*from cuppang_order;
!!ERROR!!100까지 생성되면 그 다음번호 생성할때 아래와 같이 오류 발생
!ORA-08004: 시퀀스 SEQ1.NEXTVAL exceeds MAXVALUE은 사례로 될 수 없습니다!
이 오류가 나기전에 dba 는 지금 현재 몇번까지 번호가 생성되었는지 확인을하고
maxvalue 에 도달할 것 같으면 maxvalue 값을 늘려주셔야합니다.
※ dba 가 관리해야하는 일중에서 시퀀스 maxvalue 값에 도달해서 에러나지
않겠금해야하는 일을 챙겨야합니다.
문제풀며 익히기
Q1.
시퀀스를 생성하는데, 시퀀스 이름은 seq2로 하고 시작 숫자는 1로 하고 최대값은 200으로 하고 nocyle, cache 20으로 생성하시오
A. select seq2.nextval ---> nextval은 한번 볼 때마다 생성시킴from dual; ----> 결과를 보기 위한 가상의 테이블
#시퀀스의 다음값을 보고싶을 때 사용. 단 볼때마다 증가함
select seq2.currval
from dual;
#시퀀스의 현재값을 보고싶을 때 사용
Q3.
seq2의 설정값이 어떻게 되어있는지 확인하시오.
A. select*from user_sequences;
Q4.
seq2의 maxvalue 값을 400으로 늘리시오
A. ALTER SEQUENCE seq2
MAXVALUE 400;
select*from user_sequences;
Q5. (OCP시험문제)
seq2의 증가치를 1이 아니라 10으로 변경하시오.
A. alter sequence seq2
increment by10;
select*from user_sequences;
Q6.
seq3 시퀀스를 생성하는데 시작값은 1로 하고 최대값은 100000,
cycle 로 하고 cache는 20으로 줘서 시퀀스를 생성하시오 !
증가치는 1로 하세요.(minvalue를 안쓰면 default가 1)
A. alter sequence se3
startwith5;
!!ERROR!!
ORA-02283: 시퀀스 시작 번호는 변경할 수 없습니다
=> 시퀀스의 시작값은 alter 명령어를 사용해서 수정할 수 없습니다.
startwith 만 빼고 다 변경이 가능합니다
Q8. (참고사항 포함)
seq1 시퀀스를 drop 하시오
A. drop sequence seq3;
※ 시퀀스를 drop 했어도 이미 생성한 번호들을 그대로 잘 테이블에 입력되어
있습니다. -19C OCP
즉, NULL로 바뀌지 않고 그대로 테이블에 데이터가 있는 것
Q9.
시퀀스 ocp 시험문제 환경을 내 db 에 구성하시오
create sequence ord_seq
increment by1startwith1
maxvalue 100000cycle
cache 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할때 ord_no 에 대한 값을 넣지 않으면 ord_seq.nextval 값이 들어간다는 의미
## primary key 제약으로 인해 ord_no와 item_no는 중복값과 널값을 가질 수 없
insert into ord_items(item_no, qty, expiry_date)
values(22,300,sysdate);
#즉 ord_no에 암시적으로 데이터 값을 넣은것
select*from ord_items;
primary key 제약이 걸려있으면 null 값을 입력할 수 없습니다.
default ord_seq.nextval 로 설정 되어있으므로 암시적으로 null 을
입력하면 디폴트 값이 입려되는데 디폴트를 시퀀스로 지정했기 때문에
시퀀스 값이 입력됩니다.
■ Flashback으로 데이터 복구하기
💡
오라클 FLASHBACK의 종류 6가지
FLASHBACK QUERY
: 과거로 가는것이 아닌 현재 시점에서 과거 데이터 확인
: delete한 데이터는 flashback으로 되돌릴 수 있지만,
truncate한 데이터는 못되돌림
FLASHBACK TABLE : 테이블을 완전히 과거로 되돌림
FLASHBACK DROP
FLASHBACK VERSION QUERY
FLASHBACK TRANSACTION QUERY
FLASHBACK DATABASE (오라클 관리 수업때 배움)
099 실수로 지운 데이터 복구하기 1(FLASHBACK QUERY)
📖
실수로 지운 데이터 복구하기 (FLASHBACK QUERY)
"특정 테이블의 과거의 데이터를 확인할 때 사용하는 쿼리문"
-오라클의 FLASHBACK 기능이란? 오라클의 타임머신 기능
💡
오라클 FLASHBACK의 종류 6가지
FLASHBACK QUERY : 과거로 가는것이 아닌 현재 시점에서 과거 데이터 확인
FLASHBACK TABLE : 테이블을 완전히 과거로 되돌림
FLASHBACK DROP
FLASHBACK VERSION QUERY
FLASHBACK TRANSACTION QUERY
FLASHBACK DATABASE (오라클 관리 수업때 배움)
예제1) 현재 시간 확인하기
select systimestamp from dual;
=>23/12/1315:00:19.355000000+09:00
날짜 시 분 초 밀리세컨 시간대
예제2) emp테이블을 전부 지우고 commit 하기
deletefrom emp;
commit;
예제3) emp 테이블의 데이터가 지워지기 전 5분 전의 상태를 확인하시오.
select*from emp
ASOFtimestamp #ASOF 사용함!!!!
to_timestamp('23/12/13 14:55:19', 'rrrr/mm/dd HH24:MI:SS');
-----------------------------------------------------------------
위의 시간대에는 EMP테이블이 있고
데이터도 문제가 없이 잘 있구나 라고 확인했으면
이 시간을 소중히 간직합니다.
이 시간으로 EMP 테이블 FLASHBACK하면 됨!
예제4) FALSHBACK하기
1. FLASHBACK이 가능한 상태로 만들기
alter table emp enable row movement;
2. ENABLE로 잘 바뀐지 확인하기
select table_name, row_movement
from user_tables
where table_name ='EMP';
3. FLASHBACK하기
FLASHBACK TABLE emp totimestamp to_timestamp('23/12/13 14:55:19', 'rrrr/mm/dd HH24:MI:SS');
4. 살아났는지 확인하기
select*from emp;
문제풀며 익히기
Q1.
dept테이블을 전부 delete하고 commit한 후에 flashback query로 dept 테이블이 지워지기 전의 데이터를 모두 확인하시오.
select systimestamp from dual;
=>23/12/1315:18:42.039000000+09:00deletefrom dept;
commit;
select systimestamp from dual;
=>23/12/1315:19:38.692000000+09:00select*from emp
ASOFtimestamp
to_timestamp('23/12/13 15:18:42', 'rrrr/mm/dd HH24:MI:SS');
Q2.
위에서 확인한 시간으로 DEPT테이블을 FLASHBACK TABLE하여 복구하시오.
A.
alter table dept enable row movement;
select table_name, row_movement
from user_tables
where table_name ='DEPT';
FLASHBACK TABLE dept totimestamp to_timestamp('23/12/13 15:18:42', 'rrrr/mm/dd HH24:MI:SS');
select*from dept;
Q3.
사원테이블의 월급을 모두 0으로 변경하고 commit 하시오
update emp
set sal =0;
commit;
select sal from emp;
Q4.
위의 데이터를 복구하시오
1. 시간 확인
select systimestamp from dual;
=>23/12/1315:26:22.536000000+09:002. FLASHBACK QUERY : 현재 시점에서 과거 데이터 확인하기
select*from emp
asoftimestamp
to_timestamp('23/12/13 15:24:22', 'rrrr/mm/dd HH24:MI:SS');
3. FLASHBACK이 가능한 상태로 변경하기
alter table emp enable row movement;
4. ENABLE 상태로 바뀌었는지 확인하기
select table_name, row_movement
from user_tables
where table_name ='EMP';
5. FALSHBACK TABLE 하기
FLASHBACK TABLE emp totimestamp to_timestamp('23/12/13 15:24:22', 'rrrr/mm/dd HH24:MI:SS');
6. FLASHBACK 성공 여부 확인하기
select*from emp;
<휴지통 속 테이블 복구하기>
FLASHBACK TABLE [테이블명] to before drop;
<테이블과 함께 복구되면서, 휴지통 속이름 그대로로 변경된 INDEX명 변경하기>alter index "[변경된 인덱스 명]" rename to [변경할 인덱스명];
#변경된것 확인하기
select index_name
from user_indexes
where table_name ='[테이블명]';
예제1) 데모 스크립트 실행
SQL>@demo.sql
예제2) 휴지통 비우기
purge recyclebin;
=> Recyclebin이(가) 비워졌습니다.
예제3) 사원테이블의 월급에 인덱스를 생성하시오
create index emp_sal
on emp(sal);
예제4) emp table을 drop하시오
droptable emp;
=> 테이블 drop하면 index도 삭제
예제5) 휴지통 확인하기
show recyclebin;
예제6) 휴지통에서 emp테이블 복구하기
FLASHBACK TABLE emp to before drop;
select*from emp;
문제풀며 익히기
Q1.
짝꿍의 자리에서 테이블을 확인하고 테이블 2개 지우
1. 테이블확인하기
select table_name
from user_tables;
1-1 테이블 삭제
droptable [테이블명];
2. 휴지통 확인하기
show recyclebin;
3. 휴지통에 있는 테이블 flashback으로 복구하기
flashback table emp18 to before drop;
flashback table telecom_table to before drop;
Q2. emp테이블을 drop한 후 휴지통에서 복구하
droptable emp;
show recyclebin;
flashback table emp to before drop;
Q3.
emp_sal 인덱스가 다시 살아났는지 확인하시오
select index_name
from user_indexes
where table_name ='EMP';
===================================
살아났지만 INDEX이름이 휴지통 속에 들어갔을 때의 이름으로 변경됨
즉, 아직 오라클에서 해결하지 못한 버그(BUG)임
따라서 DBA가 INDEX이름을 원래 이름으로 변경해주는 작업이 필요함
Q4.
변경된 INDEX이름을 emp_sal로 다시 변경하시오.
alter index "BIN$f3Ll7BsZToOKb3cZAchy3w==$1" rename to emp_sal;
select index_name
from user_indexes
where table_name ='EMP';
102 실수로 지운 데이터 복구하기 4(FLASHBACK VERSION QUERY)
📖
💡
오라클 FLASHBACK의 종류 6가지
FLASHBACK QUERY
: 과거로 가는것이 아닌 현재 시점에서 과거 데이터 확인
: delete한 데이터는 flashback으로 되돌릴 수 있지만,
truncate한 데이터는 못되돌림
FLASHBACK TABLE : 테이블을 완전히 과거로 되돌림
FLASHBACK DROP
FLASHBACK VERSION QUERY
FLASHBACK TRANSACTION QUERY
FLASHBACK DATABASE (오라클 관리 수업때 배움)
FLASHBACK VERSION QUERY
그동안 특정 테이블이 어떻게 변경이 되어왔는지 그 이력정보를 확인하는 쿼리
select [컬럼명], VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION
from [테이블명]
VERSIONS betweentimestamp to_timestamp('[특정시간]', 'rrrr/mm/dd HH24:MI:SS')
AND maxvalue #MAXVALUE는 가장 최근까지라는 의미
where [테이블의 특정 컬럼의 특정데이터의 변경이력을 보고싶을때 조건 사용];
예제1) 현재 시간을 확인하기
select systimestamp from dual; =>23/12/1316:19:06.313000000+09:00
예제2) KING의 월급을 9000으로 변경하기
commit;
update emp
set sal =9000where ename ='KING';
commit;
예제3) KING의 부서번호를 30번으로 변경하시오.
update emp
set deptno =30where ename ='KING';
commit;
예제4) KING의 행을 지우시오
deletefrom emp
where ename ='KING';
commit;
예제5) VERSION QUERY
emp table의 KING의 데이터가 그동안 어떻게 변경되어왔는지 이력정보 확인하시오.
select ename, sal, deptno, VERSIONS_STARTTIME, VERSIONS_ENDTIME,
VERSIONS_OPERATION
from emp
VERSIONS betweentimestamp to_timestamp('23/12/13 16:19:06', 'rrrr/mm/dd HH24:MI:SS')
AND maxvalue #MAXVALUE는 가장 최근까지라는 의미
where ename ='KING';
===========================================================
맨 위가 과거 맨 아래가 현재
문제풀며 익히기
Q1.
다시 EMP 테이블을 '23/12/13 16:19:06' 이 시간으로 FLASHBACK TABLE 하시오
103 실수로 지운 데이터 복구하기 5(FLASHBACK TRANSACTION QUERY)
📖
FLASHBACK TRANSACTION QUERY
특정 테이블에 대해서 그동안 변경해왔던 이력정보를 가지고 다시 과거로 되돌리기 위한 DML 문장을 보여주는 쿼리문
* 특징 : 아주 상세하게 복구 가능
(DBA에게 백업과 복구는 DBA생존을 위해서 필수 기술)
* 복구할 때는 미리 검증된 스크립트를 잘 복사 붙여넣기하고 잘 수정해서 실행!
바로 엔터치지말고 한참 눈으로 보고 검증하고 확신이 서면 엔터를 치기
* SCN : System Change Number 의 약자로 commit할 때 생성되는 번호
select current_scn
from v$database;
=> scn번호를 확인할 수 있는 쿼리
scn번호는 아래와 같이 계속 바뀜
SQL>select current_scn
2from v$database;
CURRENT_SCN
-----------14411827SQL>select current_scn
2from v$database;
CURRENT_SCN
-----------14411834SQL>select current_scn
2from v$database;
CURRENT_SCN
-----------14411845
TIP
만약 SCN을 모른다면 다음과 같이 SCN번호를 찾으면 됨
즉 시간으로 SCN번호를 알아낼 수 있음
!! TIMESTAMP_TO_SCN('[확인했던 시간 날짜 및 초까지만 복붙]') !!
시간으로 SCN을 찾는 함수
=========================================================1. 현재 시간 확인하기
select systimestamp from dual;
2. 시간으로 scn번호를 찾아내기
select timestamp_to_scn('[확인했던 시간 날짜 및 초까지만 복붙]')
from dual;
3. scn번호로 시간을 찾기
select scn_to_timestamp('[scn번호]')
from dual;
해보기)
아래의 내용들은 DOS창(명령프롬프트창)에서 실시
SQL developer는 종료 후 실시해야함
=====================================================1. dba의 최고 권한 계정으로 접속
C:\Users\itwill> sqlplus sys/oracle_4U as sysdba
2. DB 정상 종료하기
SQL> shutdown immediate
3. db올리기
SQL> startup mount
4. 아카이브모드로 변환
(나중에 복구가 가능할 수 있도록 로그를 자동하게 기록하게 설정하기)
SQL>alter database archivelog;
5. DML문이 기록될 수 있도록 설정
SQL>alter database add supplemental log data;
6. DB를 OPEN 시키기
SQL>alter database open;
7. 설정이 잘 되었는지 확인하기
SQL> archive log list
8. scott 유져로 접속해서 KING 의 월급과 부서번호를 조회합니다.
SQL>connect c##scott/tiger
연결되었습니다.
SQL>select ename, sal, deptno
from emp
where ename ='KING';
ENAME SAL DEPTNO
-------------------- ---------- ----------
KING 5000109. KING의 부서번호를 20번으로 갱신/ 월급도 8000으로 변경
SQL>update emp
set deptno =20where ename ='KING';
commit;
1 행이 업데이트되었습니다.
SQL>update emp
set sal =8000where ename ='KING';
commit;
1 행이 업데이트되었습니다.
SQL>select ename, sal, deptno
from emp
where ename ='KING';
ENAME SAL DEPTNO
-------------------- ---------- ----------
KING 8000209. FLASHBACK VERSION QUERY를 이용해서 KING의 데이터가 어떻게 변경되어왔는지
그 이력정보를 확인하시오. (commit을 해야 확인 가능)
SQL>select VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_OPERATION,
SAL, DEPTNO
from emp
VERSIONS BETWEEN scn minvalue and maxvalue
where ename ='KING';
#SCN : SystemCommit Number
VERSIONS_STARTSCN VERSIONS_ENDSCN VE SAL DEPTNO
----------------- --------------- -- ---------- ----------14407144 U 80002014407144500010
#두개를 갱신 후 한번에 커밋하면 위와 같이 출력됨.
10. FLASHBACK TRANSACTION QUERY를 이용해서 KING의 데이터를 과거로 되돌리기 위한
DML문장을 출력하시오
select undo_sql
from flashback_transaction_query
where table_owner ='C##SCOTT'and table_name ='EMP'AND commit_scn between [versions_startscn에 나온 값 중 작은값]
and [versions_endscn에 나온 값 중 큰 값]
orderby start_timestamp desc;
-------------------------------------------------------<적용>SQL>select undo_sql
from flashback_transaction_query
where table_owner ='C##SCOTT'and table_name ='EMP'AND commit_scn between14407144and14407144orderby start_timestamp desc;
UNDO_SQL
--------------------------------------------------------------------------------update "C##SCOTT"."EMP" set "DEPTNO" ='10'where ROWID ='AAATN5AAHAAAQAEAAA';
update "C##SCOTT"."EMP" set "SAL" ='5000'where ROWID ='AAATN5AAHAAAQAEAAA';
문제풀며 익히기
Q1.
ALLEN 의 커미션 7000으로 변경하고 직업을 PRESIDENT로 변경하시오.
SQL>commit;
커밋이 완료되었습니다.
SQL>update emp
set comm =7000where ename ='ALLEN';
1 행이 업데이트되었습니다.
SQL>commit;
커밋이 완료되었습니다.
SQL>update emp
set job ='PRESIDENT'where ename ='ALLEN';
1 행이 업데이트되었습니다.
SQL>commit;
Q2.
ALLEN 의 데이터가 그동안 어떻게 변경되어 왔는지 flashback version query 로
그 이력정보를 확인하고 flashback transactoin query 로 복구 dml 문장을 추출하시오
< flashback version query >SQL>select versions_startscn, versions_endscn, versions_operation,
sal, deptno
from emp
versions between scn minvalue and maxvalue
where ename ='ALLEN';
VERSIONS_STARTSCN VERSIONS_ENDSCN VE SAL DEPTNO
----------------- --------------- -- ---------- ----------14409707 U 1600301440962714409707 U 16003014409627160030===================================================================< flashback transactoin query >SQL>select undo_sql
from flashback_transaction_query
where table_owner ='C##SCOTT'and table_name ='EMP'AND commit_scn between14409627and14409707orderby start_timestamp desc;
UNDO_SQL
--------------------------------------------------------------------------------update "C##SCOTT"."EMP" set "JOB" ='SALESMAN'where ROWID ='AAATN5AAHAAAQAEAAF';
update "C##SCOTT"."EMP" set "COMM" ='300'where ROWID ='AAATN5AAHAAAQAEAAF';
=> 이 결과 그대로 입력하면, update됨
■ 제약 조건
💡
제약의 종류 5가지
제약조건
PRIMARY KEY : 중복된 데이터와 null 값을 입력되지 않게 하는 제약
(테이블 당 1개만 생성 가능 (ocp출제))
UNIQUE : 중복된 데이터를 입력 안되게 하는 제약
NOT NULL : null값을 입력되지 않게 하는 제약
CHECK : 지정된 문자 외에 다른 데이터는 입력되지 않게 하는 제약
FOREIGN KEY : 참조 하는 컬럼에 거는 제약
104 데이터의 품질 높이기 1(PRIMARY KEY)
📖
제약(constraint)이 왜 필요한가?
데이터베이스의 데이터의 품질을 높이기 위해서 필요
oracleyu23gmail.com <-- @가 없음! 테이블에 데이터 입력시, 에러가 발생하게끔 해줘야함
#우정사업본부 프로젝트 당시
##데이터 품질을 높이는 고가의 소프트웨어를 도입했지만, 크게 효과적이지 않음.
###처음부터 질 좋은 데이터가 들어와야 함
####테이블 만들때부터 DBA가 제약을 걸면서 말들어줘야함
💡
제약의 종류 5가지
제약조건
PRIMARY KEY : 중복된 데이터와 null 값을 입력되지 않게 하는 제약
(테이블 당 1개만 생성 가능 (ocp출제))
UNIQUE : 중복된 데이터를 입력 안되게 하는 제약
NOT NULL : null값을 입력되지 않게 하는 제약
CHECK : 지정된 문자 외에 다른 데이터는 입력되지 않게 하는 제약
FOREIGN KEY : 참조 하는 컬럼에 거는 제약
PRIMARY KEY
< constarint 설정 방법 >1. 테이블을 생성할 때 제약조건 함께 생성
1.1COLUMN LEVEL
create table [테이블명]
( empno number(10) CONSTRAINT [제약조건 이름] PRIMARY KEY,
ename varchar2(10));
1.2TABLE LEVEL
create table [테이블명]
( [컬럼명] [데이터타입], ...,
CONSTRAINT [제약조건명] PRIMARY KEY([제약조건 걸 컬럼명]) );
-------------------------------------------------------------------2. 이미 만들어진 테이블에 제약조건 넣기
@demp.sqlalter table [테이블명]
add constraint [제약조건명] primary key([컬럼명]);
-------------------------------------------------------------------<drop 방법 >alter table [테이블명]
dropconstraint [지울 pk명];
예제1)
다음과 같이 테이블을 생성하면서 empno에 primary key 제약을 걸어서 생성하시오.
예제 4) 다시 중복된 데이터와 NULL값을 입력해야해서 primary key제약을 삭제할 때
1. 제약조건 이름 확인하기
select t.table_name, t.constraint_name, c.column_name
from user_constraints t, user_cons_columns c
where t.constraint_name = c.constraint_name and t.table_name ='EMP51';
TABLE_NAME CONSTRAINT_NAME COLUMN_NAME
----------------------------------------------
EMP51 SYS_C008559 EMPNO
2. 제약조건 이름이 어려우니 생성할 때 부터 의미있게 생성하기! 다시 생성
droptable emp51;
create table emp51
( empno number(10) CONSTRAINT emp51_empno_pk PRIMARY key,
ename varchar2(10));
3.select t.table_name, t.constraint_name, c.column_name
from user_constraints t, user_cons_columns c
where t.constraint_name = c.constraint_name and t.table_name ='EMP51';
TABLE_NAME CONSTRAINT_NAME COLUMN_NAME
----------------------------------------------
EMP51 EMP51_EMPNO_PK EMPNO
4. pk drop 하기
alter table emp51
dropconstraint emp51_empno_pk;
A1. 중복데이터 삽입 가능 확인
insert into emp52 ( empno, ename, sal, hiredate)
values( 1111, 'smith', 4000, '2023/12/15');
!!ERROR!!
ORA-00001: 무결성 제약 조건(C##SCOTT.EMP52_EMPNO_PK)에 위배됩니다
-----------------------------------------------------------------
A2. NULL값 입력 가능 확인
insert into emp52 ( empno, ename, sal, hiredate)
values( null , 'smith', 4000, '2023/12/15');
!!ERROR!!SQL 오류: ORA-01400: NULL을 ("C##SCOTT"."EMP52"."EMPNO") 안에 삽입할 수 없습니다
Q5.
우리반 테이블의 empno에 primary key 제약을 거시오.
A. alter table emp18
add constraint emp18_empno_pk primary key(empno);
----------------< 제약조건 확인 방법 >select t.table_name, t.constraint_name, c.column_name
from user_constraints t, user_cons_columns c
where t.constraint_name = c.constraint_name and t.table_name ='EMP18';
제약조건은 SQL Developer로 확인하는게 편함.
105 데이터의 품질 높이기 2(UNIQUE)
📖
💡
제약의 종류 5가지
제약조건
PRIMARY KEY : 중복된 데이터와 null 값을 입력되지 않게 하는 제약
(테이블 당 1개만 생성 가능 (ocp출제))
UNIQUE : 중복된 데이터를 입력 안되게 하는 제약
NOT NULL : null값을 입력되지 않게 하는 제약
CHECK : 지정된 문자 외에 다른 데이터는 입력되지 않게 하는 제약
FOREIGN KEY : 참조 하는 컬럼에 거는 제약
예제1) 아래와 같이 unique제약을 걸어서 테이블 생성 및 변경 하시오.
**<column level 방식 >**create table emp55
( empno number(10),
ename varchar2(10) constraint emp55_ename_un UNIQUE,
sal number(10) );
-------------------------------------------------------**<table level 방식 >**create table emp56
( empno number(10),
ename varchar2(10),
sal number(10),
constraint emp56_ename_un UNIQUE(ename) );
--------------------------------------------------------**< 기존 table에 제약 걸기 >**alter table emp
add constraint emp_ename_un UNIQUE(ename);
alter table emp
add constraint emp_sal_nn NOT NULL(sal);
!!ERROR!!
ORA-00904: : 부적합한 식별자
00904.00000- "%s: invalid identifier"
=>add로 not null 제약을 추가할 수 없음
================================================alter table emp
MODIFY sal constraint emp_sal_nn NOT NULL;
=> MODIFY [컬럼명] CONSTRAINT [제약조건명] NOT NULL; 문법으로 해야
문제풀며 익히기
Q1.
우리반 테이블(emp18 테이블)에 ename에 not null 제약을 거시오
alter table emp18
modify ename constraint emp18_ename_nn NOT NULL;
Q2. DEPT 테이블에 loc 컬럼에 not null 제약을 거시오
alter table dept
modify loc constraint dept_loc_nn NOT NULL;
Q3. dept 테이블에 20번의 부서번호의 loc를 null로 변경하시오.
update dept
set loc =null #혹은 SET loc ='' (공백없이 싱글 쿼테이션 마크로 두루기)
where deptno =20;
!!ERROR!!SQL 오류: ORA-01407: NULL로 ("C##SCOTT"."DEPT"."LOC")을 업데이트할 수 없습니다
01407.00000- "cannot update (%s) to NULL"
create table emp65
( empno number(10),
ename varchar2(10),
loc varchar2(10) constraint emp65_loc_ck
CHECK(loc in ('DALLAS', 'CHICAGO', 'BOSTON') ) );
--------------------------------------------------------
emp65 테이블에 loc 컬럼에 값을 입력 또는 갱신할 때
부서위치 'DALLAS', 'CHICAGO', 'BOSTON' 외에는 입력 또는 수정할 수 없음
예제2) 아래 데이터들을 insert해보시오
insert into emp65(empno, ename, loc)
values(1111, 'scott', 'DALLAS');
insert into emp65(empno, ename, loc)
values(2222, 'allen', 'seoul');
!!ERROR!!
오류 보고 -
ORA-02290: 체크 제약조건(C##SCOTT.EMP65_LOC_CK)이 위배되었습니다
예제3)
emp 테이블에 부서번호 check 제약을 거는데 부서번호가 10, 20, 30 번외에는 입력 또는 수정되지 않도록 체크 제약 거시오.
alter table emp
add constraint emp_deptno_ck check(deptno in (10, 20, 30) );
문제풀며 익히기
Q1. 다음의 테이블을 생성하시오.
테이블명 emp66, 컬럼명 empno, ename, gender(남,여만 입력 or 수정될 수 있도록 할 것)
emp테이블의 sal에 체크 제약을 거는데, 월급이 0~9500 사이의 데이터만 입력 또는 수정되도록 check제약을 거시오
alter table emp
add constraint emp_sal_ck check(sal between0and9500);
Q3.
KING의 월급을 9600으로 수정하시오
update emp
set sal =9600where ename ='KING';
!!ERROR!!
오류 보고 -
ORA-02290: 체크 제약조건(C##SCOTT.EMP_SAL_CK)이 위배되었습니다
Q4. (OCP문제)
emp_sal_ck제약을 중지 후 다시 KING의 월급을 9600으로 수정하시오
< 제약 중지 시키기 >alter table emp
DISABLE constraint emp_sal_ck;
< 제약조건 및 상태 확인하기 >select constraint_name, status
from user_constraints #제약을 확인하는 데이터 사전
where table_name ='EMP'#반드시 대문자
----------
수정
update emp
set sal =9600where ename ='KING';
select ename, sal
from emp
where ename ='KING';
Q5. (OCP시험문제)
다시 emp_sal_ck 제약을 활성화 시키시오.
alter table emp
ENABLE CONSTRAINT emp_sal_ck;
!!ERROR!!
ORA-02293: (C##SCOTT.EMP_SAL_CK)을 검증할 수 없습니다 - 잘못된 제약을 확인합니다
02293.00000- "cannot validate (%s.%s) - check constraint violated"
=> 제약에 위반된 데이터(king의 월급)이 있기 때문에 재활성화 되지 않음
===============================================================< 제약에 위반된 데이터가 있더라도 제약을 활성화 시키고 싶다면?>alter table emp
enable NOVALIDATE constraint emp_sal_ck;
#NOVALIDATE 사용
Q6.
우리반 테이블에서 이름에 성씨를 출력하는데, 중복을 제거하여 출력하시오.
selectdistinct substr(ename,1,1) as 성씨
from emp18;
Q7.
우리반 테이블의 ename에 체크 제약을 거는데, 성씨가 우리반에 이미 있는 성씨 외에는 입력 또는 수정되지 못하도록 체크 제약을 거시오.
emp테이블과 dept 테이블이 있을 때
dept테이블의 deptno 컬럼에 pk를 걸고 , emp테이블의 deptno에 fk를 걸어서
emp.deptno 가 dept.deptno를 참조하게 하는 것
이때 dept = 부모 테이블, emp = 자식 테이블
emp.deptno에 40번 값을 입력하려고 할 때 dept.deptno에서 해당 값이 존재하는지 확인하고 존재하면 입력하고 , 존재하지 않다면 입력되지 않게 함
dept 테이블의 데이터도 함부로 삭제할 수 없음.
=========================
선생님 설명
위와 같이 부모 자식 관계가 형성되면, emp테이블에 부서번호를 아무거나 입력 또는 수정
할 수 없고, dept 테이블에 부서번호를 함부 삭제하지 못하는 상황이 됨.
alter table emp
add constraint emp_deptno_fk foreign key(deptno) REFERENCES dept(deptno);
====================================
[OCP문제]
위의 명령어가 성공하려면, DEPT 테이블의 DEPTNO에 PK 제약이 걸려있어야함.
예제3) EMP 테이블에 부서번호 70번을 입력해보시오.
insert into emp(empno, ename, sal, deptno)
values(1234, 'aaa', 4000, 70);
!!ERROR!!
ORA-02291: 무결성 제약조건(C##SCOTT.EMP_DEPTNO_FK)이 위배되었습니다- 부모 키가 없습니다
=>70번이 부모 테이블의 DEPTNO에 없는 값이므로 무결성 제약조건 위배
예제4) DEPT 테이블에 부서번호 10번을 삭제하시오.
deletefrom dept
where deptno =10;
!!ERROR!!
ORA-02292: 무결성 제약조건(C##SCOTT.EMP_DEPTNO_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다
=> 자식 테이블에 부서번호 10번의 값이 존재하기 때문에 위배되어 삭제 안됨
예제5) (dba의 업무 중 하나인 ETL(데이터 이행하는 일)업무)
dept 테이블의 모든 데이터를 dept700 테이블에 입력하시오.
insert into dept700(deptno, loc, dname)
select deptno, loc, dname
from dept;
commit;
select*from dept700;
예제6) [dba의 업무 중 하나인 etl업무]
emp테이블의 empno, ename, sal, deptno의 모든 데이터를emp700테이블에 이행하시오.
insert into emp700(empno, ename, sal, deptno)
select empno, ename, sal, deptno
from emp;
select*from emp700;
--------------------------------------------
제약에 위반된 데이터가 없기 때문에 잘 입력되는 것
alter table telecom_table
add constraint telecom_table_telecom_pk primary key(telecom);
Q4.
emp18테이블의 telecom에 FK 제약을 걸면서 telecom_table의 telecom를 참조하겠다고 하시오.
alter table emp18
add constraint emp18_telecom_fk foreign key(telecom)
references telecom_table(telecom);
-----------------------------------!!ERROR!!
ORA-02298: 제약 (C##SCOTT.EMP18_TELECOM_FK)을 사용 가능하게 할 수 없음 - 부모 키가 없습니다
02298.00000- "cannot validate (%s.%s) - parent keys not found"
*Cause: an alter table validating constraint failed because the table has
child records.
*Action: Obvious
=> 즉, 자식 테이블에 부모 테이블에 없는 값이 있어서 발생
=> 위 제약도 대소문자를 가리기 때문!--------------------------------------selectdistinct telecom
from emp18;
select*from telecom_table;
=> 값이 다른 것을 확인할 수 있음
---------------------------------------update emp18
set telecom =lower(telecom);
commit;
# telecom 을 소문자로 일괄 변경하기
alter table emp18
add constraint emp18_telecom_fk foreign key(telecom)
references telecom_table(telecom);
emp18값 변경 전
변경 후 제약 성
108 데이터의 품질 높이기 5(FOREIGN KEY)
📖
💡
제약의 종류 5가지
제약조건
PRIMARY KEY : 중복된 데이터와 null 값을 입력되지 않게 하는 제약
(테이블 당 1개만 생성 가능 (ocp출제))
UNIQUE : 중복된 데이터를 입력 안되게 하는 제약
NOT NULL : null값을 입력되지 않게 하는 제약
CHECK : 지정된 문자 외에 다른 데이터는 입력되지 않게 하는 제약
FOREIGN KEY : 참조 하는 컬럼에 거는 제약
** NOT NULL 제약만 COLUMN LEVEL로만 생성 가능 **
** 하나의 컬럼에 여러개의 제약조건 걸 수 있음 **
FOREIGN KEY
“참조하는 컬럼에 거는 제약”
특정 조건들 덕분에 outer 조인을 안해도 되는 경우가 발생해서 좋음.
outer join을 사용하게 되면 성능이 좋지 않기 때문.
따라서 부모자식 관계를 잘 설정해 놓는 것이 중요함
emp테이블과 dept 테이블이 있을 때
dept테이블의 deptno 컬럼에 pk를 걸고 , emp테이블의 deptno에 fk를 걸어서
emp.deptno 가 dept.deptno를 참조하게 하는 것
이때 dept = 부모 테이블, emp = 자식 테이블
emp.deptno에 40번 값을 입력하려고 할 때 dept.deptno에서 해당 값이 존재하는지 확인하고 존재하면 입력하고 , 존재하지 않다면 입력되지 않게 함
dept 테이블의 데이터도 함부로 삭제할 수 없음.
=========================
선생님 설명
위와 같이 부모 자식 관계가 형성되면, emp테이블에 부서번호를 아무거나 입력 또는 수정
할 수 없고, dept 테이블에 부서번호를 함부 삭제하지 못하는 상황이 됨.
alter table emp
add constraint emp_deptno_fk foreign key(deptno) REFERENCES dept(deptno);
====================================
[OCP문제]
위의 명령어가 성공하려면, DEPT 테이블의 DEPTNO에 PK 제약이 걸려있어야함.
예제3) EMP 테이블에 부서번호 70번을 입력해보시오.
insert into emp(empno, ename, sal, deptno)
values(1234, 'aaa', 4000, 70);
!!ERROR!!
ORA-02291: 무결성 제약조건(C##SCOTT.EMP_DEPTNO_FK)이 위배되었습니다- 부모 키가 없습니다
=>70번이 부모 테이블의 DEPTNO에 없는 값이므로 무결성 제약조건 위배
예제4) DEPT 테이블에 부서번호 10번을 삭제하시오.
deletefrom dept
where deptno =10;
!!ERROR!!
ORA-02292: 무결성 제약조건(C##SCOTT.EMP_DEPTNO_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다
=> 자식 테이블에 부서번호 10번의 값이 존재하기 때문에 위배되어 삭제 안됨
예제5) (dba의 업무 중 하나인 ETL(데이터 이행하는 일)업무)
dept 테이블의 모든 데이터를 dept700 테이블에 입력하시오.
insert into dept700(deptno, loc, dname)
select deptno, loc, dname
from dept;
commit;
select*from dept700;
예제6) [dba의 업무 중 하나인 etl업무]
emp테이블의 empno, ename, sal, deptno의 모든 데이터를emp700테이블에 이행하시오.
insert into emp700(empno, ename, sal, deptno)
select empno, ename, sal, deptno
from emp;
select*from emp700;
--------------------------------------------
제약에 위반된 데이터가 없기 때문에 잘 입력되는 것
alter table telecom_table
add constraint telecom_table_telecom_pk primary key(telecom);
Q4.
emp18테이블의 telecom에 FK 제약을 걸면서 telecom_table의 telecom를 참조하겠다고 하시오.
alter table emp18
add constraint emp18_telecom_fk foreign key(telecom)
references telecom_table(telecom);
-----------------------------------!!ERROR!!
ORA-02298: 제약 (C##SCOTT.EMP18_TELECOM_FK)을 사용 가능하게 할 수 없음 - 부모 키가 없습니다
02298.00000- "cannot validate (%s.%s) - parent keys not found"
*Cause: an alter table validating constraint failed because the table has
child records.
*Action: Obvious
=> 즉, 자식 테이블에 부모 테이블에 없는 값이 있어서 발생
=> 위 제약도 대소문자를 가리기 때문!--------------------------------------selectdistinct telecom
from emp18;
select*from telecom_table;
=> 값이 다른 것을 확인할 수 있음
---------------------------------------update emp18
set telecom =lower(telecom);
commit;
# telecom 을 소문자로 일괄 변경하기
alter table emp18
add constraint emp18_telecom_fk foreign key(telecom)
references telecom_table(telecom);
emp18값 변경 전
변경 후 제약 성
💡
[OCP 시험문제, SQLD, SQLP]
ON DELETE CASCADE 옵션
emp = 자식 테이블, dept = 부모 테이블일 때 deptno가 각각 FK, PK일때
dept의 deptno를 지우면 emp 테이블의 deptno 도 같이 지워지는 옵션
ON DELETE SET NULL 옵션
dept 테이블의 deptno 를 지우면, emp 테이블의 deptno 는 null 로 변경 되는 옵션
예제
Q1. 아래와 같이 제약조건을 설정하라
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);
alter table dept
dropconstraint dept_deptno_pk CASCADE;
#CASCADE 명령으로 인해 한번에 삭제됨
#DEPT 테이블의 PK가 날아가면서 FK제약도 한큐에 삭제
Q4. 이번에는 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)
ONDELETE CASCADE;
Q5. DEPT 테이블의 부서번호 10번을 지우시오.
deletefrom dept
where deptno =10;
rollback;
#emp table의 deptno도 함께 날아가기 때문에 지울 수 있는 것
##이는 ONDELETE CASCADE옵션 덕분에 가능한 것.
문제
Q1. 이번에는 ON DELETE SET NULL을 테스트 하시오.
1. 제약 지우기
alter table dept
dropconstraint dept_deptno_pk cascade;
2. 제약 생성하기
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)
ONDELETESETNULL;
3. DEPT 테이블의 부서번호 10번 지워보기
deletefrom dept
where deptno =10;
select ename, deptno
from emp;
rollback;
■ WITH 절
109 WITH절 사용하기 1(WITH ~ AS)
📖
with절을 사용하는 이유
하나의 SQL안에서 반복되는 SLOW 쿼리문이 여러 번 작성되어서 사용되고 있을 때 성능을 높이기 위해서 사용되는 sql문법
성능 높이는 방법 : 다른 sql과는 다르게 한번 쿼리한 결과(SLOW SQL일 때)를 TEMPORARY TABLESPACE에 저장 후 그 결과를 TEMP 테이블로 구성해서 성능을 높임.
예제1) WITH절을 사용하지 않았을 때(sql200제의 책 279p)
직업별 토탈월급을 출력하는데, 직업별 토탈월급들의 평균값보다 큰 것 출력
select job, sum(sal) as 토탈
from emp
groupby job
havingsum(sal) > (selectavg( sum(sal) )
from emp
groupby job);
main쿼리와 sub쿼리가 각각 20분이 소요된다고 했을 때
groupby 가 있는 select 문장(main)을 slow sql이라고 가정하고
with절로 튜닝할 것
예제2) with절을 사용했을 때(sql 200제의 책 278p)
with job_sumsal as ( select job, sum(sal) as 토탈
from emp
groupby job) #아래 줄을 한칸 띄지 말고 select절 입력
select job, 토탈
from job_sumsal
where 토탈 > (selectavg(토탈) from job_sumsal );
================================================================
job_sumsal과 함께라는 의미.!with절에 있는 select문장의 테이블명이 job_sumsal
이라는 의미이며, 이것은 임시 테이블임.
job_sumsal이라는 임시테이블을 TEMPORARY TABLESPACE(빈 저장 공간)에 저장 후
그 결과를 TEMP 테이블로 구성
즉, create table로 테이블을 만드는게 아니라 with절을 이용해서 생성한것
create table은 dba만이 할 수 있기 때문에, 권한이 없을 때 임의적으로!----------------------------------------------------------------with절로 slow쿼리의 결과를 temp table로 구성을 하고
이 temp table에서 데이터 엑세스 하는 쿼리문을 그 아래에 작성한다.
with절 내의 select문 결과
전체 실행 결과
예제3) 실행계획 확인하기
explain plan forwith job_sumsal as (select job, sum(sal) as 토탈
from emp
groupby job )
select job, 토탈
from job_sumsal
where 토탈 > (selectavg(토탈) from job_sumsal );
select*fromtable(dbms_xplan.display);
--------------------------------------------------------------
temp table명은 임의로 오라클이 부여함
예제4) with절을 사용하지 않은 일반 서브쿼리문의 실행계획을 보시오.
explain plan forselect job, sum(sal)as 토탈
from emp
groupby job
havingsum(sal) > (selectavg( sum(sal) )
from emp
groupby job);
select*fromtable(dbms_xplan.display);
=========================================================
차이 : EMP 테이블을 두 번 SELECT 함
따라서 WITH절은 한 번 SELECT 하기 때문에 성능이 더 빨라 튜닝시 많이 사용
문제풀며 익히기
Q1,
아래의 SQL을 WITH절로 변경하시오.
select deptno, count(*)
from emp
groupby deptno
havingcount(*) > (selectavg(count(*))
from emp
groupby deptno);
-----------------------------------------------with deptno_cnt as ( select deptno, count(*) as cnt
from emp
groupby deptno )
select deptno, cnt
from deptno_cnt
where cnt > ( selectavg(cnt)
from deptno_cnt);
110 WITH절 사용하기 2(SUBQUERY FACTORING)
📖
SUBQUERY FACTORING
기존의 서브쿼리문으로는 구현하지 못하는 기능을 with절로 할 수 있음
WITH절 튜닝 방법( 중요한 HINT ★ )
1./*+ materialize */ : tmep table을 구성하라고 명령하는 힌트
#확실하게 temporary tablespace에 구성하게 할 수 있음.
#아무도 db에 with절 넣지 않고 한가하다면 명화하게 구성하기
2./*+ inline */
: temp table을 구성하지 말고 subquery를 구현하는 힌트
이 경우는 temp table을 구성하는게 더 느린 경우 사용
#temporary tablespace (즉, disk 저장공간 같은)에
#다른 with절들도 계속 들어오게 되면 용량 부족..
#그럼 느려짐!
#그래서 inline으로 사용하라는 힌트를 준 것
#DB를 공유해서 사용하는 회사의 경우 유용
-----------------------------------------------------------
힌트 위치는 자유롭게
===========================================================
따라서 가장 좋은 방법은
WITH절을 사용하는 USER들 즉 세션들이 많으면,
TEMP TABLESPACE를 서로 사용하겠다고 경합을 벌이는거라 다 같이 느려짐
=> 이럴 땐 DBA가 with절을 사용할 시간대를 팀별로 분배해줘야한다.
분배되지 않을 경우, 힌트 2개를 사용해서
스스로 temp tablespace사용을 조절해야함.
예제1) 서브쿼리로는 구현하지 못하는 결과
select deptno, sum(sal)
from ( select job, sum(sal) 토탈
from emp
groupby job ) as job_sumsal,
( select deptno, sum(sal) 토탈
from emp
groupby deptno
havingsum(sal) > ( selectavg(토탈) +3000from job_sumsal )
) as deptno_sumsal;
!!ERROR!!
ORA-00933: SQL 명령어가 올바르게 종료되지 않았습니다
00933.00000- "SQL command not properly ended"
============================================================
첫번째 서브쿼리의 인라인 뷰의 별칭(job_sumsal)을
두번째 서브쿼리의 인라인뷰에 사용하려고 하면 안됨
예제2) 위의 결과를 with절로 만드시오.
with job_sumsal as ( select job, sum(sal) 토탈
from emp
groupby job ),
deptno_sumsal as ( select deptno, sum(sal) 토탈
from emp
groupby deptno
havingsum(sal) > (selectavg(토탈) +3000from job_sumsal ) )
select deptno,토탈
from deptno_sumsal ;
=========================================================
위와 같이 temp table을 여러개 만들 수 있는 것
예제3)
explain plan forwith deptno_cnt as ( select/*+ inline */ deptno, count(*) as cnt
from emp
groupby deptno )
select deptno, cnt
from deptno_cnt
where cnt > ( selectavg(cnt)
from deptno_cnt);
select*fromtable(dbms_xplan.display);
문제풀며 익히기
Q1.
■ DBA 를 위한 TIP
DBA가 반드시 알아야할 컬럼 변경 명령어 4가지
컬럼추가
예) EMP테이블에 email컬럼을 추가하기
alter table emp
add email varchar2(30);
ALTER TABLE [테이블명]
ADD [컬럼명] [데이터타입(길이)]
컬럼삭제
ALTER TABLE [테이블명]
DROPCOLUMN [컬럼명];
*주의사항
: 컬럼삭제는 일단 수행되고나면 FLASHBACK도 안되고 ROLLBACK도 안됨
컬럼변경
ALTER TABLE [테이블명]
MODIFY [컬럼명] [바꿀 데이터타입(길이)];
컬럼 감추기
필요한 이유?
위의 컬럼 삭제 명령어는 낮에 주간에 하면 안됨.
왜냐하면, 컬럼 삭제 명령어를 수행하게 되면, 관련 테이블을 SELECT할 때 성능이 느려지기 때문임(이유는 오라클 관리 수업시간에!) ⇒ 삭제할 때 DB성능 느려짐
지금 낮이라서 DROP은 할 수 없고 , 밤에 해야하는데 이 데이터를 엑세스를 하면 안되어서 감추고 싶을 때 감추기 사용
1. 감추기
ALTER TABLE [테이블명]
SET UNUSED COLUMN [컬럼명];
2. 다시 나타내기
못함,,,! 감추었다는 것은 나중에 DROP하려고 감춘 것이므로 DROP용임.
3. 몇 개 감춘지는 확인할 수 있음
select*from USER_UNUSED_COL_TABS;
4. 나중에 감춰진 컬럼들을 일괄 삭제 가능
ALTER TABLE emp
DROP UNUSED COLUMNS;
DBA를 위한 TIP
컬럼 삭제 요청이 들어오면? 업무 시간 외에 수행
빨리 삭제해달라고 요청이 들어오면? 컬럼을 감추고 밤에 삭제
컬럼 추가 문제 풀기
Q1.
emp 테이블에 address 컬럼을 추가하시오. 길이는 200byte
alter table emp
add address varchar2(200);
Q2.
emp테이블에 job컬럼을 삭제하시오.
alter table emp
dropcolumn job;
Q3.
emp테이블의 deptno의 컬럼의 길이를 number(10)으로 변경하시오
alter table emp
modify deptno number(10);
desc emp;
■ 인덱스 생성 지침 [OCP 시험문제]
** 다음의 경우에 인덱스를 생성합니다. **1. 테이블의 크기가 클때 :
EMP 테이블 처럼 작은 테이블은 인덱스가 필요없습니다.
인덱스 없이도 검색속도가 빠릅니다.
2.null 값이 많은 컬럼에 인덱스를 걸면 좋다.
인덱스 구성시 null 값은 구성되지 않습니다. 책에 빈페이지는 목차에 구성 안됩니다.
3.where 절에 자주 검색되는 컬럼에 인덱스를 걸어줘야 합니다. (가장 중요)
select empno, ename, sal
from emp
where ename='SCOTT';
select job, deptno, ename
from emp
where ename='ALLEN';
4. 검색하려는 데이터의 행이 전체 테이블의 2~4% 미만인 컬럼에 인덱스를 거는게 유용하다.
인덱스를 통해서 가져오는 데이터가 얼마 안될때 유용한 것.
즉, 목차를 통해서 책의 절반을 읽는것은 비효율적. 차라리 full scan이 나음.
------------------------------------------------------------------------------------** 다음의 경우는 인덱스를 생성하지 마세요 !**1.where 절에 자주 검색되지 않는 컬럼
2. 테이블이 작거나 검색하는 행이 테이블 전체의 2~4% 이상의 컬럼
3. 자주 갱신되는 컬럼(★)
테이블의 데이터를 변경하면 인덱스도 같이 변경해줘야합니다.
책의 내용을 변경하면 목차도 다시 변경해야합니다.
그런데 책은 그냥 변경하면 간단하지만 목차는 ㄱㄴㄷ 순서이기 때문에
변경할 때 시간이 많이 걸립니다.
인덱스가 있는 테이블에 insert, update 를 하면 insert, update 속도가 느려집니다
특히 insert가 느림.
4. 인덱스화 된 열이 컬럼에 표현식으로 사용되는 경우
select ename, sal
from emp
where sal *12=36000;
문제로 확인하기
Q1.
인덱스가 생성되어있는 테이블과 생성되어있지 않은 테이블에 대량의 데이터를 insert 할때 속도를 확인하시오
1. 다음 2개의 테이블을 생성
create table sales_no_index
asselect*from sh.sales where1=2;
create table sales_with_index
asselect*from sh.sales where1=2 ;
#대용량 테이블을 만들었을 때 sh라는 유저의 sales테이블을 가져오는것
2. 위의 2개의 테이블중에 sales_with_index 테이블에만 인덱스를 생성하시오
create index sales_index_1
on sales_with_index(amount_sold, promo_id);
3. 아래와 같이 대량의 데이터를 2개 테이블에 각각 입력하고 속도를 비교하시오!set timing on; #경과시간 보는 sqlinsert into sales_no_index
select*from sh.sales;
>918,843개 행 이(가) 삽입되었습니다.
경과 시간: 00:00:00.425set timing on;
insert into sales_with_index
select*from sh.sales;
>918,843개 행 이(가) 삽입되었습니다.
경과 시간: 00:00:01.637===========================================
테이블에 인덱스가 있으면 insert 가 느려집니다
![[SQL-4] SQL중급 2](https://image.inblog.dev?url=https%3A%2F%2Fsource.inblog.dev%2Ffeatured_image%2F2024-03-31T09%3A08%3A24.935Z-61b5e45c-74e9-482e-bc38-8546771597be&w=2048&q=75)
