DATABASE 7일차 (딕셔너리뷰,시퀀스,동의어, 인덱스,VIEW,WITH CHECK OPTION,ALTER TABLE,WITH)
데이터 딕셔너리
- DB를 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블
- 사용자가 테이블을 생성하거나 변경 할 때 자동으로 갱신된다.
- 사용자는 DATA 딕셔너리의 내용을 직접 수정/삭제가 불가능 하다.
- 데이터베이스의 구조 정보 즉 메타 데이터를 담고 있다.
데이터 딕셔너리는 DB를 구성하는 모든 요소에 대한 정보를 가지고있다.
- 오라클 사용자의 정보
- 오라클 권한과 롤 정보
- 데이터베이스 스키마 객체(TABLE,VIEW,INDEX,CLUSTER,SYNONYM,SQUENCE...)정보
- 무결성 제약조건에 관한 정보
- 오라클 데이터베이스의 함수 와 프로시져 및 트리거에 대한 정보
- 기타 일반적인 DATABASE정보
딕셔너리 정보 보기
SELECT * FROM DICTIONARY;ALL_XXX
한 특정 사용자가 조회 가능한 모든 데이터사전을 의미(A사용자(ALL) B사용자의 객체를 조회 하려면 A사용자가 B사용자의 객체를 조회 할 수 있는 권한이 있어야한다)
SELECT table_name,tablespace_name
FROM ALL_TABLES;USER_XXX
한 특정 사용자에게 종속되어 있고 그 사용자가 조회 가능한 데이터 사전 뷰들로 ALL_XXX 데이터 사전의 모든 정보의 부분집합이다.
SELECT object_name, object_type, created, status
FROM user_objects
ORDER BY object_type;
DBA_XXX
DBA권한을 가진 사용자 만이 조회할 수 있는 데이터 사전으로서 모든 오라클 데이터베이스 객체에 대한 정보를 볼 수 있다, SELECT ANY TABLE 권한이 있는 사용자 또는 질의가 가능하며 다른 사용자가 질의 하려면 앞에 SYS. 이라는 접두어를 사용해야한다.
SELECT OWNER,OBJECT_NAME
FROM SYS.DBA_OBJECTS;
딕셔너리 뷰
데이터 딕셔너리를 사용자가 이해 할 수 있도록 변환하여 제공
테이블 정보 확인
DESCRIBE user_tables
SELECT table_name
FROM user_tables;열에 대한 정보
DESCRIBE user_tab_columns
SELECT column_name, data_type, data_length,data_precision, data_scale, nullable
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES';CONSTRAINTS
유저의 테이블에 있는 제약조건의 정의를 설명
ex) user_CONS_COLUMNS는 유저가 소유하고 제약 조건에 지정된 열에 대해 설명
DESCRIBE user_constraints
ex)
SELECT constraint_name, constraint_type,search_condition, r_constraint_name,delete_rule, status
FROM user_constraints
WHERE table_name = 'EMPLOYEES';
DESCRIBE user_cons_columns
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'EMPLOYEES';
데이터베이스 객체
다른 유저의 테이블 참조
- 다른 유저가 소유한 테이블은 유저의 스키마에 없다.
- 이러한 테이블에는 소유자의 이름을 접두어로 사용해서 검색 해야한다.
시퀀스
유일한 값을 생성해준다.
보통 Primary key 값을 생성하는데 사용한다.
시퀀스 값이 메모리에 캐쉬된 경우 엑세스 속도가 향상된다.
공유할 수 있는 객체이다.
시퀀스 생성
- departments 테이블의 Primary key 에 사용할 dept_deptid_seq라는 시퀀스를 생성
- cycle옵션을 사용하면 숫자값의 중복이 발생 될 수 있다.
CREATE SEQUENCE dept_deptid_seq
START WITH 280
INCREMENT BY 10
MAXVALUE 9999
NOCACHE
NOCYCLE;
#start with 초기값
#increment by 증감값
#maxvalue 최대값
NEXTVAL : 현재 시퀀스 값의 다음값을 반환한다.
CURRVAL : 가장 최근에 발생된 숫자값을 반환 한다. ==> CURRVAL를 통해 확인된 숫자 이후에 숫자가 NEXTVAL을
통해 발생 된다.
INSERT INTO departments(department_id,
department_name, location_id)
VALUES (dept_deptid_seq.NEXTVAL,
'Support', 2500);
SELECT dept_deptid_seq.CURRVAL
FROM dual;
#현재 시퀀스 값 반환
SELECT dept_deptid_seq.NEXTVAL
FROM dual;
#다음 시퀀스 값 반환
시퀀스 값에 간격이 발생하는 경우 3가지
- 롤백이 발생한 경우
- 시스템이 중단되는 경우
- 시퀀스가 다른 테이블에서 사용되는 경우
ALTER를 이용한 시퀀스 값 수정
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;시퀀스 수정 지침
- 시퀀스의 소유자 이거나 ALTER 권한이 있어야 한다.
- 후속 시퀀스 번호에만 적용
- 다른 번호로 시퀀스를 재시작 하려면 시퀀스를 삭제하고 다시 재시작 해야한다.
- 시퀀스 삭제 => DROP문
동의어
- 동의어는 별칭이다.
- 기본 스키마 객체의 ID 및 위치를 숨기는데 유용합니다.
동의어 생성
CREATE SYNONYM d_sum
FOR dept_sum_vu;
#dept_sum_vu 라는 네임을 d_sum이라는 별칭으로 지정동의어 삭제
DROP SYNONYM d_sum;만든 동의어 정보 확인
DESCRIBE user_synonyms
SELECT *
FROM user_synonyms;
인덱스
- 오라클 서버에서 포인터를 사용하여 행 검색 속도를 높이는 데 사용할 수 있습니다.
- 인덱스의 대상인 테이블에 종속적 이다.
- 책에서 목차와 같은 개념이다.
인덱스 생성 방식 2가지
- 자동 : 테이블 정의에서 primary key 또는 unique 제약 조건을 정의하면 고유 인덱스가 자동으로 생성된다.
- 수동 : 행에 액세스 하는 속도를 높이기 위해 유저가 열의 고유 또는 비고유 인덱스를 생성할 수 있다.
last_name열에 대한 query 엑세스 속도를 향상 시킨다.
CREATE INDEX emp_last_name_idx
ON employees(last_name);create table 문과 함께 create index 절 사용
CREATE TABLE NEW_EMP
(employee_id NUMBER(6)
PRIMARY KEY USING INDEX
(CREATE INDEX emp_id_idx ON
NEW_EMP(employee_id)),
first_name VARCHAR2(20),
last_name VARCHAR2(25));
SELECT INDEX_NAME, TABLE_NAME
FROM USER_INDEXES
WHERE TABLE_NAME = 'NEW_EMP';
인덱스 제거
DROP INDEX index;
DROP INDEX emp_last_name_idx;
VIEW
뷰는 가상 테이블 이다.
실제 테이블을 가지고 있지는 않지만 뷰를 통해서 원하는 정보만 불러와서 확인 할 수 있으며 이를 통해 수정도 가능하다.
뷰는 변수와도 비슷한 개념이다.
create view 를 통해서 view명 을 선언하고 as 뒤에 select문부터 해서 qurey문을 넣어주면 나중에는 복잡한 qurey 문을 사용 하지 않아도 된다.
뷰에 대한 규칙
- 단순 뷰에서는 대개 DML 작업을 수행할 수 있다.
- 뷰에 다음 항목이 포함되면 행을 제거할 수 없다.
- 그룹함수
- GROUP BY절
- DISTINCY 키워드
- pseudocolumn ROWNUM 키워드
뷰 생성
CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;뷰 에 대한 설명
DESCRIBE empvu80;
뷰 생성 예제
CREATE VIEW salvu50
AS SELECT employee_id ID_NUMBER, last_name NAME,
salary*12 ANN_SALARY
FROM employees
WHERE department_id = 50;뷰 데이터 검색
SELECT *
FROM salvu50;create or replayce view 절을 이용한 view 수정
CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS SELECT employee_id, first_name || ' '
|| last_name, salary, department_id
FROM employees
WHERE department_id = 80;
WITH CHECK OPTION 절
VIEW에 대한 갱신이나 삽입 연산을 실행할 때 VIEW 정의 조건을 위한하면 실행이 거부 된다는 것을 의미한다.
- 데이터의 무결성을 유지할 수 있다.
- INSERT 문과 UPDATE 문으로 뷰에 정의된 조건에 위배된 투플을 생성할 수 없음
WITH CHECK OPTION 절 사용
CREATE OR REPLACE VIEW empvu20
AS SELECT *
FROM employees
WHERE department_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck ;WITH READ ONLY 절 사용
- VIEW에 대한 갱신을 불가해진다.
WITH READ ONLY 사용 예제
CREATE OR REPLACE VIEW empvu10
(employee_number, employee_name, job_title)
AS SELECT employee_id, last_name, job_id
FROM employees
WHERE department_id = 10
WITH READ ONLY ;
VIEW 제거
DROP VIEW view;
DROP VIEW empvu80;ALTER TABLE 문
- 제약 조건 추가 / 삭제
- 제약조건 활성화 / 비활성화
- MODIFY 절을 이용하여 수정
기본 구조
ALTER TABLE <table_name>
ADD [CONSTRAINT <constraint_name>]
type (<column_name>);
ALTER TABLE 예제
==> EMP2 테이블에 FOREIGN KEY 제약조건 추가
ALTER TABLE emp2
MODIFY employee_id PRIMARY KEY;
ALTER TABLE emp2
ADD CONSTRAINT emp_mgr_fk
FOREIGN KEY(manager_id)
REFERENCES emp2(employee_id);
drop_constraint_clause를 사용하여 제약조건 삭제
ALTER TABLE emp2
DROP CONSTRAINT emp_mgr_fk;
constraint online 삭제
online 키워드를 지정하여 제약 조건을 삭제하는 동안 테이블에서의 DML 작업이 허용됨을 나타낸다.
ALTER TABLE myemp2
DROP CONSTRAINT emp_name_pk ONLINE;상위 키가 삭제될 경우 하위 행도 삭제하기 위해서는 ON DELETE CASCADE 절을 사용한다.
ALTER TABLE dept2 ADD CONSTRAINT dept_lc_fk
FOREIGN KEY (location_id)
REFERENCES locations(location_id) ON DELETE CASCADE;상위 키가 삭제될 때 하위 행 값을 NULL로 설정 하려면 ON DELETE SET NULL 절을 사용한다.
ALTER TABLE emp2 ADD CONSTRAINT emp_dt_fk
FOREIGN KEY (Department_id)
REFERENCES departments(department_id) ON DELETE SET NULL;테이블의 이름 변경 : ALTER TABLE 문의 RENAME TABLE 절 사용
ALTER TABLE marketing RENAME to new_marketing;테이블 열의 이름 변경 : ALTER TABLE 문의 RENAME COLUMN 절 사용
ALTER TABLE new_marketing RENAME COLUMN team_id
TO id;테이블에 대한 기존 제약 조건의 이름 변경 : ALTER TABLE 문의 RENAME CONSTRAINT 절
ALTER TABLE new_marketing RENAME CONSTRAINT mktg_pk
TO new_mktg_pk;제약조건 비활성화 : ALTER TABLE DISABLE절 사용
※PRIMARY KEY 를 비활성화 하려면 CASCADE 옵션을 적용
ALTER TABLE emp2
DISABLE CONSTRAINTS emp_dt_pk;
ALTER TABLE dept2
DISABLE primary key CASCADE;제약조건 활성화 : ENABLE 절 사용
ALTER TABLE emp2
ENABLE CONSTRAINT emp_dt_fk;
WITH절
WITH 절은 임시 테이블을 만든다는 관점에서 VIEW 와 비슷 할 순 있지만 차이점은 WITH 절은 한번 실행할 쿼리문 내에 정의 되어 있을 경우, 그 쿼리문안에서만 실행된다.즉 트랜잭션이 끝아면 종료된다.
하지만 VIEW는 DROP될 때 까지 계속 실행 된다.
예제
WITH CNT_DEPT AS
(
SELECT department_id,
COUNT(1) NUM_EMP
FROM EMPLOYEES
GROUP BY department_id
)
SELECT employee_id,
SALARY/NUM_EMP
FROM EMPLOYEES E
JOIN CNT_DEPT C
ON (e.department_id = c.department_id);
알아둘 것
옵티마이져 : sql을 가장 빠르고 효율적으로 수행할 최적의 처리 경로를 생성해주는 dbms 내부의 핵심 엔진이다.
삭제한 테이블 확인 하는 곳( 휴지통)
drop table emp2 ==> emp3 삭제
desc recyclebin; ==> 휴지통 확인
DROP TABLE emp2 PURGE; ==> 휴지통에 안버리고 영구 삭제
Subquery를 사용하여 데이터 검색
1. 임의 사원의 부서 번호와 급여를 커미션을 받는 사원의 부서 번호 및 급여와 비교하여 값이
일치하는 사원의 성, 부서 번호 및 급여를 표시하는 query를 작성합니다.
2. 급여 및 job_ID가 위치 ID 1700에 있는 사원의 급여 및 job_ID와 일치하는 사원의 성,
부서 이름 및 급여를 표시합니다.
3. 급여 및 manager_ID가 Kochhar와 동일한 모든 사원의 성, 채용 날짜 및 급여를 표시하는
query를 작성합니다.
주: 결과 집합에 Kochhar를 표시하지 마십시오.
4. 모든 영업 관리자(JOB_ID = 'SA_MAN')보다 많은 급여를 받는 사원을 표시하는 query를
작성합니다. 급여 결과를 하향식으로 정렬합니다.
5. 이름이 T로 시작하는 도시에 거주하는 사원의 사원 ID, 성 및 부서 ID와 같은 세부 정보를
표시합니다.
6. 해당 부서의 평균 급여보다 급여 수준이 높은 모든 사원을 찾는 query를 작성합니다. 해당
부서에 대해 사원의 성, 급여, 부서 ID 및 평균 급여를 표시합니다. 평균 급여를 기준으로
정렬하고 소수점 2자리수로 반올림합니다. 예제 출력에 표시된 대로 query에 의해 검색되는
행에 alias를 사용합니다.
8. 해당 부서의 평균 급여보다 급여 수준이 낮은 사원의 성을 표시하는 query를 작성합니다.
9. 같은 부서에서 채용 날짜는 더 늦지만 더 높은 급여를 받는 1인 이상의 동료가 있는 사원의
성을 표시하는 query를 작성합니다.
10. 모든 사원의 사원 ID, 성 및 부서 이름을 표시하는 query를 작성합니다.
주: scalar subquery를 사용하여 SELECT 문에서 부서 이름을 검색하십시오.
11. 총 급여 비용이 전체 회사의 총 급여 비용의 8분의 1(1/8)을 초과하는 부서의 부서 이름을
표시하는 query를 작성합니다. WITH 절을 사용하여 이 query를 작성하고 query 이름을
SUMMARY로 지정합니다.