SQL 첫걸음 6장
6장 데이터베이스 객체 작성과 삭제
데이터베이스 객체 = 테이블이나 뷰, 인덱스 등 데이터베이스 내에 정의하는 모든 것을 일컫는 말
실체 유무로 구별 가능하다
객체 명명 규칙
스키마
데이터베이스 객체는 스키마라는 그릇 안에 만들어진다. 따라서 객체의 이름이 같아도 스키마가 서로 다르다면 상관없다.
테이블 작성/삭제/변경
작성
CREATE TABLE sample62 (
no INTEGER NOT NULL,
a VARCHAR(30),
b DATE);
삭제
DROP TABLE sample62;
TRUNCATE TABLE sample62;
- DELETE 명령은 행 단위로 여러 가지 내부처리가 일어나므로 삭제할 행이 많으면 처리속도가 느리다. 이런 경우 삭제할 행을 지정할 수 없고 WHERE 구를 지정할 수도 없지만, 모든 행을 삭제해야 할때 빠른 속도로 삭제할 수 있는 TRUNCATE TABLE 명령을 사용한다.
변경
ALTER TABLE sample62 ADD newcol INTEGER;
+) MODIFY 하부명령으로 열 속성도 변경 가능하다
+) CHANGE 하부명령으로 열 이름도 변경 가능하다
+) DROP 하부명령으로 열 삭제도 가능하다
ALTER TABLE로 테이블 관리
- 최대길이 연장
실제로 시스템을 운용하다 보면 처음에는 한 자리로 충분했던 용량이 시간이 지나면서 부족해지는 일이 많다. 이러한 경우 ALTER TABLE로 열의 자료형만 변경해 대응할 수 있다. 열의 자료형은 처음 CREATE TABLE을 실행할 때 결정된다. 특히 문자열형의 경우 최대길이를 지정하는데, 이 최대길이를 ALTER TABLE로 늘릴 수 있다.
ALTER TABLE sample MODIFY col VARCRAR(30)
- 열 추가
테이블에 열을 추가하는 일은 자주 일어난다. 시스템의 기능 확장 등 이유는 여리 가지가 있다.
ALTER TABLE sample ADD new_col INTEGER
제약
NOT NULL 제약
테이블 작성 시 제약 정의
CREATE TABLE sample631 ( a INTEGER NOT NULL, b INTEGER NOT NULL UNIQUE, c VARCHAR(30) );
a열에는 NOT NULL 제약, b열에는 NOT NULL 제약과 UNIQUE 제약, c열에는 미지정 제약 걸려있다.
이처럼 열에 대해 정의하는 제약을 '열 제약'이라 부른다.
'복수열에 의한 기본키 제약'처럼 한 개의 제약으로 복수의 열에 제약을 설명하는 경우를 '테이블 제약'이라 부른다.
- 제약에 이름을 붙이면 관리가 쉽다 -> CONSTRAINT 키워드로 지정한다.
제약 추가/삭제
-
열 제약 추가
ALTER TABLE sample631 MODIFY c VARCHAR (30) NOT NULL;
ALTER TABLE로 열의 정의 변경 -
테이블 제약 추가
ALTER TABLE sample631 ADD CONSTRAINT pkey_sample631 PRIMARY KEY(a);
ALTER TABLE의 ADD 하부명령으로 추가할 수 있다. -
제약 삭제
ALTER TABLE sample631 MODIFY c VARCHAR(30);
ALTER TABLE sample631 DROP CONSTRAINT pkey_smaple631;
기본키
기본기 제약을 설정하기 위해서는 테이블은 어떤 조건을 만족해야 하는가?
기본기는 테이블의 행 한 개를 특정한 수 있는 검색기이다. 기본키 제약이 설정된 테이블에서는 기본기로 검색했을 때 복수의 행이 일치하는 데이터를 작성할 수 없다. 간단히 말하면. 기본기로 설정된 열이 중복하는 데이터 값을 가지면 제약에 위반된다.
열을 기본키로 지정해 유일한 값을 가지도록 하는 구조가 바로 기본키 제약이다.
기본키 제약이 설정된 열에는 중복된 값을 저장할 수 없다
인덱스
인덱스는 테이블과는 별개로 독립된 데이터베이스 객체로 작성된다. 하지만 테이블에 의존하기 때문에, 테이블 삭제 시 같이 삭제된다.
데이터베이스의 인덱스에 쓰이는 대표적인 검색 알고리즘 '이진 트리'
(대량의 데이터 검색 시 이진 탐색이 빠르기 때문에)
+) 이진 트리에는 중복하는 값을 등록할 수 없다.
인덱스 작성/삭제
CREATE INDEX
DROP INDEX
CREATE INDEX 인덱스명 ON 테이즐명 (열명1, 열명2, ... )
DROP INDEX 인덱스명 ON 테이블명
EXPLAIN
인덱스 작성을 통해 쿼리의 성능 향상을 기대한다. 이때 실제로 인덱스를 사용해 검색하는지를 확인하려면 EXPLAIN 명령을 사용한다.
EXPLAIN SQL명령
최적화
내부 처리에서는 SELECT 명령을 실행하기에 앞서 실행계획을 세운다. EXPLAIN 명령은 이 실행계획을 확인하는 명령이다. 실행계획에서는 인덱스의 유무뿐만 아니라 인덱스를 사용할 것인지 여부에 대해서도 데이터베이스 내부의 최적화 처리를 통해 판단한다.
뷰
CREATE VIEW 뷰명 AS SELECT명령
DROP VIEW 뷰명
뷰를 작성하는 것으로 복잡한 SELECT 명령을 간략하게 표현할 수 있다.
뷰 작성/삭제
뷰는 데이터베이스 객체이기 때문에 DDL로 작성하거나 삭제한다. 작성할 때는 CREATE VIEW, 삭제할 때는 DROP VIEW를 사용한다.
CREATE VIEW 뷰명 AS SELECT 명령
DROP VIEW sample_view_67;
+) 뷰의 약점
-
처리 속도 저하 (데이터 양이 많은 경우, 뷰 중첩 사용)
+) 위의 약점을 해결하는 **머터리얼라이즈드 뷰(Materialized View)** 뷰를 테이블처럼 저장장치에 저장해두고 사용한다. -
상관 서브쿼리에서 언급한 것처럼, 부모 쿼리와 어떤 식으로든 연관된 서브쿼리의 경우 뷰의 SELECT 명령으로 사용할 수 없다
+) 함수 테이블을 사용하여 회피할 수 있다.
함수에는 인수를 지정할 수 있기 때문에 인수의 값에 따라 WHERE 조건을 붙여 결괏값을 바
꿀 수 있다. 그에 따라 상관 서브쿼리처럼 동작할 수 있다.
