[easyupclass] 초심자를 위한 데이터베이스 및 빅데이터 플랫폼 구축과정(SQL, 하둡) 17 - 20

<데이터 유형>

1. 문자형: varchar2

2. 숫자형: number

3. 날짜형: date

 

<임시 테이블>

1. on commit delete rows: 커밋을 하면 데이터 삭제

2. on commit preserve rows: 세션을 종료하면 데이터 삭제

 

<인덱스>

- 전체를 조회 = full table scan (where sal=3000)

- rowid: 행의 주소 = file 번호 + 블럭 번호 + row 번호

- create index table

   -> 테이블 조회

 

<versions query>

1. commit이 진행되어야 version이 업데이트 됨

2. versions_starttime의 초기값은 null -> null first로 봐야 시간순

3. to_timestamp 값은 특정 시점을 시작지점으로 설정할 수 있음 (between timestamp 구문에서 종료시점 설정 가능)

 

FLASHBACK TRANSACTION QUERY:

-- 서열 (level) 출력
-- rpad : 공백 채우기
select rpad(' ',level*3) || ename, level, sal, job
from emp
start  with  ename='KING'
connect by prior empno = mgr;

-- 서열에서 blake와 blake 팀원들 출력 X
select rpad(' ',level*3) || ename, level, sal, job
from emp
start  with  ename='KING'
connect by prior empno = mgr and ename != 'BLAKE';

-- 서열 순서 유지 + 월급이 높은 순서대로
select rpad(' ',level*3) || ename, level, sal, job
from emp
start  with  ename='KING'
connect by prior empno = mgr
order siblings by sal desc;

select rpad(' ',level*3) || ename, level, sal, job
from emp
start  with  ename='BLAKE'
connect by prior empno = mgr 
order siblings by sal;

-- 서열 순서 출력
select ename
    , sys_connect_by_path(ename, '/') as path
from emp
start with ename='KING'
connect by prior empno = mgr;

-- DDL 명령어 
-- create table
create table emp93
(empno number(10), 
 --varchar2 200까지 가능
 ename varchar2(10),
 -- 2자리 소숫점 허용
 sal number(10,2),
 hiredate date );
 
insert into emp93
values (7788, 'scott', 3000, to_date('81/12/21','RR/MM/DD') );

select * from emp93;

drop table emp93;

-- 임시 테이블 새성하기 (create temporary table)
-- commit 하면 데이터가 사라짐
create global temporary table emp37
(empno number(10),
 ename varchar2(10),
 sal number(10) )
on commit delete rows;

insert into emp37 values(1111, 'scott', 3000);
insert into emp37 values(2222, 'smith', 4000);

select * from emp37;

commit;

select * from emp37;


-- VIEW
create view emp_view
as (
select empno, ename, job, mgr, deptno
from emp
where job='SALESMAN'
);

select * from emp_view;

-- emp_view 테이블 값 수정하면 emp에도 반영됨
-- view는 바라보는거지 데이터를 가지고 있지 않음 = view와 원래 테이블이 별개가 아님
update emp_view
set mgr=0
where ename='MARTIN';

select * from emp_view;
select * from emp;

rollback;

select * from emp;

-- view : 2. 복잡한 쿼리 단순하게 하기, 간단하게 검색
-- 그룹함수 컬럼에 별칭 반드시 써야 함
create view emp_view2
as (
select deptno, round(avg(sal)) as avgsal
from emp
group by deptno
);

select * from emp_view2
order by deptno;

-- index : 데이터 검색 속도 높이기
-- 객체 (테이블, 뷰, 인덱스)
-- index = 검색속도를 높이는 객체 = 목차 역할
-- where 조건절이 index에 목차로 있고 테이블에도 같은 index 목차가 있음

-- full table scan (where)
-- 실행계획 보기
explain plan for (
select ename, sal
from emp
where sal = 3000
);

select * from table(dbms_xplan.display);

--월급 index
create index emp_sal
on emp(sal);

explain plan for
select ename, sal
from emp
where sal=3000;

select * from table(dbms_xplan.display);

select rowid, empno, ename
from emp;

-- index의 모습 (where 절에 조선을 줘야 함)
-- 문자 > ''
select sal, rowid
from emp
where sal >= 0;

-- sequence : 절대 중복되지 않는 번호 만들기
-- 사원번호, 거래번호 등
create sequence seq1;

select seq1.nextval
from dual;

create sequence seq2
start with 1
maxvalue 100
increment by 1 -- 증가
nocycle; -- 번호를 순환하지 않고 숫자가 다 되면 종료, 그 이상은 error

select seq2.nextval
from dual;

create sequence seq3
start with 1
maxvalue 100
increment by 1;

create table emp500
(empno number(10),
 ename varchar2(10) );
 
 insert into emp500
 values (seq3.nextval, 'scott');
 
 select * from emp500;
 
 insert into emp500
 values (seq3.nextval, 'smith');
 
 select * from emp500;
 
 
 -- 타임머신 기능 : FLASHBACK QUERY
 -- 실수로 지운 데이터 복구하기
 select * from emp;
 delete from emp;
 commit;
 select * from emp;
 rollback; -- 커밋 후에는 롤백 불가능

-- 5분전 데이터 복구
insert into emp (
 select * 
 from emp
 as of timestamp(systimestamp - interval '5' minute)
);

select * from emp;
commit;
-- 한계 : undo_retention value(900초=15분) 안에만 가능
show parameter undo;

-- sal = 0 후 원래 값으로 갱신
merge into emp e
using (select empno, sal
        from emp as of timestamp (systimestamp - interval '5' minute) s
on (e.empno = s.empno)
when matched then 
update set e.sal = s.sal;

-- FLASHBACK TABLE로 데이터 복구하기
delete from emp;

alter table emp enable row movement; -- 복구 가능한 상태로 만들기
-- 역시 15분 안에서만 복구 가능
flashback table emp to timestamp(systimestamp - interval '5' minute);

select * from emp;
commit;

-- FLASHBACK DROP : 휴지통(drop)에서 복구
drop table emp; -- 테이블 구조, index 까지 삭제함

select * from emp; -- Error: table or view does not exist

select *
from user_recyclebin
order by droptime desc; -- 휴지통 보기

flashback table emp to before drop; -- 가장 최근에 drop된 테이블 복구
select * from emp;

purge recyclebin; -- 휴지통 비우기

-- FLASHBACK VERSION QUERY : 히스토리 확인하기
select systimestamp from dual;

update emp
set sal=0
where ename='KING';

commit;

delete from emp;

select ename, sal, deptno,
        versions_starttime, versions_endtime, versions_operation
        -- versions_operation: u = update, d = delete
from emp
versions between timestamp to_timestamp('22/11/23 20:10:00', 'RRRR-MM-DD HH24:MI:SS')
            AND MAXVALUE
where ename='KING'
order by versions_starttime nulls first;

** 수업 다 듣고 예제 103번 복습하기 (20차시 10분 13초)