SQL의 정석/Ch13. Object
Ch13. Object
developer-soyun
2024. 11. 5. 17:03
13.1 Object의 종류 및 기능
| 오브젝트 | 설명 |
| 테이블(table) | 행과 열의 조합으로 구성된 기본 저장단위 |
| 시퀀스(sequence) | 자동으로 고유한 숫자값을 생성해주며 주로 기본키 값을 생성하기 위해 사용 |
| 인덱스(index) | 쿼리(query) 속도를 향상시키기 위해 사용 |
| 뷰(view) | 하나 또는 그 이상의 테이블로부터 논리적으로 데이터를 추출한 부분집합으로 논리적이고 가상적인 테이블 |
| 시노님(synonym) | Object에 대한 또다른 이름으로 ALIAS 역할 |
| 프로그램 유닛(program unit) | SQL, PL/SQL문으로 작성한 Procedure, Function, Trigger, Package |
13.2 Sequence란?
- 자동으로 Unique number을 생성(중간에 Gap) 발생가능
- 공유가능한 Object
- 일반적으로 Primary Key 값 생성을 위해서 사용
CREATE SEQUENCE c_emp_id
INCREMENT BY 1
START WITH 26
MAXVALUE 9999999
NOCACHE
NOCYCLE;13.3 Sequence 생성하기
CREATE SEQUENCE 이름
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]- INCREMENT BY n
- SEQUENCE 번호 간의 간격, 생략되면 SEQUENCE는 1씩 증가
13.3 SEQUENCE 예제
-- 현재 유저가 소유한 모든 SEQUENCE 정보를 출력하시오
SELECT sequence_name, min_value, max_value, increment_by, last_number
FROM user_sequences;13.5 SEQUENCE 사용법
- 의사 컬럼(Pseudo columns) NEXTVAL, CURRVAL의 사용
- NEXTVAL은 사용 가능한 다음 SEQUENCE 값을 반환한다
- NEXTVAL은 다른 사용자에 의해 참조되더라도 매번 고유한 값을 반환한다
- CURRVAL은 현재 SEQUENCE 값을 포함한다
- CURRVAL은 반드시 NEXTVAL 사용 후에 참조되어야 한다
13.7 SEQUENCE의 변경과 삭제
ALTER SEQUENCE 이름
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]
DROP SEQUENCE 시퀀스이름;13.8 인덱스(INDEX) 의 개념
- 테이블의 데이터를 좀 더 빠르게 검색하기 위해 사용하는 데이터베이스 Object이다
- ORACLE Server가 최적화 방법(Optimization method)에 따라 어떤 Index를 사용할 것인지, 혹은 Index를 사용하지 않을 것인지 결정한다
- B+Tree의 검색방법으로 디스크 입출력(I/O) 횟수를 줄인다
- ORACLE Server가 Index를 자동적으로 사용하고 유지보수한다
- Index는 논리적, 물리적으로 테이블과는 독립적이다
- Index는 자동으로 생성되기도 하고 사용자가 필요에 의해 만들기도 한다
13.9 Index의 생성방법에 따른 종류
| Unique Index | Non-Unique Index |
| Primary Key, Unique 제약조건에 의해 자동으로 생성 | 사용자에 의해서 생성 |
| 각 Index(Table)값이 유일함을 보장 | 각 Index(Table)의 값이 유일하지 않음 |
13.10 인덱스의 구조
- 인덱스는 테이블의 각 행에 대응하는 주소(ROWID)와 인덱스 컬럼값으로 구성된다
- ORACLE Server는 인덱스에 의해 B*TREE 구조를 이용한다
- ORACLE Server는 해당 ROWID를 갖는 컬럼값이 있는 노드에 이를 때까지 트리를 탐색한다
13.11 B*TREE 의 특징
- 이 트리는 어떤 행에 대한 액세스 횟수도 동일하게 하는 이진의 균형 탐색 구조이다
- 행이 테이블의 시작이나 중간, 또는 끝에 있어도 거의 같은 횟수 내에 지정된 값을 액세스하는 효율적인 방법이다
- ORACLE Server가 만드는 인덱스는 트리에 정렬된 여러 개의 저장 장소 페이지로 구성된다
- 각 페이지는 키 값이 데이터의 위치를 가리킬 때까지 구조의 아래쪽으로 향하는 페이지에 대한 포인터와 일련의 키값을 가지고 있다
13.2 Oracle 에서 제공하는 최적화 방법
- 규칙 기준(Rule based) 최적화 <hard> - 잘 안쓰려한다
- 비용 기준(Cost based) 최적화 <soft> - 잘 쓰인다
- 인덱스가 있는 통계정보를 분석하여 가장 비용이 적게 드는 방식으로 액세스 경로를 결정한다
- 통계가 하나도 없다면 규칙 기준으로 시작해서 써가면서 통계를 획득한다
13.13 Index의 생성 및 삭제
CREATE INDEX 인덱스명
ON 테이블명(컬럼 [, 컬럼] ...);
DROP INDEX 인덱스명;13.14 인덱스 생성을 위한 지침
- 데이텅인덱스를 만드는 때
- 조건절(WHERE)이나 조인(JOIN) 조건에서 컬럼을 자주 이용할 때
- 컬럼이 넓은 범위 값을 가질 때
- 많은 NULL 값을 갖는 컬럼일 때
- 테이블의 데이터가 많고 그 테이블에서 조회되는 행의 수가 전체의 10-15% 정도일때 (소량검색)
- 인덱스를 만들지 않아야 할 때
- 테이블이 작을 때
- 컬럼이 조회의 조건으로 사용되는 경우가 별로 없을 대
- 대부분의 조회가 행의 10-15% 이상을 검색한다고 예상될 때
- 테이블이 자주 변경될 때
13.16 VIEW란?
- 테이블이나 다른 뷰를 기초로 한 가상(virtual)의 테이블
- 자체 데이터는 없지만 테이블의 데이터를 보고나 변경할 수 있는 창과 같다
- VIEW는 실제적으로는 SQL문으로 저장된다
- 장점
- 데이터베이스에 대한 액세스를 제한한다 (보안상의 이유로)
- 복잡한 SQL문을 통해 얻을 수 있는 결과를 간단한 SQL문을 써서 구할 수 있게 한다
- 한 개의 뷰로 여러 테이블에 대한 데이터를 검색할 수 있다 (JOIN)
13.17 VIEW의 생성
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW 이름 [(ALIAS [,ALIAS]...)]
AS subQUERY
[WITH CHECK OPTION [CONSTRAINT 제약조건]]
[WITH READ ONLY];- 뷰를 정의하는 SQL문은 조인, 그룹, SubQuery를 포함하는 복잡한 SELECT문장으로 구성될 수 있다
- 뷰를 정의하는 SQL문에는 ORDER BY 절을 쓸 수 없없다
- 제약조건의 이름을 명시하지 않으면 시스템이 SYS_Cn형태의 이름을 지정한다
- 뷰를 삭제하거나 재생성하지 않고 뷰의 정의를 변경하려면 ORREPLACE 옵션을 쓸 수 있다
13.18 View에 대한 DML 사용 규칙
- 간단한 뷰 (테이블 1개)에서는 DML 연산 수행가능
- 뷰가 join, 그룹함수, group by, distinct를 포함하는 경우 데이터를 삭제, 수정, 추가가 불가능하다
- 뷰가 다음 사항을 포함하는 경우 데이터를 수정할 수 없다
- 식으로 정의된 컬럼
- 뷰가 다음 사항을 포함하는 경우 데이터를 추가할 수 없음
- view에 선택되지 않은 not null 컬럼
13.20 View의 확인 및 삭제
SELECT view_name, test
FROM user_views;
DROP VIEW view이름;13.21 SYNONYM
- 특정 Object에 부여하는 또 다른 이름이다
- Synonym은 사용자의 편의나 참조를 빠르게 하기 위해서 사용한다
CREATE [PUBLIC] SYNONYM Synonym명
FOR Object명;