플래시백
conn / as sysdba
SELECT * FROM v$option;
WHERE parameter like 'Flashback%'; ← 플래시백 유료 옵션 확인하기
* 플래시백 실습
conn hr/hr
UPDATE employees
SET salary = salary*1.5
WHERE employee_id = 141;
commit;
SELECT salary FROM employees
WHERE employee_id = 141;
=> 롤백은 안 되지만, undo 파일에 남아있을 것임.
* 플래시백 쿼리
SELECT employee_id, salary FROM employees
as of timestamp (systimestamp-5(24*60))
WHERE employee_id = 141;
* 플래시백 버전 쿼리
col versions_starttime for a25
col versions_endtime for a25
set line 120
SELECT versions_starttime, versions_endtime, salary
FROM employees
versions between timestamp minavalue and maxvalue
WHERE employee_id = 141;
Time Zones
* 시간대
표준 시 : 그리니치(Greenwich) 천문대 시간
우리나라 : 표준 시 + 9
- sysdate : date type을 반환
- systimestamp : timestamp(date/time) type을 반환
SELECT sysdate, systimestamp FROM dual;
-> 서버 시간
SELECT current_date, current_timestamp, localtimestamp FROM dual;
-> 내 시간
ALTER SESSION set time_zone = '-07:00';
-> 나의 세션 설정 바꾸기(미국 시간)
SELECT current_date, current_timestamp, localtimestamp FROM dual;
-> 다시 확인해보면 날짜가 바뀌어 있다.
※ 글로벌 환경에서는 시간대 조정이 필요할 수 있다.
* 시간 데이터 타입 확인하기
실습)
CREATE TABLE timetest1
(a date,
b timestamp,
c timestamp with time zone,
d timestamp with local time zone);
INSERT INTO timetest1
VALUES(sysdate, sysdate, sysdate, sysdate);
SELECT * FROM timetest1;
>> local time zone : 클라이언트의 시간대로 바꿔준다.
>> 시간 정보에서 초(sec)의 정밀도는 기본(default)값이 6, 최대 9까지 지정 가능(Max 11byte)
SELECT * FROM tab;
※ DB에 있는 테이블 보기
* 데이터 타입 변경하기
CREATE TABLE dept50
AS
SELECT employee_id, last_name, hire_date, job_id
FROM employees
WHERE department_id = 50;
SELECT * FROM dept50;
ALTER TABLE dept50
MODIFY hire_date TIMESTAMP(2); ← 바로 변경 가능
ALTER TABLE dept50
MODIFY hire_date TIMESTAMP(2) with time zone; ← with time zone 속성은 테이블에 값이 들어있으면 불가
* Interval : 간격
CREATE TABLE timetest2
(a interval year to month,
b interval day to second);
INSERT INTO timetest2
VALUES(interval '5-10' year to month, ← 5년 10개월
interval '90 12:30:30' day to second); ← 90일 12시간 30분 30초
SELECT systimestamp+a, systimestamp+b
FROM dual, timetest2;
* extract : 날짜 함수 (TO_CHAR 등으로 변환하지 않고 날짜 그대로 추출)
* TO_YMINTERVAL, TO_DSINTERVAL
* Daylight Saving Time (DST, 일광시간절약제, 서머타임)
* 고급 서브쿼리
>> Multiple-column Subquery
SELECT employee_id, manager_id, department_id
FROM empl_demo
WHERE (manager_id, department_id) IN (SELECT manager_id, department_id
FROM empl_demo
WHERE first_name = 'John')
AND first_name <> 'John';
이 문장과 아래의 문장은 다르다.
SELECT employee_id, manager_id, department_id
FROM empl_demo
WHERE manager_id IN
(SELECT manager_id
FROM empl_demo
WHERE first_name = 'John')
AND department_id IN
(SELECT department_id
FROM empl_demo
WHERE first_name = 'John')
AND first_name <> 'John';
※ IN이 AND보다 우선순위가 높다.
* Scalar Subqueries: Examples
SELECT employee_id, last_name,
(CASE
WHEN department_id = (SELECT department_id FROM departments
WHERE location_id = 1800)
THEN 'Canada' ELSE 'USA' END) location
FROM employees;
SELECT employee_id, last_name
FROM employees e
ORDER BY (SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id);
* 상호 관련(Correlated) 서브쿼리
- 메인 쿼리의 행이 다 떨어질 때까지 서브쿼리를 실행하여 비교하는 방법으로 수행됨.
- 구조가 좋지 않은 대표적인 SQL 중 하나.
ex)
SELECT last_name, salary, department_id
FROM employees outer_table
WHERE salary > (SELECT AVG(salary)
FROM employees inner_table
WHERE inner_table.department_id = outer_table.department_id);
-> 서브쿼리가 여러 번 실행된다.
※ 성능을 좋게 하는 SQL 문장 튜닝의 예 >
SELECT e.employee_id, e.last_name, e.salary, b.avg_sal, e.department_id
FROM employees e JOIN (SELECT department_id, AVG(salary) avg_sal
FROM employees
GROUP BY department_id) b
ON (e.department_id = b.department_id)
WHERE e.salary > b.avg_sal;
-> 조건을 FROM절에 작성.
* EXISTS 연산자 : 상호 관련 서브쿼리
- 조건에 맞는 것이 발견되면 추가로 더 이상 찾지 않는다.
- 성능을 개선할 수 있음.
ex)
SELECT employee_id, last_name, job_id, department_id
FROM employees outer
WHERE EXISTS (SELECT 'X'
FROM employees
WHERE manager_id = outer.employee_id);
* Correlated UPDATE : 상호 관련 업데이트
실습)
SELECT * FROM dept50;
ALTER TABLE dept50
ADD salary NUMBER(10); ← 행을 추가한 후,
UPDATE dept50 d
SET salary = (SELECT salary FROM employees e
WHERE e.employee_id = d.employee_id);
← 데이터를 기존에 있던 테이블과 똑같이 넣고 싶을 때 사용.
* Correlated DELETE : 상호 관련 삭제
* WITH문 : 인라인뷰를 확장한 기능.
ex)
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;