5/29 오후
>> ON DELETE CASCADE
>> ON DELETE SET NULL
ex) 쇼핑몰에서 물건을 구매한 이력이 있는 회원이 탈퇴했을 때, 구매 이력은 남겨놓고 회원 정보만 삭제할 때 사용.
* 테이블 만들기에서 서브쿼리 사용
CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;
※ 계산식 컬럼을 만들 때 별칭은 필수
* ALTER TABLE
>> READ ONLY / READ WRITE
* Dropping a Table
>> DROP TABLE dept80;
Creating Other Schema Objects
* What Is a View? : 뷰란?
한 테이블에서 내용을 제한적으로 보여주는 테이블
물리적으로 저장되지 않음.
* Creating a View
>> 뷰를 생성하기 전에, 생성할 수 있는 권한을 부여해준다.
SQL> conn / as sysdba
Connected.
SQL> GRANT create view TO hr;
Grant succeeded.
>> 권한 부여 후, 뷰를 생성한다.
CREATE VIEW empvu80
AS
SELECT employee_id, last_name, salary*12 ann_sal, hire_date
FROM employees
WHERE department_id = 80;
* 뷰 조회하기
SELECT view_name, text FROM user_views;
* 뷰 수정하기
>> 오라클에서는 CREATE OR REPLACE VIEW 문장을 사용.
다른 DBMS에서는 ALTER TABLE를 사용하기도 함.
CREATE OR REPLACE VIEW empvu80
AS
SELECT employee_id, last_name, salary, hire_date
FROM employees
WHERE department_id = 80;
* View에서의 DML 명령어
>> DELETE → 심플 뷰에서는 대부분 가능
- 제약이 가장 적음.
- 무조건 다 되는 것은 아님.
ex)
DELETE FROM empvu80
WHERE employee_id = 176;
>> UPDATE → 대부분 가능
- 그 외 불가능한 조건은 DELETE 명령과 비슷함.
- 표현식으로 된 컬럼은 불가능.
ex)
UPDATE empvu80
SET salary=salary*1.2;
>> INSERT → 불가능
- 제약 조건 때문에 불가.
* 뷰 삭제
>> DROP VIEW view;
※ 뷰 만들기 실습
* Sequences : 시퀀스
시퀀스란?
- UNIQUE 값을 생성해주는 오라클 객체.
- 보통 PRIMARY KEY 값을 생성하기 위해 사용.
- 메모리에 Caching 되었을 때 시퀀스 값의 액세스 효율이 증가한다.
- 시퀀스는 테이블과는 독립적으로 저장되고 생성된다.
- 한 번 사용한 번호를 재활용하지 않는다.
CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 200
NOCACHE
NOCYCLE;
SELECT * FROM departments;
INSERT INTO departments
VALUES(dept_deptid_seq.nextval, 'Support', null, 2500); ← 다음에 사용할 시퀀스 번호 확인
ROLLBACK;
INSERT INTO departments
VALUES(dept_deptid_seq.nextval, 'Customer Support', null, 2500);
SELECT * FROM departments;
SELECT dept_deptid_seq.currval FROM dual; ← 현재까지 사용한 시퀀스 번호 확인
CREATE SEQUENCE emp_empid_seq
INCREMENT BY 1
START WITH 415;
INSERT INTO employees
VALUES(emp_empid_seq.nextval, 'Jason', 'Kim', 'JKIM', '053.1236.1276', sysdate, 'IT_PROG', 6700, null, null, 60);
SELECT * FROM employees;
COMMIT;
>> 시퀀스 정보 보기
SELECT sequence_name, last_number
FROM user_sequences;
>> 시퀀스 수정하기
ALTER SEQUENCE
>> 시퀀스 삭제하기
DROP SEQUENCE
* Indexes : 색인
검색 성능을 향상시키기 위한 것.
>> Full Table Scan 방식과의 차이
=> Index 검색 방식
- ID와 Rowid(Pointer)를 함께 저장
- 밸런스 트리 구조
Root - Branch - Leaf
- 자료가 많을 수록 효과가 좋음.
>> 인덱스가 자동으로 만들어지는 경우
- PRIMARY KEY 또는 UNIQUE : 중복 체크용
- 제약 조건 조회
SELECT table_name, constraint_name, constraint_type
FROM user_constraints
WHERE constraint_type IN ('U', 'P')
ORDER BY 1;
- 유니크 인덱스 조회
SELECT table_name, index_name, uniqueness
FROM user_indexes
ORDER BY 1;
- 자동으로 만들어지는 인덱스는 삭제 불가
>> 수동으로 인덱스 만들기
CREATE INDEX
※ BITMAP INDEX(비트맵 인덱스) : 0 or 1
* SQL 자동 추적
SQL> conn / as sysdba
Connected.
SQL> GRANT SELECT ANY DICTIONARY to HR;
Grant succeeded.
-> 권한 부여 후,
SELECT * FROM employees
WHERE employee_id = 176;
※ SQL Development의 자동 추적 기능(F6 키) → cost가 더 높을수록 Index를 만들어 주면 검색에 유리하다.
>> Sequence 만들기 실습
CREATE TABLE bigemp
AS
SELECT * FROM employees;
INSERT INTO bigemp
SELECT * FROM bigemp;
commit;
SELECT COUNT(*) FROM bigemp;
CREATE SEQUENCE bigemp_id_seq
INCREMENT BY 1
START WITH 1
NOCYCLE
NOCACHE;
UPDATE bigemp
SET employee_id = bigemp_id_seq.NEXTVAL;
>> Index 만들기 실습
CREATE INDEX bigemp_id_ix
ON bigemp(employee_id); ← employee_id의 index를 만듦.
CREATE INDEX bigemp_sal_ix
ON bigemp(salary); ← salary의 index를 만듦.
SELECT * FROM bigemp
WHERE salary = 24000;
SELECT * FROM bigemp
WHERE salary*12 = 288000;
* salary의 index를 만들었으나, salary를 이용한 연산식을 조회할 때는 사용하지 않는다.
- 해결 방법 : 함수 기반 index를 만든다.
CREATE INDEX bigemp_annsal_ix
ON bigemp(salary*12);
SELECT * FROM bigemp
WHERE salary*12 = 288000;