SQL-INDEX

* 인덱스

-index와 table은 서로 다른 segment기 때문에 무분별하게 index를 지정하면 시스템에 굉장한 부담이 된다.

-index는 기준 데이터가 자동 정렬되어 테이블이 생성되며 ROWID가 일치하는 조건의 데이터를 찾은뒤 데이터를 출력한다.ROWID는 물리적인 위치정보를 나타내는 값이다.

- table은 기준 데이터가 자동정렬되지 않으며 데이터를 저장하며 모든 값을 비교한다.원하는 데이터를 찾더라도 무조건적으로 마지막 데이터까지 조회하며 비교하는데 조건값이 중복되는 경우가 있을 수 있기 때문이다.

 

인덱스 종류

고유 인덱스(Unique index) - 중복되면 안 되는 값을 사용. 
비 고유 인덱스(Non unique index) - 중복되도 무관한 값을 사용. 

 

인덱스 생성

1. 자동 생성
- PK, UK 가 설정된 컬럼에 자동 생성.
- 고유 인덱스(Unique index)가 생성.

2. 수동 생성
- CREATE INDEX 명령으로 생성.
- 비 고유 인덱스(Non unique index)가 생성.

 

인덱스 사례
- 전체 데이터의 10%~15% 정도의 데이터를 검색하는 경우
- where 절이나 조인에 사용되는 컬럼
- 데이터의 행이 매우 많은 경우

 

인덱스 생성과 삭제
CREATE INDEX 인덱스
ON 테이블 (컬럼 | 함수 | 수식);

DROP INDEX 인덱스;

// 사용자가 생성한 index만 삭제 가능. 고유 index는 pk  또는 uk의 조건을 삭제후 index삭제가 가능하다.

 

SELECT c.index_name, c.column_name, c.column_position, i.uniqueness
FROM user_indexes I, user_ind_columns c
WHERE c.index_name = i.index_name;

 

uniqueness에서는 index가 고유 인덱스인지 비고유 인덱스인지 확인이 가능하고 column position을 통해 인덱스의 순서를 확인할 수 있다. 만약 두 개의 컬럼을 PK로 사용할 경우 어떤 것을 우선 정렬할지는 시스템이 자체적으로 판단한다. 다만 FOREIGN KEY 또한 인덱스를 생성해줘야 하는데 sno같은 경우는 unique인덱스로 지정되어 있기때문에 유지하고 cno는 non unique index로 지정해준다.. 

 

SELECT index_name, column_expression
FROM user_ind_expressions;

 

인덱스가 컬럼, 함수, 수식중에 어떤 형식으로 지정되었는지를 확인할때 사용한다.

 

[예제 1] 다음 스크립트를 실행하고 인덱스를 조회.

 

@c:\sql\school2.sql

SELECT i.table_name, i.index_name, c.column_name, c.column_position, i.uniqueness
FROM user_indexes i, user_ind_columns c
WHERE c.index_name = i.index_name
AND i.table_name IN ('STUDENT', 'PROFESSOR', 'COURSE', 'SCORE')
ORDER BY i.table_name;

 

SELECT c.table_name, c.constraint_name, c.constraint_type, s.column_name
FROM user_constraints c, user_cons_columns s
WHERE c.constraint_name = s.constraint_name
AND c.table_name IN ('STUDENT', 'PROFESSOR', 'COURSE', 'SCORE')
ORDER BY c.table_name, c.constraint_name;

 

[예제 2]테이블의 제약 조건과 관련된 필요한 인덱스를 추가.(FK 인덱스가 없기때문에 FK 인덱스를 추가)

CREATE INDEX course_pno_fk
ON course (pno);

 

CREATE INDEX score_cno_fk
ON score (cno);

SELECT i.table_name, i.index_name, c.column_name, c.column_position, i.uniqueness
FROM user_indexes i, user_ind_columns c
WHERE c.index_name = i.index_name
AND i.index_name LIKE '%FK'
ORDER BY c.table_name;

 

[예제 3]다양한 인덱스를 생성하고 조회.
CREATE INDEX student_sname_indx
ON student (sname); // 기본 인덱스 생성

CREATE INDEX student_major_sname_indx
ON student (major, sname); // 복합 인덱스 생성

CREATE INDEX student_coavr_indx
ON student (avr*4.5/4.0); // 함수 기반 인덱스 생성

SELECT c.index_name, c.column_name, c.column_position, i.uniqueness
FROM user_indexes I, user_ind_columns c
WHERE c.index_name = i.index_name

AND c.table_name = 'STUDENT'
ORDER BY c.index_name, c.column_position; // 함수 기반 인덱스는 커럼명이 표시되지 않는다.

SELECT index_name, column_expression
FROM user_ind_expressions
WHERE index_name = 'STUDENT_COAVR_INDX'; // 함수 기반 인덱스의 컬럼의 형식을 보고 싶을때 사용

 

[예제 4]Student 테이블의 인덱스를 삭제한다.

SELECT c.index_name, c.column_name, c.column_position, i.uniqueness
FROM user_indexes I, user_ind_columns c
WHERE c.index_name = i.index_name
AND c.table_name = 'STUDENT';

DROP INDEX student_major_sname_indx;

DROP INDEX student_sno_pk; // 오류

SELECT c.index_name, c.column_name, c.column_position, i.uniqueness

FROM user_indexes I, user_ind_columns c
WHERE c.index_name = i.index_name
AND c.table_name = 'STUDENT';

 

 

[실습] 
1. 다음 표를 보고 테이블을 생성하는 스크립트를 작성한다.
- 제약 조건은 반드시 ALTER 문으로 설정한다.
- 필요한 부분에 사용자 정의 인덱스를 추가한다.
- 판매 전표에 대한 검색은 대부분 판매일자로 수행하므로 이를 위한 인덱스를 추가한다.

SELECT i.table_name, i.index_name, c.column_name, c.column_position, i.uniqueness 
FROM user_indexes i, user_ind_columns c 
WHERE c.index_name = i.index_name 
AND i.table_name IN ('PRODUCT', 'SLIP', 'DETAILSLIP') 
ORDER BY i.table_name;

을 사용하여 인덱스를 조회.

 

CREATE INDEX detailslip_pno_fk

ON detailslip(pno);

-> detailslip의 FK 2개중에 컬럼순위가 2인 pno만 사용자 정의 인덱스를 생성한다.

 

CREATE INDEX slip_sdate_indx

ON slip(sdate);

-> 판매일자 인덱스 생성

추가된 index 확인

 

2. 다음 테이블에 대한 상세 도표를 보고 테이블을 생성을 위한 스크립트를 생성한다.
- 제약 조건은 반드시 ALTER 문으로 설정한다.

- 필요한 인덱스를 추가한다.
- Emp 테이블에 대해 연봉에 대한 검색이 많이 수행됩니다. 이를 위한 인덱스를 추가한다.

SELECT i.table_name, i.index_name, c.column_name, c.column_position, i.uniqueness 
FROM user_indexes i, user_ind_columns c 
WHERE c.index_name = i.index_name 
AND i.table_name IN ('DEPT', 'EMP') 
ORDER BY i.table_name;

을 사용하여 인덱스를 조회.

 

CREATE INDEX dept_director_fk

ON dept(director); // FK의 index 추가

 

CREATE INDEX emp_mgr_fk

ON emp(mgr); // FK의 index 추가

 

CREATE INDEX emp_dno_fk

ON emp(dno); // FK의 index 추가

 

CREATE INDEX emp_ysal_indx

ON emp(sal*12+comm); // 연봉 검색에 대한 index추가

추가된 index 확인

 

* dictionary

user_tables : 테이블의 정보를 조회해 줌

user_constraints : 유저가 소유한 제약조건을 모두 조회

user_cons_columns : 컬럼에 할당된 제약조건을 조회

dba_tablespaces : 테이블의 스페이스를 확인할때 사용.

dba_users : DB에 있는 모든 사용자 목록을 조회

user_tab_columns  : 테이블, 뷰, 클러스터의 칼럼과 관련된 정보 조회

user_indexes : 테이블이 가지고 잇는 인덱스 목록 조회

user_ind_columns : 인덱스의 컬럼 조회

user_ind_experession : 함수 기반 인덱스의 컬럼 정보 확인