인덱스(Index) 생성, 인덱스 구조와 이해
인덱스 = 색인
• 인덱스종류
‐ 고유
고유인덱스(Unique Index), 비 고유 인덱스( Non unique Index)
‐ 물리구조
B-Tree Index, bitmap Index
• 인덱스생성
‐ 자동생성
. PK, UK로 지정된컬럼은자동으로생성된다.
. Unique Index가 생성된다.
‐ 수동생성
. CREATE INDEX 명령을 통해 직접 생성한다.
. Non unique Index 가 생성된다.
. FK 컬럼에반드시생 성한다.
데이터가 너무 많으면 사용 안 한다.
Index 생성과 삭제
CREATE INDEX 인덱스
ON 테이블 (컬럼 | 함수 | 수식);
DROP INDEX 인덱스;
- 인덱스생성조건
‐ 전체데이터의 10%~15% 정도의 데이터를 검색하는 경우
． 검색데이터가 한 번에 확인이 가능한 정도라면의미가 있다.
‐ WHERE 절이나 조인에 사용되는 컬럼
‐ 데이터의 행이 매우 많은 경우
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;
SELECT index_name, column_expression
FROM user_ind_expressions;
‐ mn_position : 여러 컬럼으로 인덱스가 생성된 경우 컬럼의순서
‐ Column_expressions : 수식에 의해 만들어진 인덱스의 해당 수식
제약조건을 추가하고 Index 확인
‐ school_con.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;
‐ PK(UK)에 의해서 자동으로 생성된 인덱스의 이름은 PK(UK)와 동일하다.
‐ SCORE 테이블의 PK는 SNO, CNO컬럼으로 구성되어 컬럼의 순서가 매우 중요하다.
인덱스생성
• STUDENT 테이블에 다양한 인덱스를 생성하고 조회
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);
CREATE INDEX pr_ord_indx
ON professor (SUBSTR(orders,1,1));
카디날리티가 낮은 놈이 앞으로 높은 놈은 뒤로 만든다.
‐ 여러 컬럼으로 인덱스가 만들어질 때 구조는 해당컬럼으로 정렬하는 경우 와동일하다.
‐ 인덱스는 컬럼뿐 아니라 수식으로도 생성가능하다.
==================
alter table score add constraint score_pk primary key (sno,cno);
create index score_cno_fk on score(cno);
pk는 만들어질 때 자동으로 index가 만들어지는데
이미 sno로 index가 만들어지기 때문에
cno만 만들면 된다.
====================
테이블을 다 만들고 인덱스 만들어준다.
인덱스확인
• STUDENT 테이블의 인덱스를 확인
SELECT c.index_name, c.column_name, c.column_position
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';
수식을 이용한 인덱스의 경우 COLIMN_NAME의 값이 일련번호로 검색된다.
‐ 수식은 USER_IND_EXPRESSIONS에서 검색한다.
인덱스삭제
DROP INDEX student_major_sname_indx;
DROP INDEX student_sno_pk;
ㄴ pk를 디스에이블, 드롭하면 자동으로 지워진다.
view
- 뷰의 종류
‐ 단순뷰(Simple view)
． DML문장수행가능
‐ 복합뷰(Complex view)
． 다중테이블이나 수식 등이 포함된 뷰
view는 테이블처럼 똑같이 사용가능
세그먼트가 아님.
아이콘 같은 느낌이다.
view는 보통 select만 한다.
반드시 서브쿼리를 사용해서 만든다.
view 생성
CREATE [OR REPLACE][FORCE | NOFORCE] VIEW 뷰 (컬럼 ... )
AS (SELECT 문장 : sub query)
[WITH CHECK OPTION [CONSTRAINT constrant_name]]
[WITH READ ONLY [CONSTRAINT constrant_name]];
‐ OR REPLACE
: ALTER VIEW를 대체한다.
: SEGMENT가 아님
‐ FORCE
: 기반 테이블 없이 쀼를 생성한다.
‐ WITH CHECK OPTION
: 뷰에 의해서 검색가능한 행만 DML 작업이 가능하도록 제약한다.
‐ WITH READ ONLY
: 뷰를 통해서는 SELECT만 가능하도록 제한한다.
생성 예)
create view sno_sname
as select sno,sname from student;
ㄴ 보일 때 sno, sname을 가진 테이블로 보인다.
view는 테이블에 액세스하는 소프트링크? 같은 거
ㄴ 복잡한 sql문을 쉽게 액세스할 수 있게 만들어주는 거.
view 삭제와 확인
DROP VIEW 뷰;
SELECT view_name, text FROM user_views; → view이름 보기
‐ text : 뷰 생성 서브쿼리
: LONG 타입으로 설정이 필요하다. → 크기를 조절해서 봐야 함.
view 생성 주의사항
‐ 뷰를 생성할 수 있는 'CREATE VIEW' 권한이 보통은 RESOURCE, CONNECT 롤에 포함되어 있지 않음으로 사용자에게 반드시 해당 권한을 할당해야 한다.
‐ 사용자에게 직접 할당하거나 할당된 RESOURCE, CONNECT 롤에 할당한다.
view 생성
일반화학과목의학과 별기말고사평균을 뷰로생성
CREATE VIEW ma_result (과목번호, 과목명, 학과, 기말고사평균)
AS SELECT c.cno, cname, major, ROUND(AVG(result))
FROM student s, course c, score r
WHERE s.sno=r.sno AND r.cno=c.cno
AND cname='일반화학' GROUP BY c.cno, cname, major;
SET LONG 1000 ← 1000 byte까지 출력
SELECT view_name, text FROM user_views;
DESC ma_result;
SELECT * FROM ma_result;
WITH CHECK 옵션
CREATE VIEW st_ch
AS SELECT sno, sname, syear, avr
FROM student
WHERE syear = 1;
INSERT INTO st_ch VALUES ('000001', '시현', 2, 4.0);
SELECT * FROM student WHERE sname = '시현';
SELECT * FROM st_ch WHERE sname = '시현'; ← 검색되지 않는다.
• 뷰에 보이지않는행이뷰를 통해 입력될 수 있다.
인라인뷰(Inline View)
SELECT ...
FROM (SELECT 문장 : sub query) [별명]
‐ 인라인뷰는 FROM절에 쓰인 서브쿼리다.
‐ 인라인뷰는 문장이 실행되는 동안만 존재하는 뷰이다.
‐ 인라인뷰는 일반뷰와 동일하게 조인이나 검색에 이용된다.
인라인뷰사용
• 각 부서별 최소급여자를 검색
SELECT eno, ename, d.dno, sal, msal
FROM emp e,
(SELECT dno, MIN(sal) msal FROM emp
GROUP BY dno) d
WHERE e.dno = d.dno
AND sal = msal;