소스: sql-src/mid_09_index/01_create_index.sql. dept·emp 테이블을 만들며 CREATE INDEX · CREATE UNIQUE INDEX · DROP INDEX 의 기본 동작과 인덱스 목록 조회 방법을 확인합니다.
CREATE TABLE dept (
code VARCHAR(10)
, dname VARCHAR(20)
, loc VARCHAR(20)
, CONSTRAINT pk_dept PRIMARY KEY (code)
);
CREATE TABLE emp (
id INT
, name VARCHAR(20)
, dept VARCHAR(10)
, mgr INT
, sal INT
, hired DATE
, email VARCHAR(30)
, CONSTRAINT pk_emp PRIMARY KEY (id)
, CONSTRAINT uq_emp_email UNIQUE (email)
);
INSERT INTO dept VALUES
('개발', '개발팀', '서울'),
('영업', '영업팀', '부산');
INSERT INTO emp VALUES
(1, '김철수', '개발', NULL, 500, DATE '2022-03-01', '[email protected]'),
(2, '이영희', '영업', 1, 450, DATE '2023-01-10', '[email protected]'),
(3, '박민수', '개발', 1, 480, DATE '2021-05-20', NULL);emp 는 pk_emp 와 uq_emp_email 두 제약을 함께 선언했습니다. 아직 CREATE INDEX 를 한 번도 쓰지 않았다는 점을 다음 예제에서 확인합니다.
SELECT index_name, table_name, index_type_name
FROM information_schema.indexes
WHERE table_name = 'EMP'
ORDER BY index_name;INDEX_NAME | TABLE_NAME | INDEX_TYPE_NAME
---------------------+------------+----------------
PRIMARY_KEY_10 | EMP | PRIMARY KEY
UQ_EMP_EMAIL_INDEX_1 | EMP | UNIQUE INDEX
(2행)CREATE INDEX 를 한 번도 쓰지 않았는데 인덱스가 이미 2개 있습니다. pk_emp 가 PRIMARY_KEY_10을, 이름 붙인 UNIQUE 제약 uq_emp_email 이 UQ_EMP_EMAIL_INDEX_1을 자동으로 만든 것입니다. 인덱스 이름 끝의 번호는 H2 가 내부적으로 붙이는 일련번호라 값 자체는 신경 쓰지 않아도 됩니다.
Oracle 의 인덱스 목록은 USER_INDEXES·USER_IND_COLUMNS, MySQL 은 SHOW INDEX FROM t, MSSQL 은 sys.indexes 또는 EXEC sp_helpindex t 로 조회합니다(문법 검토만).
CREATE INDEX ix_emp_dept ON emp (dept);
CREATE UNIQUE INDEX ux_emp_dept_id ON emp (dept, id);
SELECT
i.index_name
, i.index_type_name
, c.column_name
, c.ordinal_position
FROM information_schema.indexes i
JOIN information_schema.index_columns c
ON c.index_name = i.index_name
AND c.table_name = i.table_name
WHERE i.table_name = 'EMP'
ORDER BY i.index_name, c.ordinal_position;INDEX_NAME | INDEX_TYPE_NAME | COLUMN_NAME | ORDINAL_POSITION
---------------------+-----------------+-------------+-----------------
IX_EMP_DEPT | INDEX | DEPT | 1
PRIMARY_KEY_10 | PRIMARY KEY | ID | 1
UQ_EMP_EMAIL_INDEX_1 | UNIQUE INDEX | EMAIL | 1
UX_EMP_DEPT_ID | UNIQUE INDEX | DEPT | 1
UX_EMP_DEPT_ID | UNIQUE INDEX | ID | 2
(5행)ux_emp_dept_id 는 dept·id 두 컬럼을 묶은 복합 인덱스라, ORDINAL_POSITION이 1·2로 나뉘어 두 행으로 나옵니다. INFORMATION_SCHEMA.INDEXES는 인덱스 이름과 종류를, INDEX_COLUMNS는 인덱스에 속한 컬럼을 따로 담고 있어 두 뷰를 조인해야 전체 그림이 보입니다.
DROP INDEX ix_emp_dept;
SELECT index_name FROM information_schema.indexes WHERE table_name = 'EMP' ORDER BY index_name;INDEX_NAME
--------------------
PRIMARY_KEY_10
UQ_EMP_EMAIL_INDEX_1
UX_EMP_DEPT_ID
(3행)ix_emp_dept 를 지우자 목록에서 사라져 3개만 남았습니다. PK·UNIQUE 제약이 만든 인덱스는 DROP INDEX 가 아니라 그 제약 자체를 지워야 함께 없어집니다.
CREATE INDEX IF NOT EXISTS ix_emp_sal ON emp (sal);
CREATE INDEX IF NOT EXISTS ix_emp_sal ON emp (sal);
DROP INDEX IF EXISTS ix_emp_sal;
DROP INDEX IF EXISTS ix_emp_sal;
SELECT index_name FROM information_schema.indexes WHERE table_name = 'EMP' ORDER BY index_name;INDEX_NAME
--------------------
PRIMARY_KEY_10
UQ_EMP_EMAIL_INDEX_1
UX_EMP_DEPT_ID
(3행)같은 CREATE INDEX IF NOT EXISTS·DROP INDEX IF EXISTS 문을 두 번씩 실행해도 오류 없이 넘어갑니다. 배포 스크립트를 여러 번 돌려도 안전하게 만들고 싶을 때 이 옵션을 씁니다.